WHERE with IN list in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of WHERE with IN list filtering

Given the table Employees with columns id, name, and department, what is the output of this query?

SELECT name FROM Employees WHERE department IN ('Sales', 'Marketing');

SQL

CREATE TABLE Employees (id INT, name VARCHAR(50), department VARCHAR(50));
INSERT INTO Employees VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'Engineering'),
(3, 'Charlie', 'Marketing'),
(4, 'Diana', 'HR');

A Alice, Charlie

B Bob, Diana

C Alice, Bob, Charlie

D Charlie, Diana

💡 Hint

Look for employees whose department is either 'Sales' or 'Marketing'.

The WHERE clause filters rows where the department is in the list ('Sales', 'Marketing'). Only Alice (Sales) and Charlie (Marketing) match.

📝 Syntax

intermediate

Identify the syntax error in WHERE with IN list

Which option contains a syntax error in the use of the IN list in the WHERE clause?

SQL

SELECT * FROM Products WHERE category IN ('Books', 'Electronics');

A SELECT * FROM Products WHERE category IN ('Books' 'Electronics');

B SELECT * FROM Products WHERE category IN 'Books', 'Electronics';

C SELECT * FROM Products WHERE category IN ('Books', 'Electronics');

D SELECT * FROM Products WHERE category IN ('Books', 'Electronics',);

💡 Hint

Check the parentheses and commas around the list values.

Option B is missing parentheses around the list, causing a syntax error. The IN operator requires parentheses enclosing the list.

❓ optimization

advanced

Optimizing WHERE with IN list for large datasets

You have a large table Orders with millions of rows. You want to filter orders where status is in a list of 1000 possible values. Which approach is most efficient?

A Use WHERE status IN (list of 1000 values) directly in the query.

B Filter the Orders table in application code after selecting all rows.

C Use multiple OR conditions like WHERE status = val1 OR status = val2 ... for all 1000 values.

D Create a temporary table with the 1000 values and join it with Orders on status.

💡 Hint

Consider how databases optimize joins versus large IN lists.

Joining with a temporary table containing the 1000 values is more efficient than a large IN list or many OR conditions. Filtering in application code is inefficient.

🧠 Conceptual

advanced

Understanding NULL behavior with WHERE IN list

Consider a table Students with a column grade that can be NULL. What is the result of this query?

SELECT * FROM Students WHERE grade IN (NULL, 'A', 'B');

A Returns all rows regardless of grade.

B Returns rows where grade is 'A', 'B', or NULL.

C Returns rows where grade is 'A' or 'B' only, NULLs excluded.

D Returns no rows because NULL in IN list causes error.

💡 Hint

Remember how NULL comparisons behave in SQL.

NULL in an IN list does not match any value, including NULLs. So only rows with grade 'A' or 'B' are returned.

🔧 Debug

expert

Debugging unexpected results with WHERE IN list

A developer runs this query:

SELECT * FROM Inventory WHERE product_code IN ('P001', 'P002', 'P003');

But the result is empty even though these product codes exist. Which option explains the most likely cause?

A The product_code column has trailing spaces, so values don't match the list exactly.

B The IN list syntax is incorrect and causes a syntax error.

C The product_code column is numeric but the list uses strings.

D The table Inventory is empty.

💡 Hint

Think about string matching and whitespace in SQL.

If product_code values have trailing spaces, they won't match the exact strings in the IN list, causing no rows to be returned.

Practice

1. What does the SQL clause WHERE column_name IN (value1, value2, value3) do?

easy

A. It selects rows where column_name matches any value in the list.

B. It updates rows with values in the list.

C. It deletes rows not matching the list.

D. It creates a new table with listed values.

Solution

1. Step 1: Understand the IN clause purpose

   The IN clause checks if a column's value matches any value inside the parentheses list.

2. Step 2: Identify the effect on row selection

   Rows with the column value equal to any listed value are selected; others are ignored.

3. Final Answer:

   It selects rows where column_name matches any value in the list. -> Option A

4. Quick Check:

   WHERE with IN filters rows by listed values [OK]

Hint: IN checks if value matches any in the list [OK]

Common Mistakes:

