Lets say I have a list of employee names and I want to select all rows with those names:
SELECT * FROM employee WHERE name IN ('Chris', 'Bob', 'Jane', 'Joe')
Perhaps Jane and Joe don’t exist in this table. How can I create a query that identifies items in my list that don’t exist at all.
I’m not looking for any specific format as the returned result; it can be comma separated or a list of rows with the respective unmatched names.
Advertisement
Answer
You need to save that list as a table and then left join it with “employees”.
select lt.list_name, e.*
from list_table lt
left join employee e
on e.name = lt.list_name;
this will output all the names from your list in the first column and respective data from employees table. When there is no suitable record in “employees” table, you’ll see first column populated only.
There only one way I see to achieve desired result if you don’t have enough right in order for creating tables – it is to use a CTE:
with names_list(name) as (select 'Chris' union all select 'Bob' union all ...)
select nl.name, e.*
from names_list nl
left join employee e
on e.name = nl.name;