Shulou(Shulou.com)05/31 Report--

This article mainly explains why the subquery after NOT IN can not contain NULL value. Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn why subqueries after NOT IN can't contain null values.

If null values may be part of the results of a subquery, they should not be used with the NOT IN operator in the main query. Why? Just take a look at the following example.

The subquery after NOT IN has a record of NULL, but the main query cannot find a record.

Select * from emp where 3 not in (1,2, null)

No rows selected

Because this query can be understood as

Select * from emp where 3 1 and 3 2 and 3 null

Because the null value cannot participate in the comparison operator, the condition is not valid, so the data cannot be queried.

If it is an IN operation, the data can be found.

Select * from emp where 1 in (1,2, null)

Because the query can be understood as

Select * from emp where 1 = 1 or 1 = 2 or 1 = null

Although the NULL value also cannot be compared, or the short-circuit effect of the operation makes the whole condition hold when the previous condition is established, so the result is output.

At this point, I believe you have a deeper understanding of why the subquery after NOT IN can not contain NULL values, so you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.