Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Processing when NULL is included in Oracle's where condition in/not in

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

We often use the in condition when writing SQL. If in contains non-null values, then there is nothing special, but if the values in in contain null values (for example, in is followed by a subquery, and the result returned by the subquery has a NULL value), what will Oracle do?

Create a test table t_in

Zx@TEST > create table t_in (id number); Table created.zx@TEST > insert into t_in values (1); 1 row created.zx@TEST > insert into t_in values (2); 1 row created.zx@TEST > insert into t_in values (3); 1 row created.zx@TEST > insert into t_in values (null); 1 row created.zx@TEST > insert into t_in values (4); 1 row created.zx@TEST > commit;Commit complete.zx@TEST > select * from t_in ID- 1 2 3 4

Now there are five records in the tin table.

1. Cases where NULL is not included in the in condition

Zx@TEST > select * from t_in where id in (1Magol 3), ID- 1.32 rows selected.

The above condition is equivalent to id = 1 or id = 3, and the result is exactly 2; looking at the execution plan, you can see that 2-filter ("ID" = 1 OR "ID" = 3) shows that our previous guess is correct.

2. Case where the in condition contains NULL

Zx@TEST > select * from t_in where id in, ID- 1 32 rows selected.

The above condition is equivalent to id = 1 or id = 3 or id = null. Let's take a look at the following figure how Oracle handles the id = null condition.

As you can see from the figure above, the result of id = NULL is UNKNOWN, which is also equivalent to FALSE, regardless of whether the id value is null or non-null. So the above results only found 1 and 3 records.

Check the execution plan to see the optimizer's rewriting of IN

3. The null value is not included in the not in condition.

Zx@TEST > select * from t_in where id not in (1MJ 3), ID- 242 rows selected.

The where condition queried above is equivalent to id! = 1 and id! = 3. In addition, there is a behavior null in the tin table. Although it satisfies! = 1 and! = 3, according to the above rules, the result of comparing NULL with other values is UNKNOWN, so only 2 and 4 are found.

We can see the optimizer's rewriting of IN from the execution plan.

4. Cases where null value is included in the not in condition

Zx@TEST > select * from t_in where id not in (1pm 3pm null); no rows selected

The where condition queried above is equivalent to idled conditions 1 and idled values 3 and invalid null. According to the above rules, the result of comparing NULL with other values is UNKNOWN, so the whole condition is equivalent to FALSE, and no data is found in the end.

View the optimizer's rewriting of IN from the execution plan

To sum up, rows with null values in the target column are never found when using in as a condition, and no results are returned if the not in condition contains null values, including subqueries in in. Therefore, in the actual work, we must pay attention to whether the subqueries contained in not in contain null values.

Zx@TEST > select * from t_in where id not in (select id from t_in where id = 1 or id is null); no rows selected

Official document: http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements005.htm#SQLRF51096

Http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions013.htm#SQLRF52169

Http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions004.htm#SQLRF52116

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report