Subqueries in WHERE with IN in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find employees working in departments located in 'New York'

Given the tables employees(emp_id, emp_name, dept_id) and departments(dept_id, dept_name, location), what is the output of the following query?

SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'New York');

PostgreSQL

SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'New York');

A List of employee names who work in departments located in New York

B List of all employee names regardless of location

C Empty result set

D Syntax error due to subquery

💡 Hint

Think about how the subquery filters departments by location and how IN matches dept_id.

The subquery selects dept_id values for departments in New York. The main query returns employees whose dept_id matches any of those values, so only employees in New York departments are listed.

📝 Syntax

intermediate

Identify the syntax error in subquery with IN

Which option contains a syntax error in the use of a subquery with IN in PostgreSQL?

Consider the query:

SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

PostgreSQL

SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

A SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

B SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = Electronics);

C SELECT product_name FROM products WHERE category_id = IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

D SELECT product_name FROM products WHERE category_id IN SELECT category_id FROM categories WHERE category_name = 'Electronics';

💡 Hint

Check the placement of parentheses around the subquery.

Option D is missing parentheses around the subquery, which is required syntax for IN with a subquery. Option C has an invalid '= IN' operator combination. Option B is missing quotes around the string literal 'Electronics'. Only option A is correct syntax.

❓ optimization

advanced

Optimize query using IN with subquery for better performance

You have a query:

SELECT order_id FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE region = 'West');

Which option is the best optimization to improve performance in PostgreSQL?

PostgreSQL

SELECT order_id FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE region = 'West');

A Add an index on orders.order_id

B Rewrite using EXISTS:<br>SELECT order_id FROM orders WHERE EXISTS (SELECT 1 FROM customers WHERE customers.customer_id = orders.customer_id AND region = 'West');

C Rewrite using JOIN:<br>SELECT orders.order_id FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE customers.region = 'West';

D Rewrite using NOT IN instead of IN

💡 Hint

Consider how JOINs can be more efficient than IN subqueries in some cases.

Option C uses a JOIN which often performs better than IN with a subquery because it allows the database to optimize the join operation. Option C with EXISTS can be efficient but may not always outperform JOIN. Option C adds an index on order_id which is not relevant for filtering by customer_id. Option C changes logic and is incorrect.

🔧 Debug

advanced

Debug why query returns empty result with IN subquery

Given the query:

SELECT student_name FROM students WHERE class_id IN (SELECT class_id FROM classes WHERE teacher = 'Mr. Smith');

The query returns no rows, but you know students exist in classes taught by Mr. Smith. What is the most likely cause?

PostgreSQL

SELECT student_name FROM students WHERE class_id IN (SELECT class_id FROM classes WHERE teacher = 'Mr. Smith');

A The teacher name in classes table is stored with trailing spaces

B The subquery returns NULL values causing IN to fail

C The students table has no class_id column

D The query syntax is invalid

💡 Hint

Think about string matching and how trailing spaces affect equality.

If the teacher names have trailing spaces, the equality condition in the subquery fails to match 'Mr. Smith' exactly, so the subquery returns no rows. This causes the main query to return no students. Option A is unlikely because NULLs in subquery do not cause empty results with IN. Option A would cause a syntax error. Option A is false because the query is valid.

🧠 Conceptual

expert

Understanding NULL behavior in IN subqueries

Consider the query:

SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE discontinued IS NULL);

What will be the result if the subquery returns some NULL values along with valid category_ids?

PostgreSQL

SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE discontinued IS NULL);

A The query raises an error due to NULL values in subquery

B The query returns no rows because NULL in subquery causes IN to fail

C The query returns all products regardless of category_id

D The query returns all products with category_id matching non-NULL category_ids only

💡 Hint

Remember how NULLs are treated in IN conditions in SQL.

In SQL, NULL values in the list for IN are ignored when matching. The query returns products whose category_id matches any non-NULL category_id returned by the subquery. It does not return all products or fail due to NULLs. No error is raised.

Practice

