SQL Server exists and in with exists and not in

1, exists and in

1.1 Under normal conditions the effect of exists and in is the same, the test

It doesn't matter if the subquery contains null, it still works

1.2 In and exists efficiency comparison

First Look in

By the figure can be 1 million of the data in the time is 14 seconds

The time spent with exist is also 14 seconds.

The rule of summary is that in and exists query efficiency is similar

2, not in and not exists

You can see that not in if there are nul in the subquery will not show any value.

Therefore, it is best to add the is not NULL to the subquery

It is shown by the graph and the restrictions are normal.

See NOT EXISTS again

You can see that the correct data can be found even if the subquery finds null

2.2 In terms of efficiency not in and no exists are also similar

SQL Server exists and in with exists and not in