Subqueries in WHERE with IN in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the IN keyword do in a SQL WHERE clause?

The IN keyword checks if a value matches any value in a list or the result of a subquery. It helps filter rows where the column's value is inside a set of values.

beginner

How does a subquery work inside a WHERE ... IN clause?

A subquery inside WHERE ... IN runs first and returns a list of values. The main query then checks if each row's value is in that list to decide if it should be included.

beginner

Write a simple example of a query using WHERE ... IN with a subquery.

Example:
SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');
This finds employees who work in departments located in New York.

intermediate

Why use a subquery with IN instead of multiple OR conditions?

Using a subquery with IN is cleaner and easier to read, especially when checking many values. It also lets the database handle the list dynamically, which is more flexible.

intermediate

Can the subquery inside WHERE ... IN return duplicate values? What happens then?

Yes, the subquery can return duplicates. The IN clause treats the list as a set, so duplicates do not affect the result. The main query checks if the value is present at least once.

What does the following query do?
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Paris');

A Finds customers who made orders in Paris.

B Finds orders made by customers living in Paris.

C Finds orders shipped to Paris.

D Finds customers who live in cities with orders.

The subquery selects customer IDs from Paris. The main query finds orders where the customer ID matches those IDs.

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

A SELECT * FROM products WHERE price IN (SELECT price FROM products);

B SELECT * FROM products WHERE price = IN (SELECT price FROM products);

C SELECT * FROM products WHERE IN price (SELECT price FROM products);

D SELECT * FROM products WHERE price IN SELECT price FROM products;

Option A correctly uses IN with a subquery inside parentheses.

What happens if the subquery in WHERE ... IN returns no rows?

A The main query returns NULL values.

B The main query returns all rows.

C The main query throws an error.

D The main query returns no rows.

If the subquery returns no rows, the IN list is empty, so no rows match and the main query returns no rows.

Can the subquery in WHERE ... IN reference columns from the outer query?

A No, subqueries cannot reference outer query columns.

B Only if the outer query uses GROUP BY.

C Yes, this is called a correlated subquery.

D Only if the subquery uses DISTINCT.

Subqueries that reference outer query columns are called correlated subqueries and are allowed.

Which keyword can be used instead of IN to check if a value is NOT in the subquery result?

A NOT IN

B EXISTS

C ANY

D ALL

NOT IN checks that the value is not present in the list returned by the subquery.

Explain how a subquery inside a WHERE ... IN clause works and why it is useful.

Think about the order of execution and how the main query uses the subquery's result.

Describe a real-life example where using WHERE ... IN with a subquery would be helpful.

Imagine you want to find all items related to a certain group or category.

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