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

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

CodePudding user response：

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
)

CodePudding user response：

You could define a CTE with contain all company names which you want to check. Then query from defined CTE.

WITH list_names AS (
    SELECT 'CompA' as name
    UNION ALL
    SELECT 'CompB' as name
    UNION ALL
    SELECT 'CompC' as name
)
SELECT ln.* 
FROM list_names ln
WHERE NOT EXISTS (SELECT 1 FROM db.companies dc WHERE dc.name = ln.name)

CodePudding user response：

Without a temp table, abit more complex and requiring a new line for each name you want to search:

select case when (select count() from db.companies dc where dc.name = 'CompA') > 0 then 'exists' else 'not found' end CompA, case when (select count() from db.companies dc where dc.name = 'CompB') > 0 then 'exists' else 'not found' end CompB