Rows are eliminated from LEFT JOIN when using OR in WHERE clause

user_10842320 0
Viewed 533

I've run into a problem with an SQL script that I can't explain with my understanding of LEFT JOINs. I've actually identified and fixed the issue but wanted to understand why the Broken Join version below does not work.

CREATE TABLE #LeftTable
(RunID BIGINT,PolicyRef NVARCHAR(MAX),Val NVARCHAR(MAX))

INSERT INTO #LeftTable
VALUES (100,'pol1','hi'),(100,'pol2','hi2'),(100,'pol3','hi3')

CREATE TABLE #RightTable
(RunID BIGINT,PolicyRef NVARCHAR(MAX),Assured NVARCHAR(MAX))

INSERT INTO #RightTable
VALUES (80,'pol1','celec'),(90,'pol2','colorado'),(100,'pol2','colorado')

--SELECT * FROM #LeftTable
--SELECT * FROM #RightTable

-- Proper Join
SELECT *
FROM #LeftTable lt
LEFT OUTER JOIN #RightTable rt ON lt.PolicyRef = rt.PolicyRef AND lt.RunID = rt.RunID

-- Broken Join (eliminates Pol1 from LeftTable)
SELECT *
FROM #LeftTable lt
LEFT OUTER JOIN #RightTable rt ON lt.PolicyRef = rt.PolicyRef
WHERE lt.RunID = rt.RunID OR rt.runid IS NULL

DROP TABLE #LeftTable
DROP TABLE #RightTable

I would expect the two queries to return the same thing, but the pol1 row is eliminated in query 2. I assume this is because there is a record for pol1 where the RunID is not the RunID we need. But I don't see why that should eliminate the row.

left-join tsql sql-server sql
3 Answers

in this query:

SELECT *
FROM #LeftTable lt
LEFT OUTER JOIN #RightTable rt ON lt.PolicyRef = rt.PolicyRef
WHERE lt.RunID = rt.RunID OR rt.runid IS NULL

this part 

SELECT *
FROM #LeftTable lt
LEFT OUTER JOIN #RightTable rt ON lt.PolicyRef = rt.PolicyRef

will give you 3 result:

100,'pol1','hi',80,'pol1','celec'

100,'pol2','hi2',90,'pol2','colorado'

100,'pol2','hi2',100,'pol2','colorado'

but the where statement want that set of result have the same id, so this is the only possible result:

100,'pol2','hi2',100,'pol2','colorado'

user_107212070

For this you need to write and in place of where as shown below

-- Broken Join (eliminates Pol1 from LeftTable)
SELECT *
FROM #LeftTable lt
LEFT OUTER JOIN #RightTable rt ON lt.PolicyRef = rt.PolicyRef
AND lt.RunID = rt.RunID OR rt.runid IS NULL
user_105325000

This is expected behavior... First thing to understand, these are NOT logically equivalent queries... The reason you're seeing the "pol1" row being filtered out in the 2nd query is simple. rt.RunID is neither equal to lt.RunID, nor is it NULL... It has a value of "80" and therefore fails to meet either of the OR conditions.

user_82330790
Related