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

Concept Flow - Subqueries with EXISTS

Start Outer Query

↓

Check EXISTS Subquery

↓

Return Row

↓

Next Outer Row

↓

End

The outer query checks each row, then runs the EXISTS subquery. If the subquery finds any matching row, EXISTS is true and the outer row is returned. Otherwise, it skips that row.

Execution Sample

MySQL

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

This query returns names of customers who have at least one order.

Execution Table

Step	Outer Row (c.id, c.name)	EXISTS Subquery Condition	Subquery Result	EXISTS Result	Outer Query Action	Output
1	(1, 'Alice')	SELECT 1 FROM orders WHERE customer_id=1	Found rows	TRUE	Include row	Alice
2	(2, 'Bob')	SELECT 1 FROM orders WHERE customer_id=2	No rows found	FALSE	Skip row
3	(3, 'Carol')	SELECT 1 FROM orders WHERE customer_id=3	Found rows	TRUE	Include row	Carol
4	(4, 'Dave')	SELECT 1 FROM orders WHERE customer_id=4	No rows found	FALSE	Skip row
5	No more rows	-	-	-	End query

💡 No more outer rows to check, query ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
c.id	N/A	1	2	3	4	End
c.name	N/A	Alice	Bob	Carol	Dave	End
EXISTS Result	N/A	TRUE	FALSE	TRUE	FALSE	End
Output Rows	[]	['Alice']	['Alice']	['Alice', 'Carol']	['Alice', 'Carol']	['Alice', 'Carol']

Key Moments - 2 Insights

Why does the query include 'Alice' and 'Carol' but not 'Bob' or 'Dave'?

Because the EXISTS subquery found matching orders for Alice and Carol (see execution_table rows 1 and 3), so EXISTS is TRUE and those rows are included. For Bob and Dave (rows 2 and 4), no matching orders were found, so EXISTS is FALSE and those rows are skipped.

Does the subquery return data to the outer query?

No, the subquery only checks if any row exists that meets the condition. It returns TRUE or FALSE to the outer query, not the actual data. This is shown in the 'EXISTS Result' column in the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the EXISTS Result for the outer row with c.id = 2?

A TRUE

B FALSE

C No result

D Depends on orders

💡 Hint

Check the row where Outer Row is (2, 'Bob') in the execution_table.

At which step does the query stop checking outer rows?

A Step 3

B Step 4

C Step 5

D Step 1

💡 Hint

Look for the row with 'No more rows' in the Outer Row column in the execution_table.

If the orders table had an order for customer_id=4, what would change in the execution_table?

A EXISTS Result for c.id=4 would be TRUE and 'Dave' would be included in output

B Nothing would change

C The query would return an error

D All rows would be skipped

💡 Hint

EXISTS depends on whether subquery finds rows; see execution_table row 4.

Concept Snapshot

Subqueries with EXISTS check if a subquery returns any rows.
Syntax: WHERE EXISTS (subquery)
If subquery finds rows, EXISTS is TRUE, outer row included.
If no rows, EXISTS is FALSE, outer row skipped.
Used to filter rows based on related data existence.

Full Transcript

This visual execution shows how a query with EXISTS works. For each row in the outer table 'customers', the database runs the subquery to check if there is at least one matching order in 'orders'. If the subquery finds any rows, EXISTS returns TRUE and the outer row is included in the result. If not, the row is skipped. The execution table traces each outer row, the subquery condition, whether rows were found, and the final decision to include or skip the row. Variables like c.id, c.name, and EXISTS result change step by step. Key moments clarify why some rows appear and others don't, and that EXISTS only returns TRUE or FALSE, not data. The quiz tests understanding of these steps and outcomes. This helps beginners see how EXISTS filters rows by checking related data presence.