SQL "in" vs Join for delete [duplicate]

Question

I have another programmer who wrote a bunch of delete statements that look like this:

DELETE dbo.Test WHERE TestId IN (SELECT TestId FROM #Tests )

(This one is simple but there are others with sub and sub-sub in statements like this)

I always write those kinds of statements as a join. It seems to me that this is like having an in-line function that will be called over and over.

However, I know the optimizer is capable of some serious magic, and new things are added all the time. I have not researched the difference between Join vs In for a while and I thought I would ask if it is still something that should be a join.

Does it matter if you use "join" or "in"?

Answer 1

Most modern SQL optimizers will figure out a join from a clause like this, but it's not guaranteed, and the more complex the query gets, the less likely the optimizer will choose the proper action.

As a general rule, using IN in this sort of scenario is not a good practice. (personal opinion warning) It's really not meant to be used that way.

A good rule of thumb (again, this is debatable but not wrong) is, for using IN, stick to finite lists. For example:

SELECT DISTINCT * FROM foo WHERE id IN (1, 2, 3, ...);

When going against another table, one of these is preferable:

SELECT DISTINCT f.* FROM foo AS f 
INNER JOIN bar as b on b.foo_id = f.id;

SELECT DISTINCT * FROM foo AS f
WHERE EXISTS (SELECT NULL FROM bar AS b WHERE b.foo_id = f.id);

Depending on what you are doing, and the nature of your data, your mileage will vary with these.

Note that in this simple example, the IN, the JOIN, and the EXISTS will very likely produce exactly the same query plan. When you start getting into some serious business logic against multiple tables, however, you may find the query plans significantly diverge.

Answer 2

There are three ways we can look at code. Does it functionally work? Does it provide good code maintenance/read-ability? And does it perform well?

Functionally speaking, there is no difference between writing the IN clause or using the join, if both preform the same operation.

From a maintenance/read-ability aspect, one could argue that in the simple cases the join syntax would be straightforward. However, if the sub-query used within the IN clause was a complex multi-join operation, then that may be more descriptive and easier to debug at a later time (put yourself in the shoes of the person who has to look at the code with limited context.)

Finally, from a performance perspective, this would depend on the number of rows in the tables, indexes available (including their statistics), and how the cost based optimizer handles the query ( which may vary depending on the SQL version) as to which would perform better.

So as with most decisions in the IT field, the real answer is … it depends.