Free lesson · Mastering SQL

Three-Valued Logic and NULL Semantics - The Most Common SQL Interview Trap

Easy10 min readsqldatabase

Alex: You have mentioned NULLs a few times now and each time you had this tone like you were warning me about something dangerous. What is the deal with NULLs?

Emeka: NULLs are the single most misunderstood concept in SQL. They cause more interview failures, more production bugs, and more confused debugging sessions than any other feature of the language. And the reason is simple: SQL does not work the way your brain expects it to work when NULLs are involved.

Let me start with what NULL means, because even that is more subtle than you think.

What NULL Actually Means

In most programming languages, null means "nothing" or "no value." In Python, None is the absence of a value. In Java, null is an uninitialized reference. You can check for it with == None or == null and get a straightforward true or false.

In SQL, NULL does not mean "nothing." It means "unknown." The value exists, but we do not know what it is. This sounds like a philosophical distinction, but it has brutal practical consequences.

Consider a customers table where some customers have not provided their phone number:

| id | name       | phone        |
|----|------------|--------------|
| 1  | Amara Osei | 233-555-0101 |
| 2  | Raj Patel  | NULL         |
| 3  | Sofia Garcia| 52-555-0202 |

Raj's phone is NULL. Does that mean Raj does not have a phone? No. It means we do not know Raj's phone number. Maybe he has one and never gave it to us. Maybe he does not have one. The point is: the database does not know.

Alex: Okay, so it means "unknown." Why does that matter?

Emeka: Because of what happens when you compare something to an unknown value.

The Trap - Comparing to NULL

Here is the query that ruins interviews and production systems:

-- How many customers have no phone number?
SELECT COUNT(*) FROM customers WHERE phone = NULL;

Alex: That should return 1, right? Raj has a NULL phone.

Emeka: It returns zero. Every time. No matter what is in the table.

Alex: ...what?

Emeka: This is the moment. Let me explain it carefully, because once you understand this, you will never make this mistake again.

The expression phone = NULL asks: "Is this phone number equal to an unknown value?" The database's answer is: "I do not know." Not true. Not false. Unknown.

And here is the key: WHERE only returns rows where the condition evaluates to TRUE. Not false. And not unknown. A row where the condition evaluates to UNKNOWN is excluded, just like a row where the condition evaluates to FALSE.

So WHERE phone = NULL evaluates to UNKNOWN for every row - including the rows where phone actually IS NULL - and the result set is empty.

graph TD
    Q["WHERE phone = NULL"] --> E["Evaluates to UNKNOWN<br/>for EVERY row"]
    E --> R["UNKNOWN is not TRUE"]
    R --> F["Zero rows returned"]
    
    style Q fill:#ef4444,color:#fff
    style E fill:#f59e0b,color:#000
    style F fill:#ef4444,color:#fff

This diagram shows the evaluation chain for WHERE phone equals NULL. The expression evaluates to UNKNOWN for every row. UNKNOWN is not TRUE, so zero rows are returned. All three boxes are highlighted in red and amber to indicate this is a dangerous trap.

The Correct Way

-- Correct: use IS NULL
SELECT COUNT(*) FROM customers WHERE phone IS NULL;
-- Returns: 1

-- Also correct: IS NOT NULL
SELECT COUNT(*) FROM customers WHERE phone IS NOT NULL;
-- Returns: 2

IS NULL and IS NOT NULL are special SQL operators designed specifically for testing NULL values. They return TRUE or FALSE - never UNKNOWN. These are the only safe way to check for NULLs.

Important: The rule is absolute:

• = NULL → always UNKNOWN → never matches
• != NULL → always UNKNOWN → never matches
• IS NULL → TRUE for NULL values, FALSE otherwise
• IS NOT NULL → FALSE for NULL values, TRUE otherwise

There is no exception to this. = NULL never works, in any database, in any context.

Three-Valued Logic - TRUE, FALSE, UNKNOWN

SQL has three truth values, not two. Every comparison in SQL evaluates to one of: TRUE, FALSE, or UNKNOWN. UNKNOWN appears whenever NULL is involved.

