(以上图片均摘自网络)
我们可以看到在and真值表中and和null碰到一起时无非两种结果,一种是null,一种是false(false+null),也就是说在and中和null有关的运算都不会返回数据。我们来做以下实验:
实验表结构:
EMPNONOTNULLNUMBER(4)
ENAMEVARCHAR2(10)
JOBVARCHAR2(9)
MGRNUMBER(4)
HIREDATEDATE
SALNUMBER(7,2)
COMMNUMBER(7,2)
DEPTNONUMBER(2)
实验表数据:
SQL>set pages 30
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 0 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 0 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 0 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
and是同时满足,只有当两个条件都为true时才能返回结果
SQL> select * from scott.emp where sal>500 and job is null;
no rows selected
这条语句能返回结果吗?
SQL>select * from scott.emp where sal>500 and job is null;
norowsselected
因为job字段当中没有为null的值,所以jobisnull的结果为null,true+null的结果也就为null了。
我们再来看OR
OR运算的算法是只要一个条件为true就返回结果
上面那条语句我们将and改为or
SQL> select * from scott.emp where sal>500 or job is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 0 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 0 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 0 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
接着
SQL> select * from scott.emp where not (not mgr=null and mgr!=0);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 0 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 0 08-SEP-81 1500 0 30
7900 JAMES CLERK 0 03-DEC-81 950 30