MySQL EXISTS Operator
MySQL EXISTS Operator
The EXISTS operator in MySQL is used to check whether a subquery returns any rows.
It is often used in correlated subqueries to test the existence of related data.
✅ Returns TRUE if the subquery returns one or more rows, otherwise FALSE.
Syntax
-
subquery→ A SELECT statement that checks for the presence of rows. -
Often used with WHERE to filter rows based on the subquery result.
🧠 Example Tables
Students Table (students)
| student_id | name | dept_id |
|---|---|---|
| 1 | John | 1 |
| 2 | Emma | 2 |
| 3 | Raj | 1 |
| 4 | Sara | 3 |
Departments Table (departments)
| dept_id | dept_name |
|---|---|
| 1 | IT |
| 2 | HR |
| 3 | Finance |
| 4 | Sales |
Example 1: Select Departments That Have Students
Result:
| dept_name |
|---|
| IT |
| HR |
| Finance |
✅ Notice:
Salesis excluded because no student belongs to it.Subquery checks existence of students in each department.
Example 2: Select Students Only If Their Department Exists
Result:
| name |
|---|
| John |
| Emma |
| Raj |
| Sara |
✅ All students are returned because their
dept_idexists in thedepartmentstable.
Key Points
-
EXISTS returns TRUE or FALSE based on whether the subquery has rows.
-
Usually used in correlated subqueries, where the subquery depends on the outer query.
-
Efficient for checking presence of related data without returning actual values.
-
Often combined with NOT EXISTS to find rows without related data.
Difference: EXISTS vs IN
| EXISTS | IN |
|---|---|
| Checks existence of rows | Checks specific values in a list |
| Can be faster with subqueries | Can be slower with large lists |
| Often used with correlated subqueries | Often used with simple subqueries |
