Oracle SQL - find NOT values ​​in table

Question

Take this table of WORDS.

WORD Hello Aardvark Potato Dog Cat 

And this list:

 ('Hello', 'Goodbye', 'Greetings', 'Dog') 

How can I return a list of words that are NOT in the word table but are in my list?

If I have a table that “contains all possible words”, I can do:

 SELECT * from ALL_WORDS_TABLE where word in ('Hello', 'Goodbye', 'Greetings', 'Dog') and word not in (SELECT word from WORDS where word in ('Hello', 'Goodbye', 'Greetings', 'Dog') ); 

However, I do not have such a table. How else can this be done?

In addition, creating a new table is not an option, because I do not have that level of access.

Answer 1

Instead of hard coding the list values ​​into strings, use DBMS_DEBUG_VC2COLL to dynamically convert the delimited list to strings, and then use the MINUS operator to exclude strings in the second query that are not in the first query:

 select column_value from table(sys.dbms_debug_vc2coll('Hello', 'Goodbye', 'Greetings', 'Dog')) minus select word from words; 

Answer 2

You can turn your list into the same view:

 select 'Hello' as word from dual union all select 'Goodbye' from dual union all select 'Greetings' from dual union all select 'Dog' from dual 

Then you can choose from this:

 select * from ( select 'Hello' as word from dual union all select 'Goodbye' from dual union all select 'Greetings' from dual union all select 'Dog' from dual ) where word not in (select word from words); 

This may not be the easiest solution you could hope for ...

You say that you do not have sufficient permissions to create tables, so you may not be able to create types either, but if you can find a suitable type "lying" in your database, you can do this:

 select * from table (table_of_varchar2_type('Hello','Goodbye','Greetings','Dog')) where column_value not in (select word from words); 

Here table_of_varchar2_type is represented by a type name that is defined as:

 create type table_of_varchar2_type as table of varchar2(100); 

One of those types that you can probably find is SYS.KU$_VCNT , which is the VARCHAR2 TABLE (4000).

Answer 3

Try this solution:

 SELECT a.word FROM ( SELECT 'Hello' word FROM DUAL UNION SELECT 'Goodbye' word FROM DUAL UNION SELECT 'Greetings' word FROM DUAL UNION SELECT 'Dog' word FROM DUAL ) a LEFT JOIN ALL_WORDS_TABLE t ON t.word = a.word WHERE t.word IS NULL