Comparison between exists and in

Exists:

This predicate returns a row based on whether the subquery returns the row. True Or False Different from other predicates and logical expressions, no matter whether the input subquery returns rows, Exists Will not return Unknown . If the filter of A subquery is a row Unknown , The row is not returned. In the filter, Unknown Is considered to be similar False In other words, when the input subquery contains a filter, as long as the filter is at least one row True , Then Exists Returns True .

 

In Exists In the subquery of the predicate, Exists The optimizer will ignore Select List.

 

In versions earlier than SQL Server 2000, there are actually differences between the two. The optimizer will generate different execution plans for them, and the exists has better performance because it has a short circuit function. in SQL Server 2000 and later versions, the optimizer usually generates the same plan for two logically equivalent queries.

We are aware that exists and in are different. The controversy lies in SQL's three-value Logic (true, false, unknown). Unlike exists, when the input list contains null, in actually produces an unknown logical result. for example:The result of In (B, c, null) is unknown.However, because the unknown and false methods in the filter are similar, the results of queries using in and exists are the same, so the optimizer will generate the same execution plan.
 

Next, let's compare whether not exists and not in are the same? First, let's look at the situation of not exists:
 
1. Select customer, customer_description
2. From DBO. Customers as C
3. Where Country = 'China'
4. And not exists
5 .(
6. Select * from orders as O
7. Where o. Customer = C. Customer
8 .)
 

Assume that the orders table contains an order with the customer being null, but it has nothing to do with us. The query will still get all consumers from China who have no orders.
This plan scans the MERs table and filters consumers from China. For each matching consumer, this plan performs a search for the index on orders. Customer.TopOperator, because you only need to determine whether at least one order matches the consumer. This is exists'sShort Circuit Function. When orders. the density of the customer column is relatively large (that is, it contains a large number of duplicates), and the use of top is particularly efficient. each consumer only performs a single search (seek) and only scans a row at the leaf level (the bottom layer of the index) to find one and not all matches.
 

If not in is used to solve the same problem, you may write the SQL statement as follows:

1. Select customer, customer_description
2. From DBO. Customers as C
3. Where Country = 'China'
4. and customer not in (select customer from DBO. Orders)

If you try to insert a record whose customer is null in the orders table, you will find that the not in statement returns an empty set becauseWhen the orders. Customer column contains null, The in query will never return false !!, Returns true and unknown, so not in returns not true or not unknown, not true (This sentence is the key ).
The following is an example of the preceding example. If the orders list is (a, B, null), a in (a, B, null), true is returned, then a not in (a, B, null) returns not true, that is, false, so query does not return a. Then, when C in (a, B, null, if the logical result is unknown, C not in (a, B, null) returns not unknown or unknown. therefore, C does not return values, that is, no matter what the value in the MERs table is, As long as the customer in the orders table has a null value, this query will not actually return any results . Therefore, not exists and not in are not logically equivalent. Therefore, their execution plans are different and the returned results are different.
In this case, we only need to add a filter in the not in subquery to exclude null values. For example, in the preceding SQL statement, select customer from DBO. orders where customer is not null.
Of course, in practice, it is impossible for everyone to allow the customer column in the orders table to be null, When using not in, make sure that the subquery does not have a null value. !!

 

This article is from the csdn blog. For more information, see