Last post Jan 25, 2016 06:57 PM by limno
Jan 25, 2016 02:20 PM|GrassProgrammer|LINK
I have two tables with identical structures, but somewhat varying data.
First I joined the tables to see the data:
SELECT a.ID, b.ID
FROM tableA a
LEFT JOIN tableB b on a.ID = b.ID
This gives me a list of all IDs in the table A.
If the record also exists in B, I will see the same number in the second column, else I will see NULL.
It stands to logic that I could then add the following clause:
WHERE b.ID = NULL
With this, I expect it to filter out all of the records that exist in both tables, and only give me the records in table A that do not exist in table B.
However, adding this clause returns no data.
I know other ways to get around this problem, but would like to learn why this clause doesn't work as I expect.
Edit: Solved my own problem. Apologies.
There is an apparent difference between "= NULL" and "is NULL" as described
The cleanest solution is to either use "is NULL", or set this configuration prior to running your query:
set ansi_nulls off
Jan 25, 2016 02:31 PM|gapimex|LINK
WHERE b.ID is NULL
Hope this help
Jan 25, 2016 06:57 PM|limno|LINK
Don't mess with ansi_nulls setting for this.
Besides the IS NULL, you can also use except:
SELECT a.ID FROM tableA a
SELECT b.ID FROM tableB b