SQL UNKNOWN

What is SQL UNKNOWN?

Represents the third logical truth value produced when a predicate cannot be evaluated as TRUE or FALSE, usually because of NULLs.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL UNKNOWN: PostgreSQL, MariaDB 10.5+, Firebird, H2, DuckDB, IBM Db2, and any engine that fully implements SQL-1999 three-valued logic. MySQL prior to 8.0 and SQL Server lack direct IS UNKNOWN support but still operate internally with UNKNOWN in WHERE evaluation.

SQL UNKNOWN Full Explanation

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. UNKNOWN occurs when a search condition references at least one NULL and the overall truth cannot be decided. For example, the comparison NULL = 5 is neither TRUE nor FALSE, so it yields UNKNOWN. In filters (WHERE, HAVING, CHECK), rows with UNKNOWN are treated the same way as FALSE and are excluded unless explicitly tested. The SQL standard lets you examine this state with the IS [NOT] UNKNOWN predicate. UNKNOWN is not a literal you can store; boolean columns store NULL to indicate an indeterminate value. Understanding UNKNOWN is essential for predictable NULL handling, constraint design, and complex conditional logic.

SQL UNKNOWN Syntax

<search_condition> IS [NOT] UNKNOWN

SQL UNKNOWN Parameters

Example Queries Using SQL UNKNOWN

-- 1. Demonstrate UNKNOWN result
SELECT (NULL = 5) AS result;  -- returns NULL, evaluated as UNKNOWN

-- 2. Keep only rows whose predicate is UNKNOWN
SELECT id, price, cost
FROM products
WHERE (price > cost) IS UNKNOWN;  -- catches rows where either column is NULL

-- 3. Combine IS UNKNOWN with other truth tests
SELECT id,
       (deleted_at IS NULL)             AS is_active,
       (deleted_at IS NULL) IS UNKNOWN  AS check_unknown
FROM users;

Expected Output Using SQL UNKNOWN

Query 1 returns a single column with value NULL, representing UNKNOWN
Queries 2 and 3 return only those rows where the evaluated predicate could not be classified as TRUE or FALSE because one or more operands were NULL

Use Cases with SQL UNKNOWN

Filtering rows where a comparison involves NULLs and you need to treat them separately
Building CHECK constraints that reject UNKNOWN instead of silently allowing it
Debugging unexpected NULL-driven logic by surfacing UNKNOWN rows
Writing robust conditional expressions that must handle nullable boolean columns

Common Mistakes with SQL UNKNOWN

Assuming UNKNOWN is the same as NULL in all contexts; NULL is a marker for missing data, UNKNOWN is a logical result
Forgetting that WHERE and HAVING drop UNKNOWN rows, causing silent data loss
Trying to assign the literal UNKNOWN to a column instead of using NULL
Believing that NOT UNKNOWN is the same as TRUE; NOT UNKNOWN is TRUE when the predicate is not UNKNOWN (that is, either TRUE or FALSE)

First Introduced In

SQL-92 (formalized in SQL-1999 with IS UNKNOWN predicate)

Frequently Asked Questions

What is the UNKNOWN truth value in SQL?

UNKNOWN is the result of a predicate when SQL cannot evaluate it as TRUE or FALSE, usually because one or more operands are NULL.

How can I check if a condition is UNKNOWN?

Append IS UNKNOWN to the boolean expression, or use IS NOT UNKNOWN when you want TRUE or FALSE only.

Does UNKNOWN equal NULL?

They are related but different. NULL is stored data indicating missing information, while UNKNOWN is a runtime logical result produced by evaluating expressions containing NULL.

Why was my row filtered out when the predicate seemed inconclusive?

WHERE and HAVING clauses discard rows whose predicates evaluate to UNKNOWN, treating them like FALSE. Explicitly check for UNKNOWN if you need to keep them.