Join two tables in Oracle

Question

I need something like:

select * from table1 where {if PKID = MasterPKID - (that PKID should not be present as refNo in table2)}.

ie- 652 should not be present in Table2

I am totally clueless.

Can we achieve it in one query?

if PKID = MasterPKID - (that PKID should not be present as refNo in table2) . What do you mean by that. Please give appropriate example with some more data. — Utsav
– Utsav, Commented Sep 20, 2015 at 5:14

Pரதீப் · Accepted Answer · 2015-09-20 05:15:33Z

2

Use Not exists

select * from table1 a where PKID = MasterPKID and not exists (select 1 from table2 b where a.PKID = b.refno)

answered Sep 20, 2015 at 5:15

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Can these be achieved with JOIN as well?

That is a join -- technically, it's an anti-join. EXISTS on its own is a semi-join.

Nitin Tripathi · Accepted Answer · 2015-09-20 05:28:06Z

Try any of these!!

If MaterPKID can contain NULL Values then go for not exists

select * from table1 a where PKID = MasterPKID and not exists (select 1 from table2 b where a.PKID = b.refno)

else go with not in

select * from table1 a where PKID = MasterPKID and PKID not in (select REFNO from table2)

I would think about the impact of nulls the other way round -- if you specifically want a NULL in the subquery to cause the predicate to evaluate as false (pretty rare condition) then use NOT IN, but otherwise use NOT EXISTS for everything.