NULL值处理

处理NULL值

 

    --NULL值从何而来？

   

        --1.表中的字段没有值，则其值视为NULL

        --2.表达式计算的结果

        --3.子查询的结果

       

    --1.含有NULL值的表达式

        --1）NULL参与算术运算

       

            --例:

                SELECT employee_id,first_name,last_name,salary+salary*commission_pctAS

                income

                FROM employees;

                --NULL值参与算术运算，表达式的结果一定为NULL

               

        --2)NULL值参与比较运算

           

            --例1:

                SELECTemployee_id,first_name,last_name,salary

                FROM employees

                WHERE commission_pct= NULL;

                -----------------------------------------

                SELECTemployee_id,first_name,last_name,salary

                FROM employees

                WHERE commission_pct!= NULL;

               

                --如果NULL值参与比较运算，结果一定为NULL

                --因为NULL值得存在，SQL中的逻辑成为了“3种逻辑”：TRUE/FALSE/NULL

               

            --例2：

                SELECTemployee_id,first_name,last_name,salary

                FROM employees

                WHERE commission_pctIS NULL;

                -------------------------------

                SELECTemployee_id,first_name,last_name,salary

                FROM employees

                WHERE commission_pctIS NOT NULL;

               

        --3)NULL值参与逻辑运算

       

            --例1：

                SELECTemployee_id,first_name,last_name,job_id

                FROM employees

                WHERE job_id ='IT_PROG' AND job_id != NULL;

                --逻辑AND取值规律：FALSE-------------NULL------------>TRUE

            --例2：

                SELECTemployee_id,first_name,last_name,job_id

                FROMemployees

                WHERE job_id ='IT_PROG' OR job_id != NULL;

                --逻辑OR取值规律：TRUE------------->NULL------>false

               

            --例3：

                SELECTemployee_id,first_name,last_name,job_id

                FROM employees

                WHERE NOT job_id !=NULL;

                --逻辑NOT取值规律：NOT NULL ==NULL

               

        --4)ORDER BY子句中的NULL

       

            --例1：

                SELECTemployee_id,first_name,last_name,commission_pct

                FROM employees

                ORDER BYcommission_pct;

                --升序排列时，NULL值在后

               

                SELECTemployee_id,first_name,last_name,commission_pct

                FROM employees

                ORDER BYcommission_pct NULLS FIRST;--NULLS  LAST

    --2.NVL和NVL2

        --例1：

                SELECTemployee_id,first_name,last_name,salary+salary*NVL(commission_pct,0) AS

                income

                FROM employees;

               

        --例2：

            SELECTemployee_id,first_name,last_name,salary+salary*NVL(commission_pct,commission_pct,0)AS

                income

                FROM employees;

               

    --3.NULLIF 如果两个值相等就返回NULL

        --例：

            SELECTemployee_id,first_name,last_name,NVL(NULLIF(salary,17000),7000) AS nsalary

            FROM employees;

            --语法NULLIF(exp1,exp2)

            --如果1和2相等，则返回NUL，否则返回1

            --注意：参数的类型必须一致

           

    --4。COALESCE

   

        --例1：

                SELECTemployee_id,first_name,last_name,salary+salary*COALESCE(commission_pct,0) AS

                income

                FROM employees;

                --可以接受N个参数，返回第一个部位NULL的参数

                --参数的类型必须一致