Find Records from One Table Which Don't Exist in Another

Select records from a table, which don't exist in another table

Check for NULLs in second table like:

SELECT TABLE1.name, TABLE1.surname, TABLE1.id 
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.id = TABLE2.id
WHERE TABLE2.id IS NULL

Alternate solution with NOT EXISTS:

SELECT TABLE1.name, TABLE1.surname, TABLE1.id 
FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE2 WHERE TABLE1.id = TABLE2.id)

One more with NOT IN:

SELECT TABLE1.name, TABLE1.surname, TABLE1.id 
FROM TABLE1
WHERE TABLE1.id NOT IN(SELECT id FROM TABLE2)

SQL - find records from one table which exist in another and delete if not

A solution for MySql with which in 1 statement you can delete from all 4 tables:

delete t1, t2, t3, t4
from (
  select u.product, count(*) counter 
  from (
    select * from table1 union all
    select * from table2 union all
    select * from table3 union all
    select * from table4
  ) u  
  group by u.product
) t 
left join table1 t1 on t1.product = t.product
left join table2 t2 on t2.product = t.product
left join table3 t3 on t3.product = t.product
left join table4 t4 on t4.product = t.product
where t.counter = 1; 

See the demo.

Results:


Table1

> Product | Date_Purchase
> :------ | :------------
> abc     | 06-Jan-19    
> def     | 05-Jan-18    
> ghi     | 05-Apr-19    
> abc     | 06-Feb-19  

Table2

> Product | Date_Purchase
> :------ | :------------
> mno     | 2-Aug-18     
> ghi     | 9-May-19     
> pqr     | 1-Sep-19 

Table3

> Product | Date_Purchase
> :------ | :------------
> ghi     | 2-Aug-18     
> mno     | 9-May-19     
> pqr     | 2-Aug-18     
> abc     | 06-Jan-19 

Table4

> Product | Date_Purchase
> :------ | :------------
> ghi     | 9-May-19     
> def     | 05-Jan-18 

Related Topics