BOOLEAN and NULL logic

Concepts covered: Three-Valued Logic

A BOOLEAN column has 3 possible values. TRUE and FALSE are recorded answers. NULL means the system never recorded an answer. Take an email_verified flag on a users table. Rows written after the verification feature shipped carry TRUE or FALSE. Rows written before it carry NULL, because nobody asked the question back then. Reading that NULL as FALSE would flag long-time customers as unverified, and reading it as TRUE would skip a security check. The database does neither. It keeps the unknown unknown, and every operator that touches it follows rules that fit in a truth table.

The truth table with a third value

Read AND as 'both sides must be TRUE'. When one side is FALSE the answer is settled no matter what the other side holds, so FALSE AND NULL is FALSE: whatever the unknown turns out to be, both sides cannot be TRUE. When one side is TRUE, the result depends entirely on the other side, so TRUE AND NULL stays NULL. OR works as the mirror image. TRUE OR NULL is TRUE because one TRUE is enough, and FALSE OR NULL is NULL because the unknown side decides. NOT swaps TRUE and FALSE and leaves NULL alone, since the opposite of an unknown answer is still an unknown answer.

Watching the rules run on real rows

The users table below has 6 rows. email_verified and phone_verified are its 2 flags, and across the rows they cover every combination of the 3 BOOLEAN values. The query computes 3 new BOOLEAN columns from them. Before you run it, predict the row for user 3, where email is FALSE and phone is NULL.

SELECT
  user_id,
  email_verified,
  phone_verified,
  email_verified AND phone_verified AS fully_verified,
  email_verified OR phone_verified AS any_verified,
  NOT email_verified AS email_unverified
FROM users
ORDER BY user_id

User 3 comes out FALSE, NULL, TRUE. The AND is FALSE because a FALSE email check settles it. The OR is NULL because the phone flag could still be the TRUE that rescues it. NOT FALSE is TRUE. User 4, with NULL in both flags, produces NULL in all 3 new columns: no operator can manufacture a known answer out of 2 unknown ones. User 2 shows the asymmetry inside a single row: TRUE AND NULL is NULL while TRUE OR NULL is TRUE.

A row has a = NULL and b = FALSE. What does NOT (a AND b) return?

• NULL, because a is NULL and NULL spreads through every operator it touches
• TRUE, because a AND b is FALSE once either side is FALSE, and NOT FALSE is TRUE
• FALSE, because the AND evaluates to TRUE when the unknown side is skipped
• An error, because NOT cannot be applied to an expression that might be NULL

Comparisons produce BOOLEAN values

A comparison is an expression whose type is BOOLEAN. country = 'US' yields TRUE for a US row and FALSE for a Canadian one. When country is NULL the comparison yields NULL, because an unknown country might or might not be the US. That is why country = NULL never finds anything: comparing against an unknown returns unknown for every row, including the rows where the column really is NULL. The operator built for that question is IS NULL. It inspects the slot rather than the value, so it returns TRUE or FALSE and never NULL, and IS NOT NULL gives the opposite.

SELECT
  account_id,
  country,
  country = 'US' AS is_us,
  NOT (country = 'US') AS is_not_us,
  country IS NULL AS country_missing
FROM accounts
ORDER BY account_id

Account 3 returns NULL for both is_us and is_not_us, so a row can fail the US test and fail the non-US test at the same time. Only country_missing returns TRUE for it. Any time a report splits rows into 2 buckets with a comparison, a third bucket of NULL rows is sitting between them, and it stays invisible until a query tests for it by name.

About this lesson

This section is part of the Data Types: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.