? MySQL EXISTS

? Quick Overview

The EXISTS operator checks whether a subquery returns any rows. It returns TRUE if at least one row is found and FALSE otherwise. Use EXISTS when you only need to test for presence, not to retrieve data from the subquery.

? Key Concepts

Boolean Result: EXISTS yields TRUE or FALSE.
Correlated Subquery: Often used with references to the outer query (e.g. employees.employee_id).
Performance: Use SELECT 1 inside the subquery to signal intent — the DB only needs to check existence.

⚙️ Syntax / Theory

? View Code Example

// Syntax for EXISTS: check if subquery returns any row(s)
SELECT column1
FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE condition);

? Code Examples

? View Code Example

// Find employees who have made at least one order (correlated subquery)
SELECT employee_name
FROM employees
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.employee_id = employees.employee_id);

? View Code Example

// Get products that have been ordered at least once
SELECT product_name
FROM products
WHERE EXISTS (SELECT 1 FROM order_items WHERE order_items.product_id = products.product_id);

? Explanation

EXISTS: Tests whether the subquery returns any rows. TRUE if one or more rows exist.
Usage locations: Typically used in WHERE or HAVING clauses.
Behavior: The subquery may be correlated to the outer query; the engine can stop scanning once a match is found.

Live Output / Explanation

Example result: The first example returns the names of employees for whom the subquery (orders) finds at least one matching row. The DB engine evaluates the subquery for each employee until it finds a matching order; then it treats EXISTS as TRUE for that employee.

✅ Tips

Prefer EXISTS (SELECT 1 ...) to signal intent — the actual column selected inside the subquery is irrelevant.
For large datasets, benchmark EXISTS vs equivalent JOIN approaches — performance depends on indexes and database engine.
Use correlated subqueries when the condition depends on the current row of the outer query.

? Try It Yourself

Write a query to find customers who have placed at least one order using EXISTS.
Convert an INNER JOIN query into an EXISTS-based query and compare results and execution plan.
Test performance by adding indexes on referenced columns (e.g. orders.employee_id, order_items.product_id).