How to use NOT EXISTS In SQL

NOT EXISTS clause is used to check for the presence of rows in the subquery. It is often used in conjunction with a related subquery to check for the non-existence of the specific records that satisfy the particular conditions.

Example: Find Employees Who Haven’t Recorded any Attendance Using NOT EXISTS

SELECT * FROM employees
WHERE NOT EXISTS (
    SELECT 1
    FROM attendances
    WHERE employees.employee_id = attendances.employee_id
);

Explanation:

SELECT * FROM employees: This selects all columns (*) from the employees table.

SELECT 1 FROM attendances WHERE employees.employee_id = attendances.employee_id: This subquery selects the value 1 from the attendances table where there exists a match between employees.employee_id and attendances.employee_id.

WHERE NOT EXISTS (subquery): The NOT EXISTS condition is being used to find if the subquery returns any rows. If the subquery which runs on the employees table do not get any row for a particular employee in the table that means the employee does not have any attendance record in the attendances table.

Output:

In database management, one of the most common tasks is to compare records either to identify differences or missing records in certain tables. This phase is crucial for data validation, reconciliation, and complete data integrity.

On SQLite, a lightweight relational database management system, this is probably the most straightforward task to do through SQL queries.

This article is to help you know how to find records that are not present in another table in the SQLite database.