SQL EXISTS

The EXISTS keyword in SQL is used in a subquery to test for the existence of rows that meet certain criteria. When used, it returns TRUE if the subquery returns one or more rows, and FALSE otherwise. It is often used to check for the presence of related data in another table.

Basic Syntax

SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);

Example Scenario

Suppose you have two tables: employees and departments.

employees Table:

departments Table:

Example 1: Check for Employees in a Specific Department

Objective: Find all employees who belong to a department that exists in the departments table.

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

Explanation: The subquery checks if there is at least one row in the departments table with a department_id that matches the department_id of an employee. If such a department exists, the employee is included in the results.

Output:

Example 2: Find Employees Not Assigned to a Valid Department

Objective: Find employees who do not have a valid department (i.e., their department does not exist in the departments table).

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

Explanation: The subquery checks if there are no matching rows in the departments table for the department_id of an employee. If no match is found, the employee is included in the results.

Output:

Example 3: Verify if a Department Exists

Objective: Check if there is at least one employee in the 'Finance' department.

SELECT 1
FROM departments d
WHERE d.department_name = 'Finance'
AND EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

Explanation: The subquery checks if there are employees in the 'Finance' department. If the department exists and has employees, the query returns 1.

Output:

If the 'Finance' department had no employees, the query would return no rows.