How to select from a table where in a list and then diff that against the same list

oracle

Here is what I need:

I have a LIST of keys:

A
B
C
D

A TABLE keys that contains:

A
B
**... and millions of other entries** ( but `NOT` `C` or `D` )

I want to see what is in the list AND NOT IN the table AND list.

the expected output would be:

C
D

Since C and D are in the LIST AND IN the TABLE.

I have found examples of how to create a table of a type and wrap the list I just could not get the syntax correct by putting together disparate examples to get what I want and they all failed.

Here is what I am stuck on:

[42000][904] ORA-00904: "MISSING_KEY": invalid identifier

CREATE OR REPLACE TYPE MISSING_KEYS AS TABLE OF VARCHAR2(256);

SELECT missing_key
FROM TABLE (MISSING_KEYS('A','B','C','D'))
WHERE missing_key NOT IN (
    SELECT key
    FROM schema.keys
    WHERE key IN ('A','B','C','D')
);

How do I give the column from the MISSING_KEYS TYPE a name?

Best Answer

If you only need your list to have the keys (I'm using minus to find the difference between the two lists, you could modify it to use NOT IN instead if you'd rather)

create type tbl_keys2 is table of varchar2(10);
/

create table tbl (
  key varchar2(10)
);

insert into tbl values( 'list' );
insert into tbl values( 'of' );

with keys as (
  select *
    from table( tbl_keys2( 'list', 'of', 'some', 'keys' ))
)
select k.column_value key
  from keys k
minus
select t.key
  from tbl t;

If your list potentially needs to have multiple columns (i.e. a key and a value where you're looking for everything that doesn't match), you can create an object type that can have those additional columns and do

create type obj_keys is object( key varchar2(10) );
/

create type tbl_keys is table of obj_keys;
/

with keys as (
  select *
    from table( tbl_keys( obj_keys('list'), 
                          obj_keys('of'), 
                          obj_keys('some'), 
                          obj_keys('keys') ))
)
select k.key
  from keys k
minus
select t.key
  from tbl t;
Related Question