SQL EXISTS Tutorial

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

department_id	department_name
1	HR
2	Finance
3	IT

Employees Table

employee_id	employee_name	department_id	salary
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

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:

department_name	HR
	Finance
	IT

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
    );