I have two SQL Server tables :
Table 1
Id name description version
----------------------------------
1 Book1 Book 1 Title v1
2 Book2 Book 2 Title v2
3 Book3 Book 3 Title NULL
4 Book5 Book 5 Title v3
Table 2
Id name description version
----------------------------------
1 Book1 Book 1 Title v1
2 Book2 Book 2 Title v2
3 Book3 Book 3 Title NULL
4 Book4 Book 4 Title NULL
5 Book5 Book 5 Title NULL
I want to select all data from table 2 that doesn't exist in table 1 so I can insert them into another table.
Here's the SQL query :
SELECT t2.name, t2.description, t2.version
FROM Table2 AS t2
WHERE
NOT EXISTS (SELECT t1.name, t1.description, t1.version
FROM Table1 as t1
WHERE t2.name = t1.name
AND t2.description = t1.description
AND t2.version = t1.version)
The result expected is this :
Id name description version
-----------------------------------
4 Book4 Book 4 Title NULL
5 Book5 Book 5 Title NULL
But I'm getting this instead:
Id name description version
---------------------------------
3 Book3 Book 3 Title NULL
4 Book4 Book 4 Title NULL
Why the null value are not evaluated in my NOT EXIST statement and are displayed?
Optimized Query:
SELECT t2.name, t2.description, t2.version
FROM Table2 AS t2
LEFT JOIN Table1 AS t1
ON t2.name = t1.name
AND t2.description = t1.description
AND (t2.version = t1.version
OR (t2.version IS NULL AND t1.version IS NULL))
WHERE t1.ID is NULL