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