What is three-valued logic in SQL?

Three-valued logic means SQL boolean expressions can evaluate to TRUE, FALSE, or UNKNOWN — not just true or false. UNKNOWN arises whenever NULL appears in a comparison or arithmetic expression. WHERE and HAVING clauses only include rows where the condition evaluates to TRUE.

How do I check if a column is NULL in SQL?

Use IS NULL to find rows where a column has no value, and IS NOT NULL to find rows where it does. These are the only reliable NULL predicates in SQL. Example: SELECT * FROM orders WHERE shipped_at IS NULL;

Does NULL equal NULL in SQL?

No. NULL = NULL evaluates to UNKNOWN, not TRUE. Two unknown values cannot be confirmed equal. If you need NULL to match NULL (for example in a merge key), use IS NOT DISTINCT FROM in PostgreSQL, or the MySQL-specific operator.

How does NULL affect COUNT in SQL?

COUNT(*) counts all rows regardless of NULL values. COUNT(column) counts only the rows where that column is not NULL. SUM, AVG, MIN, and MAX also silently skip NULLs. To include NULLs as zero in an average, use AVG(COALESCE(column, 0)).

SQL NULL Comparison: Why = NULL Never Works

sqlfmt▍

SQL NULL Comparison: Why = NULL Never Works — sqlfmt

If you've ever written WHERE deleted_at = NULL and gotten zero rows back — even when you know nulls are in that column — you've hit one of SQL's most reliable trip wires. The = operator simply does not work with NULL. Not because of a bug, but because of how SQL defines NULL itself.

NULL Is Not a Value, It Is the Absence of One

NULL in SQL represents an unknown or missing value — not zero, not an empty string, not false. Because it is unknown, any arithmetic or comparison involving NULL produces another unknown. SQL calls this three-valued logic: every expression can evaluate to TRUE, FALSE, or UNKNOWN. A WHERE clause only passes rows where the condition is TRUE. UNKNOWN is treated like FALSE — the row is dropped.

Three-valued logic — every comparison with NULL returns UNKNOWN

-- These all return UNKNOWN (not FALSE)
SELECT NULL = NULL;      -- UNKNOWN
SELECT NULL = 1;         -- UNKNOWN
SELECT NULL != NULL;     -- UNKNOWN
SELECT NULL > 0;         -- UNKNOWN
SELECT 1 + NULL;         -- NULL

-- IS NULL / IS NOT NULL are the only operators that return TRUE or FALSE
SELECT NULL IS NULL;     -- TRUE
SELECT NULL IS NOT NULL; -- FALSE

= NULL silently returns nothing — IS NULL returns the right rows

-- Table: users
-- id | name    | deleted_at
--  1 | Alice   | 2024-01-15
--  2 | Bob     | NULL
--  3 | Charlie | NULL

-- Returns 0 rows — = NULL is always UNKNOWN
SELECT *
FROM users
WHERE deleted_at = NULL;

-- Returns 2 rows (Bob and Charlie)
SELECT *
FROM users
WHERE deleted_at IS NULL;

IS NULL and IS NOT NULL in practice

-- Find orders that haven't shipped yet
SELECT order_id, customer_id, created_at
FROM orders
WHERE shipped_at IS NULL;

-- Find orders that have shipped
SELECT order_id, customer_id, shipped_at
FROM orders
WHERE shipped_at IS NOT NULL;

-- Avoid double negation — this is harder to read than IS NULL
SELECT order_id
FROM orders
WHERE NOT shipped_at IS NOT NULL;

COALESCE and NULLIF

-- COALESCE: return the first non-NULL value
SELECT
  user_id,
  COALESCE(display_name, email, 'Anonymous') AS name
FROM users;

-- NULLIF: return NULL when the two arguments are equal (avoids divide-by-zero)
SELECT
  product_id,
  revenue / NULLIF(units_sold, 0) AS revenue_per_unit
FROM sales;

-- Chain them: turn empty strings into NULL, then apply a default
SELECT
  COALESCE(NULLIF(TRIM(phone), ''), 'N/A') AS phone
FROM contacts;

NULL behavior in aggregate functions

-- scores: 10, NULL, 30, NULL, 50

SELECT
  COUNT(*)     AS total_rows,       -- 5  (counts all rows)
  COUNT(score) AS non_null_scores,   -- 3  (skips NULLs)
  SUM(score)   AS total,             -- 90 (skips NULLs)
  AVG(score)   AS average            -- 30.00 (90 / 3, not 90 / 5)
FROM quiz_results;

-- Treat NULL as zero in AVG
SELECT AVG(COALESCE(score, 0)) AS average_with_nulls_as_zero  -- 18.00
FROM quiz_results;

Anti-join: find customers who have never ordered

-- LEFT JOIN + IS NULL is faster than NOT IN / NOT EXISTS on most engines
SELECT
  c.customer_id,
  c.name
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Always test a non-nullable column (e.g. primary key) in the IS NULL check.
-- If the right-side column can itself be NULL, you may get false positives.

Dialect-specific NULL convenience functions

-- COALESCE: SQL standard — works in all dialects
SELECT COALESCE(phone, 'N/A') FROM contacts;

-- ISNULL: SQL Server / T-SQL only (two-argument shorthand for COALESCE)
SELECT ISNULL(phone, 'N/A') FROM contacts;

-- IFNULL: MySQL and BigQuery
SELECT IFNULL(phone, 'N/A') FROM contacts;

-- <=>: MySQL NULL-safe equality (returns TRUE when both sides are NULL)
SELECT * FROM contacts WHERE phone <=> NULL;  -- equivalent to IS NULL

-- IS NOT DISTINCT FROM: PostgreSQL / SQL standard NULL-safe equality
SELECT * FROM contacts WHERE phone IS NOT DISTINCT FROM NULL;

SQL NULL Comparison: Why = NULL Never Works

Quick answer

FAQs

Why does WHERE column = NULL return no rows?

What is three-valued logic in SQL?

How do I check if a column is NULL in SQL?

Does NULL equal NULL in SQL?

How does NULL affect COUNT in SQL?

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

SQLite "Datatype Mismatch" (Error 20): Type Affinity and Strict Tables

PostgreSQL statement_timeout (Error 57014): Causes and Prevention

NULL Is Not a Value, It Is the Absence of One

Why WHERE column = NULL Returns Zero Rows

The Correct Operators: IS NULL and IS NOT NULL

Replacing NULL with a Default: COALESCE and NULLIF

How NULL Affects Aggregate Functions

Using IS NULL to Find Unmatched Rows in a JOIN

NULL Syntax Across SQL Dialects

NULL Handling Checklist

Quick answer

FAQs

Why does WHERE column = NULL return no rows?

What is three-valued logic in SQL?

How do I check if a column is NULL in SQL?

Does NULL equal NULL in SQL?

How does NULL affect COUNT in SQL?

Keep reading

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

SQLite "Datatype Mismatch" (Error 20): Type Affinity and Strict Tables

PostgreSQL statement_timeout (Error 57014): Causes and Prevention

NULL Is Not a Value, It Is the Absence of One

Why WHERE column = NULL Returns Zero Rows

The Correct Operators: IS NULL and IS NOT NULL

Replacing NULL with a Default: COALESCE and NULLIF

How NULL Affects Aggregate Functions

Using IS NULL to Find Unmatched Rows in a JOIN

NULL Syntax Across SQL Dialects

NULL Handling Checklist