SQL query to search for record with id not in another table

Question

I have two tables with a required primary key in the database, and I want to find a disjoint set between them. For example,

• Table1 has columns ( ID, Name ) and data examples: (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 get the row with the identifier from Table1 , which is not in Table2 . In this case, return (3, Mary) ?

Ps. The identifier is the primary key for these two tables.

Thanks in advance.

Answer 1

try it

 SELECT ID, Name FROM Table1 WHERE ID NOT IN (SELECT ID FROM Table2) 

Answer 2

Use LEFT JOIN

 SELECT a.* FROM table1 a LEFT JOIN table2 b on a.ID = b.ID WHERE b.id IS NULL 

Answer 3

Quick alternative

I did some tests (on Postgres 9.5) using two tables with ~ 2M rows in each. This query is at least 5 * lower than the other suggested queries:

 -- 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; 

Answer 4

Keeping in mind the points made in the @John Woo comment / link comment above, I usually handled this:

 SELECT t1.ID, t1.Name FROM Table1 t1 WHERE NOT EXISTS ( SELECT TOP 1 NULL FROM Table2 t2 WHERE t1.ID = t2.ID ) 

Answer 5

There are three main approaches to this: not exists , not in not exists and left join/is null .

It remains to join 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 ) 

DOES NOT EXIST

 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 may be better broken down into large specific DBMS vendors. Generally speaking, the use of select... where... in (select...) should be avoided when the number of entries in the subquery is unknown. Some suppliers may limit the size. Oracle, for example, has a limit of 1000 . It’s best to try all three and show a plan of implementation.

In particular, the PostgreSQL form, NOT EXISTS execution plan, and LEFT JOIN/IS NULL same. Personally, I prefer NOT EXISTS because it shows intent better. After all, the semantics is that you want to find entries in A that do not exist in B.

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

Answer 6

 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