How to Find Records From One Table Which Don't Exist in Another SQLite?

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.

Discovering Missing Records in SQLite Tables

In SQLite, to find records from one table that don't exist in another, two methods are commonly used: NOT EXISTS and LEFT JOIN. NOT EXISTS employs a subquery to filter non-existent records, while LEFT JOIN compares tables directly.

We can find records from one table that don't exist in another using two methods:

1. Using NOT EXISTS
   
2. Using LEFT JOIN

Let us start by making some easy tables and filling them with some data. We shall create two tables, the EMPLOYEE and ATTENDANCE table whereby the EMPLOYEE table will have more records than the ATTENDANCE table.

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name TEXT
);

INSERT INTO employees (employee_id, employee_name) VALUES
(1, 'Minal Pandey'),
(2, 'Vivek Sharma'),
(3, 'Mivi Sharma');

CREATE TABLE attendances (
    attendance_id INTEGER PRIMARY KEY,
    employee_id INTEGER,
    date TEXT
);

INSERT INTO attendances (attendance_id, employee_id, date) VALUES
(1, 1, '2024-03-18'),
(2, 1, '2024-03-19'),
(3, 2, '2024-03-20');

The following is the initial data in both the EMPLOYEE and ATTENDANCE tables:

EMPLOYEE Table data

ATTENDANCE Table data

Now that we have our tables and data set up, let's perform the operations to find employees who haven't recorded any attendance using both methods.

1. Using NOT EXISTS

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:

2. Using LEFT JOIN

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:

Conclusion

The SQL queries with the NOT EXISTS clause will enable you to find records in one table that don't exist in another table based on the specified conditions and are more efficient in doing so. This is an essential element for data analysis, validation, and database consistency assurance. With SQLite's simplicity and superiority, such tasks can be done effectively even in lightweight environments.

In conclusion, mastering SQL querying techniques empowers you to manipulate and analyze data effectively, facilitating informed decision-making and ensuring the integrity of your database systems.