CODEWITHSUNDEEP
mysqlsql
Pepster K.
Pepster K.

Reputation: 349

SQL to find elements NOT IN a table

I want to find elements in a set which are not in customers table. Here's an example table:

customer_id | customer_name
1             joe smith
2             john doe
3             jane doe

Here's an example set:

('joe smith', 'pepi fogelhut', 'jane doe', 'smiley whiplash')

The output of the query for this data should be:

pepi fogelhut
smiley whiplash

I'm pretty sure i need to use a left join. Something like this?

select customer_name as n1 from customers as c1 left outer join customers as c2 on n1=c2.customer_name

Upvotes: 0

Views: 2170

Answers (1)

dognose
dognose

Reputation: 20889

If you put the data into a temporary table, you can simple select all values from the table that do NOT appear in your original table. Something like:

SELECT name FROM temporaryTable WHERE name NOT IN (SELECT name FROM customerTable);

this will return you any customer name that is IN the temporary table but not in your original customer table.

Upvotes: 2

Related Questions