CODEWITHSUNDEEP
sqloracle-databaseselect
mrbungle
mrbungle

Reputation: 1931

Return null results using where variables

I want to see the values I'm using in the where clause if the results come back null

 select *
 from (select PartNum, PONum from db where PartNum = '12345' and PONum = 'POX123' union
       select PartNum, PONum from db where PartNum = '67890' and PONum = 'POX456' union
       select PartNum, PONum from db where PartNum = '98765' and PONum = 'POX789') d
 where PartNum is null

PartNum 98765 does not live in the db and that's what I need returned back for comparison somewhere else and this clearly does not work but I can't wrap my head on how to return empty rows, seems counter-intuitive.

Upvotes: 0

Views: 29

Answers (1)

GMB
GMB

Reputation: 222682

You can enumerate the values, then use not exists (or an anti left join) to filter on those that do not exist in the table.

In SQL Server:

select p.partNum
from (values (12345), (67890), (98765)) p(partNum)
where not exists (select 1 from db d where d.partNum = p.partNum)

In Oracle:

select p.partNum
from (
    select 12345 partNum from dual
    union all select 67890 from dual
    union all select 98765 from dual
) p
where not exists (select 1 from db d where d.partNum = p.partNum)

Note that this treats partNum as a number, because it does look like a number. If it is actually of a string-like datatype, then you can surround the values with single quotes.

Upvotes: 1

Related Questions