Subqueries in WHERE with IN in PostgreSQL - Deep Dive

Overview - Subqueries in WHERE with IN

What is it?

A subquery in WHERE with IN is a way to filter data by checking if a value exists in a list returned by another query. It lets you ask a question inside another question, like asking if a value is among a set of results. This helps you find rows that match any of the values from the subquery. It is a powerful tool to connect data from different tables or conditions.

Why it matters

Without subqueries in WHERE with IN, you would have to manually combine data or write complex joins to filter results. This would be slow, error-prone, and hard to read. Using IN with subqueries makes queries simpler, more readable, and efficient. It helps you answer real questions like 'Which customers bought products from a certain category?' easily.

Where it fits

Before learning this, you should understand basic SELECT queries and simple WHERE filters. After this, you can learn about JOINs, EXISTS subqueries, and advanced filtering techniques. This concept is a stepping stone to mastering complex data retrieval in SQL.

Mental Model

Core Idea

A subquery in WHERE with IN checks if a value matches any value from a list produced by another query.

Think of it like...

Imagine you have a guest list for a party, and you want to check if a person is invited by looking if their name is on that list. The main query is asking about people, and the subquery is the guest list.

Main Query
  │
  ▼
Filter rows where value IN (Subquery results)
  │
  └─ Subquery: returns a list of values

Example:
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE city = 'Paris'
);

Build-Up - 7 Steps

1

Foundation

Understanding Basic WHERE Filters

Concept: Learn how WHERE filters rows based on a condition.

The WHERE clause lets you pick rows that meet a condition. For example, SELECT * FROM products WHERE price > 100; returns products costing more than 100.

Result

Only rows with price greater than 100 are shown.

Knowing how WHERE works is essential because subqueries in WHERE build on this filtering concept.

2

Foundation

Introduction to Subqueries

Concept: A subquery is a query inside another query that returns data used by the outer query.

You can write a query inside parentheses. For example, SELECT id FROM customers WHERE city = 'Paris'; returns customer IDs from Paris. This can be used inside another query.

Result

The subquery returns a list of customer IDs from Paris.

Understanding subqueries lets you use dynamic lists of values instead of fixed ones.

3

Intermediate

Using IN with Static Lists

Concept: IN checks if a value matches any value in a fixed list.

Example: SELECT * FROM products WHERE category IN ('Books', 'Toys'); returns products in Books or Toys categories.

Result

Rows with category Books or Toys are selected.

IN simplifies checking multiple values without writing many OR conditions.

4

Intermediate

Combining IN with Subqueries

🤔Before reading on: do you think the subquery runs once or multiple times for each row? Commit to your answer.

Concept: Use a subquery inside IN to filter rows based on dynamic lists from another table.

Example: SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE city = 'Paris' ); This returns orders from customers living in Paris.

Result

Orders placed by customers from Paris are shown.

Knowing that the subquery produces a list used by IN helps you filter data across tables easily.

5

Intermediate

Subquery Returns and Data Types

Concept: The subquery must return a single column with values compatible with the outer query's column.

If the outer query filters customer_id, the subquery must return customer IDs, not names or multiple columns. For example, SELECT id FROM customers is valid, but SELECT name FROM customers is not if filtering by customer_id.

Result

Query runs successfully only if data types match.

Matching data types prevents errors and ensures meaningful filtering.

6

Advanced

Performance Considerations of IN Subqueries

🤔Before reading on: do you think IN with subqueries is always faster than JOINs? Commit to your answer.

Concept: IN with subqueries can be less efficient than JOINs in some cases, depending on database optimization.

Databases may execute IN subqueries by running the subquery once or multiple times. Sometimes rewriting with JOIN improves speed. EXPLAIN ANALYZE helps check performance.

Result

Understanding performance helps write faster queries.

Knowing when IN subqueries slow down queries guides you to better query design.

7

Expert

NULL Values and IN Subqueries Behavior

🤔Quick: If the subquery returns NULL, does IN return true, false, or unknown? Commit to your answer.

Concept: IN with subqueries behaves differently when NULLs appear, affecting query results unexpectedly.

If the subquery returns NULL among values, IN may return UNKNOWN, causing rows to be excluded. Use NOT IN carefully, as NULLs can cause no rows to match. IS NULL checks or EXISTS may be safer.

Result

Understanding NULL impact prevents subtle bugs in filtering.

Recognizing NULL's effect on IN avoids common logical errors in queries.

Under the Hood

When a query with WHERE ... IN (subquery) runs, the database first executes the subquery to get a list of values. Then, for each row in the outer query, it checks if the column's value is in that list. The database may optimize by caching the subquery results or rewriting the query internally to improve speed.

Why designed this way?

