Handle missing values

TutorialApplies to: Unified engine deployments

Understand NULL, supply defaults, and count missing values correctly.

Before you start

You need Workbench and a running SQL engine. Every example includes its own input rows. No catalog, files, credentials, or external data is needed. These queries create no stored tables. Copy the SQL into Workbench and run the statements in order. Compare the result with the table below the query. WITH names input data for one statement only. Each example can run on its own.

1. Find missing values

Which item has no label?

WITH items(id, label, amount) AS (VALUES (1, 'Books', 20), (2, CAST(NULL AS STRING), CAST(NULL AS INT)), (3, 'Games', 0), (4, 'Garden', 40))
SELECT id FROM items WHERE label IS NULL;

Expected result

id
2

Use IS NULL to test a missing value. Change one thing: Use IS NOT NULL. Expect IDs 1, 3, and 4.

2. Supply a label

How can we display an unknown label?

WITH items(id, label, amount) AS (VALUES (1, 'Books', 20), (2, CAST(NULL AS STRING), CAST(NULL AS INT)), (3, 'Games', 0), (4, 'Garden', 40))
SELECT id, COALESCE(label, 'Unknown') AS label FROM items ORDER BY id;

Expected result

id	label
1	Books
2	Unknown
3	Games
4	Garden

COALESCE returns the first non-null argument. Change one thing: Replace Unknown with Unassigned. Item 2 uses that label.

3. Count present values

How many amounts are present?

WITH items(id, label, amount) AS (VALUES (1, 'Books', 20), (2, CAST(NULL AS STRING), CAST(NULL AS INT)), (3, 'Games', 0), (4, 'Garden', 40))
SELECT COUNT(*) AS rows, COUNT(amount) AS present FROM items;

Expected result

rows	present
4	3

COUNT(*) counts rows. COUNT(column) skips nulls. Change one thing: Count label instead. The present count is still 3.

4. Turn zero into NULL

Which amounts remain when zero means missing?

WITH items(id, label, amount) AS (VALUES (1, 'Books', 20), (2, CAST(NULL AS STRING), CAST(NULL AS INT)), (3, 'Games', 0), (4, 'Garden', 40))
SELECT id, NULLIF(amount, 0) AS amount FROM items ORDER BY id;

Expected result

id	amount
1	20
2	NULL
3	NULL
4	40

NULLIF returns NULL when its arguments are equal. Change one thing: Use NULLIF(amount, 20). Item 1 becomes NULL and item 3 stays zero.

5. Filter nullable data

Which amounts are positive?

WITH items(id, label, amount) AS (VALUES (1, 'Books', 20), (2, CAST(NULL AS STRING), CAST(NULL AS INT)), (3, 'Games', 0), (4, 'Garden', 40))
SELECT id FROM items WHERE amount > 0 ORDER BY id;

Expected result

id
1
4

A null comparison is not true, so WHERE removes it. Change one thing: Use amount >= 0. Item 3 also appears.

6. Control null ordering

Can missing amounts appear last?

WITH items(id, label, amount) AS (VALUES (1, 'Books', 20), (2, CAST(NULL AS STRING), CAST(NULL AS INT)), (3, 'Games', 0), (4, 'Garden', 40))
SELECT id, amount FROM items ORDER BY amount ASC NULLS LAST, id;

Expected result

id	amount
3	0
1	20
4	40
2	NULL

NULLS LAST makes the position of missing values explicit. Change one thing: Use NULLS FIRST. Item 2 moves to the top.

Continue

Continue to Summarize data, or return to Learn Laminar.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Handle missing values

Before you start

1. Find missing values

2. Supply a label

3. Count present values

4. Turn zero into NULL

5. Filter nullable data

6. Control null ordering

Continue

​Before you start

​1. Find missing values

​2. Supply a label

​3. Count present values

​4. Turn zero into NULL

​5. Filter nullable data

​6. Control null ordering

​Continue

Before you start

1. Find missing values

2. Supply a label

3. Count present values

4. Turn zero into NULL

5. Filter nullable data

6. Control null ordering

Continue