Subquery with multiple select statements

Question

to check the subquery having multiple select statement inside 'not in' condition

Eg.

select id from tbl where 
id not in (select id from table1) and 
id not in (select id from table2) and
id not in (select id from table3)

instead of repeating the same id 'not in' condition , i need the subquery which will check in one shot from multiple tables..

pls help..

Answer 1

You could use a union, so you just have one in:

select  id
from    tbl 
where   id not in 
        (
        select id from table1
        union all select id from table2
        union all select id from table3
        )

Note: not in does not work well with nullable columns, but I assume id is not nullable here.