CODEWITHSUNDEEP
sqloracle-database
Subburaj
Subburaj

Reputation: 2334

How to get the Not Exists Record List from the Given List in Oracle?

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

enter image description here

Upvotes: 1

Views: 2710

Answers (2)

sstan
sstan

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 alls. 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

ScaisEdge
ScaisEdge

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

Related Questions