> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Handle missing values

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

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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/learn/summarize-data.md), or return to [Learn Laminar](https://docs.e6data.com/transformation/learn.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.