CODEWITHSUNDEEP
sqloracle-database
grizzasd
grizzasd

Reputation: 383

Oracle DB: Select Values from a list that do not exist in DB

I have a list of values. Some of which do not exist in the DB. I'd like to find which values in the list do not exist.

Eg. Given the table:

id name
--------
1 John
2 Amy
3 Kevin
4 Matt
5 Mark
6 Jim
7 Angela
8 Erin

With a list containing:

John, Amy, Sarah, Sam

I'd like to only return:

Sarah, Sam

How can I achieve this using Oracle DB? I know I could create a new table, insert the values from my list, do a join, then drop the table. Is there an easier way?

Upvotes: 0

Views: 70

Answers (4)

user3315556
user3315556

Reputation: 175

You can also use a unpivot table approach :

      select Number as list from (    
        select * from (
           select 'jn','jimmy','ronny' from dual ) 
        unpivot
       (  
        "Values" FOR "Number" IN ("'JN'","'JIMMY'","'RONNY'")   
       )
       )
     minus
    
     select list from your_table;

Upvotes: 0

William Robertson
William Robertson

Reputation: 16001

You can use a collection constructor, for example:

select column_value from soda_key_list_t('John', 'Amy', 'Sarah', 'Sam');

You can create your own "table of strings" collection type if you don't already have one, e.g.

create or replace type varchar2_tt as table of varchar2(4000);

or else see what existing types are available:

select owner, type_name, length
from   all_coll_types t
where  t.coll_type = 'TABLE'
and    t.elem_type_name = 'VARCHAR2'
order by 1,2;

I've used soda_key_list_t which is included with Oracle 18c onwards as part of the Simple Oracle Document Access feature. Others include dbms_debug_vc2coll and ora_mining_varchar2_nt.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Create a table on-the-fly:

select name from
(

  select 'John'  as name from dual union all
  select 'Amy'   as name from dual union all
  select 'Sarah' as name from dual union all
  select 'Sam'   as name from dual
) names
where name not in (select name from mytable);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269813

You would use a left join or not exists:

select name
from (select 'John' as name from dual union all
      select 'Amy' as name from dual union all
      select 'Sarah' as name from dual union all
      select 'Sam' as name from dual 
     ) n left join
     t
     using (name)
where t.name is null;

Upvotes: 1

Related Questions