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: August 03, 2021 - 4:16 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 Chris 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

  (4 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

Obrigado

Eduardo Bertolucci, August 02, 2021 - 8:42 pm UTC

Obrigado, resolveu meu problema!
Connor McDonald
August 03, 2021 - 4:16 am UTC

feliz por podermos ajudar
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.