1. What does the IN keyword do in a SQL query when used with a subquery in the WHERE clause?

easy

A. It filters rows where a column's value matches any value returned by the subquery.

B. It joins two tables together based on matching columns.

C. It updates rows in the table with values from the subquery.

D. It deletes rows that do not exist in the subquery.

Solution

1. Step 1: Understand the role of IN with subqueries

   The IN keyword checks if a value exists in a list of values returned by the subquery.

2. Step 2: Identify the filtering effect

   Rows are kept only if their column value matches any value from the subquery's result.

3. Final Answer:

   It filters rows where a column's value matches any value returned by the subquery. -> Option A

4. Quick Check:

   IN filters rows by subquery values [OK]

Hint: Think of IN as 'is in this list' from the subquery [OK]

Common Mistakes:

• Confusing IN with JOIN
• Thinking IN updates or deletes rows
• Assuming IN returns a single value only

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

easy

A. SELECT * FROM employees WHERE department_id IN (SELECT * 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: Check the correct use of parentheses

   The subquery must be enclosed in parentheses after IN.

2. Step 2: Verify the subquery returns a single column

   The subquery should return one column of values to compare against.

3. Final Answer:

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

4. Quick Check:

   Subquery in parentheses with single column [OK]

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

Common Mistakes:

• Using = IN instead of just IN
• Omitting parentheses around subquery
• Selecting multiple columns in subquery

3. Given the tables:
employees(id, name, department_id)
departments(id, name)
What will this query return?

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

medium

A. An error because subquery returns multiple rows.

B. Names of all employees regardless of department.

C. Names of employees who do not work in Sales.

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

Solution

1. Step 1: Understand the subquery filtering departments

   The subquery selects department IDs where the department name is 'Sales'.

2. Step 2: Filter employees by matching department IDs

   The main query returns employee names whose department_id matches those IDs.

3. Final Answer:

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

4. Quick Check:

   IN filters employees by Sales department IDs [OK]

Hint: Subquery filters departments, main query filters employees [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing NOT IN with IN
• Assuming subquery causes error with multiple rows

4. Identify the error in this query:

SELECT * FROM employees WHERE department_id IN SELECT id FROM departments WHERE name = 'HR';

medium

A. Missing parentheses around the subquery after IN.

B. Using IN instead of EXISTS.

C. Subquery returns multiple columns.

D. department_id cannot be compared with id.

Solution

1. Step 1: Check syntax for subquery with IN

   The subquery must be enclosed in parentheses after the IN keyword.

2. Step 2: Identify missing parentheses causing syntax error

   The query lacks parentheses around the subquery, causing invalid syntax.

3. Final Answer:

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

4. Quick Check:

   Subquery must be in parentheses after IN [OK]

Hint: Always wrap subqueries in parentheses after IN [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Confusing IN with EXISTS syntax
• Assuming subquery returns multiple columns

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

hard

A. SELECT name FROM customers WHERE id IN (SELECT id FROM products WHERE category = 'Electronics');

B. SELECT name FROM customers WHERE id = IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics'));

C. SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics'));

D. SELECT name FROM customers WHERE id IN (SELECT product_id FROM orders WHERE customer_id IN (SELECT id FROM products WHERE category = 'Electronics'));

Solution

1. Step 1: Identify the correct subquery for products in 'Electronics'

   The innermost subquery selects product IDs where category is 'Electronics'.

2. Step 2: Use these product IDs to find matching orders

   The middle subquery selects customer IDs from orders with those product IDs.

3. Step 3: Select customer names whose IDs match those customer IDs

   The main query selects customer names where their ID is in the list from the orders subquery.

4. Final Answer:

   SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics')); -> Option C

5. Quick Check:

   Nested IN subqueries filter customers by product category [OK]

Hint: Use nested IN subqueries to filter step-by-step [OK]

Common Mistakes:

• Using = IN instead of IN
• Mixing up customer_id and product_id columns
• Selecting wrong columns in subqueries