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.