SQL question - find a number from a list

Question

I have a table with 100 numbers in it. Now i have a list of 11 numbers, one of them is not in the table (different for the 100 numbers from it).

When i query after my list of 11 numbers select * from table where number in (' ',' ',' '......) it returns the 10 numbers that are in the table.

Now my question is: how do i find the number from my list which is not in the table? (only with SQL, because right now i am using Excel to do that).

Thank you!

Answer 1

You need to use the values clause:

with yourlist as (
select 1 as i from dual
union all
select 2 as i from dual
...
union all
select 3 as i from dual
)
select yourlist.i
from yourlist
left join yourtable
on yourtable.num = yourlist.i
where yourtable.num is null

Answer 2

One way would be to put your list of numbers in a second table, then use a left join to find where it doesn't match

SELECT number FROM list
LEFT JOIN table USING(number)
WHERE table.number IS NULL;