NULL behavior in comparisons in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of NULL comparison in WHERE clause

Consider a table Employees with a column ManagerID that can be NULL. What rows will this query return?

SELECT * FROM Employees WHERE ManagerID = NULL;

SQL

SELECT * FROM Employees WHERE ManagerID = NULL;

A All rows are returned regardless of ManagerID value.

B All rows where ManagerID is NULL are returned.

C No rows are returned because NULL = NULL is unknown.

D The query causes a syntax error.

💡 Hint

Remember that NULL means unknown, so comparisons with NULL don't behave like normal values.

In SQL, comparing any value to NULL using = returns unknown, which is treated as false in WHERE clauses. To check for NULL, use IS NULL.

❓ query_result

intermediate

Effect of IS NULL vs = NULL in filtering

Given a table Orders with a column ShippedDate that can be NULL, which query returns all orders that have not been shipped?

1) SELECT * FROM Orders WHERE ShippedDate = NULL;
2) SELECT * FROM Orders WHERE ShippedDate IS NULL;

SQL

SELECT * FROM Orders WHERE ShippedDate IS NULL;

A Neither query returns any rows.

B Only query 1 returns the correct rows.

C Both queries return the same rows.

D Only query 2 returns the correct rows.

💡 Hint

Think about how SQL treats NULL in equality comparisons versus IS NULL.

The = NULL comparison always returns unknown, so query 1 returns no rows. Query 2 uses IS NULL which correctly filters rows with NULL values.

🧠 Conceptual

advanced

Understanding three-valued logic with NULL

In SQL, what is the result of the expression (NULL <> 5) in a WHERE clause filter?

A TRUE

B UNKNOWN (treated as FALSE in WHERE)

C FALSE

D Syntax error

💡 Hint

Remember SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN.

Any comparison with NULL results in UNKNOWN, which acts like FALSE in WHERE filters, so the row is not selected.

📝 Syntax

advanced

Correct syntax to check for NULL in SQL

Which of the following SQL WHERE clauses correctly filters rows where the column Price is NULL?

A WHERE Price IS NULL

B WHERE Price == NULL

C WHERE Price = NULL

D WHERE Price != NULL

💡 Hint

Think about the special keyword used to check NULL values.

The correct syntax to check for NULL is using IS NULL. Using = NULL or != NULL is invalid or returns unknown.

❓ optimization

expert

Optimizing queries with NULL checks

You want to find all customers who have not placed any orders. The Orders table has a nullable CustomerID column. Which query is most efficient and correct?

A) SELECT * FROM Customers WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);
B) SELECT * FROM Customers WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE CustomerID IS NOT NULL);
C) SELECT * FROM Customers WHERE CustomerID NOT EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);
D) SELECT * FROM Customers WHERE CustomerID IS NULL;

A Option C

B Option A

C Option D

D Option B

💡 Hint

Consider how NULLs affect NOT IN and EXISTS clauses.

NOT IN with NULLs in subquery can cause no rows to be returned due to unknown comparisons. Using NOT EXISTS avoids this problem and is more efficient.