Here is the complete truth table:

The last one surprises people. NULL = '' is UNKNOWN, not TRUE. NULL is not an empty string. NULL is not zero. NULL is not false. NULL is "unknown." Comparing anything to it - anything at all, including another NULL - produces UNKNOWN.

Alex: Wait - NULL = NULL is UNKNOWN? Two NULLs are not equal to each other?

Emeka: Correct, and the logic is sound once you accept the "unknown" semantics. If I say "I do not know Raj's phone number" and "I do not know Sofia's phone number," are their phone numbers equal? I do not know. They might be the same. They might be different. The answer is unknown. SQL behaves consistently with that reasoning.

How UNKNOWN Behaves with AND, OR, and NOT

Three-valued logic extends to logical operators. Here is how UNKNOWN interacts with AND, OR, and NOT:

AND Truth Table

The key insight: FALSE AND anything is FALSE (even UNKNOWN). This makes sense - if one condition is definitely false, the combined condition cannot be true regardless of the other.

OR Truth Table

The key insight: TRUE OR anything is TRUE (even UNKNOWN). If one condition is definitely true, the combined condition is true regardless.

NOT

NOT UNKNOWN is still UNKNOWN. You cannot negate an unknown value into a known one.

The NOT IN Trap - The Career-Ending Gotcha

This is the most dangerous NULL trap in SQL, and it shows up in interviews regularly.

-- Find all customers who are NOT in the VIP list
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM vip_customers);

This looks safe. But if the vip_customers table has even one NULL in the customer_id column, the entire query returns zero rows.

Alex: What? The entire result is empty because of one NULL?

Emeka: Yes. Let me walk through why.

NOT IN (1, 2, NULL) expands to:

customer_id != 1 AND customer_id != 2 AND customer_id != NULL

The last condition - customer_id != NULL - evaluates to UNKNOWN. And from our AND truth table: TRUE AND TRUE AND UNKNOWN = UNKNOWN. So the WHERE clause evaluates to UNKNOWN for every row, and no rows are returned.

One NULL in the subquery. Entire result set wiped out. No error message. No warning. Just an empty result that looks correct if you do not know to check.

graph TD
    Q["NOT IN (1, 2, NULL)"] --> E1["id != 1 → TRUE"]
    Q --> E2["id != 2 → TRUE"]
    Q --> E3["id != NULL → UNKNOWN"]
    E1 --> AND["TRUE AND TRUE AND UNKNOWN"]
    E2 --> AND
    E3 --> AND
    AND --> R["= UNKNOWN"]
    R --> F["Row excluded from results"]
    
    style Q fill:#ef4444,color:#fff
    style E3 fill:#f59e0b,color:#000
    style R fill:#ef4444,color:#fff
    style F fill:#ef4444,color:#fff

This diagram shows the evaluation of NOT IN with a NULL value. The expression NOT IN (1, 2, NULL) expands to three comparisons: id not equal to 1 (TRUE), id not equal to 2 (TRUE), and id not equal to NULL (UNKNOWN). These are combined with AND: TRUE AND TRUE AND UNKNOWN equals UNKNOWN. Because the result is UNKNOWN, not TRUE, the row is excluded from results. The NULL comparison and result boxes are highlighted in red to indicate danger.

The Fix

Use NOT EXISTS instead of NOT IN whenever the subquery might contain NULLs:

-- Safe version: NOT EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM vip_customers v
    WHERE v.customer_id = c.customer_id
);

NOT EXISTS checks whether a matching row exists. It does not compare against a list of values, so it is not affected by NULLs in the subquery. This is a strictly superior pattern in almost every case.

Warning: The rule: never use NOT IN with a subquery unless you are absolutely certain the subquery cannot produce NULLs. In practice, you are rarely that certain. Use NOT EXISTS as the default. It is safer and often faster. We cover this in depth in Module 5 (Topic 5.2).

NULL in Aggregations

NULLs also behave unexpectedly in aggregate functions:

