IN and NOT IN operators in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of IN operator with multiple values

What rows will be returned by this query?

SELECT name FROM employees WHERE department_id IN (1, 3, 5);

Assume the employees table has these rows:

• (name: 'Alice', department_id: 1)
• (name: 'Bob', department_id: 2)
• (name: 'Charlie', department_id: 3)
• (name: 'Diana', department_id: 4)
• (name: 'Eve', department_id: 5)

MySQL

SELECT name FROM employees WHERE department_id IN (1, 3, 5);

A Bob, Diana

B Alice, Charlie, Eve

C Alice, Bob, Charlie, Diana, Eve

D No rows returned

💡 Hint

IN operator returns rows where the column matches any value in the list.

The query selects employees whose department_id is either 1, 3, or 5. Only Alice (1), Charlie (3), and Eve (5) match.

❓ query_result

intermediate

Output of NOT IN operator excluding values

What rows will be returned by this query?

SELECT product_name FROM products WHERE category_id NOT IN (2, 4);

Assume the products table has these rows:

• (product_name: 'Pen', category_id: 1)
• (product_name: 'Notebook', category_id: 2)
• (product_name: 'Eraser', category_id: 3)
• (product_name: 'Marker', category_id: 4)
• (product_name: 'Ruler', category_id: 5)

MySQL

SELECT product_name FROM products WHERE category_id NOT IN (2, 4);

A No rows returned

B Notebook, Marker

C Pen, Eraser, Ruler

D Pen, Notebook, Eraser, Marker, Ruler

💡 Hint

NOT IN excludes rows with listed values.

The query excludes products with category_id 2 and 4, so Pen (1), Eraser (3), and Ruler (5) remain.

📝 Syntax

advanced

Identify the syntax error in IN operator usage

Which option contains a syntax error in using the IN operator?

MySQL

SELECT * FROM orders WHERE order_id IN 1, 2, 3;

