NULL Basics

SQL · Reference cheat sheet

📋 Overview

NULL means unknown / missing, not the number zero and not an empty string. SQL uses three-valued logic (TRUE, FALSE, UNKNOWN), so comparisons with NULL need special operators.

🔧 Core concepts

Aggregates like COUNT(column) ignore nulls; COUNT(*) counts rows.

💡 Examples

Insert and test nulls:

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  due_date TEXT
);

INSERT INTO tasks (title, due_date) VALUES
  ('Write notes', '2026-07-12'),
  ('Review PR', NULL);

SELECT * FROM tasks WHERE due_date IS NULL;
SELECT * FROM tasks WHERE due_date IS NOT NULL;

Why = fails:

-- Usually returns no rows, even if due_date is NULL
SELECT * FROM tasks WHERE due_date = NULL;

COALESCE for defaults:

SELECT
  title,
  COALESCE(due_date, 'No due date') AS due_label
FROM tasks;

Counts:

SELECT COUNT(*) AS all_rows,
       COUNT(due_date) AS with_due_date
FROM tasks;

⚠️ Pitfalls

• NULL = NULL is not true — use IS NULL.
• Empty string '' is not NULL (unless your app treats them the same).
• NOT IN (SELECT ...) with nulls in the list can surprise you.
• Nullable foreign keys / optional fields are fine — document the meaning of “missing.”

🔗 Related

• tables_basics.md
• primary_keys_basics.md
• nulls.md
• getting_started.md