Subquery with EXISTS operator in SQL - Step-by-Step Execution

Concept Flow - Subquery with EXISTS operator

Start Outer Query

↓

For each row in Outer Table

↓

Run Subquery

↓

Does Subquery return any row?

Yes No↓

Include row

↓

Next row or End

The outer query checks each row and runs the subquery. If the subquery finds any matching row, EXISTS returns true and includes the outer row.

Execution Sample

SQL

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 matching orders.

Execution Table

Step	Outer customer_id	Subquery Result	EXISTS Result	Outer Query Action
1	101	Returns rows (orders found)	TRUE	Include customer 101
2	102	Returns no rows (no orders)	FALSE	Exclude customer 102
3	103	Returns rows (orders found)	TRUE	Include customer 103
4	104	Returns no rows (no orders)	FALSE	Exclude customer 104
5	105	Returns rows (orders found)	TRUE	Include customer 105
6	-	-	-	No more customers, query ends

💡 All customers checked; only those with orders included because EXISTS was TRUE

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
customer_id (outer)	101	102	103	104	105	-	-
EXISTS Result	TRUE	FALSE	TRUE	FALSE	TRUE	-	-

Key Moments - 2 Insights

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

EXISTS only cares if the subquery returns any row at all. It returns TRUE if at least one row exists, regardless of what data is inside. See execution_table rows 1 and 2 where subquery returns rows or no rows.

What happens if the subquery returns multiple rows for one outer row?

EXISTS still returns TRUE as soon as it finds the first matching row. It does not count or return multiple rows. This is why in execution_table row 1, multiple orders still result in TRUE.

Visual Quiz - 3 Questions

Look at the execution table, what is the EXISTS result for customer_id 104?

A TRUE

B FALSE

C No result

D Error

💡 Hint

Check the row where Outer customer_id is 104 in the execution_table under EXISTS Result column

At which step does the outer query exclude a customer because no orders exist?

A Step 2

B Step 1

C Step 3

D Step 5

💡 Hint

Look for 'Exclude customer' in the Outer Query Action column in execution_table

If the subquery always returned rows, what would the EXISTS result column look like?

A All FALSE

B Alternating TRUE and FALSE

C All TRUE

D No values

💡 Hint

EXISTS returns TRUE if subquery returns any rows; see variable_tracker for EXISTS Result values

Concept Snapshot

Subquery with EXISTS operator:
- Syntax: WHERE EXISTS (subquery)
- Checks if subquery returns any rows
- Returns TRUE if at least one row exists
- Used to filter outer query rows
- Efficient for existence checks without data retrieval

Full Transcript

This visual execution shows how the SQL EXISTS operator works with a subquery. For each customer in the outer query, the subquery checks if there are any orders for that customer. If the subquery returns any rows, EXISTS returns TRUE and the customer is included in the result. If no rows are found, EXISTS returns FALSE and the customer is excluded. The execution table traces each customer_id, the subquery result, the EXISTS boolean, and the action taken. The variable tracker shows how customer_id and EXISTS result change step-by-step. Key moments clarify that EXISTS only checks for presence of rows, not their content, and that multiple rows still result in TRUE. The quiz tests understanding of these steps and outcomes. This helps beginners see how EXISTS filters rows based on subquery existence.

Practice

1. What does the EXISTS operator do in an SQL query?

easy

A. Checks if a subquery returns any rows and returns TRUE or FALSE.

B. Counts the 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 operator checks if the subquery returns at least one row.

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 rows found [OK]

Hint: EXISTS means "is there at least one row?" [OK]

Common Mistakes:

• Thinking EXISTS counts rows instead of checking existence
• Confusing EXISTS with JOIN operations
• Assuming EXISTS deletes or modifies data

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

easy

A. SELECT * FROM table WHERE EXISTS (SELECT column FROM table2);

B. SELECT * FROM table WHERE EXISTS = (SELECT column FROM table2);

C. SELECT * FROM table WHERE EXISTS IN (SELECT column FROM table2);

D. SELECT * FROM table WHERE EXISTS LIKE (SELECT column FROM table2);

Solution

1. Step 1: Review correct EXISTS syntax

   EXISTS is used as EXISTS (subquery) without any operator like =, IN, or LIKE.

2. Step 2: Identify incorrect options

   Options B, C, and D misuse operators (=, IN, LIKE) with EXISTS, causing syntax errors.

3. Final Answer:

   SELECT * FROM table WHERE EXISTS (SELECT column FROM table2); -> Option A

4. Quick Check:

   EXISTS uses only parentheses for subquery [OK]

Hint: EXISTS always followed by (subquery) without operators [OK]

Common Mistakes:

• Adding = or IN after EXISTS
• Using LIKE with EXISTS
• Forgetting parentheses around subquery

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

SELECT name FROM Customers c WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.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 incorrect subquery.

Solution

1. Step 1: Understand the EXISTS subquery condition

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

2. Step 2: Determine which customers are returned

   Only customers with matching orders exist, so only those customers' names are returned.

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 with matching related data [OK]

Common Mistakes:

• Thinking it returns all customers regardless of orders
• Confusing with JOIN that returns all orders
• Assuming syntax error due to subquery

4. Identify the error in this query:

SELECT * FROM Employees e WHERE EXISTS SELECT * FROM Salaries s WHERE s.emp_id = e.id;

medium

A. EXISTS cannot be used in WHERE clause.

B. Using SELECT * inside EXISTS is not allowed.

C. Missing parentheses around the subquery after EXISTS.

D. The alias 'e' is not defined.

Solution

1. Step 1: Check EXISTS syntax

   EXISTS requires the subquery to be enclosed in parentheses.

2. Step 2: Identify the missing parentheses

   The query lacks parentheses around the subquery after EXISTS, causing a syntax error.

3. Final Answer:

   Missing parentheses around the subquery after EXISTS. -> Option C

4. Quick Check:

   EXISTS (subquery) needs parentheses [OK]

Hint: Always put parentheses around subquery after EXISTS [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Thinking SELECT * is invalid inside EXISTS
• Misunderstanding alias usage

5. You want to find all products that have never been ordered. Given tables Products(id, name) and Orders(id, product_id), which query correctly uses EXISTS to find these products?

hard

A. SELECT name FROM Products p WHERE NOT IN (SELECT product_id FROM Orders);

B. SELECT name FROM Products p WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.product_id = p.id);

C. SELECT name FROM Products p WHERE EXISTS NOT (SELECT 1 FROM Orders o WHERE o.product_id = p.id);

D. SELECT name FROM Products p WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.product_id = p.id);

Solution

1. Step 1: Understand the goal

   We want products with no matching orders, so the subquery should check for orders and we want those where no such orders exist.

2. Step 2: Use NOT EXISTS correctly

   NOT EXISTS with a subquery checking orders for the product returns products never ordered.

3. Step 3: Evaluate other options

   SELECT name FROM Products p WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.product_id = p.id); returns products with orders, C has invalid syntax, D uses NOT IN which can cause issues with NULLs.

4. Final Answer:

   SELECT name FROM Products p WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.product_id = p.id); -> Option D

5. Quick Check:

   NOT EXISTS finds missing related rows [OK]

Hint: Use NOT EXISTS to find items with no related rows [OK]

Common Mistakes:

• Using EXISTS instead of NOT EXISTS for missing data
• Incorrect syntax like EXISTS NOT
• Using NOT IN which may fail with NULLs