SQL NULL and Three-Valued Logic

NULL is easy to misread as an unusual spelling of zero, an empty string, or false. In SQL it means that a value is absent or unknown. That distinction changes comparisons, logical expressions, filters, joins, constraints, and even otherwise harmless-looking expressions such as NOT IN.

PHP and SQL make the contrast especially clear. PHP evaluates logical operators with ordinary two-valued Boolean logic after converting their operands. SQL keeps uncertainty in the expression and therefore needs a third logical result: UNKNOWN.

PHP Converts NULL to a Boolean

For ||, &&, and !, PHP converts both operands to bool before applying the operator. NULL becomes false, and the logical operator itself returns a Boolean:

var_dump((bool) NULL);       // bool(false)
var_dump(NULL || true);     // bool(true)
var_dump(NULL || false);    // bool(false)
var_dump(NULL && true);    // bool(false)
var_dump(!NULL);            // bool(true)

This does not mean that NULL, false, an empty string, and an empty array are interchangeable PHP values. Loose comparison with == applies type-juggling rules; strict comparison with === preserves the type distinction. Logical conversion is a separate operation with a simple result: the operand is either truthy or falsy.

SQL Preserves the Unknown

SQL does not coerce NULL to false. A comparison with an absent value cannot normally be declared true or false, so SQL evaluates it as UNKNOWN:

SELECT
  NULL = 5,       -- UNKNOWN
  NULL <> 5,      -- UNKNOWN
  NULL = NULL;    -- UNKNOWN

The result NULL = NULL is UNKNOWN by design. Two missing values are not known to be equal merely because both are represented by NULL. SQL logical expressions therefore operate on TRUE, FALSE, and UNKNOWN. MySQL displays UNKNOWN as NULL, but the logical concept is still useful because it explains every result without special-case guessing.

SQL Truth Tables

OR is true as soon as either operand is known to be true. Otherwise an unknown operand remains relevant:

AND is false as soon as either operand is known to be false. Otherwise uncertainty remains:

NOT reverses a known truth value but cannot manufacture information:

These tables are symmetric: changing the order of the operands does not change AND or OR. The result follows from whether a known value already determines the expression. It does not depend on an interpreter reading one side first.

Use OR, Not Double Pipes, in MySQL

Write OR instead of || in SQL examples and application queries. MySQL normally accepts || as logical OR, but the PIPES_AS_CONCAT SQL mode changes it into string concatenation. OR is explicit, portable, and immune to that configuration difference:

SELECT TRUE OR NULL;   -- 1
SELECT FALSE OR NULL;  -- NULL

Test NULL with IS

Ordinary equality cannot answer whether a value is missing because column_name = NULL evaluates to UNKNOWN for every row. Use IS NULL or IS NOT NULL:

-- Wrong: this condition is never TRUE.
SELECT *
FROM orders
WHERE shipped_at = NULL;

-- Correct.
SELECT *
FROM orders
WHERE shipped_at IS NULL;

MySQL also provides the NULL-safe equality operator <=>. It returns true when both operands are NULL, false when only one is NULL, and otherwise behaves like equality. Use it when comparing two nullable expressions rather than merely checking whether one column is missing.

WHERE Keeps Only TRUE

WHERE keeps only rows for which the predicate is TRUE. Both FALSE and UNKNOWN are filtered out. This is why negating a nullable comparison does not necessarily select the rows omitted by the original query:

-- Neither query includes rows whose status is NULL.
SELECT * FROM tasks WHERE status = 'done';
SELECT * FROM tasks WHERE NOT (status = 'done');

-- Include the missing state explicitly when that is the intention.
SELECT *
FROM tasks
WHERE status <> 'done' OR status IS NULL;

The same TRUE-only rule governs JOIN ... ON matching and searched CASE WHEN branches. Unknown is not the same as false, even when both cause a row or branch not to be selected.

The NOT IN Trap

NOT IN is particularly easy to get wrong when its list or subquery can contain NULL:

SELECT 3 NOT IN (1, 2, NULL);  -- NULL, not TRUE

The expression expands conceptually to 3 <> 1 AND 3 <> 2 AND 3 <> NULL. The first two comparisons are TRUE, the last is UNKNOWN, and TRUE AND TRUE AND UNKNOWN is UNKNOWN. In a WHERE clause, the row disappears.

When excluding rows based on another table, a correlated NOT EXISTS expresses the anti-join directly and is not poisoned by a nullable result column:

SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM blocked_customers AS b
  WHERE b.customer_id = c.customer_id
);

If NOT IN is otherwise the clearest form, make the subquery's non-null contract explicit with a schema constraint or a reliable IS NOT NULL predicate.

Use COALESCE Deliberately

COALESCE returns the first non-null argument and is useful when the domain provides a real replacement value:

SELECT COALESCE(display_name, legal_name, 'Anonymous')
FROM users;

It should not be used merely to make UNKNOWN disappear. Replacing a missing discount with zero may be correct if zero is the documented business default; replacing an unknown measurement with zero silently invents data. Preserve NULL until the application can assign a semantically valid value.

Aggregation Has Its Own NULL Contract

Most SQL aggregate functions ignore NULL. COUNT(column_name) counts known values, while COUNT(*) counts rows. An average over a nullable column therefore describes only the rows with known measurements:

SELECT
  COUNT(*) AS all_rows,
  COUNT(score) AS known_scores,
  AVG(score) AS average_known_score
FROM results;

This behavior is often useful, but the two counts should be reported together when the proportion of missing data matters.

A Practical Mental Model

• NULL is a marker for an absent value, not a value equal to every other NULL.
• Ordinary comparisons involving NULL produce UNKNOWN.
• AND, OR, and NOT propagate UNKNOWN only when no known operand already determines the result.
• Filters and joins accept TRUE, not merely “not false.”
• Use IS NULL, IS NOT NULL, NULL-safe equality where appropriate, and NOT EXISTS for nullable anti-joins.
• Replace NULL with COALESCE only when the replacement has a documented meaning.

References

• [MySQLLogical]MySQL 8.4 Reference Manual: Logical Operators.
• [MySQLComparison]MySQL 8.4 Reference Manual: Comparison Functions and Operators.
• [PHPLogical]PHP Manual: Logical Operators.
• [PHPBoolean]PHP Manual: Booleans.