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

I previously wrote an article about the impact of NULL on in and not in results: the treatment when NULL is included in the where condition in/not in of Oracle. Today let's take a look at the effect of null values in exists and not exists on the results.

There are often examples of performance comparisons and swaps between in and exixts, not in and not exists, but can they really be easily interchangeable? Let's take a look at it through the following experiment.

Experimental environment: Oracle 11.2.0.4

1. Create a table and insert test data

Create table T1 (id number); create table T2 (id number); insert into T1 values (1); insert into T1 values (2); insert into T1 values (3); insert into T1 values (4); insert into T1 values (null); commit;insert into T2 values (3); insert into T2 values (4); insert into T2 values (5); insert into T2 values (6); commit;zx@ORA11G > select * from T1 ID- 1 2 3 45 rows selected.zx@ORA11G > select * from T2; ID- 3 45 64 rows selected.

The first case: the query of exists/in does not contain NULL, and the outer query contains NULL

Zx@ORA11G > select * from T1 where exists (select 1 from T2 where t1.id=t2.id), ID- 3 42 rows selected.zx@ORA11G > select * from T1 where id in (select id from T2), ID- 3 42 rows selected.

From the query results above, we can see that both exists and in have found two pieces of data for id=2 and 3.

The second case: the query of not exists/not in does not contain NULL, and the outer query contains NULL

Zx@ORA11G > select * from T1 where not exists (select 1 from T2 where t1.id=t2.id), ID- 1 23 rows selected.zx@ORA11G > select * from T1 where id not in (select id from T2), ID- 1 22 rows selected.

You can see from the above results that id=1 and 2 are found in both queries, but not exists also finds the row in the T1 table where id is NULL. The reason is that the row whose id is NULL in Table T1 is False, but if you add a not before it, the result will be True.

The third case: the subquery of exists/in contains NULL, and the outer query contains NULL

Zx@ORA11G > insert into T2 values (null), 1 row created.zx@ORA11G > commit;Commit complete.zx@ORA11G > select * from T1 where id in (select id from T2), ID- 3 42 rows selected.zx@ORA11G > select * from T1 where exists (select 1 from T2 where t1.id=t2.id), ID- 3 42 rows selected.

From the above results, we can see that the results of exist and in are consistent.

The fourth case: the query of not exists and not in contains NULL

Zx@ORA11G > select * from T1 where not exists (select 1 from T2 where t1.id=t2.id); ID- 1 23 rows selected.zx@ORA11G > select * from T1 where id not in (select id from T2); no rows selected

From the above query results, you can see that the two results are very different. Not exists checks out the values of id=1 and 2 and NULL, while the result of not in is empty. The reason why the no in result is empty can be found in the previous article, and the reason for not exists is similar to the second case.

The fifth case: there is no NULL value in the subquery of not in/not exists and no NULL value in the outer query.

Zx@ORA11G > delete from T1 where id is null;1 row deleted.zx@ORA11G > delete from T2 where id is null;1 row deleted.zx@ORA11G > commit;Commit complete.zx@ORA11G > select * from T1 where id not in (select id from T2), ID- 1 22 rows selected.zx@ORA11G > select * from T1 where not exists (select 1 from T2 where t1.id=t2.id), ID- 1 22 rows selected.

The sixth case: there is no NULL value in the subquery of in/exists and no NULL value in the outer query.

Zx@ORA11G > select * from T1 where id in (select id from T2), ID- 3 42 rows selected.zx@ORA11G > select * from T1 where exists (select 1 from T2 where t1.id=t2.id), ID- 3 42 rows selected.

The seventh case: null value in in/exists subquery and no NULL value in outer query.

Zx@ORA11G > insert into T2 values (null), 1 row created.zx@ORA11G > commit;Commit complete.zx@ORA11G > select * from T1 where id in (select id from T2), ID- 3 42 rows selected.zx@ORA11G > select * from T1 where exists (select 1 from T2 where t1.id=t2.id), ID- 3 42 rows selected.

Eighth case: null value in subquery of not in/not exists, no NULL value in outer query

Zx@ORA11G > select * from T1 where id not in (select id from T2), no rows selectedzx@ORA11G > select * from T1 where not exists (select 1 from T2 where t1.id=t2.id), ID- 1 22 rows selected.

From the above eight situations, we can sum up as follows:

1. In and exists can be converted to each other with or without NULL.

2. Not in and not exists can be converted to each other only when there is no null value.

Reference: https://mp.weixin.qq.com/s/rHKBFMQrrBf1TiUo6UmEmQ

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

Http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions012.htm#SQLRF52167

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.