I'm looking for a simple way of seeing which items in my IN-LIST are not actually in the table. It seemed really simple but I think I'm probably over-thinking it.

I have a long list of item codes supplied by a customer and they would like an attribute in the table updating for each of those item codes. However, I need to let the customer know if any of the codes he has provided don't actually exist in the table. I've tried doing a COUNT(*) and a GROUP BY and I've tried some variations using MINUS but it always comes down to the fact that if the value isn't in the IN-LIST I can't refer to it.

Example: Item table has 3 entries


User supplies list of


I want to highlight to the customer that the code '81764' is not a valid item code. I can do this easily with PL/SQL but I would like a SQL solution if possible.

1 Answer 1


You need to create a list of values which is unfortunately quite cumbersome in Oracle (due to the missing values() clause).

But the following will work:

with id_list as (
  select 12345 as id from dual union all 
  select 98765 from dual union all 
  select 65436 from dual union all 
  select 81764 from dual
select il.id as missing_id
from id_list il
  left join item i on i.item_code = il.id
where i.item_code is null;

But given the nature of the query this is a bit complicated to parameterize though. The best solution is probably to create a (global) temporary table, then put the list of IDs you receive into that table and use that instead of the common table expression (the with (...) part)


There is a (cumbersome) way to "explode" a comma separated list of values, which might be "easier" in this case:

with base_value(id_string) as (
  select '12345,98765,65436,81764' from dual
), id_list(id) as (
  select regexp_substr (id_string, '[^,]+', 1, level)
  from base_value
  connect by regexp_substr (id_string, '[^,]+', 1, level) is not null
select il.id as missing_id
from id_list il
  left join item i on i.item_code = to_number(il.id)
where i.item_code is null;

But that might quickly hit Oracle's limit of 4000 bytes for a string literal....

  • That certainly works though as you point out ceating the list is quite messy. I already have a solution that uses a temporary table alongside a merge statement (this is to drive an update ultimately) that could easily be used to get the missing values. however i was hoping for something straightforward that I could give the customer to run to check his values in a simple SQL statement. The customer lacks confidence in all but the most basic SQL. Commented Oct 6, 2015 at 12:16
  • @BriteSponge: there is another (cumbersome - again) way of doing this.
    – user1822
    Commented Oct 6, 2015 at 13:11
  • I have to admit that I'm going to need some time to work out what that is doing . . . but that's what make this job interesting. I'm going to tick the 'Correct Answer' but I'll certainly upvote anything else that looks interesting. Commented Oct 6, 2015 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.