0Pricing
SQL Academy · Lesson

What NULL Really Means

Unknown, not zero and not empty.

What NULL Really Means is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Meet NULL

NULL is SQL's way of saying "I don't know". It is not zero, not an empty string, and not false — it represents the absence of a value.

Whenever a column has no known value, the database stores NULL there. Learning how NULL behaves is one of the most important steps to writing correct queries.

-- A customers table where some phone numbers are unknown
SELECT id, name, phone
FROM customers;

-- id | name    | phone
-- ---+---------+------------
--  1 | Alice   | 555-0101
--  2 | Bob     | NULL        <- phone unknown
--  3 | Carol   | NULL

NULL Is Not Zero

A common mistake is treating NULL like the number 0. They are completely different.

  • 0 is a known value: zero.
  • NULL means the value is missing or unknown.

If an account balance is 0, the customer has no money. If it is NULL, we simply don't know the balance yet.

SELECT id, balance
FROM accounts;

-- id | balance
-- ---+--------
--  1 | 0        <- known: empty wallet
--  2 | NULL     <- unknown: not recorded yet

NULL Is Not an Empty String

For text columns, NULL and the empty string '' also differ.

  • '' is a string of length zero — a known, present value.
  • NULL means no string was provided at all.

You can store an empty note on purpose, or leave the note absent entirely. SQL tells them apart.

SELECT id, note, length(note) AS note_len
FROM tickets;

-- id | note | note_len
-- ---+------+----------
--  1 | ''   |        0   <- empty but present
--  2 | NULL |     NULL   <- absent; length is also unknown

Three-Valued Logic

Normal logic has two outcomes: true and false. SQL adds a third: unknown.

Any comparison involving NULL yields unknown, because you cannot compare against a value you don't have. This is called three-valued logic.

-- Each comparison with NULL returns UNKNOWN, not true/false
SELECT
  NULL = 5      AS eq,      -- unknown
  NULL <> 5     AS neq,     -- unknown
  NULL = NULL   AS self_eq, -- unknown (!)
  NULL > 0      AS gt;      -- unknown

NULL = NULL Is Not True

The most surprising rule: NULL = NULL is not true. It evaluates to unknown.

Why? If two values are both unknown, you can't say they are equal — they might be anything. To check whether something is NULL, you must use IS NULL instead of =.

-- Wrong: never returns rows, because NULL = NULL is unknown
SELECT * FROM customers WHERE phone = NULL;

-- Right: use IS NULL
SELECT * FROM customers WHERE phone IS NULL;

WHERE Keeps Only TRUE Rows

A WHERE clause keeps a row only when its condition is true. Rows where the condition is false or unknown are dropped.

This is why filtering on a NULL column with = silently removes rows you might expect to see.

-- Bob and Carol have NULL phones, so phone = '555-0101'
-- is UNKNOWN for them and they are excluded.
SELECT name
FROM customers
WHERE phone = '555-0101';

-- Returns only Alice

NULL Propagates Through Arithmetic

NULL is contagious in expressions. Almost any arithmetic or string operation involving NULL produces NULL.

If you don't know one of the inputs, you can't know the result either.

SELECT
  10 + NULL    AS sum,    -- NULL
  100 * NULL   AS prod,   -- NULL
  NULL / 2     AS div,    -- NULL
  'Hi ' || NULL AS greet; -- NULL (string concat)

Where NULLs Come From

NULLs appear in several common situations:

  • An INSERT that omits a nullable column.
  • An outer join with no matching row on one side.
  • An aggregate over zero rows (e.g. SUM of an empty set returns NULL).

Knowing the source helps you decide how to handle them.

-- Omitting phone inserts NULL automatically
INSERT INTO customers (id, name) VALUES (4, 'Dan');

SELECT id, name, phone FROM customers WHERE id = 4;
-- 4 | Dan | NULL

Preventing NULLs: NOT NULL

If a column should always have a value, declare it NOT NULL. The database then rejects any row that tries to leave it empty.

This is a powerful way to keep your data clean and avoid NULL surprises later.

CREATE TABLE users (
  id    integer PRIMARY KEY,
  email text NOT NULL,   -- must always be provided
  bio   text             -- nullable: optional
);

-- This fails: email cannot be NULL
INSERT INTO users (id, bio) VALUES (1, 'hi');

NULL and DISTINCT / GROUP BY

There is one place where SQL treats NULLs as equal: grouping and de-duplication.

In DISTINCT and GROUP BY, all NULL values are bundled into a single group, even though NULL = NULL is unknown elsewhere. This special case keeps grouping useful.

SELECT phone, count(*)
FROM customers
GROUP BY phone;

-- phone    | count
-- ---------+------
-- 555-0101 |     1
-- NULL     |     2   <- all NULLs grouped together

Mental Model Recap

Hold these rules in your head whenever you see a NULL:

  • NULL means unknown, not zero or empty.
  • Comparisons with NULL give unknown — use IS NULL / IS NOT NULL.
  • WHERE keeps only true rows.
  • Arithmetic with NULL yields NULL.
  • Grouping treats NULLs as one group.
-- The two correct NULL tests
SELECT name FROM customers WHERE phone IS NULL;
SELECT name FROM customers WHERE phone IS NOT NULL;

Quick Check

What does the expression NULL = NULL evaluate to in standard SQL?

Recap

You now understand what NULL really is: a marker for a missing or unknown value, distinct from 0 and ''.

You saw SQL's three-valued logic, why = NULL never works, how NULL propagates through arithmetic, and how WHERE drops unknown rows. Next, you'll learn the correct way to test for NULLs with IS NULL and IS NOT NULL.

-- Remember the golden rule
SELECT * FROM customers WHERE phone IS NULL;     -- correct
-- SELECT * FROM customers WHERE phone = NULL;   -- always empty!

Frequently asked questions

Is the “What NULL Really Means” lesson free?

Yes — the full text of “What NULL Really Means” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “What NULL Really Means”?

Unknown, not zero and not empty. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “What NULL Really Means” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. What NULL Really Means
  2. IS NULL and IS NOT NULL
  3. COALESCE and NULLIF
  4. NULLs in Aggregates and Joins
← Back to SQL Academy