Which IN-LIST values are not in the table

Question

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

Item_code
12345
98765
65436

User supplies list of

12345
98765
65436
81764

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.

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)

Edit

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....