-- Sample data
| id | amount |
|----|--------|
| 1  | 100    |
| 2  | NULL   |
| 3  | 200    |

The AVG behavior catches people. If you have three amounts - 100, NULL, 200 - the average is 150 (300 / 2), not 100 (300 / 3). SQL excludes the NULL row entirely from the average calculation, which changes the denominator.

Interview: This is an interview question: "What is the difference between COUNT(*) and COUNT(column_name)?" The answer is NULLs. COUNT(*) counts all rows. COUNT(column_name) counts only rows where that column is not NULL. If an interviewer asks this, they are testing whether you understand NULL behavior in aggregations.

NULL in Sorting

When you ORDER BY a column that contains NULLs, where do the NULL rows go?

It depends on the database:

PostgreSQL lets you control this explicitly:

ORDER BY amount ASC NULLS FIRST;   -- NULLs at the top
ORDER BY amount ASC NULLS LAST;    -- NULLs at the bottom (PostgreSQL default)

Warning: Dialect difference: This is one of the areas where PostgreSQL and MySQL differ silently. If your query depends on where NULLs appear in the sort order, specify it explicitly with NULLS FIRST or NULLS LAST. Do not rely on default behavior.

COALESCE - Providing Default Values for NULLs

COALESCE returns the first non-NULL value from a list of arguments:

SELECT name, COALESCE(phone, 'No phone on file') AS phone_display
FROM customers;

COALESCE is the standard way to handle NULL values in output. You will use it constantly - in display queries, in calculations where NULL would propagate (since any arithmetic with NULL produces NULL), and in conditional logic.

The Complete NULL Survival Guide

Alex: This is a lot. Can you give me a cheat sheet?

Emeka: Here are the rules. Memorize them. They will save your interview and your production code.

Important: The NULL Survival Guide:

1. NULL means "unknown," not "empty" or "zero."
2. Never use = NULL or != NULL. Always use IS NULL and IS NOT NULL.
3. Any comparison with NULL produces UNKNOWN. Including NULL = NULL.
4. WHERE only returns TRUE rows. UNKNOWN rows are excluded, same as FALSE.
5. NOT IN with NULLs returns zero rows. Use NOT EXISTS instead.
6. Aggregates ignore NULLs. AVG(100, NULL, 200) = 150, not 100.
7. COUNT(*) counts all rows. COUNT(column) counts non-NULLs only.
8. NULL sort order varies by database. Use NULLS FIRST/LAST explicitly.
9. Arithmetic with NULL produces NULL. 5 + NULL = NULL.
10. Use COALESCE to provide default values.

Every rule on this list will come up again in later modules. When they do, I will reference back to this survival guide. This is your foundation for thinking about NULLs correctly throughout the rest of the course.

Alex: I feel like I have been warned about something genuinely dangerous.

Emeka: You have. NULLs are not an edge case. They are everywhere in real data - optional fields, missing records, outer joins that produce unmatched rows. Every SQL query you write will encounter NULLs eventually. The difference between a developer who understands three-valued logic and one who does not is the difference between code that works and code that silently returns wrong results with no error message to tell you something went wrong.

And the fact that you understand it now, in Module 1, puts you ahead of candidates who learn it the hard way in an interview when their NOT IN query returns empty and they cannot figure out why.

Next module, we start writing real queries. Module 2: Querying Data.

Exercise: Starter: What does this query return? Explain why.

SELECT * FROM orders WHERE discount != NULL;

Intermediate: Given a table employees(id, name, manager_id) where the CEO has manager_id = NULL, write a query to find all employees who are NOT the CEO. Then explain why WHERE manager_id != NULL is wrong and what the correct approach is.

Advanced: A table scores(student_id, subject, score) has some NULL scores (students who were absent). Write a query that computes each student's average score, but treats NULL scores as 0 instead of excluding them. (Hint: use COALESCE.)

Tip: Your next step: Move on to Module 2, Topic 2.1 - The SELECT Statement - where you write your first real SQL queries and learn the logical execution order that explains everything.

Back to Mastering SQL