Not Exists vs Left Join

Database
N/A

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 ;

Tags (2)
4 REPLIES
N/A

Re: Not Exists vs Left Join

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

N/A

Re: Not Exists vs Left Join

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

Re: Not Exists vs Left Join

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,

Re: Not Exists vs Left Join

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.