• Thinking IN updates or deletes rows
• Confusing IN with creating tables
• Assuming IN matches all values at once

2. Which of the following is the correct syntax to select rows where status is either 'active', 'pending', or 'closed'?

easy

A. SELECT * FROM orders WHERE status = IN ('active', 'pending', 'closed');

B. SELECT * FROM orders WHERE status IN ('active', 'pending', 'closed');

C. SELECT * FROM orders WHERE status IN 'active', 'pending', 'closed';

D. SELECT * FROM orders WHERE status IN ('active' 'pending' 'closed');

Solution

1. Step 1: Check correct use of IN syntax

   The correct syntax is column_name IN (value1, value2, ...) without an equals sign.

2. Step 2: Identify correct punctuation and parentheses

   Values must be inside parentheses and separated by commas.

3. Final Answer:

   SELECT * FROM orders WHERE status IN ('active', 'pending', 'closed'); -> Option B

4. Quick Check:

   IN syntax = column IN (values) [OK]

Hint: No '=' before IN; use commas inside parentheses [OK]

Common Mistakes:

• Using '=' before IN
• Missing commas between values
• Omitting parentheses around values

3. Given the table employees with column department, what rows will this query return?

SELECT * FROM employees WHERE department IN ('HR', 'Sales');

medium

A. Rows where department is 'HR' or 'Sales'.

B. Rows where department is 'HR' only.

C. Rows where department is not 'HR' or 'Sales'.

D. All rows regardless of department.

Solution

1. Step 1: Understand the IN filter on department

   The query filters rows where department matches any value in ('HR', 'Sales').

2. Step 2: Determine which rows match

   Only rows with department 'HR' or 'Sales' are returned; others are excluded.

3. Final Answer:

   Rows where department is 'HR' or 'Sales'. -> Option A

4. Quick Check:

   IN filters rows matching listed values [OK]

Hint: IN returns rows matching any listed value [OK]

Common Mistakes:

• Thinking it returns rows not in the list
• Assuming it returns only one value
• Confusing IN with NOT IN

4. Identify the error in this SQL query:

SELECT * FROM products WHERE category IN 'Electronics', 'Toys', 'Books';

medium

A. Incorrect table name.

B. Using IN instead of = operator.

C. Missing parentheses around the list of values.

D. Missing commas between values.

Solution

1. Step 1: Check syntax of IN clause

   The list of values after IN must be enclosed in parentheses.

2. Step 2: Identify missing parentheses

   The query has values listed without parentheses, causing syntax error.

3. Final Answer:

   Missing parentheses around the list of values. -> Option C

4. Quick Check:

   IN requires parentheses around values [OK]

Hint: Always put parentheses around IN value list [OK]

Common Mistakes:

• Omitting parentheses around values
• Using IN with equals sign
• Forgetting commas between values

5. You want to select customers from the orders table whose country is either 'USA', 'Canada', or 'Mexico', but exclude those with status 'cancelled'. Which query correctly uses WHERE with IN and excludes cancelled orders?

hard

A. SELECT * FROM orders WHERE country IN 'USA', 'Canada', 'Mexico' AND status != 'cancelled';

B. SELECT * FROM orders WHERE country IN ('USA', 'Canada', 'Mexico') OR status != 'cancelled';

C. SELECT * FROM orders WHERE country = IN ('USA', 'Canada', 'Mexico') AND status != 'cancelled';

D. SELECT * FROM orders WHERE country IN ('USA', 'Canada', 'Mexico') AND status != 'cancelled';

Solution

1. Step 1: Use IN correctly for country filter

   The country column must be checked with IN ('USA', 'Canada', 'Mexico') without equals sign.

2. Step 2: Exclude cancelled orders with AND condition

   Use AND status != 'cancelled' to exclude those rows.

3. Final Answer:

   SELECT * FROM orders WHERE country IN ('USA', 'Canada', 'Mexico') AND status != 'cancelled'; -> Option D

4. Quick Check:

   IN filters list, AND excludes cancelled [OK]

Hint: Use AND to combine IN filter and exclusion [OK]

Common Mistakes:

• Using OR instead of AND causing wrong results
• Adding '=' before IN
• Omitting parentheses around IN values