Project:M36 Blog - Are You Qualified To Use NULL in SQL?

Boolean with NULL

SELECT NULL AND TRUE;

NULL FALSE

Boolean with NULL 2

SELECT NULL AND FALSE;

FALSE NULL

SUM with NULL

SELECT SUM(a) FROM (VALUES (3),(NULL)) as x(a);

3 NULL

COUNT with NULL

SELECT COUNT(a) FROM (VALUES (3),(NULL)) as x(a);

1 NULL

NULL equality

SELECT * FROM (VALUES (3),(NULL)) as x(a) WHERE a = NULL;

(0 rows) (1 row)

IS NULL

SELECT * FROM (VALUES (3),(NULL)) as x(a) WHERE a IS NULL;

(0 rows) (1 row)

Difficulty: Level 2

OUTER JOIN with NULL

SELECT * FROM (VALUES (3),(NULL)) as x(a) LEFT OUTER JOIN (VALUES (3),(NULL)) as y(a) ON x.a = y.a;

(2 rows) (1 row)

SUM of column with NULL

SELECT SUM(a) FROM (VALUES (NULL::int)) AS x(a);

NULL 0

SUM of an empty table

SELECT SUM(a) FROM (VALUES (1),(2)) as x(a) WHERE false;

0 NULL

AVG with NULL

SELECT AVG(a) FROM (VALUES (3),(NULL)) as x(a);

NULL 3.0

NULL with IN

SELECT * FROM (VALUES (3),(NULL)) as x(a) WHERE a IN (NULL,3);

(1 row) (2 rows)

NULL with NOT IN

SELECT * FROM (VALUES (3),(NULL)) as x(a) WHERE a NOT IN (1);

(1 row) (2 rows)

NULL with CONCAT

SELECT CONCAT('uh oh', NULL);

uh oh NULL

NULL with ||

SELECT 'uh oh' || NULL;

uh oh NULL

Difficulty: Level 3

NULL in CHECK constraint

CREATE TABLE test(a INTEGER CHECK(a>0)); INSERT INTO test(a) VALUES (NULL);

Constraint error, INSERT rejected No error, INSERT succeeds

NULL with DISTINCT

SELECT DISTINCT(a) FROM (VALUES (3),(NULL),(NULL)) as x(a);

2 rows 1 row

NULL with GROUP BY

SELECT a FROM (VALUES (3),(NULL),(NULL)) as x(a) GROUP BY a;

2 rows 1 row

NULL with GROUP BY + CASE

SELECT CASE WHEN a IS NULL THEN 5 ELSE a END FROM (VALUES (3),(NULL),(NULL)) as x(a) GROUP BY a;

3,5 3,NULL

NULL WITH GROUP BY + NULL equality + CASE

SELECT CASE WHEN a = NULL THEN 5 ELSE a END FROM (VALUES (3),(NULL),(NULL)) as x(a) GROUP BY a;

3,NULL 3,5

NULL with UNIQUE constraint

CREATE TABLE y(a INTEGER, UNIQUE(a));
	INSERT INTO y(a) VALUES (NULL),(NULL),(3);
	table y;

NULL,NULL,3 NULL,3

NULL with EXISTS

SELECT 5 WHERE EXISTS(SELECT NULL);

(1 row) (0 rows)

Typed NULL

SELECT CAST(NULL AS INTEGER) IS DISTINCT FROM CAST(NULL AS TEXT);

False type error

Are You Qualified To Use Null in SQL?

Difficulty: Level 1