0Pricing
SQL Interview Prep · Lesson

Three-Valued Logic and UNKNOWN

Why NULL = NULL is not true and how UNKNOWN propagates through conditions.

Three-Valued Logic and UNKNOWN is a free SQL Interview Prep 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 Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why NULL Trips Up Candidates

NULL is the number one source of wrong answers in SQL interviews. The trap is treating it like a normal value, when in reality NULL means 'unknown' or 'missing', not zero and not an empty string.

Interviewers love this because the syntax looks correct but the result is silently wrong. They might show you a filter that 'should' return a row and ask why it returns nothing.

In this lesson you will build the mental model that defuses every NULL question: three-valued logic. Once you internalize that comparisons can return TRUE, FALSE, or UNKNOWN, the rest follows.

NULL Is Not a Value

The single most important sentence to say in an interview: NULL is the absence of a value, not a value itself.

That means you cannot compare it with = the way you compare numbers. The database has no idea whether two unknowns are equal, so it refuses to commit to TRUE or FALSE.

  • NULL = 5 is not FALSE, it is UNKNOWN
  • NULL = NULL is not TRUE, it is UNKNOWN
  • NULL <> NULL is also UNKNOWN

This is why a naive equality filter on a nullable column quietly drops rows.

Two-Valued vs Three-Valued Logic

Most programming languages use two-valued logic: an expression is either TRUE or FALSE. SQL adds a third outcome, UNKNOWN, whenever a NULL is involved in a comparison.

So any predicate in SQL can evaluate to one of three results: TRUE, FALSE, or UNKNOWN. The WHERE clause keeps a row only when its predicate is exactly TRUE. UNKNOWN behaves like FALSE for filtering, but it is not the same thing logically.

Interviewers test whether you know this distinction, because UNKNOWN behaves differently under NOT than FALSE does.

A Filter That Silently Drops Rows

Here is the classic worked example. Suppose bonus is sometimes NULL. A recruiter asks: 'This query should return everyone whose bonus is not 1000. Why does it skip employees with no bonus?'

For a row where bonus is NULL, bonus <> 1000 evaluates to UNKNOWN, not TRUE. WHERE keeps only TRUE rows, so those employees disappear.

The fix is to explicitly handle NULL, which we cover in the next lesson. For now, recognize that the missing rows are a logic outcome, not a bug.

SELECT name, bonus
FROM employees
WHERE bonus <> 1000;
-- Rows where bonus IS NULL are excluded:
-- NULL <> 1000 evaluates to UNKNOWN, not TRUE

NULL in AND Expressions

Three-valued logic changes how AND behaves. Memorize the rule and you can answer any truth-table question on the spot.

  • TRUE AND UNKNOWN = UNKNOWN
  • FALSE AND UNKNOWN = FALSE
  • UNKNOWN AND UNKNOWN = UNKNOWN

The intuition: AND only needs one FALSE to be definitively FALSE. So FALSE AND anything stays FALSE. But TRUE AND unknown is still unknown, because the unknown side might turn out either way.

-- If status = 'active' is TRUE but bonus = 100 is UNKNOWN:
SELECT *
FROM employees
WHERE status = 'active' AND bonus = 100;
-- Combined result is UNKNOWN, so the row is NOT returned

NULL in OR Expressions

OR mirrors AND. It only needs one TRUE to be definitively TRUE, so TRUE short-circuits the unknown away.

  • TRUE OR UNKNOWN = TRUE
  • FALSE OR UNKNOWN = UNKNOWN
  • UNKNOWN OR UNKNOWN = UNKNOWN

So a row can still match an OR condition even when one branch is unknown, as long as another branch is genuinely TRUE. This is a frequent follow-up after the AND question.

SELECT *
FROM employees
WHERE department = 'Sales' OR bonus = 100;
-- A Sales employee with NULL bonus:
-- TRUE OR UNKNOWN = TRUE, so the row IS returned

NOT Flips TRUE/FALSE but Not UNKNOWN

Here is the subtle one interviewers save for last. NOT inverts TRUE to FALSE and FALSE to TRUE, but NOT UNKNOWN is still UNKNOWN.

This is why you cannot just wrap a failing condition in NOT to flip the result. If bonus = 1000 is UNKNOWN for a NULL row, then NOT (bonus = 1000) is also UNKNOWN, and the row is still excluded.

Negation does not rescue NULL rows. Only an explicit IS NULL test does.