This design allows flexible filtering using dynamic sets of values without complex joins. It separates concerns: the subquery defines the set, and the outer query filters rows. Alternatives like JOINs exist but can be more complex or less readable. IN with subqueries balances expressiveness and simplicity.

┌───────────────┐       ┌───────────────┐
│ Outer Query   │       │ Subquery      │
│ SELECT * FROM │──────▶│ SELECT id FROM│
│ orders       │       │ customers     │
│ WHERE        │       │ WHERE city=   │
│ customer_id  │       │ 'Paris'       │
│ IN (Subquery)│       └───────────────┘
└───────────────┘
       │
       ▼
Filter orders where customer_id matches any id from subquery

Myth Busters - 4 Common Misconceptions

Quick: Does IN with a subquery always return rows if the subquery returns NULL? Commit yes or no.

Common Belief: If the subquery returns NULL, IN will still match rows as if NULL wasn't there.

Reality: If the subquery returns NULL, IN can return UNKNOWN, causing no rows to match.

Why it matters: This can cause queries to return empty results unexpectedly, leading to confusion and bugs.

Quick: Is IN with subqueries always faster than JOINs? Commit yes or no.

Common Belief: IN with subqueries is always more efficient than JOINs because it looks simpler.

Reality: Sometimes JOINs are faster because databases optimize joins better than IN subqueries.

Why it matters: Choosing the wrong approach can slow down applications and waste resources.

Quick: Does the subquery inside IN run once or for every row? Commit your answer.

Common Belief: The subquery runs once and its results are reused for all rows in the outer query.

Reality: While often the subquery runs once, some databases or query plans may run it multiple times, affecting performance.

Why it matters: Assuming it runs once can lead to unexpected slow queries if the subquery is expensive.

Quick: Can the subquery inside IN return multiple columns? Commit yes or no.

Common Belief: The subquery can return multiple columns and IN will check all of them.

Reality: The subquery must return exactly one column; multiple columns cause errors.

Why it matters: Misunderstanding this causes syntax errors and wasted debugging time.

Expert Zone

1

IN subqueries can sometimes be rewritten by the optimizer as semi-joins, which improves performance without changing query logic.

2

The presence of NULLs in subquery results can silently change query outcomes, so explicit NULL handling is crucial in production queries.

3

Using EXISTS instead of IN can avoid some pitfalls with NULLs and performance, but changes the logic subtly and must be chosen carefully.

When NOT to use

Avoid IN with subqueries when the subquery returns large datasets or when performance is critical; consider JOINs or EXISTS instead. Also, avoid IN if NULL values in subquery results are not handled properly; use EXISTS or explicit NULL checks.

Production Patterns

In real systems, IN with subqueries is often used for filtering by user permissions, categories, or related entities. It is common to combine with indexes on the subquery columns for speed. Sometimes, developers rewrite IN subqueries as JOINs or use materialized views for heavy queries.

Connections

JOINs in SQL

Alternative method to combine data from multiple tables, often interchangeable with IN subqueries.

Understanding JOINs helps you choose the best way to filter data, balancing readability and performance.

Set Theory in Mathematics

IN with subqueries represents checking membership in a set, a fundamental concept in set theory.

Knowing set membership concepts clarifies why IN works as a filter and how it relates to unions and intersections.

Filtering in Spreadsheet Software

Similar to filtering rows based on a list of values in spreadsheet filters or conditional formatting.

Recognizing this connection helps non-technical users grasp SQL filtering by relating it to familiar spreadsheet tasks.

Common Pitfalls

#1 Using NOT IN without considering NULLs in subquery results.

Wrong approach:

SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers WHERE city = 'Paris');

Correct approach:

SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers WHERE city = 'Paris' AND id IS NOT NULL);

Root cause:

NOT IN returns no rows if the subquery contains NULL, so filtering fails silently.

#2 Subquery returns multiple columns causing syntax error.

Wrong approach:

SELECT * FROM orders WHERE customer_id IN (SELECT id, name FROM customers);

Correct approach:

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers);

Root cause:

IN expects a single column list; multiple columns cause errors.

#3 Assuming subquery runs once when it runs multiple times, causing slow queries.

Wrong approach:

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE expensive_condition);

Correct approach:

WITH customer_ids AS (SELECT id FROM customers WHERE expensive_condition)
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customer_ids);

Root cause:

Not caching subquery results leads to repeated expensive computations.

Key Takeaways

Subqueries in WHERE with IN let you filter rows by checking if a value is in a dynamic list from another query.

The subquery must return a single column with compatible data types to the outer query's column.

NULL values in subquery results can cause unexpected behavior, especially with NOT IN, so handle NULLs carefully.

Performance of IN subqueries varies; sometimes JOINs or EXISTS are better choices.

Understanding how IN with subqueries works helps write clearer, more efficient, and correct SQL queries.

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