探索where x=x 和 where x = y
先创建如下的一张表，再往表中插入一些测试用的数据。
create table occupancy (
pet_id number not null primary key,
name varchar2(30) not null,
room_number varchar2(30) not null,
occupied_dt date,
checkout_date date );
SQL> select count(*) from occupancy;
COUNT(*)
----------
9
再来创建如下的一张表，此处的where 1= 2 是false 因此
occupancy_history 表会根据select 语句选中的列的定义
创建。
create table occupancy_history as
select pet_id,
name,
checkout_date
from occupancy
where 1 = 2;
SQL> select count(*) from occupancy_history;
COUNT(*)
----------
0
如果把上面的创建语句修改成下面的样子运行也是会创建相应的表的。
不过这时候会根据select 语句从表occupancy 中选择数据插入新建的
表中。where 1= 1 是 true。
create table occupancy_history01 as
select pet_id,
name,
checkout_date
from occupancy
where 1 = 1;
SQL> select count(*) from occupancy_history01;
COUNT(*)
----------
9
根据上面的测试似乎我们可以得出这样的结论：
当where x=x （ture） 的时候才会根据select 语句查询指定的表中的列。
当where x=y (false)的时候只会查询select 语句中指定的列的定义返回0 行。
之所以搞的那么纠结，和oracle sql 语句不支持boolean 数据类型有关。
不然直接一个ture或者false 不是很和谐吗?
再来继续验证这种判断。where 2 = 2 是 ture.
SQL> create table occupancy_history02 as
2 select pet_id,
3 name,
4 checkout_date
5 from occupancy
6 where 2 = 2;
Table created.
SQL> select count(*) from occupancy_history02;
COUNT(*)
----------
9
在下面的语句中 where 3 = 2 is false.
SQL> create table occupancy_history03 as
2 select pet_id,
3 name,
4 checkout_date
5 from occupancy
6 where 3 = 2;
Table created.
SQL> select count(*) from occupancy_history03;
COUNT(*)
----------
0
再来深入一点本质的东西。
SQL> select pet_id,
2 name,
3 checkout_date
4 from occupancy
5 where 3 = 2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3014914507
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| OCCUPANCY | 9 | 351 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
我们来看看那个过滤条件中的null is not null 是啥东东。null is not null 一下子
就把人搞晕了。
SQL> begin
2 if null is not null
3 then
4 dbms_output.put_line('null is not null equal true');
5 else
6 dbms_output.put_line('null is not null equal false');
7 end if;
8 end;
9 /
null is not null equal false
PL/SQL procedure successfully completed.
通过执行上面的plsql 语句我们可以判断出oracle 中的null is not null 就是等于
逻辑判断上的 false.再来看看where x=x 的情形。
SQL> select pet_id,
2 name,
3 checkout_date
4 from occupancy
5 where 2 = 2;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3314582639
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 351 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| OCCUPANCY | 9 | 351 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
712 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
我们把where 子句去掉，执行的结果是一样的，并且plan hash value 的值也是一样的
因此我们可以认为这两条语句是相同的，并且那个where x=x 是多余的。
SQL> select pet_id,
2 name,
3 checkout_date
4 from occupancy;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3314582639
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 351 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| OCCUPANCY | 9 | 351 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
712 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed