Reputation: 2334
How to get the list of Not Available (Not Exists) Record List from the Given Input Condition?
If we are using Not IN Operator, it will result all the not matching records from the table. But I want to get the not matching record from the Given Input Condition. I have given some sample example.
Table Name: Country
Upvotes: 1
Views: 2710
Reputation: 36553
Logically, you want to return the country codes from your input list that don't exist in the country
table. That's can be expressed very simply with not exists
.
The only difficulty is that you need to convert the input list of country codes into a list of rows. You can do that by using a series of union all
s. Or, since you are using Oracle, you can use SYS.DBMS_DEBUG_VC2COLL
to perform the list-to-table transformation, which I think makes the query more readable:
select i.column_value as country_code
from table(SYS.DBMS_DEBUG_VC2COLL('AU', 'IN', 'ZA', 'DK', 'CH', 'NL')) i
where not exists (select null
from country c
where c.country_code = i.column_value)
Upvotes: 3
Reputation: 133410
Could be using a set of select union and a minus
( select 'AU' from dual
union
select 'IN' from dual
union
select 'ZA' from dual
union
select 'DK' from dual
union
select 'CH' from dual
union
select 'NL' from dual )
minus
SELECT countrycode
FROM country
WHERE countrycode IN ('AU','IN','ZA','DK','CH','NL')
Upvotes: 1