All Forums Database
Jigar 70 posts Joined 09/11
30 Nov 2011
Not Exists vs Left Join

I have read somewhere that Not exist will almost always perform better than Left join with null check in where clause,Can someone tell me the reason for this ?

eg :

SEL A.*

FROM TABLE A

WHERE NOT EXISTS

(SEL 1

FROM TABLE B

WHERE A.KEY=B.KEY

)

Would perform better than

 

SEL A.*

FROM TABLE A

LEFT JOIN TABLE B

ON A.KEY=B.KEY

WHERE B.KEY IS NULL ;

 

 

 

dnoeth 4628 posts Joined 11/04
30 Nov 2011

Look at the explain and you'll see that LEFT JOIN creates the result of the join first and then filters for NULLs while NOT EXISTS filters directly while joining.

Dieter

Dieter

Jigar 70 posts Joined 09/11
30 Nov 2011

Yes, Not exists used merge exlucsion join whereas left join used merge joi nand then excluded nulls later.Thanks for the help

eejimkos 73 posts Joined 01/12
18 Jun 2012

Hello,

 

Can i ask something on this?

 

if the queries are like :

 

**1

SEL A.*

FROM TABLE A

WHERE NOT EXISTS

(SEL 1

FROM TABLE B

WHERE

              A.KEY=B.KEY

       and A.KEY1=B.KEY1

)

 

  **2

SEL A.*

FROM TABLE A

LEFT JOIN TABLE B

ON   A.KEY=B.KEY

and  A.KEY1=B.KEY1

WHERE B.KEY IS NULL ;

 

 

Do they produce the same result?

 

Thank you very much,

tejasopalli 3 posts Joined 06/15
09 Oct 2015

Is Left outer join and not exists produce the same results?
I am pretty sure they give different results and if I am wrong please let me know.
 

teja sopalli

You must sign in to leave a comment.