SQL Check one list against Another

Question

I'd appreciate any pointers on how in SQL to check whether elements in one list also appear in another.

List A = Live Customers in April
List B = Live Customers in May

How can I check which Customers in List A also appear in List B ? to identify those Customers which have been lost

i.e. Customers in A but not in B.

Thank you for your help. Gav

There are a number of ways to handle this problem, but you need to show us some table structure if you want an actionable answer. — Tim Biegeleisen, Aug 17 '16 at 16:19
also please mention RDBMS you are using like SQLServer,Oracle,mysql,postgre.. — TheGameiswar, Aug 17 '16 at 16:20
The SQL Standard defines `INTERSECT` and `EXCEPT` [Set operators](https://en.wikipedia.org/wiki/Set_operations_(SQL)), but not all DBMS implement them. But as stated, it depends what database vendor and version you are using. — GarethD, Aug 17 '16 at 16:30
Possible duplicate of [SQL - find records from one table which don't exist in another](http://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — Matt, Aug 17 '16 at 16:40

score 8 · Answer 1 · answered Aug 17 '16 at 16:33

Different ways to pull the results

SELECT customer
FROM ListA a 
WHERE NOT EXISTS (SELECT 1 FROM ListB b WHERE a.customer=b.customer)

OR

SELECT a.customer
FROM ListA a 
  LEFT JOIN ListB b ON a.customer=b.customer
WHERE b.customer is null

OR

SELECT customer
FROM ListA

except

SELECT customer
FROM ListB

OR

SELECT customer
FROM ListA 
WHERE customer NOT IN (SELECT customer FROM ListB )

score 1 · Answer 2 · edited Aug 17 '16 at 16:26

1

Try the not in clause

example

select * 
from mytable 
where id not in (select id from table2)

this will return results that are not in another table. quick and simple

edited Aug 17 '16 at 16:26

Nick

answered Aug 17 '16 at 16:21

Tdubs

Check this answer on impact of not in :http://stackoverflow.com/questions/173041/not-in-vs-not-exists – TheGameiswar Aug 17 '16 at 16:32

2 Answers2