Skip to course content
Free SQL course

SQL for Data Analysis and AI

Unit 03.01: Three-valued logic: TRUE, FALSE, and UNKNOWN

Unit ID: SQL-M03-U02 - Estimated active time: 14-18 minutes Objective: evaluate conditions involving NULL correctly and predict which rows survive a filter.

NULL means unknown, so comparisons return unknown

SELECT NULL = NULL;   -- NULL
SELECT NULL <> 'IN';  -- NULL
SELECT NULL > 0;      -- NULL

Is one unknown value equal to another unknown value? There is no way to know, so SQL answers "unknown" rather than guessing. That is the whole rule; everything else follows from it.

WHERE keeps only rows where the condition is TRUE. UNKNOWN is not TRUE, so those rows are dropped.

Seeing it in the data

Three-valued logic: the rows a filter cannot decide about are dropped The customers table holds 4,812 rows: 2,813 with country IN, 1,699 with another recorded country (902 GB, 516 AE and 281 SG), and 300 where country is NULL. For a row with country IN, the condition country not equal to IN evaluates to FALSE and the row is excluded. For a row with country GB it evaluates to TRUE and the row is kept. For a row where country is NULL it evaluates to UNKNOWN, and WHERE keeps only rows that are TRUE, so those 300 rows are silently dropped. The query returns 1,699 rows rather than the 1,999 a reader expects from 4,812 minus 2,813. WHERE keeps TRUE. It does not keep UNKNOWN. Evaluating country <> 'IN' against all 4,812 customers country = 'IN' 2,813 rows FALSE excluded, as intended another recorded country 1,699 rows — GB, AE, SG TRUE kept country IS NULL 300 rows UNKNOWN dropped, with no warning The query returns 1,699 rows. A reader doing 4,812 − 2,813 expects 1,999 The 300 missing rows are the ones the condition could not decide about. “Known to be outside India” country <> 'IN' → 1,699 “Not recorded as India” country <> 'IN' OR country IS NULL → 1,999
A filter keeps only TRUE. Rows it cannot decide about are dropped without warning.

300 of our 4,812 customers have no recorded country:

SELECT COUNT(*) FROM customers WHERE country IS NULL;     -- 300
SELECT COUNT(*) FROM customers WHERE country <> 'IN';     -- 1699
SELECT COUNT(*) FROM customers WHERE country = 'IN';      -- 2813

2,813 + 1,699 = 4,512, not 4,812. The two filters together miss 300 rows. Neither = 'IN' nor <> 'IN' is true for a NULL, so those customers belong to neither group.

This is the single most common way a category split silently loses people.

Writing the condition you meant

-- fragment: WHERE clause shown on its own
-- Customers we know are outside India
WHERE country <> 'IN'

-- Customers not recorded as being in India
WHERE country <> 'IN' OR country IS NULL

-- Everyone, split into three honest buckets
SELECT
  CASE WHEN country IS NULL THEN 'unknown'
       WHEN country = 'IN'  THEN 'India'
       ELSE 'international' END AS segment,
  COUNT(*) AS customers
FROM customers
GROUP BY segment
ORDER BY customers DESC;
-- India         | 2813
-- international | 1699
-- unknown       |  300

The third version is usually the right answer for a report, because it adds up to the total and shows the reader the uncertainty instead of hiding it.

Non-example

IS NULL is not a comparison and behaves normally:

SELECT COUNT(*) FROM customers WHERE country IS NULL;  -- 300, always reliable

Use IS NULL / IS NOT NULL. Never = NULL - that is always UNKNOWN and matches nothing.

Practice

Predict each result, then run them:

  1. SELECT COUNT(*) FROM feedback WHERE rating < 3;
  2. SELECT COUNT(*) FROM feedback WHERE rating >= 3;
  3. Do 1 and 2 add up to 500? If not, where did the rest go?
Check your answer
  1. 180
  2. 260
  3. No - they total 440. The other 60 rows have rating IS NULL, so neither condition is TRUE for them.

To account for everyone:

SELECT
  CASE WHEN rating IS NULL THEN 'no rating'
       WHEN rating < 3 THEN 'low'
       ELSE 'high' END AS band,
  COUNT(*)
FROM feedback
GROUP BY band;
-- high      | 260
-- low       | 180
-- no rating |  60

Takeaway

= and <> both reject NULL. Any two-way split built from them will quietly lose the unknown rows - check that your buckets sum to the total.

---