Rahul Rahul - 3 months ago 8
SQL Question

select 1 -select 2

In the query,

select id,name,feature,marks from (....)

I want to remove the ones whose id is present in another select statement .
select id from (...)


I'm new to sql .Is there a statement to do this directly ?

Answer

Several approaches.

Join:

select t1.id, t1.name, t1.feature, t1.marks 
from t1
left outer join (some select statement) t2
on t1.id = t2.id
where t2.id is null

Antijoin:

select t1.id, t1.name, t1.feature, t1.marks 
from t1
where not exists (
   select id 
   from (some select statement) t2 
   where t2.id = t1.id
)

"Not In":

select t1.id, t1.name, t1.feature, t1.marks 
from t1
where t1.id not in (
   select id 
   from (some select statement)
)
Comments