CODEWITHSUNDEEP
mysqlsqldatabase
matrixguy
matrixguy

Reputation: 306

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 ?

Upvotes: 1

Views: 1213

Answers (3)

WestCoastProjects
WestCoastProjects

Reputation: 63042

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

Upvotes: 1

Mureinik
Mureinik

Reputation: 311188

The two straight forward ways are to use an in operator:

SELECT id, name, feature, marks
FORM   table1
WHERE  id NOT NOT (SELECT id FROM table2)

or an exists operator:

SELECT id, name, feature, marks
FORM   table1
WHERE  NOT EXISTS (SELECT *
                   FROM   table2
                   WHERE  table1.id = table2.id)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

There are several ways to do this. One uses a LEFT JOIN to filter off the unwanted records:

SELECT id, name, feature, marks
FROM (...) t1
LEFT JOIN t2
    ON t1.id = t2.id
WHERE t2.id IS NULL

Upvotes: 0

Related Questions