Part 3: Mastering the EXISTS Operator | SQL Challenges & Capstone
SQL Set Operations & Advanced Filtering Practice · Part 3 · Lecture Notes
Mastering the EXISTS Operator
How to filter records based on whether related business data exists in a connected table — validating relationships without returning the related data.
Part 3 of 650% Complete
EXISTS
Existence Checks
Correlated Subquery
Linked Record Validation
8 Practice Questions
Learning Objectives
Use EXISTS to filter records based on whether related data exists in a connected table
Write correlated subqueries with a WHERE clause that links the subquery to the outer table
Understand that SELECT * inside EXISTS is conventional — the selected content is never returned
Use EXISTS efficiently for existence checks across all major table relationships in the database
Click any section header to open or close it
EXISTS checks whether a subquery returns any rows. If the subquery finds at least one matching record, EXISTS returns TRUE and the outer query includes that row. If the subquery returns nothing, EXISTS returns FALSE and the row is excluded. EXISTS is a relational filter — it answers “does this related activity exist?”
In industry, analysts use EXISTS to validate linked business activity, identify active customers, confirm operational relationships, filter records based on whether related events have occurred, and analyze connected business data where the existence of a relationship — not its values — determines inclusion.
The Basic Structure
The outer query scans the main table. For each row, EXISTS runs the subquery. If the subquery returns at least one matching row, the outer row is included. The correlation condition (WHERE outer_table.key = inner_table.key) is what links the two queries.
Basic EXISTS Syntax
SELECT column_name
FROM table1
WHERE EXISTS (
SELECT *
FROM table2
WHERE table1.column_name = table2.column_name
);
EXISTS evaluates to TRUE or FALSE for each row in the outer table. Users who have at least one transaction: EXISTS is TRUE. Users with no transactions: EXISTS is FALSE. The outer query returns only the rows where EXISTS evaluated to TRUE.
SELECT * Inside EXISTS
The content of SELECT inside EXISTS does not matter — it is never returned. EXISTS only cares whether the subquery returns any rows at all, not what those rows contain. Writing SELECT * is the standard convention, though SELECT 1 produces identical results.
Users Who Have Transactions
SELECT first_name
FROM users
WHERE EXISTS (
SELECT *
FROM transactions
WHERE users.user_id = transactions.user_id
);
EXISTS is the standard tool for validating that a business relationship is present. Products that have been ordered. Campaigns that have generated events. Users who have support tickets. In each case, EXISTS checks the related table and includes the outer record only if at least one link is confirmed.
EXISTS vs INNER JOIN
EXISTS and INNER JOIN often produce similar results, but they are conceptually different. INNER JOIN returns columns from both tables. EXISTS only returns columns from the outer table — the related data is checked for existence but never displayed. Use EXISTS when you only need the outer table columns.
Products That Appear in Orders
SELECT product_name
FROM products
WHERE EXISTS (
SELECT *
FROM order_items
WHERE products.product_id = order_items.product_id
);
EXISTS scales naturally to any table relationship in the database. Users with subscriptions: EXISTS against subscriptions. Campaigns with events: EXISTS against campaign_events. Orders with items: EXISTS against order_items. The same pattern — outer table WHERE EXISTS (SELECT * FROM related table WHERE key matches) — applies universally.
EXISTS Is Optimised for Existence
Because EXISTS stops scanning the subquery as soon as it finds the first match, it is often more efficient than alternatives like IN or INNER JOIN when checking for existence. On large tables, this early-exit behaviour makes EXISTS faster for relational filters.
Users With Active Subscriptions
SELECT first_name
FROM users
WHERE EXISTS (
SELECT *
FROM subscriptions
WHERE users.user_id = subscriptions.user_id
);
Campaigns With Events
SELECT campaign_name
FROM campaigns
WHERE EXISTS (
SELECT *
FROM campaign_events
WHERE campaigns.campaign_id = campaign_events.campaign_id
);
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Retrieve users who have transactions using EXISTS
AI Prompt
Write a MySQL query to retrieve users who have transactions using EXISTS.
SQL Answer
SELECT first_name
FROM users
WHERE EXISTS (
SELECT *
FROM transactions
WHERE users.user_id = transactions.user_id
);
Practice 2Retrieve users who have orders using EXISTS
AI Prompt
Write a MySQL query to retrieve users who have orders using EXISTS.
SQL Answer
SELECT first_name
FROM users
WHERE EXISTS (
SELECT *
FROM orders
WHERE users.user_id = orders.user_id
);
Practice 3Retrieve products that appear in order_items using EXISTS
AI Prompt
Write a MySQL query to retrieve products that appear in order_items using EXISTS.
SQL Answer
SELECT product_name
FROM products
WHERE EXISTS (
SELECT *
FROM order_items
WHERE products.product_id = order_items.product_id
);
Practice 4Retrieve users who have subscriptions using EXISTS
AI Prompt
Write a MySQL query to retrieve users who have subscriptions using EXISTS.
SQL Answer
SELECT first_name
FROM users
WHERE EXISTS (
SELECT *
FROM subscriptions
WHERE users.user_id = subscriptions.user_id
);
Practice 5Retrieve users who created support tickets using EXISTS
AI Prompt
Write a MySQL query to retrieve users who created support tickets using EXISTS.
SQL Answer
SELECT first_name
FROM users
WHERE EXISTS (
SELECT *
FROM support_tickets
WHERE users.user_id = support_tickets.user_id
);
Practice 6Retrieve campaigns that contain campaign events using EXISTS
AI Prompt
Write a MySQL query to retrieve campaigns that contain campaign events using EXISTS.
SQL Answer
SELECT campaign_name
FROM campaigns
WHERE EXISTS (
SELECT *
FROM campaign_events
WHERE campaigns.campaign_id = campaign_events.campaign_id
);
Practice 7Retrieve users who have session activity using EXISTS
AI Prompt
Write a MySQL query to retrieve users who have session activity using EXISTS.
SQL Answer
SELECT first_name
FROM users
WHERE EXISTS (
SELECT *
FROM sessions
WHERE users.user_id = sessions.user_id
);
Practice 8Retrieve orders that contain order_items using EXISTS
AI Prompt
Write a MySQL query to retrieve orders that contain order_items using EXISTS.
SQL Answer
SELECT order_id
FROM orders
WHERE EXISTS (
SELECT *
FROM order_items
WHERE orders.order_id = order_items.order_id
);
Knowledge Check
Five questions — select the best answer for each.
1. What does EXISTS return when the subquery finds at least one matching row?
AFALSE — the outer row is excluded
BNULL — the outer row is marked as missing
CTRUE — the outer row is included in the result
DThe actual matching rows from the subquery
Correct. EXISTS evaluates to TRUE when the subquery finds at least one match, causing the outer query to include that row. It evaluates to FALSE when no match is found, causing exclusion.
Not quite. EXISTS returns TRUE when the subquery finds at least one row — the outer query includes the row. FALSE means no match, so the row is excluded.
2. Why is SELECT * commonly used inside an EXISTS subquery?
AEXISTS requires SELECT * — other column selections cause errors
BEXISTS only checks for the existence of rows, not their content — what is selected inside doesn’t matter
CSELECT * makes EXISTS return the actual matching rows
DSELECT * makes EXISTS run faster than selecting specific columns
Correct. EXISTS only cares whether the subquery returns any rows at all. The SELECT inside EXISTS is never returned to the user. SELECT * is standard convention; SELECT 1 produces identical results.
Not quite. EXISTS doesn’t care what the subquery selects — it only checks whether any rows are returned. SELECT * is conventional but the content is irrelevant.
3. What role does the WHERE clause inside the EXISTS subquery play?
AIt filters the outer table
BIt connects the subquery to the current row of the outer table, making it a correlated subquery
CIt limits the number of rows the subquery can return
DIt is optional and can be omitted without changing the result
Correct. The WHERE clause inside EXISTS (WHERE outer.key = inner.key) is the correlation condition — it links the subquery to each row of the outer table. Without it, EXISTS would check for any rows in the subquery table, not related ones.
Not quite. The WHERE inside EXISTS is the correlation condition — it links the subquery to the current outer row, making this a correlated subquery.
4. What is the key difference between using EXISTS and INNER JOIN for the same business question?
AEXISTS returns matching columns from both tables; INNER JOIN returns only the outer table columns
BEXISTS only returns columns from the outer table; INNER JOIN returns columns from both tables
CEXISTS is slower than INNER JOIN for all queries
DINNER JOIN can only join two tables; EXISTS can check against any number of tables
Correct. EXISTS returns columns only from the outer query table — the related data is checked for existence but never displayed. INNER JOIN returns columns from both tables. Use EXISTS when you only need outer table data.
Not quite. EXISTS returns only outer table columns. INNER JOIN returns columns from both tables. Choose EXISTS when the outer table columns are all you need.
5. An analyst wants to find users who have NO transactions. Which WHERE condition achieves this?
AWHERE EXISTS (SELECT * FROM transactions WHERE users.user_id = transactions.user_id)
BWHERE NOT EXISTS (SELECT * FROM transactions WHERE users.user_id = transactions.user_id)
CWHERE transactions.user_id IS NULL
DWHERE users.user_id NOT IN transactions
Correct. NOT EXISTS is the complement of EXISTS — it includes the outer row only when the subquery returns zero matching rows. This identifies users with no matching transaction records.
Not quite. NOT EXISTS returns the outer row when the subquery finds no matches — identifying users without transactions.
Part 3 Summary
The key ideas from this lecture — keep these in mind as you move forward.
EXISTS helps analysts filter records based on whether related business activity exists in connected tables. EXISTS returns TRUE when the subquery finds at least one match. The correlated WHERE clause links the subquery to the outer row. Only outer table columns are returned.
