Skip to Main Content
  • Questions
  • Value List items that are NOT IN the Table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tony.

Asked: October 14, 2016 - 7:37 am UTC

Last updated: May 03, 2021 - 4:42 am UTC

Version: 4.0.3

Viewed 10K+ times! This question is

You Asked

How to Select Values, in a Value List, that are NOT IN a Table?
e.g. I need to find which of the following products are not on the Item Master:-
10, 20, 25, 70, 90. (this list is available only in a spreadsheet or word document)
Assume they all exist except 25 & 90 - Only these 2 products must display.
I have no permissions to create tables or import from spreadsheets.

and we said...

If you want to generate a list of IDs on-the-fly, you can use the with clause. For example:

with id_list as (
  select 10 id from dual union all
  select 20 id from dual union all
  select 25 id from dual union all
  select 70 id from dual union all
  select 90 id from dual
)
  select * from id_list;

ID  
10  
20  
25  
70  
90 


Then to find which of these aren't in your table, just use not exists:

create table t (
  x int
);

insert into t values (10);
insert into t values (20);
insert into t values (70);

with id_list as (
  select 10 id from dual union all
  select 20 id from dual union all
  select 25 id from dual union all
  select 70 id from dual union all
  select 90 id from dual
)
  select * from id_list
  where  not exists (
    select * from t
    where  x = id 
  );

ID  
90  
25 

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Select Values, in a Value List, that are NOT IN a Table

Tony Brennan, October 14, 2016 - 10:13 am UTC

Works perfectly - thank-you very much for the solution, & the fast turnaround :-)

A reader, July 04, 2018 - 9:57 am UTC


Worked correctly

A reader, April 30, 2021 - 6:52 pm UTC


Connor McDonald
May 03, 2021 - 4:42 am UTC

glad we could help