How can I show the values from my input that don't exist in a table?

Question

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

Answer 1

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

Answer 2

As you want the results to be returned as rows, you need the input to be in the form of a row set as well.

One way is to create such a row set dynamically, right in the query, using a series of SELECTs FROM DUAL combined with UNION ALL:

SELECT '43454235423' AS order_number FROM DUAL
UNION ALL
SELECT '45423534252' FROM DUAL
UNION ALL
SELECT '785686847856' FROM DUAL
UNION ALL
...

Another way would be to insert the input values into an actual table and use that table in the query.

Whatever way you represent the input, the next step will be to anti-join the order_table to the input table. There are various ways of implementing an anti-join. One very common method is using NOT EXISTS:

SELECT
  input.order_number,
  'not found' AS status
FROM
  (
    SELECT '43454235423' AS order_number FROM DUAL
    UNION ALL
    SELECT '45423534252' FROM DUAL
    UNION ALL
    SELECT '785686847856' FROM DUAL
    UNION ALL
    ...
  ) input
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      order_table o
    WHERE
      o.order_number = input.order_number
  )
;

You can also use NOT IN:

SELECT
  order_number,
  'not found' AS status
FROM
  (
    SELECT '43454235423' AS order_number FROM DUAL
    UNION ALL
    SELECT '45423534252' FROM DUAL
    UNION ALL
    SELECT '785686847856' FROM DUAL
    UNION ALL
    ...
  )
WHERE
  order_number NOT IN
  (
    SELECT
      order_number
    FROM
      order_table o
  )
;

Another common technique is LEFT JOIN + IS NULL:

SELECT
  input.order_number,
  'not found' AS status
FROM
  (
    SELECT '43454235423' AS order_number FROM DUAL
    UNION ALL
    SELECT '45423534252' FROM DUAL
    UNION ALL
    SELECT '785686847856' FROM DUAL
    UNION ALL
    ...
  ) input
  LEFT JOIN order_table ot ON o.order_number = input.order_number
WHERE
  o.id IS NULL
;

And then there is also MINUS (Oracle's equivalent of the SQL standard's EXCEPT operator):

SELECT
  order_number,
  'not found' AS status
FROM
  (
    SELECT '43454235423' AS order_number FROM DUAL
    UNION ALL
    SELECT '45423534252' FROM DUAL
    UNION ALL
    SELECT '785686847856' FROM DUAL
    UNION ALL
    ...
  )

MINUS

SELECT
  order_number,
  'not found' AS status
FROM
  order_table
;