NULL Values

Learning Focus

Use this lesson to understand NULL Values with practical syntax and examples.

Concept Overview

Definition

NULL represents a missing or unknown value. It is not the same as 0, false, or an empty string (''). PostgreSQL uses three-valued logic: many comparisons involving NULL evaluate to NULL (unknown), not true or false.

This matters because NULL affects:

• filtering (WHERE)
• joins
• aggregates (COUNT, SUM, AVG)
• sorting (NULLS FIRST/LAST)

Why is it important?

• Correctness: mishandling NULL is a common source of “why is this row missing?” bugs
• Analytics accuracy: aggregates that ignore NULL can change metrics in subtle ways
• Schema design: deciding between NULL, defaults, and separate “status” columns impacts data quality

Where does it fit?

NULL handling shows up everywhere: SELECT, WHERE, JOIN, ORDER BY, constraints, and data-cleanup operations (UPDATE/DELETE).

---

Syntax & Rules

Core Syntax

-- Filter for NULL / NOT NULL
SELECT * FROM t WHERE col IS NULL;
SELECT * FROM t WHERE col IS NOT NULL;

-- Replace NULL with a fallback
SELECT COALESCE(col, 'fallback') FROM t;

-- Turn a sentinel value into NULL
SELECT NULLIF(col, '') FROM t;

-- NULL-safe equality/inequality
SELECT (a IS DISTINCT FROM b) AS differs;
SELECT (a IS NOT DISTINCT FROM b) AS same;

Available Options / Parameters

Feature	PostgreSQL form	Typical use
NULL checks	IS NULL, IS NOT NULL	correct filtering
Fallback value	COALESCE(a, b, ...)	display values, calculations
Normalize sentinel to NULL	NULLIF(a, b)	treat empty strings or 0 as missing
NULL-safe compare	IS DISTINCT FROM	change detection, diff queries
Sort control	ORDER BY col NULLS LAST	stable ordering and UX

Key Rules and Considerations

• Do not use = NULL or != NULL. Use IS NULL / IS NOT NULL.
• COUNT(col) ignores NULLs; COUNT(*) counts rows.
• NOT IN (...) behaves unexpectedly if the list contains NULL (often yields no rows). Prefer NOT EXISTS.
• Unique constraints allow multiple NULLs (NULL is not equal to NULL).
• Be explicit about sorting with NULLS FIRST / NULLS LAST when order matters.

---

Step-by-Step Examples

Example 1: Filter NULL Correctly (Beginner)

CREATE TABLE employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  manager_id  bigint
);

INSERT INTO employees (full_name, manager_id)
VALUES
  ('Ana Ng', NULL),
  ('Ben Ito', 1),
  ('Cora Li', 1),
  ('Drew Park', NULL);

SELECT employee_id, full_name
FROM employees
WHERE manager_id IS NULL
ORDER BY employee_id;

Expected output:

 employee_id | full_name
-------------+-----------
 1           | Ana Ng
 4           | Drew Park
(2 rows)

---

Example 2: NULL-Safe Comparisons with IS DISTINCT FROM (Intermediate)

IS DISTINCT FROM treats NULL as a comparable value for equality checks.

CREATE TABLE profile_changes (
  user_id        bigint PRIMARY KEY,
  old_phone      text,
  new_phone      text
);

INSERT INTO profile_changes (user_id, old_phone, new_phone)
VALUES
  (1, NULL, NULL),
  (2, NULL, '555-0102'),
  (3, '555-0103', NULL),
  (4, '555-0104', '555-0104');

SELECT user_id
FROM profile_changes
WHERE old_phone IS DISTINCT FROM new_phone
ORDER BY user_id;

Expected output:

 user_id
---------
 2
 3
(2 rows)

Explanation:

• row 1: NULL vs NULL is not distinct
• row 2: NULL vs value is distinct
• row 3: value vs NULL is distinct
• row 4: value vs same value is not distinct

---

Example 3: Aggregates, COALESCE, and Sorting NULLs (Advanced)

