WHERE with IS NULL and IS NOT NULL in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find rows with NULL values

Given a table Employees with columns id, name, and manager_id (which can be NULL), which query returns all employees who do not have a manager assigned?

SQL

SELECT * FROM Employees WHERE manager_id IS NULL;

A SELECT * FROM Employees WHERE manager_id IS NOT NULL;

B SELECT * FROM Employees WHERE manager_id = NULL;

C SELECT * FROM Employees WHERE manager_id != NULL;

D SELECT * FROM Employees WHERE manager_id IS NULL;

💡 Hint

Remember that NULL is not equal to anything, even NULL itself.

In SQL, to check for NULL values, you must use IS NULL. Using '=' or '!=' with NULL does not work as expected and returns no rows.

❓ query_result

intermediate

Exclude rows with NULL values

Which query returns all rows from the Orders table where the shipped_date is known (not NULL)?

SQL

SELECT * FROM Orders WHERE shipped_date IS NOT NULL;

A SELECT * FROM Orders WHERE shipped_date != NULL;

B SELECT * FROM Orders WHERE shipped_date IS NULL;

C SELECT * FROM Orders WHERE shipped_date IS NOT NULL;

D SELECT * FROM Orders WHERE shipped_date = NULL;

💡 Hint

Use the correct syntax to filter out NULL values.

To find rows where a column is not NULL, use IS NOT NULL. Using '=' or '!=' with NULL does not work properly.

📝 Syntax

advanced

Identify the syntax error in NULL check

Which option contains a syntax error when trying to select rows where email is NULL in the Users table?

SQL

SELECT * FROM Users WHERE email IS NULL;

A SELECT * FROM Users WHERE email = NULL;

B SELECT * FROM Users WHERE email IS NULL;

C SELECT * FROM Users WHERE email IS NOT NULL;

D SELECT * FROM Users WHERE email != NULL;

💡 Hint

Check how NULL comparisons are done in SQL.

Using '=' with NULL is not a syntax error but returns no rows because NULL is not comparable with '='. The correct syntax is IS NULL.

❓ query_result

advanced

Count rows with NULL values

What is the result of this query on the Products table counting rows where discount_price is NULL?

SQL

SELECT COUNT(*) FROM Products WHERE discount_price IS NULL;

A Returns the total number of products regardless of discount_price.

B Returns the number of products without a discount price.

C Returns zero because NULL values are ignored in COUNT.

D Returns an error because COUNT cannot be used with IS NULL.

💡 Hint

COUNT(*) counts all rows that match the WHERE condition.

The query counts all rows where discount_price is NULL, so it returns the number of products without a discount price.

🧠 Conceptual

expert

Understanding NULL behavior in WHERE clause

Consider the query: SELECT * FROM Customers WHERE phone_number != '123-456-7890'; Which statement is true about rows where phone_number is NULL?

A Rows with NULL phone_number are excluded because any comparison with NULL returns UNKNOWN.

B Rows with NULL phone_number are included because NULL is not equal to '123-456-7890'.

C Rows with NULL phone_number cause a runtime error in the query.

D Rows with NULL phone_number are included only if IS NOT NULL is added.

💡 Hint

Think about how SQL treats NULL in comparisons.

In SQL, any comparison with NULL returns UNKNOWN, so rows with NULL phone_number are excluded unless explicitly checked with IS NULL or IS NOT NULL.