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.