SQL statement tip: Query for data records that exist in one table and not in another

Original: SQL statement tip: Query for data records that exist in one table and not in another

Method One (only for single fields)
Easy to understand, low efficiency with not in

Select a.ID from A where a.id not in (select ID from B)

Method Two (for multiple field matches)
Use left Join...on ..., "b.ID isnull" indicates a record in which the b.ID field is null after the

Select a.ID from A left join B in a.id=b.id where b.id is null

Method Three (for multiple field matches)

SELECT * FROM B where (select COUNT (1) as Num from A where a.id = b.id) = 0

Method Four (for multiple field matches)

SELECT * from A where NOT exists (select 1 from B where a.id=b.id)

Sync posted on my personal site: http://www.zuowenjun.cn/post/2014/10/09/54.html

SQL statement tip: Query for data records that exist in one table and not in another