NULL in ORACLE

May 12, 2020Posted inSQL

NULL in Oracle

NULL = NULL ?

Copy Code
SELECT CASE
          WHEN NULL = NULL THEN 'NULL Equals to NULL'
          ELSE 'NULL is NOT Equals to NULL'
       END NULL_
  FROM DUAL;

NULL_
NULL is NOT Equals to NULL

COUNT with NULLs

Copy Code
WITH tab
     AS (SELECT 10 a FROM DUAL
         UNION ALL
         SELECT NULL a FROM DUAL
         UNION ALL
         SELECT 2 a FROM DUAL)
SELECT COUNT (a)
  FROM tab;

COUNT(A)
2

SUM with NULLs

Copy Code
WITH tab
     AS (SELECT 10 a FROM DUAL
         UNION ALL
         SELECT NULL a FROM DUAL
         UNION ALL
         SELECT 2 a FROM DUAL)
SELECT SUM (a)
  FROM tab;

SUM(A)
12

LENGTH with NULLs

SELECT LENGTH (NULL) len, 'LENGTH' FROM DUAL;
LEN‘LENGTH’
LENGTH

Order by with NULLs

  • NULLS FIRST
Copy Code
WITH tab
     AS (SELECT 10 a, 'Demo 1' name FROM DUAL
         UNION ALL
         SELECT NULL a, 'Demo 2' name FROM DUAL
         UNION ALL
         SELECT 2 a, 'Demo 3' name FROM DUAL)
  SELECT a, name
    FROM tab
ORDER BY 1 NULLS FIRST;

ANAME
Demo 2
2Demo 3
10Demo 1
  • NULLS LAST
Copy Code
WITH tab
     AS (SELECT 10 a, 'Demo 1' name FROM DUAL
         UNION ALL
         SELECT NULL a, 'Demo 2' name FROM DUAL
         UNION ALL
         SELECT 2 a, 'Demo 3' name FROM DUAL)
  SELECT a, name
    FROM tab
ORDER BY 1 NULLS LAST;

ANAME
2Demo 3
10Demo 1
Demo 2

NULL Operators

  • IS NULL
Copy Code
WITH tab
     AS (SELECT 10 a, 'Demo 1' name FROM DUAL
         UNION ALL
         SELECT NULL a, 'Demo 2' name FROM DUAL
         UNION ALL
         SELECT 2 a, 'Demo 3' name FROM DUAL)
  SELECT a, name
    FROM tab WHERE a IS NULL;

ANAME
Demo 2
  • IS NOT NULL
Copy Code
WITH tab
     AS (SELECT 10 a, 'Demo 1' name FROM DUAL
         UNION ALL
         SELECT NULL a, 'Demo 2' name FROM DUAL
         UNION ALL
         SELECT 2 a, 'Demo 3' name FROM DUAL)
  SELECT a, name
    FROM tab WHERE a IS NOT NULL;

ANAME
10Demo 1
2Demo 3
Tags: