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.

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.