CODEWITHSUNDEEP
sqloracle-databasejoin
Artem Zubkov
Artem Zubkov

Reputation: 559

How make sql query "not in" more simply use only "join"?

Have query: 

select a.id from selzde.elorder a
inner join selzde.elorder b on a.name = b.name 
    and a.workname = b.workname 
    and b.id = needId
where a.id not in (select id_elorder from selzde.drugselorder)

how make sql query not in more simply use only join?

Upvotes: 1

Views: 181

Answers (3)

Roger Cornejo
Roger Cornejo

Reputation: 1547

One solution would be to use MINUS operator rather than not in as follows:

select a.id 
from selzde.elorder a 
     inner join selzde.elorder b 
on a.name = b.name      
and a.workname = b.workname      
and b.id = needId 
MINUS
select id_elorder 
from selzde.drugselorder
;

Hope this helps.

Regards, Roger

Upvotes: 1

juergen d
juergen d

Reputation: 204924

select a.id from selzde.elorder a
inner join selzde.elorder b on a.name = b.name 
and a.workname = b.workname 
and b.id = needId
left outer join selzde.drugselorder d on a.id = d.id_elorder
where d.id_elorder is null

Upvotes: 2

Xophmeister
Xophmeister

Reputation: 9219

Left join to drugselorder, on id_elorder = a.id, then choose the ones with no match (i.e. where drugselorder is null)... I wouldn't say it was any more "simple", though.

Upvotes: 2

Related Questions