NULL 사용법

 

1. NULL != 공집합

            != SPACE

            = NULL -- 결과: NULL

           산술연산 -- 결과: NULL

           비교연산 -- 결과: NULL

           논리연산: 진리표 확인

 

SQL>  select empno, sal, comm, comm             from emp;
SQL>  select empno, sal, comm, comm+100         from emp;
SQL>  select empno, sal, comm, nvl(comm, 0)+100 from emp;

SQL>  select empno, sal, comm, sal*12+comm         from emp;
SQL>  select empno, sal, comm, sal*12+nvl(comm, 0) from emp;

 

SQL>  select sal*12+nvl(comm, 0)    Ann_Sal,
         sal*12+nvl(comm, 0) as Ann_Sal,
         sal*12+nvl(comm, 0)   "\Ann Sal"
  from emp;

 

SQL> select * from emp where comm = null;                      -- 엉터리
SQL> select * from emp where comm is null;                     -- 제대로

SQL> select * from emp where deptno = 30 and comm = null;      -- 엉터리
SQL> select * from emp where deptno = 30 or  comm = null;      -- 엉터리
 

SQL> select * from emp where deptno = 30 and comm is null;     -- 제대로
SQL> select * from emp where deptno = 30 or  comm is null;     -- 제대로

 

2. IS NULL (IS NOT NULL)

SQL> select * from emp where comm = null;     -- 엉터리
SQL> select * from emp where comm is null;    -- 제대로
SQL> select * from emp where not(comm is null);