SQL query to find record with ID not in another table

Question

I have two tables with binding primary key in database and I desire to find a disjoint set between them  For example    Table1 has columns  ID  Name  and sample data   1  John    2  Peter    3  Mary   Table2 has columns  ID  Address  and sample data   1  address2    2  address2    So how do I create a SQL query so I can fetch the row with ID from table1 that is not in table2  In this case   3  Mary  should be returned    Ps  The ID is the primary key for those two tables    Thanks in advance

User · Answer

There are basically 3 approaches to that: not exists, not in and left join / is null.

LEFT JOIN with IS NULL

SELECT  l.*
FROM    t_left l
LEFT JOIN
        t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

NOT IN

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    t_right r
        )

NOT EXISTS

SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    t_right r
        WHERE   r.value = l.value
        )

Which one is better? The answer to this question might be better to be broken down to major specific RDBMS vendors. Generally speaking, one should avoid using select ... where ... in (select...) when the magnitude of number of records in the sub-query is unknown. Some vendors might limit the size. Oracle, for example, has a limit of 1,000. Best thing to do is to try all three and show the execution plan.

Specifically form PostgreSQL, execution plan of NOT EXISTS and LEFT JOIN / IS NULL are the same. I personally prefer the NOT EXISTS option because it shows better the intent. After all the semantic is that you want to find records in A that its pk do not exist in B.

Old but still gold, specific to PostgreSQL though: https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/

User · Answer

Try this   SELECT ID  Name  FROM   Table1  WHERE  ID NOT IN  SELECT ID FROM Table2

User · Answer

Use LEFT JOIN  SELECT  a   FROM    table1 a             LEFT JOIN table2 b                 on a ID   b ID WHERE   b id IS NULL

User · Answer

SELECT COUNT ID  FROM tblA a WHERE a ID NOT IN  SELECT b ID FROM tblB b     --For count   SELECT ID FROM tblA a WHERE a ID NOT IN  SELECT b ID FROM tblB b     --For results

User · Answer

Fast Alternative  I ran some tests  on postgres 9 5  using two tables with  2M rows each  This query below performed at least 5  better than the other queries proposed    -- Count SELECT count    FROM        SELECT id FROM table1  EXCEPT  SELECT id FROM table2    t1 not in t2   -- Get full row SELECT table1   FROM        SELECT id FROM table1  EXCEPT  SELECT id FROM table2    t1 not in t2 JOIN table1 ON t1 not in t2 id table1 id

User · Answer

Keeping in mind the points made in  John Woo s comment link above  this is how I typically would handle it   SELECT t1 ID  t1 Name  FROM   Table1 t1 WHERE  NOT EXISTS       SELECT TOP 1 NULL     FROM Table2 t2     WHERE t1 ID   t2 ID

[sql] SQL query to find record with ID not in another table

LEFT JOIN with IS NULL

NOT IN

NOT EXISTS

Examples related to sql

Examples related to database

Examples related to postgresql