I'm refactoring some sql at work, and stumbled on something I'm not sure how to explain. There are two queries I thought would result in the same result, but don't, and I'm not sure why.
The queries are as follows:
select *
from TableA as a
left join TableB b on a.id = b.id and b.status in (10, 100)
select *
from TableA as a
left join TableB b on a.id = b.id
where b.status is null or b.status in (10, 100)
When will these not return the same result?
The big difference with the Where condition b.status is null or b.status in (10, 100)
is when b.status is say 1 as well as b.id=a.id
In the first query you will still get the row from table A with corresponding B part as NULL as On condition is not fully satisfied. In the second query you will get the row in the JOIN for both a and b tables which will be lost in the where clause.
Let me show an example:
SELECT * INTO #A FROM (VALUES
(1),(2),(3),(4)) T(id)
SELECT * INTO #B FROM (VALUES
(1,NULL),
(2,1),
(3,10)) T(id,status)
select *
from #A as a
left join #B b on a.id = b.id and b.status in (10, 100)
select *
from #A as a
left join #B b on a.id = b.id
where b.status is null or b.status in (10, 100)
Result
id id status
----------- ----------- -----------
1 NULL NULL
2 NULL NULL
3 3 10
4 NULL NULL
id id status
----------- ----------- -----------
1 1 NULL
3 3 10
4 NULL NULL
Final response:
Logically, speaking, your second query is almost the same as:
SELECT *
FROM TableA as a
LEFT JOIN TableB b
ON a.id = b.id
WHERE b.status IN (10, 100); -- b.status is null has been removed
So then the problem comes down to the standard problem of filtering in the ON clause versus filtering in the WHERE clause. In the former case, all records from the left side of the join will be retained, even if the ON logic should fail. In the latter case, which is the case of your second query, matching records which fail the status condition would be removed, and not show up in the result set.
I said almost the same, because the b.status IS NULL check you had would in fact allow records to survive which did match in the join condition, but which happened to have a null value for the status. But, other than this, your question is really just one of filtering in the ON clause versus doing it in the WHERE clause.
select * from TableA as a left join TableB b on a.id = b.id and b.status in (10, 100)
The conditional statement AND is evaluated before the join occurs.
select * from TableA as a left join TableB b on a.id = b.id
where b.status is null or b.status in (10, 100)
The filter happens after the tables are joined.
SO this is the reason you will get different output.
Since it is a LEFT JOIN, a non-matching ON condition will generate a row where all columns from right table contain NULL.
On the other hand, a non-matching WHERE clause will eliminate the row completely regardless of join type. Consider this example:
CREATE TABLE #TableA(id INT);
INSERT INTO #TableA VALUES
(1),
(2);
CREATE TABLE #TableB(id INT, status INT);
INSERT INTO #TableB VALUES
(1, 10),
(2, -1);
SELECT *
FROM #TableA AS A
LEFT JOIN #TableB B ON A.id = B.id AND B.status IN (10)
/*
a.id | b.id | status
1 | 1 | 10
2 | NULL | NULL
*/
SELECT *
FROM #TableA AS A
LEFT JOIN #TableB B ON A.id = B.id
-- WHERE B.status IS NULL OR B.status IN (10)
/*
a.id | b.id | status
1 | 1 | 10
2 | 2 | -1
*/
Notice that I have commented out the where clause in the second query (the results are different already). Once added, it will eliminate the second row as well.
In a normal scenario, A LEFT JOIN or LEFT OUTER JOIN gives all the rows from the left table with matched rows from both tables. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
When we add a where clause with a left outer join, it behaves like an inner join, where the filter is applied after the ON clause, showing only those rows having
B.status is null or 10 or 100
