Subquery with IN operator in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the IN operator do in SQL when used with a subquery?

The IN operator checks if a value matches any value in the list returned by the subquery. It helps filter rows based on whether a column's value exists in the subquery result.

beginner

Write a simple example of a subquery with IN operator to find employees who work in departments located in 'New York'.

SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

intermediate

Can the subquery used with IN operator return multiple columns?

No, the subquery used with IN operator must return only one column. This column's values are compared against the outer query's column values.

intermediate

What happens if the subquery with IN operator returns no rows?

If the subquery returns no rows, the IN condition evaluates to false for all rows in the outer query, so no rows are returned.

intermediate

Why might you use a subquery with IN operator instead of a JOIN?

Using IN with a subquery can be simpler and more readable when you only need to check existence of values. JOINs are better for combining columns from multiple tables.

What must a subquery used with the IN operator return?

A No rows

B Multiple columns of values

C A single row with multiple columns

D A single column of values

The subquery must return a single column of values to compare with the outer query's column.

If the subquery returns no rows, what will the IN operator do?

A Return all rows from the outer query

B Cause an error

C Return no rows from the outer query

D Return NULL values

If the subquery returns no rows, the IN condition is false for all rows, so no rows are returned.

Which SQL keyword is used to check if a value exists in a list returned by a subquery?

A IN

B EXISTS

C JOIN

D LIKE

The IN operator checks if a value exists in the list returned by the subquery.

Which of these is a valid use of IN with a subquery?

A SELECT * FROM employees WHERE department_id IN (SELECT department_id, location FROM departments);

B SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments);

C SELECT * FROM employees WHERE department_id IN (SELECT location FROM departments);

D SELECT * FROM employees WHERE department_id IN ();

The subquery must return a single column; option A is valid.

Why might you choose IN with a subquery over a JOIN?

A IN is simpler when only checking existence of values

B IN returns more columns

C JOIN cannot combine tables

D IN is faster for all queries

IN with a subquery is simpler and more readable when just checking if values exist.

Explain how the IN operator works with a subquery in SQL.

Think about checking if a value is inside a list of values.

Describe a scenario where using a subquery with IN operator is helpful.

Imagine you want to find employees working in certain departments.

Practice

1. What does the IN operator do when used with a subquery in SQL?

easy

A. It checks if a value matches any value returned by the subquery.

B. It updates values in the main query based on the subquery.

C. It deletes rows that are returned by the subquery.

D. It creates a new table from the subquery results.

Solution

1. Step 1: Understand the role of IN operator

   The IN operator compares a value to a list of values and returns true if it matches any of them.

2. Step 2: Understand subquery usage

   The subquery returns a list of values that the main query uses to filter rows with the IN operator.

3. Final Answer:

   It checks if a value matches any value returned by the subquery. -> Option A

4. Quick Check:

   IN with subquery = match any value [OK]

Hint: IN checks if value is inside subquery result list [OK]

Common Mistakes:

• Thinking IN updates or deletes rows
• Confusing IN with JOIN
• Assuming IN creates new tables

2. Which of the following is the correct syntax to use a subquery with the IN operator?

easy

A. SELECT * FROM employees WHERE IN department_id (SELECT id FROM departments);

B. SELECT * FROM employees WHERE department_id = IN (SELECT id FROM departments);

C. SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments);

D. SELECT * FROM employees WHERE department_id IN SELECT id FROM departments;

Solution

1. Step 1: Review correct IN syntax

   The IN operator must be followed by parentheses enclosing the subquery.

2. Step 2: Check each option

   SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments); correctly uses IN with parentheses and a subquery. Options A, B, and D have syntax errors.

3. Final Answer:

   SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments); -> Option C

4. Quick Check:

   IN syntax = IN (subquery) [OK]

Hint: Use parentheses around subquery after IN [OK]

Common Mistakes:

• Adding = before IN
• Missing parentheses around subquery
• Placing IN before column name

3. Given the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What will this query return?

SELECT name FROM Employees WHERE dept_id IN (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who do not work in the Sales department.

B. Names of employees who work in the Sales department.

C. All employee names regardless of department.

D. An error because subquery returns multiple rows.

Solution

1. Step 1: Understand subquery filtering

   The subquery selects dept_id values where dept_name is 'Sales'.

2. Step 2: Main query filters employees

   The main query selects employee names whose dept_id matches any dept_id from the subquery.

3. Final Answer:

   Names of employees who work in the Sales department. -> Option B

4. Quick Check:

   IN filters employees by Sales dept_id [OK]

Hint: IN filters rows matching subquery values [OK]

Common Mistakes:

• Thinking it returns employees outside Sales
• Assuming subquery causes error with multiple rows
• Ignoring subquery filtering condition

4. Identify the error in this query:

SELECT emp_id FROM Employees WHERE dept_id IN SELECT dept_id FROM Departments;

medium

A. Subquery should be in the FROM clause.

B. Using IN instead of EXISTS.

C. dept_id should be compared with = not IN.

D. Missing parentheses around the subquery after IN.

Solution

1. Step 1: Check IN operator syntax

   The IN operator requires the subquery to be enclosed in parentheses.

2. Step 2: Identify missing parentheses

   The query lacks parentheses around the subquery, causing a syntax error.

3. Final Answer:

   Missing parentheses around the subquery after IN. -> Option D

4. Quick Check:

   IN needs (subquery) [OK]

Hint: Always put subquery inside parentheses after IN [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Confusing IN with EXISTS
• Using = instead of IN for multiple values

5. You want to find all customers who have placed orders for products in the 'Electronics' category. Given tables:
Customers(customer_id, name)
Orders(order_id, customer_id, product_id)
Products(product_id, category)
Which query correctly uses a subquery with IN to get these customers?

hard

A. SELECT name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

B. SELECT name FROM Customers WHERE customer_id = (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

C. SELECT name FROM Customers WHERE customer_id IN (SELECT product_id FROM Products WHERE category = 'Electronics');

D. SELECT name FROM Customers WHERE customer_id IN (SELECT order_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

Solution

1. Step 1: Understand the relationships

   Customers link to Orders by customer_id; Orders link to Products by product_id.

2. Step 2: Analyze the nested subqueries

   The innermost subquery selects product_ids in 'Electronics'. The middle subquery selects customer_ids from Orders with those product_ids. The outer query selects customer names with those customer_ids.

3. Final Answer:

   SELECT name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics')); -> Option A

4. Quick Check:

   Nested IN filters customers by Electronics orders [OK]

Hint: Use nested IN for multi-level filtering [OK]

Common Mistakes:

• Using = instead of IN for multiple customer_ids
• Comparing customer_id with product_id or order_id
• Missing nested subquery for product filtering