IS NULL and IS NOT NULL in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of IS NULL filter

Given the table Employees with columns id, name, and manager_id where manager_id can be NULL, what is the output of this query?

SELECT name FROM Employees WHERE manager_id IS NULL;

MySQL

CREATE TABLE Employees (id INT, name VARCHAR(20), manager_id INT);
INSERT INTO Employees VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', NULL), (4, 'Diana', 2);

A [{"name": "Alice"}, {"name": "Charlie"}]

B [{"name": "Alice"}, {"name": "Bob"}, {"name": "Charlie"}, {"name": "Diana"}]

C []

D [{"name": "Bob"}, {"name": "Diana"}]

💡 Hint

Look for rows where the manager_id column has no value (NULL).

The query filters rows where manager_id IS NULL, so only employees without a manager are returned: Alice and Charlie.

❓ query_result

intermediate

Output of IS NOT NULL filter

Using the same Employees table, what is the output of this query?

SELECT name FROM Employees WHERE manager_id IS NOT NULL;

MySQL

CREATE TABLE Employees (id INT, name VARCHAR(20), manager_id INT);
INSERT INTO Employees VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', NULL), (4, 'Diana', 2);

A [{"name": "Alice"}, {"name": "Charlie"}]

B []

C [{"name": "Alice"}, {"name": "Bob"}, {"name": "Charlie"}, {"name": "Diana"}]

D [{"name": "Bob"}, {"name": "Diana"}]

💡 Hint

Look for rows where the manager_id column has a value (not NULL).

The query returns employees who have a manager_id value, which are Bob and Diana.

🧠 Conceptual

advanced

Understanding NULL comparison behavior

Which statement about NULL in SQL is correct?

A NULL can be compared with any value using = and <> operators safely.

B NULL <> NULL returns FALSE because NULL is not equal to itself.

C NULL comparisons using = or <> always return UNKNOWN, so IS NULL or IS NOT NULL must be used.

D NULL = NULL returns TRUE because both are unknown values.

💡 Hint

Think about how SQL treats NULL as an unknown value.

In SQL, NULL means unknown, so comparisons with = or <> return UNKNOWN, not TRUE or FALSE. IS NULL and IS NOT NULL are used to check for NULL values.

📝 Syntax

advanced

Identify the common mistake in NULL check

Which option contains a common mistake when checking for NULL values in MySQL?

A SELECT * FROM Orders WHERE shipped_date = NULL;

B SELECT * FROM Orders WHERE shipped_date IS NOT NULL;

C SELECT * FROM Orders WHERE shipped_date IS NULL;

D SELECT * FROM Orders WHERE shipped_date <> NULL;

💡 Hint

Check which one is incorrect for NULL comparison.

Using = NULL or <> NULL is incorrect in MySQL for NULL checks because they always evaluate to UNKNOWN. IS NULL and IS NOT NULL must be used instead.

❓ optimization

expert

Optimizing NULL checks in WHERE clause

Consider a large table Products with a nullable column discount_price. Which query is the most efficient to find products without a discount price?

A SELECT * FROM Products WHERE discount_price = NULL;

B SELECT * FROM Products WHERE discount_price IS NULL;

C SELECT * FROM Products WHERE NOT discount_price IS NOT NULL;

D SELECT * FROM Products WHERE discount_price <> discount_price;

💡 Hint

Think about correct and efficient NULL checks.

Option B uses the correct and straightforward syntax to check for NULL values. Option B does not work because it always evaluates to UNKNOWN. Option B is logically correct but less clear and may be less optimized. Option B is a trick that returns no rows because NULL <> NULL is UNKNOWN.