Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Inner Join statement Vs AND operator

    Why should any body use the INNER JOIN SQL sysntax when the same results can be acheived by using an AND operator?

    e.g.

    Code:
    SELECT JS.JobseekerID
             , JS.FirstName
             , JS.LastName
             , JS.Currency
             , JS.AccountRegDate
             , JS.LastUpdated
             , JS.NoticePeriod
             , JS.Availability
             , C.CountryName
             , S.SalaryAmount
             , DD.DisciplineName
             , DT.DegreeLevel 
        FROM Jobseekers JS 
    INNER 
       JOIN Countries C 
          ON JS.CountryID = C.CountryID 
    INNER 
       JOIN SalaryBracket S 
          ON JS.MinSalaryID = S.SalaryID 
    INNER 
      JOIN DegreeDisciplines DD 
         ON JS.DegreeDisciplineID = DD.DisciplineID 
    INNER 
      JOIN DegreeType DT 
         ON JS.DegreeTypeID = DT.DegreeTypeID 
    WHERE
      JS.ShowCV = 'Yes'
    The same results can be acheived by:

    Code:
    SELECT JS.JobseekerID
             , JS.FirstName
             , JS.LastName
             , JS.Currency
             , JS.AccountRegDate
             , JS.LastUpdated
             , JS.NoticePeriod
             , JS.Availability
             , C.CountryName
             , S.SalaryAmount
             , DD.DisciplineName
             , DT.DegreeLevel 
        FROM Jobseekers JS, Countries C, SalaryBracket S, DegreeDisciplines DD
             , DegreeType DT
        WHERE
               JS.CountryID = C.CountryID 
               AND JS.MinSalaryID = S.SalaryID 
               AND JS.DegreeDisciplineID = DD.DisciplineID 
               AND JS.DegreeTypeID = DT.DegreeTypeID 
               AND  JS.ShowCV = 'Yes'
    Lets see you database gurus answer this one!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    JOIN syntax is better self-documentation, easier to understand, is standard sql, and is recommended by all database vendors (the table list syntax is "deprecated")

    that's why

    okay, now i've answered your question, now you answer one of mine

    what would your "AND" query look like to retrieve all rows of TableA, with related rows of TableB, if any

    hint: it's a left outer join, but you aren't to use JOIN syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    r937 already explained it, but I'd like to add a few things.

    AND is, of course, not the same as INNER JOIN. So what we are talking about here is the explicit join notation using the [INNER] JOIN keyword, and the implicit (inner) join notation with commas only. Basically, the difference is just syntactic sugar.

    Using INNER JOIN in the FROM clause requires an explicit join predicate. That join predicate won't litter the WHERE clause. You see which tables are joined on which conditions. The WHERE clause just filters the rows from the joined table.

    One advantage of the explicit join syntax could be (depending on the DBMS) that the DBMS can come up with a better execution plan because it can derive filter predicates for joined tables directly from the join itself - and doesn't have to figure things out on a more complicated route. But that's not an issue for any serious DBMS these days anyway...

    p.s: Using an implicit join (with the commas) is also in the SQL:2003 standard.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by ozzii
    Why should any body use the INNER JOIN SQL sysntax when the same results can be acheived by using an AND operator?

    ...

    Lets see you database gurus answer this one!
    I'll take off my database hat and put on the language design hat.

    What do you think "WHERE" means? Based on its usage in plain English, it suggests that you are looking for something, filtering things or constraining them.

    The INNER JOIN syntax is something SQL does right. It separates two distinct actions or concepts: that of joining from that of filtering.

    Incidentally, there's no such thing as an "implicit join." Putting multiple tables in the FROM clause is a cartesian product. Restricting them by
    equivalent attributes simply gives you a restricted cartesian product, which is equivalent to an inner join. Your DBMS isn't adding any hidden or implicit joins to your expression.

    In fact of matter, the cartesian product, natural join and set intersection are all the same operator.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, there are natural outer joins too

    "natural" refers to how you designate which columns to join on, but the join itself can be inner or outer

    i'm still waiting for ozzii to answer my question

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by sco08y
    In fact of matter, the cartesian product, natural join and set intersection are all the same operator.
    You care to explain that?

    The cartesian product of two tables A and B is:

    R = A x B

    The set intersection of A and B is:

    R = A /\ B

    If you look at the schemata, you have a different behavior for cartesian products, natural joins, and set intersections. So I have a hard time to understand how those operators could possibly be the same. I accept that cartesian product and natural joins are both join operators. But I fail to see how set intersection could align with them.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by stolze
    You care to explain that?

    The cartesian product of two tables A and B is:

    R = A x B

    The set intersection of A and B is:

    R = A /\ B

    If you look at the schemata, you have a different behavior for cartesian products, natural joins, and set intersections. So I have a hard time to understand how those operators could possibly be the same. I accept that cartesian product and natural joins are both join operators. But I fail to see how set intersection could align with them.
    This is all basically my way of explaining some material from Date & Darwen's "Foundations for Database Systems", but here's how it goes:

    All three operators can take from 0 to an infinite number of arguments. All three operators have the same identity value, that is, calling them with 0 arguments will give the same value. (Exactly what that is is left as an exercise to the reader. It's also listed in Foundations.)

    Start with the algorithm for the cartesian product. By definition, the cartesian product requires that the headers for each term be disjoint, that is, no two terms can share an attribute (column) name.

    The algorithm is dead simple: iterate through every combination of tuples (rows) from each of the arguments.

    So if x has (foo, bar) as attributes and 10 tuples, y has (blah, blase) as attributes and 50 tuples you get a result with (foo, bar, blah, blase) and 500 tuples.

    Now, let's relax that restriction. We'll say that *some* of the attributes can be shared between arguments. This is the requirement for the natural join.

    Logically, a value ought to be one thing or another, not two things at once., especially in the relational world where each tuple is a proposition with a truth value. So if you try to claim two different things to be true at the same time you have a contradiction, which is false.

    So let's look at a typical join:

    Code:
        (foo, bar)      (bar, qux)
    x =   1    2    y =   2    3
          3    4          3    4
    If I simply do a cartesian product of these, I get:

    Code:
            (foo, bar, bar, qux)
    x X y =   1    2    2    3
              1    2    3    4
              3    4    2    3
              3    4    3    4
    The first tuple is perfectly legitimate, we're simply showing the "bar" attribute twice. All the others have contradictory values so they are simply discarded, and you get a natural join.

    From there it's a no brainer to realize that if all the attributes are shared that you have set-intersection. Set-intersection is simply a join over all attributes, and any tuples that aren't present in all terms are discarded.

    So these three operators are simply specific cases of one general operator. Date and Darwen call this "conjoin." The conjoin operator can be invoked with 0 arguments and is fully commutative and associative and, as I mentioned earlier, has an identity property.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or you could just use the MS Access Query Wizard.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm still waiting for ozzii to answer my question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by sco08y
    Now, let's relax that restriction. We'll say that *some* of the attributes can be shared between arguments. This is the requirement for the natural join.
    No, not really a requirement. You can have a natural join w/o common attributes, too.

    However, we have to distinguish between relational model and SQL here. You are talking about relational only. There, your argumentation holds true and I agree with it. But in SQL, you can have your result table for a cross join (repeated below) and that's perfectly fine. Relational algebra doesn't allow that; you'd have to rename attributes, of course.
    Code:
            (foo, bar, bar, qux)
    x X y =   1    2    2    3
              1    2    3    4
              3    4    2    3
              3    4    3    4
    SQL treats both BAR columns separately by using qualifiers. You have actually (x.foo, x.bar, y.bar, y.qux).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    JOIN syntax is better self-documentation, easier to understand, is standard sql, and is recommended by all database vendors (the table list syntax is "deprecated")

    that's why

    okay, now i've answered your question, now you answer one of mine

    what would your "AND" query look like to retrieve all rows of TableA, with related rows of TableB, if any

    hint: it's a left outer join, but you aren't to use JOIN syntax
    Dont know what you are trying to get at - you'll need to elaborate a bit more on this for me. A left outer join will return all the rows that an inner join returns plus one row for each of the other rows in the first table that do not have a match.

    Where as an AND query will simply do an inner join i.e. only retrieve rows where there is a match.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii
    Dont know what you are trying to get at - you'll need to elaborate a bit more on this for me.
    you asked why anyone would use INNER JOIN syntax when "the same results can be acheived by using an AND operator," and i gave you some reasons

    and then i asked you to write a left outer join, but you weren't to use JOIN syntax, you were to "use an AND operator"

    i don't see what's so hard to understand about my question

    you do appear to understand what a left outer join is, so may we see you write one with your "AND" operator?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2004
    Posts
    480
    I think ozzii's original point was INNER JOIN vs AND, not the fact that you shouldn't use OUTER joins in place of ANDs.

    I would say the reason to avoid the comma syntax, apart from it being depricated as Rudy says, is that it is quite easy to leave out an AND condition and end up with improper results from a cross join, as well as once you get into the comma syntax you try to stay in that frame of mind when mixing it with OUTER JOINS. With mysql 5 we see the errors cropping up in that all the time when people are still using the comma syntax with outer joins they used to write under mysql 4.

    just my two cents as to why i've switched away from comma syntax. SILLY manual!

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Rudy's point was that AND and INNER JOIN have nothing to do with each other whatsoever. Outer joins are a way to highlight that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thank you stolze
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •