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:

employee_id name department_id
1 John Doe 101
2 Jane Smith 102
3 Alice Jones 101
4 Bob Brown NULL

departments Table:

department_id department_name
101 HR
102 Finance
103 IT

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:

name
John Doe
Jane Smith
Alice Jones

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:

name
Bob Brown

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:

1

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



twitter-white sharing button
keyboard_arrow_leftPrevious
Nextkeyboard_arrow_right

Online Learner

At Online Learner, we're on a mission to ignite a passion for learning and empower individuals to reach their full potential. Founded by a team of dedicated educators and industry experts, our platform is designed to provide accessible and engaging educational resources for learners of all ages and backgrounds.

twitter-white sharing button

Disclaimer About Us

Copyright 2023-2024 © All rights reserved.

logo
Frontend
Backend