SQL Three-Valued Logic

SQL doesn't have just TRUE and FALSE — every condition can also evaluate to UNKNOWN, which is what NULL comparisons actually produce.

What & Why

Ordinary logic has two values: TRUE and FALSE. SQL has three: TRUE, FALSE, and UNKNOWN — the result of any comparison involving NULL. A WHERE clause only keeps rows where the condition evaluates to TRUE; both FALSE and UNKNOWN get filtered out identically, which is exactly why NULL-involving comparisons so often silently exclude rows rather than erroring.

See How It Works

BUSINESS QUESTION

Product wants feedback ratings labeled positive, other, or unknown when no rating was submitted.

id	user_id	feature_id	rating	nps_score	comment	created_at
1	101	1	5	9	Clear and useful	2024-01-25 12:15:00+00
2	102	2	4	NULL	Export worked well	2024-02-20 15:40:00+00
3	103	1	4	7	Helpful setup flow	2024-03-18 09:05:00+00
4	104	3	3	NULL	Needs clearer timing	2024-04-08 17:30:00+00

EXAMPLE QUERY

SELECT
  id AS feedback_id,
  rating,
  CASE
    WHEN rating >= 4 THEN 'positive'
    WHEN rating < 4 THEN 'other'
    ELSE 'unknown'
  END AS rating_state
FROM product.feedback
ORDER BY feedback_id;

RESULT — exact output from the displayed Queryflo rows

feedback_id	rating	rating_state
1	5	positive
2	4	positive
3	4	positive
4	3	other

No displayed rating is NULL, so the unknown branch does not appear in this result.

Intermediate business practice