Three-valued logic (TRUE, FALSE, UNKNOWN) in SQL

Introduction

Three-valued logic helps databases handle situations where some information is missing or unknown. It adds a third option called UNKNOWN besides TRUE and FALSE.

When you have NULL values in your data and want to check conditions safely.

When filtering rows where some columns might not have values yet.

When you want to avoid wrong results caused by missing information.

When writing queries that involve comparisons with NULLs.

When you want to understand how SQL treats TRUE, FALSE, and UNKNOWN in conditions.

Syntax

SQL

condition
-- condition can evaluate to TRUE, FALSE, or UNKNOWN (NULL involved)

SQL uses NULL to represent unknown or missing values.

Comparisons with NULL result in UNKNOWN, not TRUE or FALSE.

Examples

This returns no rows because 'column = NULL' is UNKNOWN, not TRUE.

SQL

SELECT * FROM table WHERE column = NULL;

This correctly checks for NULL values and returns rows where column is NULL.

SQL

SELECT * FROM table WHERE column IS NULL;

If column is NULL, the condition is UNKNOWN, so the row is not returned.

SQL

SELECT * FROM table WHERE column <> 5;

Sample Program

This query shows how the condition 'age > 20' evaluates to TRUE, FALSE, or UNKNOWN (NULL) for each row.

SQL

CREATE TABLE people (id INT, age INT);
INSERT INTO people VALUES (1, 25), (2, NULL), (3, 30);

SELECT id, age, age > 20 AS is_adult FROM people;

Important Notes

UNKNOWN behaves like FALSE in WHERE clauses, so rows with UNKNOWN conditions are not returned.

Use IS NULL or IS NOT NULL to check for NULL values explicitly.

Logical operators (AND, OR, NOT) follow special rules with UNKNOWN to keep logic consistent.

Summary

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN (NULL).

Comparisons with NULL result in UNKNOWN, which affects query results.

Use IS NULL to check for unknown values properly.