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

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

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

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

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

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

Examples related to sql

Examples related to database

Examples related to postgresql