CREATE TABLE reviews (
  review_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id bigint NOT NULL,
  score integer
);

INSERT INTO reviews (product_id, score)
VALUES
  (10, 5),
  (10, NULL),
  (10, 3),
  (11, NULL);

-- COUNT(score) ignores NULL, COUNT(*) counts rows
SELECT
  product_id,
  COUNT(*) AS total_reviews,
  COUNT(score) AS scored_reviews,
  AVG(score) AS avg_scored_only,
  AVG(COALESCE(score, 0)) AS avg_missing_as_zero
FROM reviews
GROUP BY product_id
ORDER BY product_id;

-- Sorting with explicit NULL placement
SELECT product_id, score
FROM reviews
ORDER BY score DESC NULLS LAST, review_id;

Expected output (aggregates):

 product_id | total_reviews | scored_reviews | avg_scored_only | avg_missing_as_zero
------------+---------------+---------------+-----------------+---------------------
 10         | 3             | 2             | 4.0000000000000000 | 2.6666666666666667
 11         | 1             | 0             |                 | 0.0000000000000000
(2 rows)

Expected output (sorting):

 product_id | score
------------+-------
 10         | 5
 10         | 3
 10         |
 11         |
(4 rows)

Notice:

• AVG(score) returns NULL for product 11 because there are no non-NULL scores.
• NULLS LAST ensures NULL scores appear at the end.

---

Practical Use Cases

1) Soft delete

SELECT *
FROM users
WHERE deleted_at IS NULL;

2) Detect incomplete profiles

SELECT user_id, email
FROM user_profiles
WHERE phone IS NULL
   OR address_line1 IS NULL;

3) NULL-safe change detection for sync jobs

SELECT *
FROM staging_customers s
JOIN customers c ON c.customer_id = s.customer_id
WHERE c.name IS DISTINCT FROM s.name
   OR c.phone IS DISTINCT FROM s.phone;

4) Avoid NOT IN surprises

-- Prefer NOT EXISTS when NULLs are possible
SELECT u.user_id
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM banned_users b
  WHERE b.user_id = u.user_id
);

5) Sorting for UX

SELECT ticket_id, resolved_at
FROM support_tickets
ORDER BY resolved_at DESC NULLS LAST, ticket_id DESC
LIMIT 50;

---

Common Mistakes & Troubleshooting

1) Using = NULL (always wrong)

Wrong SQL:

SELECT * FROM employees WHERE manager_id = NULL;

Bad outcome:

• Returns 0 rows because the comparison is unknown.

Fix:

SELECT * FROM employees WHERE manager_id IS NULL;

---

2) col != 'x' unintentionally excludes NULLs

Wrong SQL:

SELECT * FROM users WHERE status != 'inactive';

Bad outcome:

• Rows with status IS NULL are not returned.

Fix (include NULLs explicitly if that is what you want):

SELECT *
FROM users
WHERE status != 'inactive'
   OR status IS NULL;

---

3) NOT IN with NULL behaves unexpectedly

Wrong SQL:

SELECT user_id
FROM users
WHERE user_id NOT IN (SELECT user_id FROM banned_users);

Bad outcome:

• If banned_users.user_id contains NULL, the predicate can evaluate to unknown for every row.

Fix:

SELECT u.user_id
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM banned_users b
  WHERE b.user_id = u.user_id
);

---

4) Misreading counts because COUNT(col) ignores NULL

Wrong SQL:

SELECT COUNT(last_login_at) FROM users;

Bad outcome:

• Counts only users with a non-NULL login timestamp.

Fix:

SELECT
  COUNT(*) AS total_users,
  COUNT(last_login_at) AS users_with_login
FROM users;

---

Debugging checklist:

1. Identify whether NULL means “unknown”, “missing”, or “not applicable” in your domain.
2. Replace = NULL / != NULL with IS NULL / IS NOT NULL.
3. For comparisons, consider IS DISTINCT FROM when NULL-safety matters.
4. For aggregates, decide whether missing should be ignored (default) or treated as a value (use COALESCE).
5. For ordering, be explicit with NULLS FIRST / NULLS LAST.

