EXISTS in MySQL

The EXISTS operator in MySQL is used to check whether a subquery returns any rows. It returns TRUE if the subquery finds at least one row and FALSE if it finds none.

1. EXISTS Syntax

SELECT column_name FROM table_name WHERE EXISTS (subquery);

• The subquery runs first.
• If it returns at least one row, EXISTS returns TRUE.
• If it returns zero rows, EXISTS returns FALSE.

2. Example: Using EXISTS

Tables

employees

departments

Query: Find employees who belong to a department

SELECT name FROM employees eWHERE EXISTS (    SELECT 1 FROM departments d WHERE d.id = e.department_id);

✅ Output:

+--------+| name   |+--------+| Alice  || Bob    |+--------+

🚀 Explanation:

• The subquery checks if a department exists for each employee.
• Charlie is excluded because department 103 is missing in departments.

3. EXISTS vs. IN

Example: EXISTS vs. IN

-- Using EXISTS (Faster for large tables)SELECT name FROM employees eWHERE EXISTS (    SELECT 1 FROM departments d WHERE d.id = e.department_id);-- Using IN (Slower for large tables)SELECT name FROM employeesWHERE department_id IN (SELECT id FROM departments);

✅ Use EXISTS when dealing with large datasets because it stops searching after the first match.

4. EXISTS with DELETE

You can use EXISTS to delete records based on a condition.

Example: Delete employees without a valid department

DELETE FROM employees eWHERE NOT EXISTS (    SELECT 1 FROM departments d WHERE d.id = e.department_id);

✅ Deletes Charlie because department 103 does not exist.

5. EXISTS with UPDATE

You can use EXISTS to update records.

Example: Set salaries to 10% higher for employees in existing departments

UPDATE employees eSET salary = salary * 1.10WHERE EXISTS (    SELECT 1 FROM departments d WHERE d.id = e.department_id);

6. EXISTS with NOT

You can use NOT EXISTS to find rows that do not exist in another table.

Example: Find employees without a department

SELECT name FROM employees eWHERE NOT EXISTS (    SELECT 1 FROM departments d WHERE d.id = e.department_id);

✅ Returns Charlie because department 103 does not exist.

Key Points

✅ EXISTS is faster than IN for large tables.
✅ EXISTS returns TRUE/FALSE, not values.
✅ NOT EXISTS finds missing records.
✅ EXISTS is useful for SELECT, DELETE, and UPDATE.