Over the last several months I’ve had a look at IN, Exists, Join and their opposites to see how they perform and whether there’s any truth in the advice that is often seen on forums and blogs advocating replacing one with the other.
Previous parts of this series can be found:
In this roundup post, I’m going to do multiple tests on the 6 query forms, with different numbers of rows, indexes, no indexes and, for the negative forms (NOT IN, NOT EXISTS), nullable and non-nullable join columns.
In the individual tests, I used 250000 rows in the first table and around 3000 rows in the secondary table. In this roundup, I’m going to use 3 different row counts, 1000000 rows, 100000 rows and 5000 rows. That should give a reasonable idea for performance at various table sizes. (Not much point in going smaller than 5000 rows. Everything’s fast on 100 rows)
Some notes on the tests.
- The version of SQL is SQL Server 2008 SP1 x64 Developer Edition.
- The tests were run on a laptop. Core-2 Duo, 3 GB memory. SQL limited to 1 processor, so no parallelism possible.
- Each query will be run 10 times, reads, cpu and duration measured by profiler and averaged.
- Each query will be run once before the tests start to ensure that the data is in cache and the execution plans are generated and cached.
- Reproduction scripts will be available for download.
Exists vs. In vs. Inner Join
First, no indexes on the join columns
| Table Size | Operator | CPU | Reads | Duration |
| Large | IN | 1293 | 14585 | 9649 |
| Exists | 1260 | 14585 | 9573 | |
| Inner Join | 1302 | 14585 | 9716 | |
| Medium | IN | 59 | 747 | 538 |
| Exists | 78 | 747 | 574 | |
| Inner Join | 69 | 747 | 523 | |
| Small | IN | 7 | 41 | 65 |
| Exists | 3 | 41 | 91 | |
| Inner Join | 4 | 41 | 65 |
Now with indexes on the join columns
| Table Size | Operator | CPU | Reads | Duration |
| Large | IN | 973 | 1760 | 9707 |
| Exists | 956 | 1760 | 9483 | |
| Inner Join | 1173 | 1760 | 9539 | |
| Medium | IN | 43 | 100 | 516 |
| Exists | 53 | 100 | 548 | |
| Inner Join | 59 | 100 | 498 | |
| Small | IN | 3 | 9 | 64 |
| Exists | 1 | 9 | 80 | |
| Inner Join | 4 | 9 | 67 |
Not Exists vs. Not In vs. Left Outer Join … Is Null
First test with the columns join columns nullable, no indexes
| Table Size | Operator | CPU | Reads | Duration |
| Large | NOT IN | 3194 | 2014622 | 3251 |
| NOT Exists | 820 | 14585 | 837 | |
| Outer Join | 962 | 14585 | 1025 | |
| Medium | NOT IN | 174 | 100765 | 217 |
| NOT Exists | 54 | 747 | 121 | |
| Outer Join | 53 | 747 | 79 | |
| Small | NOT IN | 12 | 5043 | 13 |
| NOT Exists | 4 | 41 | 6 | |
| Outer Join | 3 | 41 | 5 |
Then with join columns nullable with indexes
| Table Size | Operator | CPU | Reads | Duration |
| Large | NOT IN | 2677 | 2001762 | 2726 |
| NOT Exists | 569 | 1760 | 586 | |
| Outer Join | 949 | 1760 | 1029 | |
| Medium | NOT IN | 137 | 100102 | 164 |
| NOT Exists | 40 | 100 | 104 | |
| Outer Join | 48 | 100 | 69 | |
| Small | NOT IN | 11 | 5011 | 12 |
| NOT Exists | 3 | 9 | 4 | |
| Outer Join | 6 | 9 | 6 |
Now, let’s make the join columns not nullable. Again, no indexes to start with.
| Table Size | Operator | CPU | Reads | Duration |
| Large | NOT IN | 741 | 14585 | 753 |
| NOT Exists | 784 | 14585 | 790 | |
| Outer Join | 884 | 14585 | 937 | |
| Medium | NOT IN | 43 | 747 | 103 |
| NOT Exists | 49 | 747 | 120 | |
| Outer Join | 53 | 747 | 74 | |
| Small | NOT IN | 4 | 41 | 4 |
| NOT Exists | 1 | 41 | 5 | |
| Outer Join | 1 | 41 | 5 |
and finally, join columns not nullable, with indexes
| Table Size | Operator | CPU | Reads | Duration |
| Large | NOT IN | 578 | 1382 | 588 |
| NOT Exists | 585 | 1382 | 597 | |
| Outer Join | 953 | 1382 | 1006 | |
| Medium | NOT IN | 37 | 80 | 79 |
| NOT Exists | 34 | 80 | 79 | |
| Outer Join | 39 | 80 | 84 | |
| Small | NOT IN | 3 | 8 | 4 |
| NOT Exists | 1 | 8 | 5 | |
| Outer Join | 4 | 8 | 5 |
These results seem to pretty much confirm the earlier conclusions.
Exists and IN perform much the same, whether there are indexes on the join column or not. When there are indexes on the join columns, the INNER JOIN is slightly (very slightly) slower, which is more noticeable on the large tables, much less on the medium or small ones. (Note I’m mostly looking at CPU time, as the duration is also affected by sending of results to client, in this case, lots and lots of results)
When it comes to NOT In and NOT Exists they perform much the same when the columns involved are not nullable. If the columns are nullable, Not In is significantly slower because it has a different behaviour when nulls are present.
The join is slightly slower than Not Exists (or Not In on non-nullable columns), again only noticeable on the large table, probably because the optimiser has to do a full join with a secondary filter rather than the anti-semi join that it can use for Not Exists and Not In.
My conclusion from earlier posts stands. If all you are doing is looking for matching or non-matching rows and you don’t need any columns from the second table, use IN or Exists (or their negations), as appropriate for the situation. Only when you need columns from the second table should Join be used.
I think (and hope) that this adequately concludes the discussion on the Exists and In and joins, both behaviour and performance.
