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

Concept Flow - Three-valued logic (TRUE, FALSE, UNKNOWN)

Evaluate Condition

↓

Is condition TRUE?

Yes→Result TRUE

No↓

Is condition FALSE?

Yes→Result FALSE

No↓

Result UNKNOWN

SQL conditions can be TRUE, FALSE, or UNKNOWN (NULL). The logic checks TRUE first, then FALSE, else UNKNOWN.

Execution Sample

SQL

SELECT * FROM users WHERE age > 30 AND name IS NULL;

This query filters rows where age is greater than 30 and name is NULL, demonstrating three-valued logic.

Execution Table

Step	Condition	Evaluation	Result
1	age > 30	TRUE	TRUE
2	name IS NULL	TRUE	TRUE
3	TRUE AND TRUE	TRUE	Row included
4	Next row	Repeat evaluation	Depends on values
5	No more rows	-	Query ends

💡 Rows where condition evaluates to UNKNOWN or FALSE are excluded from results.

Variable Tracker

Variable	Row 1	Row 2	Row 3	Final
age > 30	TRUE	FALSE	UNKNOWN	Varies per row
name IS NULL	TRUE	FALSE	UNKNOWN	Varies per row
Combined Condition	TRUE	FALSE	UNKNOWN	Determines row inclusion

Key Moments - 2 Insights

Why does 'name = NULL' evaluate to UNKNOWN instead of TRUE or FALSE?

In SQL, NULL means unknown value, so any comparison with NULL using '=' results in UNKNOWN, as shown in execution_table step 2.

Why are rows with UNKNOWN condition results excluded from the query output?

SQL WHERE clause only includes rows where condition is TRUE; UNKNOWN and FALSE exclude rows, as seen in execution_table step 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of 'TRUE AND UNKNOWN' at step 3?

A TRUE

B UNKNOWN

C FALSE

D NULL

💡 Hint

Refer to execution_table row 3 under 'Result' column.

At which step does the query decide to exclude a row due to condition evaluation?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Check execution_table where 'Row excluded' is noted.

If 'name = NULL' was replaced with 'name IS NULL', how would the evaluation change at step 2?

A It would evaluate to TRUE or FALSE, not UNKNOWN

B It would still be UNKNOWN

C It would cause an error

D It would evaluate to NULL

💡 Hint

Consider how 'IS NULL' differs from '=' in SQL logic, check variable_tracker for condition changes.

Concept Snapshot

SQL uses three-valued logic: TRUE, FALSE, UNKNOWN (NULL).
Comparisons with NULL using '=' yield UNKNOWN.
WHERE clause filters only TRUE rows.
UNKNOWN acts like FALSE in filtering.
Use IS NULL to check NULL values explicitly.

Full Transcript

In SQL, conditions can be TRUE, FALSE, or UNKNOWN due to NULL values. When evaluating a condition like 'age > 30 AND name = NULL', the first part may be TRUE, but 'name = NULL' results in UNKNOWN because NULL means unknown value. SQL treats UNKNOWN as neither TRUE nor FALSE, so rows with UNKNOWN conditions are excluded from query results. This three-valued logic ensures that NULLs are handled carefully. Using 'IS NULL' instead of '=' checks for NULL explicitly and returns TRUE or FALSE, avoiding UNKNOWN. Understanding this helps avoid unexpected query results.