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.

Now You Try

Practice this concept

Product wants feedback ratings labeled positive, other, or unknown when no rating is present.

Available schema

product

Prefix tables with product.table_name.

feedback_idratingrating_state

product.feedback

Column	Type
id	integer
user_id	integer
feature_id	integer
rating	integer
nps_score	integer
comment	text
created_at	timestamp with time zone
moderation_bucket	text

query.sql

Intermediate business practice