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.

Practice

1. Which SQL clause correctly finds rows where the email column has no value stored (is missing)?

easy

A. WHERE email = NULL

B. WHERE email = ''

C. WHERE email IS NULL

D. WHERE email IS NOT NULL

Solution

1. Step 1: Understand NULL meaning in SQL

   NULL means no value or missing data, not an empty string or zero.

2. Step 2: Use correct syntax to check NULL

   To check for NULL, use IS NULL, not = NULL or empty string.

3. Final Answer:

   WHERE email IS NULL -> Option C

4. Quick Check:

   Check NULL with IS NULL = WHERE email IS NULL [OK]

Hint: Use IS NULL to find missing values, never = NULL [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Confusing empty string '' with NULL
• Using IS NOT NULL to find missing values

2. Which of the following is the correct syntax to select rows where the phone column is NOT missing?

easy

A. SELECT * FROM contacts WHERE phone IS NOT NULL;

B. SELECT * FROM contacts WHERE phone = NOT NULL;

C. SELECT * FROM contacts WHERE phone != NULL;

D. SELECT * FROM contacts WHERE phone NOT NULL;

Solution

1. Step 1: Recall correct syntax for NOT NULL check

   SQL uses IS NOT NULL to find rows where value exists.

2. Step 2: Identify syntax errors in other options

   Options B, C, D use invalid syntax like = NOT NULL or != NULL or missing IS.

3. Final Answer:

   SELECT * FROM contacts WHERE phone IS NOT NULL; -> Option A

4. Quick Check:

   Use IS NOT NULL to find existing values = SELECT * FROM contacts WHERE phone IS NOT NULL; [OK]

Hint: Use IS NOT NULL, not = NOT NULL or != NULL [OK]

Common Mistakes:

• Using = NOT NULL instead of IS NOT NULL
• Using != NULL which is invalid
• Omitting IS keyword

3. Given the table employees with columns id, name, and manager_id where some employees have no manager (NULL), what will this query return?

SELECT name FROM employees WHERE manager_id IS NULL;

medium

A. All employees who have a manager assigned

B. All employees who do not have a manager assigned

C. All employees including those with and without managers

D. Syntax error due to IS NULL usage

Solution

1. Step 1: Understand IS NULL condition

   The condition manager_id IS NULL filters rows where manager_id has no value (missing).

2. Step 2: Interpret query result

   The query returns names of employees without any manager assigned (NULL manager_id).

3. Final Answer:

   All employees who do not have a manager assigned -> Option B

4. Quick Check:

   IS NULL filters missing values = All employees who do not have a manager assigned [OK]

Hint: IS NULL finds missing values, so returns employees without managers [OK]

Common Mistakes:

• Thinking IS NULL finds rows with values
• Confusing NULL with empty string
• Assuming syntax error with IS NULL

4. Identify the error in this query that tries to select rows where address is NOT NULL:

SELECT * FROM customers WHERE address != NULL;

medium

A. Should use = NULL instead of != NULL

B. Missing quotes around NULL

C. No error; query is correct

D. Using != NULL is invalid; should use IS NOT NULL

Solution

1. Step 1: Check NULL comparison syntax

   In SQL, NULL cannot be compared with = or != operators.

2. Step 2: Use correct syntax for NOT NULL

   The correct way is IS NOT NULL to check for non-missing values.

3. Final Answer:

   Using != NULL is invalid; should use IS NOT NULL -> Option D

4. Quick Check:

   Use IS NOT NULL, not != NULL [OK]

Hint: Never use = or != with NULL; use IS (NOT) NULL [OK]

Common Mistakes:

• Using != NULL or = NULL
• Adding quotes around NULL
• Assuming != NULL works like other values

5. You have a table orders with columns order_id, customer_id, and shipped_date. Some orders are not shipped yet and have shipped_date as NULL. Which query lists all orders that have been shipped?

hard

A. SELECT * FROM orders WHERE shipped_date IS NOT NULL;

B. SELECT * FROM orders WHERE shipped_date IS NULL;

C. SELECT * FROM orders WHERE shipped_date != NULL;

D. SELECT * FROM orders WHERE shipped_date = NULL;

Solution

1. Step 1: Understand meaning of shipped_date NULL

   NULL means order not shipped yet, so shipped orders have non-NULL dates.

2. Step 2: Use correct syntax to find shipped orders

   Use IS NOT NULL to find rows where shipped_date has a value.

3. Final Answer:

   SELECT * FROM orders WHERE shipped_date IS NOT NULL; -> Option A

4. Quick Check:

   IS NOT NULL finds shipped orders = SELECT * FROM orders WHERE shipped_date IS NOT NULL; [OK]

Hint: IS NOT NULL finds rows with values, perfect for shipped orders [OK]

Common Mistakes:

• Using = NULL or != NULL which are invalid
• Using IS NULL to find shipped orders
• Confusing NULL with empty string