1

If there are 2 tables TableA and TableB. I want to find all the records of TableA which are not present in TableB, I can use Left join or where exists.

My question is that if there are 10 Million records in both the table which method will give better performance Left join or Where exists.?. I know Left join performs better But can someone tell me why?. I was asked this question in interview why where exists will perform slow than Left join when records are more.

sam
  • 1,242
  • 3
  • 12
  • 31
  • 1
    *"I know Left join performs better"* - Nope, best case scenario is they perform the same. `NOT EXISTS` will perform better because it can utilise an anti semi join, which means it will stop searching once a single match is found, whereas `LEFT JOIN` will return all records from TableB where there is a match, only to remove them later. This has been tested quite a bit on [SQL Performance](http://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join) and [Explain Extended](http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/) – GarethD Oct 23 '15 at 12:11
  • 2
    Also on [SQL In the wild](http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/), Or [this question](http://stackoverflow.com/q/6777910/1048425), or [this question](http://stackoverflow.com/q/2246772/1048425). Although the two questions tend to reference the 3 external articles anyway. Nonetheless all three arrive at the same conclusion, that `NOT EXISTS` will perform better. – GarethD Oct 23 '15 at 12:13
  • Maybe the point of their question was to confuse you and see if you'd veer off from your knowledge. I vouch for `NOT EXISTS` as well, based on the same posts @GarethD mentioned. – Radu Gheorghiu Oct 23 '15 at 12:13

0 Answers0