Does the placement of a condition matter?

Question

The 2 following queries are giving me different results:

SELECT A.source_code, B.quantity
FROM Table_A AS A
LEFT JOIN Table_B AS B ON B.merchant_id = A.merchant_id
   AND B.agent_id = A.agent_id
   AND B.default IS NULL
WHERE A.month='2011-10-01'
   AND B.type='600'

And

SELECT A.source_code, B.quantity
FROM Table_A AS A
LEFT JOIN Table_B AS B ON B.merchant_id = A.merchant_id
   AND B.agent_id = A.agent_id
WHERE A.month='2011-10-01'
   AND B.type='600'
   AND B.default IS NULL

I had assumed that the condition does the same operation on both queries, only at different times. Am I missing something?

Answer 1

They are and should be different.

Consider the case where you have an a with a b that matches on merchant and agend id's, and b.default is not null.

In the first case, you will find the a, and then find no b's that meet the criteria, because there is no b that matches on the id's AND has default is null. But because it's a left join, you still get a record with the "a" data in the output.

In the second case, you will find the a and find a matching b. But because b fails the WHERE clause, the record is excluded from the output.

If you are doing a full join, putting a condition in an ON versus a WHERE will not change the output. But on a left or right join, it DOES change the output in the way I tried to describe above.

Answer 2

The first will show you only matching records from B that also have a value of NULL for default.

The second variant will show you all the records from B, since the filter is being applied after the OUTER JOIN is executed. Since unmatched records show up with NULL values, this matches all records.

However, you should also be aware that when you filter on the JOINed table in the WHERE clause you are forcing an INNER JOIN, so you actually have two issues at work here.