-- For a row where bonus IS NULL:
--   bonus = 1000        -> UNKNOWN
--   NOT (bonus = 1000)  -> UNKNOWN  (still excluded)
SELECT * FROM employees WHERE NOT (bonus = 1000);

Worked Example: The NOT IN Trap

This is one of the most-asked NULL puzzles. NOT IN with a list that contains a NULL returns no rows at all, surprising candidates who expect it to just skip the NULL.

Under the hood, x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. That last comparison is UNKNOWN, and TRUE AND TRUE AND UNKNOWN collapses to UNKNOWN, so nothing qualifies.

The safe alternative is NOT EXISTS, which is not vulnerable to this.

-- Returns ZERO rows if the subquery yields any NULL
SELECT name
FROM employees
WHERE manager_id NOT IN (SELECT manager_id FROM managers);

-- Each comparison against NULL becomes UNKNOWN,
-- and the AND-chain collapses to UNKNOWN for every row.

Why UNKNOWN Behaves Like FALSE in WHERE

A common follow-up: 'If UNKNOWN is not FALSE, why does the row get dropped just like a FALSE row?'

The answer is precise: WHERE, ON, and HAVING all use a keep-only-TRUE rule. Both FALSE and UNKNOWN fail that test, so for filtering purposes they look identical.

The difference surfaces only with negation and CHECK constraints. A CHECK constraint passes a row when the condition is TRUE or UNKNOWN, so a NULL can sneak past a CHECK that you assumed would block it.

-- CHECK passes on TRUE or UNKNOWN, so NULL salary is allowed:
-- CONSTRAINT salary_positive CHECK (salary > 0)
-- INSERT ... salary = NULL  -> NULL > 0 is UNKNOWN -> allowed

Deeper Example: COUNT and the Truthiness Gap

Tie it together with a realistic interview prompt. 'We have 100 employees. SELECT COUNT(*) WHERE bonus = 100 returns 30, and WHERE bonus <> 100 returns 50. Where are the other 20?'

The missing 20 have a NULL bonus. Neither = 100 nor <> 100 is TRUE for them, both are UNKNOWN, so they fall through both filters entirely.

Saying 'the buckets do not add up to the total because NULL satisfies neither predicate' is exactly the answer interviewers want.

SELECT
  COUNT(*) FILTER (WHERE bonus = 100)  AS eq_100,
  COUNT(*) FILTER (WHERE bonus <> 100) AS ne_100,
  COUNT(*) FILTER (WHERE bonus IS NULL) AS null_bonus,
  COUNT(*) AS total
FROM employees;

Interview Talking Points

When NULL logic comes up, hit these points to sound senior:

  • NULL means unknown; comparisons with it yield UNKNOWN.
  • SQL uses three-valued logic: TRUE, FALSE, UNKNOWN.
  • WHERE, ON, and HAVING keep only TRUE rows.
  • NOT UNKNOWN is still UNKNOWN, so negation does not recover NULL rows.
  • NOT IN with any NULL returns no rows; prefer NOT EXISTS.

State the model first, then walk through the truth table. That ordering signals you understand the why, not just the trick.

Quick Check

Test your grasp of three-valued logic.

Recap

You now have the core NULL mental model:

  • NULL is unknown, not a value; never compare it with = or <>.
  • SQL is three-valued: predicates return TRUE, FALSE, or UNKNOWN.
  • Filtering clauses keep only TRUE; UNKNOWN rows vanish just like FALSE rows.
  • NOT flips TRUE and FALSE but leaves UNKNOWN unchanged.
  • The NOT IN + NULL trap returns zero rows; reach for NOT EXISTS.

Next up: the correct way to test for NULL with IS NULL, IS NOT NULL, and NULL-safe equality operators.

Frequently asked questions

Is the “Three-Valued Logic and UNKNOWN” lesson free?

Yes — the full text of “Three-Valued Logic and UNKNOWN” is free to read here on the web, and the SQL Interview Prep 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 Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Three-Valued Logic and UNKNOWN”?

Why NULL = NULL is not true and how UNKNOWN propagates through conditions. You practise SQL Interview Prep 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 Interview Prep?

No prior experience is required. SQL Interview Prep 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 “Three-Valued Logic and UNKNOWN” 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 Interview Prep lesson?

Yes. Every SQL Interview Prep 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. Three-Valued Logic and UNKNOWN
  2. IS NULL, IS NOT NULL and NULL-Safe Equality
  3. COALESCE, NULLIF and ISNULL
  4. NULLs in Aggregates, Joins and DISTINCT
← Back to SQL Interview Prep