NULL in ORACLE

Table Of Contents

NULL in Oracle

NULL = NULL ?

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

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

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
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;
A NAME
Demo 2
2 Demo 3
10 Demo 1
  • NULLS LAST
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;
A NAME
2 Demo 3
10 Demo 1
Demo 2

NULL Operators

  • IS NULL
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;
A NAME
Demo 2
  • IS NOT NULL
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;
A NAME
10 Demo 1
2 Demo 3

comments powered by Disqus