Subqueries with EXISTS in PostgreSQL - Step-by-Step Execution

Concept Flow - Subqueries with EXISTS

Start Outer Query

↓

For each row in Outer Table

↓

Run Subquery

↓

Check if Subquery returns any row

↓

Include Outer Row

↓

Next Outer Row or End

The outer query checks each row, runs the subquery, and includes the row only if the subquery finds any matching rows.

Execution Sample

PostgreSQL

SELECT c.customer_id
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Select customers who have at least one order by checking if the subquery finds any matching orders.

Execution Table

Step	Outer Row (customer_id)	Subquery Run	Subquery Result	EXISTS Condition	Outer Row Included
1	101	SELECT 1 FROM orders WHERE customer_id=101	Returns rows	True	Yes
2	102	SELECT 1 FROM orders WHERE customer_id=102	Returns no rows	False	No
3	103	SELECT 1 FROM orders WHERE customer_id=103	Returns rows	True	Yes
4	104	SELECT 1 FROM orders WHERE customer_id=104	Returns no rows	False	No
5	105	SELECT 1 FROM orders WHERE customer_id=105	Returns rows	True	Yes
6	End	-	-	-	-

💡 All outer rows checked; query ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
customer_id	None	101	102	103	104	105	End
EXISTS Result	None	True	False	True	False	True	End

Key Moments - 2 Insights

Why does the EXISTS subquery only check for presence of rows, not their content?

EXISTS returns True as soon as the subquery finds any row, so it doesn't matter what the row contains, only if it exists (see execution_table rows 1 and 2).

What happens if the subquery returns no rows for a customer?

The EXISTS condition is False, so the outer query skips that customer (see execution_table rows 2 and 4).

Visual Quiz - 3 Questions

Look at the execution table, what is the EXISTS condition for customer_id 103?

A False

B No rows returned

C True

D Not checked

💡 Hint

Check the row where Outer Row is 103 in the execution_table.

At which step does the subquery return no rows, causing the outer row to be skipped?

A Step 2

B Step 1

C Step 3

D Step 5

💡 Hint

Look for 'Returns no rows' in the Subquery Result column.

If the subquery always returned rows, what would happen to the outer query results?

A Only some outer rows would be included

B All outer rows would be included

C No outer rows would be included

D Query would error

💡 Hint

EXISTS True means include the outer row; if always True, all rows included.

Concept Snapshot

Subqueries with EXISTS:
- Syntax: WHERE EXISTS (subquery)
- Checks if subquery returns any rows
- Returns True if at least one row found
- Outer row included only if EXISTS is True
- Efficient for existence checks without fetching data

Full Transcript

This visual execution shows how a SQL query with EXISTS works. For each customer in the outer query, the subquery checks if there is at least one order for that customer. If the subquery finds any matching order, EXISTS returns True, and the customer is included in the result. If no orders are found, EXISTS returns False, and the customer is skipped. The execution table traces each customer_id, the subquery run, its result, and whether the customer is included. The variable tracker shows how customer_id and EXISTS result change step by step. Key moments clarify that EXISTS only cares about presence of rows, not their content, and that no rows means skipping the outer row. The quiz tests understanding of these steps and outcomes.

Practice

1. What does the EXISTS keyword do in a SQL query?

easy

A. Checks if a subquery returns any rows and returns true or false

B. Counts the total number of rows in a table

C. Joins two tables based on a condition

D. Deletes rows from a table

Solution

1. Step 1: Understand the purpose of EXISTS

   The EXISTS keyword is used to check if a subquery returns any rows. It returns true if there is at least one row, otherwise false.

2. Step 2: Compare with other options

   Counting rows, joining tables, or deleting rows are different SQL operations unrelated to EXISTS.

3. Final Answer:

   Checks if a subquery returns any rows and returns true or false -> Option A

4. Quick Check:

   EXISTS = true if subquery has rows [OK]

Hint: EXISTS means: 'Is there at least one row?' [OK]

Common Mistakes:

• Confusing EXISTS with COUNT
• Thinking EXISTS joins tables
• Assuming EXISTS deletes data

2. Which of the following is the correct syntax to use EXISTS in a WHERE clause?

