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: <br> SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY'); <br> 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? <br> 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.