Is there any condition?

Question

The following two queries give 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 assumed that the condition performs the same operation for both queries, only at different times. Did I miss something?

Answer 1

They must be and must be different.

Consider the case where you have a with b, which matches the seller id and agend, and b.default is not null.

In the first case, you will find a, and then find b that match the criteria, because there is no b that matches the id id and defaults to null. But since this is a left join, you still get a record with the data "a" in the output.

In the second case, you find a and find a match b. But since b does not execute the WHERE clause, the record is excluded from the output.

If you are doing a full join, including the condition in ON and WHERE will not change the result. But on the left or right connection, he changes the result as I tried to describe above.

Answer 2

The first shows you only matching entries from B that are also NULL for default .

The second option will show you all the entries from B , since the filter is applied after executing the OUTER JOIN . Since unique entries are displayed with NULL values, this matches all entries.

However, you should also know that when filtering in the JOIN ed table in the WHERE you force INNER JOIN , so you actually have two problems working here.

Answer 3

Not really, but in this case, yes.

The WHERE clause filters the result set, while the ON clause stops the rows from concatenating in the first place. The result set can be large, doing a great job of processing a large set of results. If you joined other tables, that would make a big difference, because stopping early rows later could cut a huge number of rows later.

Most modern parsers still optimize the query, so the performance effect should be the same anyway.

Finally, since the WHERE filters are blocked, the left join will contain at least one line with a condition in the ON clause, but throws it with a condition in the WHERE clause.