sql 条件中判断字段值等于NULL

来源：互联网发布：中国网安知乎编辑：程序博客网时间：2024/05/21 11:36

数据库版本：

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

在应用开发中，有时候会碰到条件中有字段值为NULL情况，但是，在SQL中，NULL = NULL是不为TRUE的，所以比较前，需要将字段通过函数进行转值。

decode和nvl函数都可以，下面用decode函数做个演示：

SQL> select * from dept1;

    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        30 SALES          CHICAGO        40 OPERATIONS     BOSTON

SQL> update dept1 set loc=null where deptno=10;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dept1;

    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING        20 RESEARCH       DALLAS        30 SALES          CHICAGO        40 OPERATIONS     BOSTON

SQL> select * from dept1 where loc=null;

no rows selected

SQL> select * from dept1 where loc is null;

    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING

SQL> var str1 varchar2(10);SQL> exec :str1 := null;

PL/SQL procedure successfully completed.

SQL> select * from dept1 where decode(loc,:str1,1)=1;

    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING

SQL> exec :str1 := 'DALLAS';

PL/SQL procedure successfully completed.

SQL> select * from dept1 where decode(loc,:str1,1)=1;

    DEPTNO DNAME          LOC---------- -------------- -------------        20 RESEARCH       DALLAS