Query to find Strings that are not in a table-CodePudding

I am trying to write a query that returns all the strings that are not present in a table when given a list of strings.

When I execute something like:

select * 
from public.person 
where person_name in ('John', 'Doe', 'Mike')

it will find the person that has the name in the list of strings given, but I want to know the names that are not in the list.

If we have Mike, John, Ben in public.person, and I want to pass a list containing

'John', 'Doe', 'Mike'

and I want the query to return

'Doe'

as it is not present.

How can I modify the query to return the Strings present in the list I give and that are not contained in the table?

CodePudding user response：

You can create a table or derived tables with the names you are wanting to feed in, then LEFT OUTER JOIN to your table.

CREATE TABLE persons (name varchar(20));

INSERT INTO persons VALUES ('mike'),('john'),('ben');

WITH inNames AS (SELECT unnest(array['john','doe','mike']) as name)
SELECT inNames.name
FROM inNames 
   LEFT OUTER JOIN persons ON inNames.name = persons.name
WHERE persons.name IS NULL;

doe

dbfiddle

CodePudding user response：

You could probably do something like this:

SELECT name FROM (VALUES('John'), ('Doe'), ('Mike')) E(name)
EXCEPT SELECT person_name FROM public.person

Or you might insert the list into a temp table and do a left join (if null):

SELECT * FROM #tempTable t LEFT public.person p ON p.person_name=t.name WHERE p.name IS NULL