53

If I want to find a set of entries in table A but not in table B, I can use either LEFT OUTER JOIN or NOT EXISTS. I've heard SQL Server is geared towards ANSI and in some case LEFT OUTER JOINs are far more efficient than NOT EXISTS. Will ANSI JOIN perform better in this case? and are join operators more efficient than NOT EXISTS in general on SQL Server?

kefeizhou
  • 5,926
  • 10
  • 40
  • 54

5 Answers5

72

Joe's link is a good starting point. Quassnoi covers this too.

In general, if your fields are properly indexed, OR if you expect to filter out more records (i.e. have a lots of rows EXIST in the subquery) NOT EXISTS will perform better.

EXISTS and NOT EXISTS both short circuit - as soon as a record matches the criteria it's either included or filtered out and the optimizer moves on to the next record.

LEFT JOIN will join ALL RECORDS regardless of whether they match or not, then filter out all non-matching records. If your tables are large and/or you have multiple JOIN criteria, this can be very very resource intensive.

I normally try to use NOT EXISTS and EXISTS where possible. For SQL Server, IN and NOT IN are semantically equivalent and may be easier to write. These are among the only operators you will find in SQL Server that are guaranteed to short circuit.

JNK
  • 60,688
  • 15
  • 118
  • 136
  • 3
    EXCEPT and INTERSECT are the same as (NOT) EXISTS too. And NOT IN fails with NULLs of course... – gbn Jul 21 '11 at 14:54
  • @gbn - didn't realize that. I normally only use those when a lot of fields are involved and it's harder to decipher in an `EXISTS` clause – JNK Jul 21 '11 at 14:55
  • Avoid `NOT IN`. At least when writing code or using an ORM, the number of elements in the "In list" is limited. See also: http://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join. – Jess Jul 17 '15 at 18:19
  • i Just have one concern. If i have 10000 matching record and use not exist in sub query. Dosen't this means my sub query will run 10000 time (i.e. for each record) and degrade the performance, on other left join will bring all the record at once and then filter 10000 matching record and give better performance – Neha Jain Nov 20 '15 at 10:36
  • 2
    **this answer is not correct anymore**. In SQL server 2017 (and later (and maybe even earlier)) both can shortcircuit. In most cases query optimizer correctly transforms `left join where is null` to `anti semi join`, just as it does with `not exists`. Sometimes each operators produces different plan. Sometimes one is faster than the other. **It depends.** – andowero May 30 '20 at 12:50
  • @andowero Please back that up, I get a left join plan on [SQLfiddle which is 2017](http://sqlfiddle.com/#!18/9eecb/113717/0) – Charlieface Mar 01 '21 at 20:55
  • @Charlieface As I have written - _It depends_. In your SQLfiddle the plan produced seems to be a "trivial plan" for that particular select, which the SQL Server deemed "good enough" since both table variables are assumed to contain 1 row (and they really do). As one SQL server senior programmer once said: "SQL Server has good optimizer, so two different queries producing identical datasets _tend to_ (emphasis on _tend_) have identical execution plans" – andowero Mar 03 '21 at 06:10
2

Personally, I think that this one gets a big old, "It Depends". I've seen instances where each method has outperformed the other.

Your best bet is to test both and see which performs better. If it's a situation where the tables will always be small and performance isn't as crucial then I'd just go with whichever is the clearest to you (that's usually NOT EXISTS for most people) and move on.

Tom H
  • 45,807
  • 14
  • 84
  • 124
  • It really depends, I just had two rewrite a query that was using not exists, and replaced `not exists` with `left outer join with null check`, yes it did perform much better. But always go for Not Exists, most of the time it will perform much better,and the intent is clearer when using `Not Exists`. – hazimdikenli Apr 22 '15 at 06:58
0

This blog entry gives examples of various ways ( NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT and NOT EXISTS ) to achieve same results and proves that Not Exists ( Left Anti Semi Join) is the best options in both cold cache and warm cache scenarios.

N30
  • 3,173
  • 4
  • 32
  • 43
0

I've been wondering how we can use the index on the table we are deleting from in these cases that the OP describes.

Say we have:

 table EMPLOYEE (emp_id int, name varchar) 
and
 table EMPLOYEE_LOCATION (emp_id int, loc_id int)

In my real world example my tables are much wider and contain 1million + rows, I have simplified the schema for example purpose.

If I want to delete the rows from EMPLOYEE_LOCATION that don't have corresponding emp_id's in EMPLOYEE I can obviously use the Left outer technique or the NOT IN but I was wondering...

If both tables have indexes with leading column of emp_id then would it be worthwhile trying to use them?

Perhaps I could pull the emp_id's from EMPLOYEE, the emp_id's from EMPLOYEE_LOCATION into a temp table and get the emp_id's from the temp tables that I want to delete.

I could then cycle round these emp_id's and actually use the index like so:

loop for each emp_id X to delete -- (this would be a cursor)
 DELETE EMPLOYEE_LOCATION WHERE emp_id = X

I know there is overhead with the cursor but in my real example I am dealing with huge tables so I think explicitly using the index is desirable.

user3257560
  • 21
  • 1
0

Answer on dba.stackexchange

An exception I've noticed to the NOT EXISTS being superior (however marginally) to LEFT JOIN ... WHERE IS NULL is when using Linked Servers.

From examining the execution plans, it appears that NOT EXISTS operator gets executed in a nested loop fashion. Whereby it is executed on a per row basis (which I suppose makes sense).

Example execution plan demonstrating this behaviour: enter image description here

pim
  • 11,125
  • 5
  • 65
  • 62