How to use LEFT JOIN In SQL

In the left join option, only the records from the left table will be returned whether there is a match or not. By running this, we can remove the records where the fields from the left table are empty.

Example: Find Employees Who Haven’t Recorded any Attendance Using LEFT JOIN

SELECT employees.* FROM employees
LEFT JOIN attendances ON employees.employee_id = attendances.employee_id WHERE attendances.employee_id IS NULL;

Explanation

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

LEFT JOIN attendances ON employees.employee_id = attendances.employee_id: This executes a left join between the employees and attendances tables according to the column employee_id. This implies that it will consist of all of the records from the employees tab no matter the presence of the matching record in the attendances table.

WHERE attendances.employee_id IS NULL: The join condition here filters the result set so that it selects only the rows that are missing the matching record in the attendances table for each employee.

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.