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.