SQL Exists

Example Tables

We will use the following tables named employees and departments for our examples:

    CREATE TABLE departments (
        department_id INT PRIMARY KEY,
        department_name VARCHAR(50)
    );

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        employee_name VARCHAR(50),
        department_id INT,
        salary DECIMAL(10, 2),
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
    );

    INSERT INTO departments (department_id, department_name) VALUES
    (1, 'HR'),
    (2, 'Finance'),
    (3, 'IT');

    INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
    (1, 'John Doe', 1, 50000.00),
    (2, 'Jane Smith', 2, 60000.00),
    (3, 'Sam Brown', 3, 70000.00),
    (4, 'Nancy White', 1, 55000.00),
    (5, 'Mike Green', 2, 65000.00);
    

Departments Table

Employees Table

Using SQL EXISTS

The SQL EXISTS operator is used to test for the existence of any record in a subquery. It returns TRUE if the subquery returns one or more records.

Example 1: Check if a Department has Employees

To check if a department has any employees, use the following query:

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

Result:

Example 2: Check if a Department has No Employees

To check if a department has no employees, use the following query:

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

Result: