In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.