SELECT 查询 NULL计算

NULL是无效、未指定、未知的值，不是0也不是空格；

SQL@itkaifang> SELECT empno,ename,sal,comm FROM emp;
     EMPNO ENAME      SAL              COMM
---------- ---------- ---------- ----------
      7369 SMITH      800
      7499 ALLEN     1600               300
      7521 WARD      1250               500
      7566 JONES     2975
      7654 MARTIN    1250              1400
      7698 BLAKE     2850
      7782 CLARK     2450
      7788 SCOTT     3000
      7839 KING      5000
      7844 TURNER    1500                 0
      7876 ADAMS     1100
      7900 JAMES      950
      7902 FORD      3000
      7934 MILLER    1300
14 rows selected.
SQL@itkaifang>

NULL值与任何值进行运算后，结果均为NULL；

例：工资加奖金

SQL@itkaifang> SELECT empno,ename,sal+comm FROM emp;
     EMPNO ENAME        SAL+COMM
---------- ---------- ----------
      7369 SMITH
      7499 ALLEN            1900
      7521 WARD             1750
      7566 JONES
      7654 MARTIN           2650
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER           1500
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
14 rows selected.
SQL@itkaifang>

使用NULL条件检索（IS NULL 或 IS NOT NULL)；

SQL@itkaifang> SELECT empno,ename,sal,comm FROM emp WHERE comm IS NULL;
     EMPNO ENAME             SAL       COMM
---------- ---------- ---------- ----------
      7369 SMITH             800
      7566 JONES            2975
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300
10 rows selected.
SQL@itkaifang> SELECT empno,ename,sal,comm FROM emp WHERE comm IS NOT NULL;
     EMPNO ENAME             SAL       COMM
---------- ---------- ---------- ----------
      7499 ALLEN            1600        300
      7521 WARD             1250        500
      7654 MARTIN           1250       1400
      7844 TURNER           1500          0
SQL@itkaifang>

使用NVL、NVL2解决NULL与值运算得NULL的问题；

NVL

语法：NVL（值1，值2）

原理：值1为NULL，则返回值2；值1不为NULL，则返回值1。（值1、2类型需相同）

例：工资加奖金

SQL@itkaifang> SELECT empno,ename,sal+NVL(comm,0) AS salary FROM emp;
     EMPNO ENAME          SALARY
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1900
      7521 WARD             1750
      7566 JONES            2975
      7654 MARTIN           2650
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300
14 rows selected.
SQL@itkaifang>

NVL2

语法：NVL2（值1，值2，值3）

原理：值1不为NULL，则返回值2；为NULL，则返回值3。（值2、3类型不同时，值3会转换为值2的类型）

例：如过奖金为NULL则加100奖金，奖金不为NULL则返回奖金数；

SQL@itkaifang> SELECT empno,ename,sal,NVL2(comm,comm,100) FROM emp;
     EMPNO ENAME             SAL NVL2(COMM,COMM,100)
---------- ---------- ---------- -------------------
      7369 SMITH             800                 100
      7499 ALLEN            1600                 300
      7521 WARD             1250                 500
      7566 JONES            2975                 100
      7654 MARTIN           1250                1400
      7698 BLAKE            2850                 100
      7782 CLARK            2450                 100
      7788 SCOTT            3000                 100
      7839 KING             5000                 100
      7844 TURNER           1500                   0
      7876 ADAMS            1100                 100
      7900 JAMES             950                 100
      7902 FORD             3000                 100
      7934 MILLER           1300                 100
14 rows selected.
SQL@itkaifang>

NULLIF

语法：NULLIF（值1，值)

值1，值2相等则返回NULL，否则返回值1；

SQL@itkaifang> SELECT NULLIF(1,1),NULLIF(2,1) FROM dual;
NULLIF(1,1) NULLIF(2,1)
----------- -----------
                      2
SQL@itkaifang>

LNNVL

语法：LNNVL（条件condition）

LNNVL<==>IS NULL OR IS NOT TRUE

例：1.表内容；

SQL@itkaifang> SELECT * FROM emp;
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20
      7839 KING       PRESIDENT            1981-11-17       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03        950                    30
      7902 FORD       ANALYST         7566 1981-12-03       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23       1300                    10
14 rows selected.
SQL@itkaifang>

2.查询奖金大于等于500或奖金为NULL的人；

SQL@itkaifang> SELECT empno,ename,sal,comm FROM emp WHERE comm>=500 OR comm IS NULL;
     EMPNO ENAME             SAL       COMM
---------- ---------- ---------- ----------
      7369 SMITH             800
      7521 WARD             1250        500
      7566 JONES            2975
      7654 MARTIN           1250       1400
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300
12 rows selected.
SQL@itkaifang>

3.利用LNNVL函数返回等同上述要求的结果。

SQL@itkaifang> SELECT empno,ename,sal,comm FROM emp WHERE LNNVL(comm<500);
     EMPNO ENAME             SAL       COMM
---------- ---------- ---------- ----------
      7369 SMITH             800
      7521 WARD             1250        500
      7566 JONES            2975
      7654 MARTIN           1250       1400
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300
12 rows selected.
SQL@itkaifang>

COALESCE

语法：COALESCE（值1，值2，值3，值4......）

第一个值非NULL值：如值1为NULL则显示值2，如值1、2都为NULL则显示值3，依此类推。

SQL@itkaifang> SELECT COALESCE(comm,0,1,2,3,4) AS bonus ,empno,ename,job FROM emp;
     BONUS      EMPNO ENAME      JOB
---------- ---------- ---------- ---------
         0       7369 SMITH      CLERK
       300       7499 ALLEN      SALESMAN
       500       7521 WARD       SALESMAN
         0       7566 JONES      MANAGER
      1400       7654 MARTIN     SALESMAN
         0       7698 BLAKE      MANAGER
         0       7782 CLARK      MANAGER
         0       7788 SCOTT      ANALYST
         0       7839 KING       PRESIDENT
         0       7844 TURNER     SALESMAN
         0       7876 ADAMS      CLERK
         0       7900 JAMES      CLERK
         0       7902 FORD       ANALYST
         0       7934 MILLER     CLERK
14 rows selected.
SQL@itkaifang>