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.
First, no indexes on the join columns
Now with indexes on the join columns
First test with the columns join columns nullable, no indexes
Then with join columns nullable with indexes
Now, let’s make the join columns not nullable. Again, no indexes to start with.
and finally, join columns not nullable, with indexes
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.