#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,118
    Rep Power
    537

    Putting condition in ON clause or WHERE clause


    I came across the following query:
    Code:
    SELECT u.Asset_Name
    FROM assets u
    INNER JOIN  Users_AssetsAvailable ua ON  ua.assetID = u.assetID AND ua.User_ID = <number>
    WHERE u.AllowByDefault = 0
    Absolutely nothing wrong with it, but I found it interesting that it was different that what I typically do. I would have done the following:
    Code:
    SELECT u.Asset_Name
    FROM assets u
    INNER JOIN  Users_AssetsAvailable ua ON  ua.assetID = u.assetID
    WHERE u.AllowByDefault = 0  AND ua.User_ID = <number>
    What are the pros and cons of both approaches. Thank you
  2. #2
  3. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,142
    Rep Power
    1319
    No difference as that is an INNER JOIN. It would make a difference in a LEFT OUTER JOIN.

    You use an outer join when you want to return all rows in the one table (in this case the left, or the first of the two mentioned) and any matching rows plus nulls from the second table.

    For instance if you had a list of students and some of them have handed in an assignment and some have not.

    You want a list of ALL students from your student table AND either their marks OR the fact that they haven't handed in the assignment.

    Code:
    SELECT
      student.id,
      student.name,
      grades.studentid,
      grades.mark
    FROM
      student
    LEFT OUTER JOIN
      grades
    ON
      student.id = grades.studentid
    so that will return the students plus either a grade or a null value if they have not turned in an assignment. Clear so far?

    Now let's suppose we also have different courses in the grades table so now it looks like:
    studentid, course, mark

    and you only want to see the marks the students got in math:

    Code:
    SELECT
      student.id,
      student.name,
      grades.studentid,
      grades.mark
    FROM
      student
    LEFT OUTER JOIN
      grades
    ON
      student.id = grades.studentid
    AND
      grades.course='math'
    That would properly return ALL students plus their grade in math OR a NULL value if they haven't received a grade.

    If you change that clause on the right hand table by moving it from the ON clause to a WHERE clause something else happens:

    Code:
    SELECT
      student.id,
      student.name,
      grades.studentid,
      grades.mark
    FROM
      student
    LEFT OUTER JOIN
      grades
    ON
      student.id = grades.studentid
    WHERE
      grades.course='math'
    Now the join returns all students from the LEFT hand table and then applies the WHERE condition AFTER the JOIN.

    So then only math marks are returned and any student who HAS NOT taken the math test will be dropped from the results.

    Now that is pretty straightforward but as the join is more complex it is easier to mistakenly put something in the WHERE clause that belongs in the JOIN.

    The thing to remember is conditions in a LEFT JOIN that are on the second or right hand table belong in the ON clause.

    Comments on this post

    • NotionCommotion agrees : Nice explaination. Thank you
    • r937 agrees : very nicely explained, great tutorial

IMN logo majestic logo threadwatch logo seochat tools logo