CODEWITHSUNDEEP
sqlsubquerycorrelated-subquery
Innova
Innova

Reputation: 4971

Subquery with multiple select statements

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..

Upvotes: 0

Views: 2297

Answers (3)

Kishore Kumar
Kishore Kumar

Reputation: 1

use union all like this --> select f.FIRST_NAME from farmer f where f.ID in (select v.ID from Village v where v.ID in (1,2) union all select s.ID from state s where s.ID in (3,4) )

Upvotes: 0

Andomar
Andomar

Reputation: 238076

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.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300549

Your query is better expressed as:

SELECT id 
FROM tbl t
LEFT JOIN table1 t1 on t1.id = t.id 
LEFT JOIN table2 t2 on t2.id = t.id 
LEFT JOIN table3 t3 on t3.id = t.id 
WHERE t1.id IS NULL AND t2.id IS NULL AND t3.id IS NULL

Upvotes: 1

Related Questions