SQL EXISTS Syntax
SELECT columns
FROM table_name
WHERE EXISTS (
SELECT 1 FROM other_table
WHERE condition
);The subquery inside EXISTS does not need to return specific columns — SELECT 1 or SELECT * both work because EXISTS only checks whether rows exist, not what they contain.
Sample Tables
We will use the Employees and Departments tables from the Joins chapters:
+----+-----------+--------+--------+
| ID | Name | DeptID | Salary |
+----+-----------+--------+--------+
| 1 | Priya K. | 101 | 55000 |
| 2 | John D. | 102 | 62000 |
| 3 | Arun M. | 101 | 78000 |
| 4 | Emma W. | 103 | 51000 |
| 5 | Ravi S. | NULL | 72000 |
+----+-----------+--------+--------++-----+----------------+-----------+
| ID | DeptName | Location |
+-----+----------------+-----------+
| 101 | IT | Delhi |
| 102 | Marketing | London |
| 103 | Sales | Mumbai |
| 104 | HR | New York |
+-----+----------------+-----------+SQL EXISTS Example
Find departments that have at least one employee:
SELECT d.DeptName, d.Location
FROM Departments AS d
WHERE EXISTS (
SELECT 1 FROM Employees AS e
WHERE e.DeptID = d.ID
);+-----------+-----------+ | DeptName | Location | +-----------+-----------+ | IT | Delhi | | Marketing | London | | Sales | Mumbai | +-----------+-----------+
HR is excluded because no employee has DeptID = 104. The subquery checks each department — if any employee matches, EXISTS returns TRUE and the department is included.
NOT EXISTS — Find Missing Records
Find departments that have no employees:
SELECT d.DeptName, d.Location
FROM Departments AS d
WHERE NOT EXISTS (
SELECT 1 FROM Employees AS e
WHERE e.DeptID = d.ID
);+-----------+-----------+ | DeptName | Location | +-----------+-----------+ | HR | New York | +-----------+-----------+
EXISTS with Additional Conditions
Find departments that have at least one employee earning over 70,000:
SELECT d.DeptName
FROM Departments AS d
WHERE EXISTS (
SELECT 1 FROM Employees AS e
WHERE e.DeptID = d.ID
AND e.Salary > 70000
);+-----------+ | DeptName | +-----------+ | IT | +-----------+
Only IT qualifies because Arun (78000) and Ravi (72000) both work in IT and earn over 70,000.
EXISTS vs IN
Both EXISTS and IN can check for related records, but they work differently:
| Feature | EXISTS | IN |
|---|---|---|
| How it works | Checks if subquery returns any rows | Checks if value matches a list |
| Stops early | Yes — stops at first match | No — evaluates entire list |
| NULL handling | Works correctly with NULLs | NOT IN fails with NULLs |
| Performance | Better for large subquery results | Better for small lists |
| Correlated | Always correlated (references outer query) | Can be non-correlated |
💡 When to Use EXISTS vs IN
Use EXISTS when the subquery returns a large number of rows or when you need to handle NULLs safely. Use IN when you have a small, static list of values. For NOT conditions, prefer NOT EXISTS over NOT IN because NOT IN fails silently when the list contains NULL.
For the complete subquery reference, see the official MySQL EXISTS documentation. In the next chapter, you will learn SQL Subqueries in full depth.
If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.
📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.
🧪 Try It Yourself
Write a query using NOT EXISTS that finds employees who are not assigned to any department (DeptID does not match any Department ID).
SELECT e.Name, e.Salary
FROM Employees AS e
WHERE NOT EXISTS (
SELECT 1 FROM Departments AS d
WHERE d.ID = e.DeptID
);Expected Output:
+-----------+--------+ | Name | Salary | +-----------+--------+ | Ravi S. | 72000 | +-----------+--------+
📝 What You Learned in This Chapter
EXISTSreturns TRUE if a subquery produces one or more rowsNOT EXISTSreturns TRUE if a subquery produces zero rows- EXISTS only checks row existence — the selected columns do not matter
- EXISTS handles NULLs correctly, unlike NOT IN
- EXISTS is typically faster for large datasets because it stops at the first match
- Use EXISTS for correlated subqueries, IN for small static lists
Frequently Asked Questions
EXISTS checks if a correlated subquery returns any rows and stops at the first match. IN compares a value against a complete list. EXISTS is better for large datasets and handles NULLs correctly, while IN is simpler for small static lists.SELECT 1, SELECT *, and SELECT column_name all produce the same result. SELECT 1 is a convention that makes the intent clear and avoids fetching unnecessary data.NOT EXISTS when the subquery column might contain NULLs. NOT IN returns no rows if the list contains NULL, which is a common bug. NOT EXISTS handles NULLs correctly and predictably.📖 Chapter 27 of 30 — SQL Tutorial on Neotech Navigators
