????2???????????????????NOT?????????????<>????=??

???????????????????????????????NOT??<>?????????????????????????ú??????????? ??ORACLE????NOT???????????????????????????????????衣

????-->??Ч??

SELECT *
FROM   emp
WHERE  NOT ( deptno = 20 );   -->?????NOT ( deptno = 20 )?????deptno <> 20????deptno <>?????????????

????-->??Ч??

SELECT *
FROM   emp
WHERE  deptno > 20 OR deptno < 20;

????-->????????????滻??????????????????????????裬?????????? > ?? < ???????????????Χ???

?????????????????Щ??? ORACLE????????????NOT???????????????????

?????????????????????????NOT????????п?????????????? ????????NOT = ?????? NOT = ????? <>

“NOT >”   to <=
“NOT >=”  to <
“NOT <”   to >=
“NOT <=”  to >

???????????????????

hr@CNMMBO> SELECT *
  2  FROM   employees
  3  where not employee_id<100; -->?????????????not??????ò???????????е???????107????????????????????

107 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7276 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7276 |     3   (0)| 00:00:01 | -->??м????????????????跽?
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   
   1 - filter("EMPLOYEE_ID">=100)           -->???????ν?????????????? >= ?????

hr@CNMMBO> SELECT *
  2  FROM   employees
  3  where not employee_id<140; -->?????????????????????????????Χ???????67???????????????????????Χ???

67 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 603312277

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    68 |  4624 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |    68 |  4624 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_EMP_ID_PK |    68 |       |     1   (0)| 00:00:01 | -->??????Χ??跽?
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    2 - access("EMPLOYEE_ID">=140)

????3????UNION ?滻OR?????????????У?

???????????￡????UNION ?滻WHERE????е?OR??????????Ч??.?????????????OR??????????????????????裬???????????????

???????????????????????????????Ч?? ?????column??б??????? ???Ч?????????????????OR???????

????-->??Ч??

SELECT deptno?? dname
FROM   dept
WHERE  loc = 'DALLAS' OR deptno = 20;

????-->??Ч??

SELECT deptno?? dname
FROM   dept
WHERE  loc = 'DALLAS'
UNION
SELECT deptno?? dname
FROM   dept
WHERE  deptno = 30

????-->?????????????????????????where????е?ν??????????????????????????????

????-->???where????д???????

scott@CNMMBO> create table t6 as select object_id??owner??object_name from dba_objects where owner='SYS' and rownum<1001;
scott@CNMMBO> insert into t6 select object_id??owner??object_name from dba_objects where owner='SCOTT' and rownum<6;
scott@CNMMBO> create index i_t6_object_id on t6(object_id);
scott@CNMMBO> create index i_t6_owner on t6(owner);
scott@CNMMBO> insert into t6 select object_id??owner??object_name from dba_objects where owner='SYSTEM' and rownum<=300;
scott@CNMMBO> commit;
scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT'??'T6'??cascade=>true);
scott@CNMMBO> select owner??count(*) from t6 group by owner;

OWNER                  COUNT(*)
-------------------- ----------
SCOTT                         5
SYSTEM                      300
SYS                        1000

scott@CNMMBO> select * from t6 where owner='SCOTT' and rownum<2;

 OBJECT_ID OWNER                OBJECT_NAME
---------- -------------------- --------------------
     69450 SCOTT                T_TEST

scott@CNMMBO> select * from t6 where object_id=69450 or owner='SYSTEM';

301 rows selected.
               
Execution Plan
----------------------------------------------------------
Plan hash value: 238853296
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |   300 |  7200 |     5   (0)| 00:00:01 |
|   1 |  CONCATENATION               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T6             |     1 |    24 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I_T6_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T6             |   299 |  7176 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | I_T6_OWNER     |   300 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_ID"=69450)
   4 - filter(LNNVL("OBJECT_ID"=69450))
   5 - access("OWNER"='SYSTEM')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         46  consistent gets
          0  physical reads
          0  redo size
      11383  bytes sent via SQL*Net to client
        712  bytes received via SQL*Net from client
         22  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        301  rows processed

scott@CNMMBO> select * from t6 where owner='SYSTEM' or object_id=69450;

301 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 238853296
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |   300 |  7200 |     5   (0)| 00:00:01 |
|   1 |  CONCATENATION               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T6             |     1 |    24 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I_T6_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T6             |   299 |  7176 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | I_T6_OWNER     |   300 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------