CODEWITHSUNDEEP
sqloracle-database
Naty Bizz
Naty Bizz

Reputation: 2342

Determine which values are not in sql table

I have this query in oracle:

select * from table where col2 in (1,2,3,4);

lets say I got this result

col1 | col2
-----------
a       1
b       2

My 'in (1,2,3,4)' part has like 20 or more options, how can I determinate which values I don't found in my table? in my example 3 and 4 doesn't exist in the table

Upvotes: 1

Views: 293

Answers (3)

sgeddes
sgeddes

Reputation: 62841

Here would be a way to do it if you're just using integers for your specific example:

SELECT *
FROM (
  Select Rownum r
  From dual
  Connect By Rownum IN (1,2,3,4)
) T
LEFT JOIN YourTable T2 ON T.r = T2.Col2
WHERE T2.Col2 IS NULL

And the Fiddle.

This creates a table out of your where criteria 1,2,3,4 and uses that to LEFT JOIN on.

--EDIT

Because values aren't ints, here is another "ugly" option:

SELECT *
FROM (
  Select 'a' r From dual UNION
  Select 'b' r From dual UNION
  Select 'c' r From dual UNION
  Select 'd' r From dual
) T
LEFT JOIN YourTable T2 ON T.r = T2.Col2
WHERE T2.Col2 IS NULL

http://www.sqlfiddle.com/#!4/5e769/2

Good luck.

Upvotes: 0

APC
APC

Reputation: 146239

IN lists are stupid, or at least not very useful. Use a SQL Type collection to store your values instead because we can turn them into tables.

In this example I'm using the obscure SYS.KU$_OBJNUMSET type, which is the only nested table of Number I know of on 10g. (There's lots more in 11g).

So 

select t.column_value 
from table ( SYS.KU$_OBJNUMSET  (1,2,3,4) ) t
        left join your_table 
          on col2 = t.column_value
where col2 is null;

Upvotes: 1

gdoron
gdoron

Reputation: 150253

You can't in the way you want.

You need to insert the values you want to find into a table and than select all the values which don't exist in the desired table.

Lets say the data you want to find is in A and you want to know which doesn't exist in B.

SELECT *
FROM   table_a A
WHERE  NOT EXISTS (SELECT * 
                   FROM table_b B 
                   WHERE B.col1 = A.col1);

Upvotes: 1

Related Questions