Three-valued logic (TRUE, FALSE, UNKNOWN) in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What are the three possible truth values in SQL's three-valued logic?

The three truth values are TRUE, FALSE, and UNKNOWN. UNKNOWN represents a condition where the truth cannot be determined, often due to NULL values.

beginner

How does SQL treat NULL in logical expressions?

In SQL, NULL represents an unknown or missing value. When used in logical expressions, it results in the UNKNOWN truth value, which affects the outcome of conditions.

intermediate

What is the result of the expression: TRUE AND UNKNOWN in SQL?

The result is UNKNOWN because TRUE AND UNKNOWN cannot be determined as TRUE or FALSE due to the unknown part.

intermediate

Explain the behavior of the NOT operator when applied to UNKNOWN in SQL.

NOT UNKNOWN results in UNKNOWN. Negating an unknown truth value remains unknown because the truth is still not determined.

beginner

Why is three-valued logic important in SQL queries involving NULLs?

Three-valued logic allows SQL to handle NULLs safely by introducing UNKNOWN. This prevents incorrect TRUE or FALSE assumptions when data is missing or unknown, ensuring accurate query results.

What does the UNKNOWN truth value represent in SQL?

A A condition that is definitely TRUE

B A condition that is definitely FALSE

C A syntax error in the query

D A condition where the truth cannot be determined

UNKNOWN means the truth of the condition cannot be determined, often due to NULL values.

What is the result of NULL OR TRUE in SQL's three-valued logic?

A TRUE

B FALSE

C UNKNOWN

D NULL

NULL OR TRUE evaluates to TRUE because OR returns TRUE if any operand is TRUE.

Which logical operator in SQL can return UNKNOWN as a result?

A AND

B NOT

C All of the above

D OR

AND, OR, and NOT can all return UNKNOWN when NULLs are involved.

What happens when you compare a value to NULL using = in SQL?

A Returns UNKNOWN

B Returns FALSE

C Returns TRUE

D Throws an error

Comparing anything to NULL with = results in UNKNOWN because NULL means unknown value.

How does SQL treat the expression: NOT FALSE?

A UNKNOWN

B TRUE

C FALSE

D NULL

NOT FALSE is TRUE in SQL's logic.

Describe how three-valued logic works in SQL and why it is necessary.

Think about how missing data affects truth in conditions.

Explain the result of logical operations (AND, OR, NOT) when one operand is UNKNOWN.

Consider how unknown truth affects each operator.

Practice

1. In SQL's three-valued logic, what is the result of comparing a column value to NULL using the = operator?

easy

A. TRUE

B. NULL

C. FALSE

D. UNKNOWN

Solution

1. Step 1: Understand comparison with NULL

   In SQL, any comparison with NULL using = does not return TRUE or FALSE but UNKNOWN.

2. Step 2: Recall three-valued logic rules

   Since NULL means unknown, the comparison result is UNKNOWN, not TRUE or FALSE.

3. Final Answer:

   UNKNOWN -> Option D

4. Quick Check:

   Comparison with NULL = UNKNOWN [OK]

Hint: Comparing with NULL using '=' always yields UNKNOWN [OK]

Common Mistakes:

• Assuming NULL equals NULL returns TRUE
• Thinking NULL comparison returns FALSE
• Confusing NULL with a string 'NULL'

2. Which of the following SQL WHERE clause conditions correctly checks if a column col contains NULL values?

easy

A. WHERE col = NULL

B. WHERE col != NULL

C. WHERE col IS NULL

D. WHERE col = 'NULL'

Solution

1. Step 1: Identify correct NULL check syntax

   In SQL, to check for NULL values, you must use IS NULL because = NULL does not work.

2. Step 2: Evaluate each option

   WHERE col IS NULL uses IS NULL, which is the correct syntax. Others compare with NULL using = or treat NULL as a string, which is incorrect.

3. Final Answer:

   WHERE col IS NULL -> Option C

4. Quick Check:

   Use IS NULL to check NULL values [OK]

Hint: Use IS NULL, not = NULL, to check for NULL values [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Comparing NULL as a string 'NULL'
• Using != NULL to find non-NULL values

3. Given a table Employees with a column Bonus that can be NULL, what will the query SELECT * FROM Employees WHERE Bonus > 1000; return for rows where Bonus is NULL?

medium

A. Rows with NULL Bonus are excluded

B. Rows with NULL Bonus are included

C. Query returns an error

D. Rows with NULL Bonus are returned as FALSE

Solution

1. Step 1: Understand comparison with NULL in WHERE clause

   Comparing NULL with any value using > results in UNKNOWN, which is treated as FALSE in WHERE filtering.

2. Step 2: Determine which rows are returned

   Rows where Bonus is NULL do not satisfy Bonus > 1000 condition, so they are excluded from the result.

3. Final Answer:

   Rows with NULL Bonus are excluded -> Option A

4. Quick Check:

   NULL comparison in WHERE excludes rows [OK]

Hint: WHERE excludes rows with NULL in condition comparisons [OK]

Common Mistakes:

• Assuming NULL > 1000 returns TRUE
• Expecting NULL rows to appear in results
• Thinking query throws error due to NULL

4. You wrote this query: SELECT * FROM Orders WHERE Status = 'Shipped' OR Status <> 'Shipped'; but it returns fewer rows than expected. What is the likely cause?

medium

A. Rows with NULL Status are excluded due to three-valued logic

B. Syntax error in the query

C. The OR operator is invalid here

D. The query returns all rows regardless of Status

Solution

1. Step 1: Analyze the WHERE clause logic

   The condition checks if Status equals 'Shipped' OR Status not equals 'Shipped'.

2. Step 2: Consider NULL values in Status

   For rows where Status is NULL, both comparisons return UNKNOWN, so those rows are excluded.

3. Final Answer:

   Rows with NULL Status are excluded due to three-valued logic -> Option A

4. Quick Check:

   NULL comparisons exclude rows in OR conditions [OK]

Hint: NULL in OR conditions can exclude rows unexpectedly [OK]

Common Mistakes:

• Assuming NULL rows satisfy OR condition
• Thinking query has syntax error
• Believing OR always returns all rows

5. You want to select all rows from Products where Discount is either greater than 20 or unknown (NULL). Which WHERE clause correctly implements this using three-valued logic?

hard

A. WHERE Discount > 20 OR Discount = NULL

B. WHERE Discount > 20 OR Discount IS NULL

C. WHERE Discount > 20 AND Discount IS NULL

D. WHERE Discount >= 20 OR Discount IS NOT NULL

Solution

1. Step 1: Identify correct NULL check syntax

   To check for NULL values, use IS NULL, not = NULL.

2. Step 2: Combine conditions correctly

   You want rows where Discount is greater than 20 OR Discount is NULL, so use OR with Discount > 20 and Discount IS NULL.

3. Final Answer:

   WHERE Discount > 20 OR Discount IS NULL -> Option B

4. Quick Check:

   Use IS NULL with OR for NULL checks [OK]

Hint: Use IS NULL with OR to include NULLs in conditions [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Using AND instead of OR
• Checking IS NOT NULL instead of IS NULL