1

I want to know the difference of the behavior of JOIN in below two queries.

Let's say for example I have a query

SELECT * 
FROM   table1 t1 
       LEFT JOIN table2 t2 
              ON t1.column1 = t2.column2 
                 AND t2.something = 'this thing' 
WHERE some other conditions


SELECT * 
FROM   table1 t1 
       LEFT JOIN table2 t2 
              ON t1.column1 = t2.column2 
WHERE some other conditions AND t2.something = 'this thing'

I am having trouble picturizing what difference its going to make by removing condition from join statement to where clause.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mandar
  • 498
  • 1
  • 4
  • 17
  • 3
    The second one will provide results similar to an INNER JOIN. The first applies the `t2.something` filter when the join takes place. – Taryn May 10 '13 at 18:26
  • Different systems may behave differently regarding that. Please edit your question to include tags for SQL Server, Oracle, MySql, PostGRE etc. if your question is specific to one of those. Otherwise just ignore this comment. – Geeky Guy May 10 '13 at 18:26

1 Answers1

1

In your first query ONLY records in t2 where t2.something = 'this thing' will be joined to T1, but any t1 records that to not have matching t2 records will be included.

In the second query ALL records in t2 will be joined to t1 but only records where t2.something = 'this thing' will be included in the final result.

Adding the condition t2.something = 'this thing' to the WHERE clause effectively removes all results that do not have a match in t2 (since in that case t2.something would be NULL). So logically it would be the same as an INNER JOIN.

D Stanley
  • 149,601
  • 11
  • 178
  • 240