A ;)3 ,2 ,1( NI di_redro EREHW sredro MORF * TCELES

B SELECT * FROM orders WHERE order_id IN (1, 2, 3);

C SELECT * FROM orders WHERE order_id IN (1 2 3);

D SELECT * FROM orders WHERE order_id IN 1, 2, 3;

💡 Hint

IN operator requires parentheses around the list of values.

Option D is missing parentheses around the list, causing a syntax error.

❓ optimization

advanced

Optimizing a query using IN operator

Which query is more efficient to find customers from specific cities?

Assume customers table has an index on city.

A SELECT * FROM customers WHERE city IN ('New York', 'Los Angeles', 'Chicago');

B SELECT * FROM customers WHERE city = 'New York' OR city = 'Los Angeles' OR city = 'Chicago';

C SELECT * FROM customers WHERE city LIKE '%New York%' OR city LIKE '%Los Angeles%' OR city LIKE '%Chicago%';

D SELECT * FROM customers WHERE city NOT IN ('New York', 'Los Angeles', 'Chicago');

💡 Hint

IN operator is optimized for matching multiple values.

Option A uses IN operator which is concise and can use the index efficiently. Option A is longer and less readable. Option A uses LIKE which is slower. Option A excludes cities, not what is asked.

🧠 Conceptual

expert

Behavior of NOT IN with NULL values

Consider this query:

SELECT * FROM employees WHERE department_id NOT IN (1, NULL, 3);

What will be the result if the department_id column has values 1, 2, 3, and NULL?

A No rows are returned because NOT IN with NULL always yields unknown.

B Rows with department_id 1 and 3 are returned.

C Rows with department_id 2 only are returned.

D All rows except those with NULL department_id are returned.

💡 Hint

NOT IN with NULL in the list causes the condition to evaluate to unknown for all rows.

When NULL is in the list for NOT IN, the comparison results in unknown, so no rows satisfy the condition.

Practice

1. What does the IN operator do in a MySQL query?

easy

A. Checks if a value is greater than all values in a list

B. Checks if a value is less than all values in a list

C. Checks if a value matches any value in a list

D. Checks if a value is equal to a single value only

Solution

1. Step 1: Understand the purpose of IN operator

   The IN operator is used to test if a value exists within a set of values.

2. Step 2: Compare with other options

   The options describing comparisons with all values (greater or less than all) are incorrect, as IN checks if a value matches any in the list. The option about equality to a single value is also wrong, since IN handles multiple values.

3. Final Answer:

   Checks if a value matches any value in a list -> Option C

4. Quick Check:

   IN operator = check value in list [OK]

Hint: IN means "is in this list"; NOT IN means "not in this list" [OK]

Common Mistakes:

• Confusing IN with equality (=) operator
• Thinking IN checks all values instead of any
• Mixing IN with greater than or less than comparisons

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

easy

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

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

C. SELECT * FROM table WHERE status NOT IN ('active', 'pending', 'closed');

D. SELECT * FROM table WHERE status IN 'active', 'pending', 'closed';

Solution

1. Step 1: Check correct IN syntax

   The correct syntax uses IN followed by parentheses containing a comma-separated list of values.

2. Step 2: Identify errors in other options

   SELECT * FROM table WHERE status = IN ('active', 'pending', 'closed'); has an extra '=' which is invalid. SELECT * FROM table WHERE status NOT IN ('active', 'pending', 'closed'); uses NOT IN which selects rows not matching the list. SELECT * FROM table WHERE status IN 'active', 'pending', 'closed'; misses parentheses around the list.

3. Final Answer:

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

4. Quick Check:

   Correct IN syntax uses parentheses and no '=' [OK]

Hint: IN must be followed by parentheses with values inside [OK]

Common Mistakes:

• Using '=' before IN
• Omitting parentheses around the list
• Confusing IN with NOT IN

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

SELECT name FROM employees WHERE department NOT IN ('HR', 'Finance');

medium

A. Names of employees in HR or Finance departments

B. Names of employees not in HR or Finance departments

C. Names of employees only in HR department

D. Syntax error due to NOT IN usage

Solution

1. Step 1: Understand NOT IN operator

   NOT IN selects rows where the column value is not in the given list.

2. Step 2: Apply to the query context

   The query returns employee names whose department is not 'HR' or 'Finance'.

3. Final Answer:

   Names of employees not in HR or Finance departments -> Option B

4. Quick Check:

   NOT IN excludes listed values [OK]

Hint: NOT IN excludes listed values from results [OK]

Common Mistakes:

• Thinking NOT IN returns only listed values
• Confusing NOT IN with IN
• Assuming syntax error with NOT IN

4. Identify the error in this query:

SELECT * FROM orders WHERE order_status NOT IN 'shipped', 'delivered';

medium

A. Missing parentheses around the list after NOT IN

B. Incorrect use of NOT IN instead of IN

C. order_status should be in quotes

D. No error, query is correct

Solution

1. Step 1: Check syntax of NOT IN usage

   NOT IN must be followed by parentheses containing the list of values.

2. Step 2: Identify missing parentheses

   The query lists values without parentheses, causing syntax error.

3. Final Answer:

   Missing parentheses around the list after NOT IN -> Option A

4. Quick Check:

   NOT IN requires parentheses around values [OK]

Hint: Always use parentheses after IN or NOT IN [OK]

Common Mistakes:

• Omitting parentheses around value list
• Using quotes incorrectly around column names
• Confusing IN and NOT IN usage

5. You want to find customers who have placed orders but exclude those from countries 'USA', 'Canada', and 'Mexico'. Which query correctly uses NOT IN to achieve this?

hard

A. SELECT customer_id FROM orders WHERE country != ('USA', 'Canada', 'Mexico');

B. SELECT customer_id FROM orders WHERE country IN ('USA', 'Canada', 'Mexico');

C. SELECT customer_id FROM orders WHERE country NOT IN 'USA', 'Canada', 'Mexico';

D. SELECT customer_id FROM orders WHERE country NOT IN ('USA', 'Canada', 'Mexico');

Solution

1. Step 1: Understand the goal

   We want customers excluding those from USA, Canada, and Mexico.

2. Step 2: Choose correct NOT IN syntax

   SELECT customer_id FROM orders WHERE country NOT IN ('USA', 'Canada', 'Mexico'); uses NOT IN with parentheses and a list, correctly excluding those countries.

3. Step 3: Identify errors in other options

   SELECT customer_id FROM orders WHERE country IN ('USA', 'Canada', 'Mexico'); includes those countries (wrong). SELECT customer_id FROM orders WHERE country NOT IN 'USA', 'Canada', 'Mexico'; misses parentheses (syntax error). SELECT customer_id FROM orders WHERE country != ('USA', 'Canada', 'Mexico'); uses != with a list, which is invalid syntax.

4. Final Answer:

   SELECT customer_id FROM orders WHERE country NOT IN ('USA', 'Canada', 'Mexico'); -> Option D

5. Quick Check:

   NOT IN with parentheses excludes listed values [OK]

Hint: Use NOT IN with parentheses to exclude multiple values [OK]

Common Mistakes:

• Using != with multiple values
• Omitting parentheses after NOT IN
• Confusing IN and NOT IN