CODEWITHSUNDEEP
sqllistcomparison
Gavin
Gavin

Reputation: 167

SQL Check one list against Another

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

Upvotes: 6

Views: 20359

Answers (2)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

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 )

Upvotes: 9

Tdubs
Tdubs

Reputation: 29

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

Upvotes: 1

Related Questions