How to show the values from the input that don’t exist in a table

oracleselect

Could you help me on this one?

See I have this order_table where I have to find the order numbers that don't exist in the table.

Let's say I have columns id, order_number in the table named order_table.

What I want is to show in my result all the values that don't exist in order_table.

Like for example:

I have order_number values 43454235423, 785686847856, 1243215421, 6437647, 978423454. I then do

SELECT *
FROM order_table
WHERE order_number = ('43454235423', '45423534252','785686847856', '1243215421',
                      '6437647', '978423454','5634636','4354235235')
--return order_numbers that don't exist in the table as 'status'

This is my sample query result:

order_number  status
------------  ---------
43454235423   not found
785686847856  not found
1243215421    not found
6437647       not found
978423454     not found

Best Answer

create table order_table(order_number number);
insert into order_table values (43454235423);
commit;

with input_values as
(
  select
    to_number(column_value) as order_number
  from
    xmltable('43454235423, 785686847856, 1243215421, 6437647, 978423454')
)
select
  v.order_number, 'not found' as status
from
  input_values v
left join
  order_table o on (v.order_number = o.order_number)
where
  o.order_number is null
;

        ORDER_NUMBER STATUS
-------------------- ---------
        785686847856 not found
          1243215421 not found
             6437647 not found
           978423454 not found
Related Question