---

Best Practices

• ✅ Use NOT NULL constraints for required fields. ❌ Avoid leaving required columns nullable “just in case”.
• ✅ Use IS DISTINCT FROM for diff/change detection. ❌ Avoid complex (a <> b OR (a IS NULL AND b IS NOT NULL) ...) rewrites.
• ✅ Use COALESCE at the edges (presentation, controlled calculations). ❌ Avoid masking missing data too early in pipelines.
• ✅ Prefer NOT EXISTS over NOT IN when NULLs are possible. ❌ Avoid NOT IN (subquery) if the subquery can return NULL.
• ✅ Be explicit in ordering: NULLS FIRST/LAST. ❌ Avoid relying on default NULL placement when UX or pagination matters.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_tasks (
  task_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  completed_at timestamptz
);

INSERT INTO practice_tasks (title, completed_at)
VALUES
  ('Draft spec', NULL),
  ('Ship feature', '2026-02-01 00:00:00+00'),
  ('Write docs', NULL);

Exercise 1 (Easy): Find incomplete tasks

Task: Select tasks that are not completed.

-- Your SQL here

Solution:

SELECT task_id, title
FROM practice_tasks
WHERE completed_at IS NULL
ORDER BY task_id;

---

Exercise 2 (Medium): NULL-safe comparison

Task: Create a table of old/new values and return only rows where they differ (NULL-safe).

-- Your SQL here

Solution:

CREATE TABLE practice_diffs (
  id bigint PRIMARY KEY,
  old_value text,
  new_value text
);

INSERT INTO practice_diffs (id, old_value, new_value)
VALUES
  (1, NULL, NULL),
  (2, NULL, 'x'),
  (3, 'y', NULL),
  (4, 'z', 'z');

SELECT id
FROM practice_diffs
WHERE old_value IS DISTINCT FROM new_value
ORDER BY id;

---

Exercise 3 (Advanced): Count NULL vs non-NULL

Task: Count total tasks and completed tasks in one query.

-- Your SQL here

Solution:

SELECT
  COUNT(*) AS total_tasks,
  COUNT(completed_at) AS completed_tasks
FROM practice_tasks;

---

Connection to Other Concepts

Concept	Why it matters
WHERE	NULL filtering depends on IS NULL / IS NOT NULL
ORDER BY	use NULLS FIRST/LAST for predictable sorting
Aggregates	COUNT(col) and AVG behaviors change with NULLs
JOIN	outer joins often produce NULLs for missing matches
Constraints	NOT NULL and defaults reduce ambiguous missingness
UPDATE	you may set values to NULL or fill NULLs with backfills

---

Visual Learning Diagram

flowchart TD
  A[Table Design] --> B[NULL Allowed?]
  B --> C[Filtering: IS NULL]
  B --> D[Aggregates: COUNT(col)]
  B --> E[Sorting: NULLS LAST]
  B --> F[Comparisons: IS DISTINCT FROM]
  C --> G[Correct WHERE Logic]
  D --> H[Accurate Metrics]
  F --> I[Change Detection]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
= NULL comparisons	returns no rows unexpectedly	use IS NULL / IS NOT NULL
NOT IN with possible NULLs	query returns zero rows	use NOT EXISTS
Forgetting that COUNT(col) ignores NULL	incorrect totals	use COUNT(*) for row counts
Masking NULLs too early with COALESCE	hides missing data issues	keep raw NULLs until a deliberate conversion
Unspecified NULL ordering	inconsistent UX/pagination	use NULLS FIRST/LAST

---

Quick Reference

WHERE col IS NULL;
WHERE col IS NOT NULL;
SELECT COALESCE(col, 'fallback') FROM t;
SELECT NULLIF(col, '') FROM t;
SELECT (a IS DISTINCT FROM b) FROM t;

What's Next

• Previous: LIMIT - Review the previous lesson to reinforce context.
• Module Overview - Return to this module index and choose another related lesson.