easy

A. WHERE EXISTS IN (SELECT * FROM orders WHERE customer_id = customers.id)

B. WHERE EXISTS SELECT * FROM orders WHERE customer_id = customers.id

C. WHERE EXISTS (SELECT * FROM orders WHERE customer_id = customers.id)

D. WHERE EXISTS = (SELECT * FROM orders WHERE customer_id = customers.id)

Solution

1. Step 1: Check correct EXISTS syntax

   The correct syntax requires EXISTS followed by a subquery enclosed in parentheses.

2. Step 2: Validate each option

   WHERE EXISTS (SELECT * FROM orders WHERE customer_id = customers.id) uses parentheses correctly. The other options have syntax errors or incorrect keywords.

3. Final Answer:

   WHERE EXISTS (SELECT * FROM orders WHERE customer_id = customers.id) -> Option C

4. Quick Check:

   EXISTS needs parentheses around subquery [OK]

Hint: EXISTS always followed by parentheses with a subquery [OK]

Common Mistakes:

• Omitting parentheses after EXISTS
• Using '=' or 'IN' with EXISTS
• Writing subquery outside parentheses

3. Given tables customers(id, name) and orders(id, customer_id), what does this query return?

SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id);

medium

A. All customers including those without orders

B. All customers who have placed at least one order

C. All orders with customer names

D. An error because of invalid subquery

Solution

1. Step 1: Understand the EXISTS subquery

   The subquery checks if there is at least one order with customer_id matching the customer's id.

2. Step 2: Determine the main query result

   The main query selects customer names only if the subquery returns rows, meaning the customer has orders.

3. Final Answer:

   All customers who have placed at least one order -> Option B

4. Quick Check:

   EXISTS filters customers with orders [OK]

Hint: EXISTS filters rows having matching related rows [OK]

Common Mistakes:

• Thinking it returns all customers
• Confusing with JOIN returning orders
• Assuming syntax error in subquery

4. Identify the error in this query:

SELECT id FROM customers WHERE EXISTS SELECT * FROM orders WHERE orders.customer_id = customers.id;

medium

A. Incorrect table alias usage

B. Using SELECT * instead of SELECT 1 in EXISTS

C. Missing WHERE clause in subquery

D. Missing parentheses around the subquery after EXISTS

Solution

1. Step 1: Check EXISTS syntax

   The EXISTS keyword must be followed by a subquery enclosed in parentheses.

2. Step 2: Identify the error in the query

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

3. Final Answer:

   Missing parentheses around the subquery after EXISTS -> Option D

4. Quick Check:

   EXISTS requires parentheses around subquery [OK]

Hint: Always put parentheses after EXISTS [OK]

Common Mistakes:

• Omitting parentheses after EXISTS
• Thinking SELECT * is invalid in EXISTS
• Confusing alias usage

5. You want to find all customers who have placed orders only in the last 30 days. Which query correctly uses EXISTS to achieve this?

A) SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id AND order_date >= CURRENT_DATE - INTERVAL '30 days');
B) SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id AND order_date <= CURRENT_DATE - INTERVAL '30 days');
C) SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id);
D) SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days');

hard

A. Select customers with orders in last 30 days using correct date filter

B. Select customers with orders older than 30 days

C. Select customers with any orders regardless of date

D. Select customers if any order in last 30 days ignoring customer link

Solution

1. Step 1: Understand the date filter requirement

   We want customers with orders placed in the last 30 days, so the order date must be >= current date minus 30 days.

2. Step 2: Analyze each query option

   Select customers with orders in last 30 days using correct date filter correctly filters orders by customer and date. Select customers with orders older than 30 days filters orders older than 30 days. Select customers with any orders regardless of date ignores date. Select customers if any order in last 30 days ignoring customer link ignores customer link.

3. Final Answer:

   Select customers with orders in last 30 days using correct date filter -> Option A

4. Quick Check:

   EXISTS with date filter and customer match [OK]

Hint: Add date condition inside EXISTS subquery with customer match [OK]

Common Mistakes:

• Using wrong date comparison operator
• Forgetting to link orders to customers
• Filtering orders without customer condition