SQL Not Exists include null value

user_9951940 0
Viewed 10130

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?

sql-server-2014 sql-server
4 Answers

NULL value when compare with anything will return FALSE. You will need to use IS NULL to handle

select t2.name, t2.description, t2.version
from   Table2 as t2
WHERE  NOT EXISTS 
       ( 
            SELECT *
            FROM   Table1 as t1 
            WHERE  t2.name        = t1.name 
            AND    t2.description = t1.description 
            AND    (
                       t2.version     = t1.version
                   OR  (t2.version IS NULL AND t1.version IS NULL)
                   )
       )
user_1856470

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
user_18410540

you can use EXCEPT for this problem. you can use a common field from two tables and then select your data from that result.

look this following code :

SELECT name, description, version FROM Table2
EXCEPT
SELECT name, description, version FROM Table1

this code give you data that exist in table2 that not exist in table1.

user_71564860

You can use simply ISNULL() function:

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 ISNULL(t2.version,0) = ISNULL(t1.version,0)
user_144508630
Related