CODEWITHSUNDEEP
sqlt-sql
mectravis
mectravis

Reputation: 51

Filter names that weren't found in a SQL select query

To simplify my problem I have a list of names:

('Jon','Joe','Jane','Dave'..)

and a table filled with the following columns

Name Age Address

I'm using the following query to select names that exist in the list:

SELECT Name,Age,Address from Table1 where Name in ('Jon','Joe','Jane'...)

but what I want is to find names in the list that are not in the table, how do I do that?

Upvotes: 0

Views: 606

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

You are looking for a left join or something similar:

select v.name
from (values ('Jon'), ('Joe'), ('Jane'), . . . 
     ) v(name)
where not exists (select 1 from table1 t1 where t1.name = v.name);

Upvotes: 1

Aaron Pereira
Aaron Pereira

Reputation: 334

This can be used to find the entries that don't have NULL in the name column

SELECT Name,Age,Address from Table1 where Name NOT in ('Jon','Joe','Jane'...)

This can be used to find the entries where NULL is present in the name column

SELECT Name,Age,Address from Table1 where Name NOT in ('Jon','Joe','Jane'...) OR Name IS NULL

Upvotes: 0

Related Questions