Search code examples
sqlamazon-redshiftdbeaver

Return names which dont exist in the database from the list provided

Let's assume I have a list of company names like so:

CompA
CompB
CompC

How would I go about only returning the names which don't exist in the database.

SELECT * FROM db.companies dc WHERE dc.name NOT IN ('CompA','CompB','CompC')

I have tried using NOT EXISTS and NOT IN but this returns all the company names which are not in the list but present in the database, but I need only the names from the specified list which does not exist.

So for example if CompC was not an existing company it should just return CompC

Solution

  • Make your list of companies into a table, and then query from it.

    create temp table tmp_companies (name varchar(100)); 
insert into tmp_companies 
values 
('CompA'), 
('CompB'), 
('CompC'); 

select * 
from tmp_companies c
where not exist (
select 1 
from db.companies dc
where dc.name = c.name
)