Three-valued logic (TRUE, FALSE, UNKNOWN) in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of WHERE clause with NULL comparison

Consider a table employees with a column manager_id that can be NULL. What rows will be returned by this query?

SELECT * FROM employees WHERE manager_id = NULL;

SQL

CREATE TABLE employees (id INT, name VARCHAR(50), manager_id INT);
INSERT INTO employees VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', NULL);

A No rows are returned because <code>manager_id = NULL</code> is UNKNOWN for all rows.

B All rows where <code>manager_id</code> is NULL are returned.

C All rows are returned regardless of <code>manager_id</code> value.

D The query causes a syntax error due to NULL comparison.

💡 Hint

Remember that in SQL, = NULL does not behave like normal equality.

In SQL, comparing any value to NULL with = results in UNKNOWN, which is treated as FALSE in WHERE clauses. To check for NULL, use IS NULL.

❓ query_result

intermediate

Effect of NOT on UNKNOWN in WHERE clause

Given a table products with a nullable column discount, what rows will this query return?

SELECT * FROM products WHERE NOT (discount > 10);

SQL

CREATE TABLE products (id INT, name VARCHAR(50), discount INT);
INSERT INTO products VALUES (1, 'Pen', 5), (2, 'Notebook', NULL), (3, 'Eraser', 15);

A Only rows with discount > 10 are returned.

B Only rows with discount <= 10 are returned; rows with NULL discount are excluded.

C Rows with discount <= 10 and discount NULL are returned.

D No rows are returned because NOT on UNKNOWN causes an error.

💡 Hint

Think about how NOT affects UNKNOWN in SQL's three-valued logic.

In SQL, discount > 10 is FALSE for <=10, UNKNOWN for NULL, TRUE for >10. NOT (discount > 10) is TRUE for <=10, UNKNOWN for NULL, FALSE for >10. WHERE clauses only return rows where the condition is TRUE, excluding UNKNOWN and FALSE.

📝 Syntax

advanced

Identify the error in three-valued logic expression

Which of the following WHERE clauses will cause a syntax error due to incorrect use of three-valued logic operators?

A WHERE (status = 'active' OR status IS NULL) AND NOT status = 'inactive'

B WHERE NOT (status = 'active' AND status IS NULL)

C WHERE status = 'active' AND NOT status IS NULL

D WHERE status = 'active' AND NOT (status IS NULL)

💡 Hint

Check operator precedence and how NOT applies to IS NULL.

Option C is invalid because NOT status IS NULL is parsed as (NOT status) IS NULL, which is a syntax error. Correct usage is status IS NOT NULL.

❓ optimization

advanced

Optimizing queries with three-valued logic and NULLs

You want to select rows where score is either greater than 50 or unknown (NULL). Which query is more efficient and why?

A SELECT * FROM results WHERE NOT (score <= 50);

B SELECT * FROM results WHERE score > 50 OR score IS NULL;

C SELECT * FROM results WHERE score >= 51 OR score IS NULL;

D SELECT * FROM results WHERE COALESCE(score, 51) > 50;

💡 Hint

Consider how COALESCE affects NULL handling and index usage.

Option D uses COALESCE to replace NULL with 51, so the condition becomes a simple comparison, which can use indexes better and avoid OR conditions that are less efficient.

🧠 Conceptual

expert

Understanding three-valued logic truth tables

Given the SQL three-valued logic, what is the result of this expression?

TRUE AND UNKNOWN OR NOT FALSE

A TRUE

B FALSE

C UNKNOWN

D Syntax error

💡 Hint

Evaluate step-by-step using SQL's three-valued logic truth tables.

TRUE AND UNKNOWN is UNKNOWN; NOT FALSE is TRUE; UNKNOWN OR TRUE is TRUE.

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