How to select all records from a table that does not exist in another table?

by

I have table A with columns key and date, and table B with columns key, date, and flag. I’d like to select all records from A where if the keys and dates match with B, B’s flag is false, as well as select records from A where the keys and dates do not match.

The keys repeat – once per date. Edited to add that A could also contain key-date pairs not existing in B, which I want in the result. For example,

if A is

Key Date
1 1/2020
1 2/2020
2 1/2020
2 2/2020
2 3/2020
3 1/2020
3 2/2020

if B is

Key Date Flag
1 1/2020 True
1 2/2020 True
2 1/2020 True
2 2/2020 False
2 3/2020 False

then I’d like the result to be

Key Date
2 2/2020
2 3/2020
3 1/2020
3 2/2020

Here’s what I came up with:

SELECT key
FROM A 
WHERE NOT EXISTS 
    (SELECT * 
     FROM B 
     WHERE A.date= B.date
     AND B.flag = TRUE)

Does the code look right? Is there a faster query using NOT IN or EXCEPT?

  • You are missing A.key = B.key in the subquery conditions. (And the date in the main SELECT clause.) Then, yes this approach is good. NOT IN is just as good, as long as there are no nulls involved and the DBMS can handle tuples with the IN clause. And EXCEPT is also equally good. Choose whatever works and what you prefer.

    – 




Does the code look right?

No, it will select records from A that don’t have corresponding entries in B at all.

Use this:

SELECT  key, date
FROM    a
INTERSECT
SELECT  key, date
FROM    b
WHERE   flag IS DISTINCT FROM true -- handles NULL values as well

This assumes that you only need key and date from A, and they are unique.

If you need other fields, or if (key, date) are not unique in A, use this:

SELECT  *
FROM    a
WHERE   (key, date) IN
        (
        SELECT  key, date
        FROM    b
        WHERE   flag IS DISTINCT FROM true
        )

Update after a substantial requirements change:

SELECT  *
FROM    a
WHERE   (key, date) NOT IN
        (
        SELECT  key, date
        FROM    b
        WHERE   flag -- if key and date are both not nullable
        )

or

SELECT  *
FROM    a
WHERE   NOT EXISTS
        (
        SELECT
        FROM    b
        WHERE   (b.key, b.date, b.flag) = (a.key, a.date, true)
        )

Leave a Comment