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

Question

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?

Answer 1

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;

Answer 2

The TABLE() function needs to take in an SQL Nested Table or Pipeline Function.

Newer versions of Oracle allow for PL/SQL Tables and the Polymorphic Table Function (a different type of Pipeline Function).

To create an SQL Nested Table User Defined Type:

create or replace type vc2_arr as table of varchar2(4000)

Putting it to use:

with keys as (
    select 'some' key from dual union all
    select 'keys' key from dual
)
select column_value as key
from table( vc2_arr('list','of','some','keys') )
where column_value not in ( select key from keys where key in ( 'list', 'of', 'some', 'keys' ) );

Although, you may want to review your Business Requirement. You can find "rows in A that don't exist in B" by using the minus keyword.

Alternative solution:

with keys as (
    select 'some' key from dual union all
    select 'keys' key from dual
)
select column_value as key
from table( vc2_arr('list','of','some','keys') )
minus
select key from keys

Answer 3

This is what I finally got to work:

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

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

The part that I was missing, the default name column_value is explained here.