Today's Question:  What does your personal desk look like?        GIVE A SHOUT

LEFT OUTER JOIN with ON condition or WHERE condition?

  Rudy Limeback        2011-11-01 02:04:29       2,683        0    

The difference is subtle, but it is a big difference. The ON condition stipulates which rows will be returned in the join, while the WHERE condition acts as a filter on the rows that actually were returned.

Simple example: Consider a student table, consisting of one row per student, with student id and student name. In a second table, a list of grades that students have received, with student_id, subject, and grade. Give me a list of all students, and show their grade in Math. This requires a LEFT OUTER JOIN, because you want all students, and you know that some of them didn't take Math. Here are two queries:

select name , grades.grade as math_grade from students left outer join grades on students.id = grades.student_id where grades.subject = 'Math'
select name , grades.grade as math_grade from students left outer join grades on students.id = grades.student_id and grades.subject = 'Math'

Now for the crucial difference: the first query returns only those students who took Math, and those who didn't are not included.

Source:http://searchoracle.techtarget.com/answer/LEFT-OUTER-JOIN-with-ON-condition-or-WHERE-condition

SQL  OUTER JO0IN  ON  WHERE  ON VS WHERE  WHERE VS ON 

Share on Facebook  Share on Twitter  Share on Weibo  Share on Reddit 

  RELATED


  0 COMMENT


No comment for this article.