SQL query - 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 (the other for 100 numbers from it).

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

Now my question is: how can I find a number from my list that is not in the table? (only with SQL, because now I use Excel for this).

Thanks!

Answer 1

You can also use NOT IN in the oracle database.

means that you used and knew about the IN keyword, in the same way, you can use NOT IN for the desired output ...

you can use the following query for this -

case 1: these are 10 numbers that you have in the table - (for example, the table name is list1)

 select list1.num from list1 where list1.num not in (select distinct table.number from table) 

case 2: how did you post your question, similar to the fact that you do not have numbers in the table -

 select list1.num from ( select number1 num from dual union select number2 num from dual union select number3 num from dual union select number4 num from dual union select number5 num from dual union select number6 num from dual union select number7 num from dual union select number8 num from dual union select number9 num from dual union select number10 num from dual union select number11 num from dual ) list1 where list1.num not in (select distinct table.number from table) 

instead of number 1 to number 11 you should insert your list number.

Answer 2

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 3

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

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

Answer 4

This is functionally equivalent to @ Denis answer, but uses slightly different methods:

 CREATE TYPE nt_number AS TABLE OF NUMBER; SELECT COLUMN_VALUE FROM table(nt_number(1,3,8,12,14)) MINUS SELECT NUMBER_COLUMN FROM YOUR_TABLE; 

Changing MINUS to INTERSECT will give you the values ​​that exist in both tables.

Answer 5

I will skip your quest first :)

The number in the temporary table and joining it is the best bet.