SQL Server - Find Records From One Table Which Don't Exist in Another

When working with databases, it is often necessary to compare data between tables to find records that exist in one table but not in another. In SQL Server, this can be achieved using various methods.

In this article, we will explore two common approaches to finding records from one table that don't exist in another are defined in the article.

SQL Server - Find Records From One Table that Don't Exist in Another

The below method helps us to Find records from one table that don't exist in another SQL server defined below:
Let's set up an environment:

-- Table: employees
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

-- Table: employees_details
CREATE TABLE employees_details (
    detail_id INT PRIMARY KEY,
    employee_id INT,
    address VARCHAR(255),
    phone_number VARCHAR(15),
    date_of_birth DATE,
    CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
-- Sample data for employees table
INSERT INTO employees (employee_id, first_name, last_name, email, department_id, hire_date, salary)
VALUES
    (1, 'John', 'Doe', '[email protected]', 101, '2020-01-15', 60000.00),
    (2, 'Jane', 'Smith', '[email protected]', 102, '2019-07-20', 65000.00),
    (3, 'Michael', 'Johnson', '[email protected]', 101, '2021-03-10', 58000.00),
    (4, 'Emily', 'Brown', '[email protected]', 103, '2022-05-05', 62000.00);

-- Sample data for employees_details table
INSERT INTO employees_details (detail_id, employee_id, address, phone_number, date_of_birth)
VALUES
    (1, 1, '123 Main St, Cityville', '+1234567890', '1985-08-10'),
    (2, 2, '456 Elm St, Townsville', '+1987654321', '1990-03-25'),
    (3, 3, '789 Oak St, Villageton', '+1122334455', '1993-11-15'),
    (4, 4, '101 Pine St, Hamletown', '+1554433221', '1988-06-20');

employees Table:

employees_details Table:

1. Using NOT EXISTS

• The NOT EXISTS clause is a powerful tool for filtering records based on the absence of corresponding entries in another table.
  
• This below method involves using a subquery to check if there are no matching records in the second table.

SELECT *
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM employees_details ed
    WHERE e.employee_id = ed.employee_id
);

Output:

employee_id | first_name | last_name | email | department_id | hire_date  | salary
------------|------------|-----------|-------|---------------|------------|--------
4           | Emily      | Brown     | [email protected] | 103 | 2022-05-05 | 62000.00

Explanation: The query begins with a select statement from the first table in the case of multiple tables it may be employees. It works by using the NOT EXISTS clause with the sub-query that checks if there are no records appearing on the employees_details table based on the employee_id.

If no matching records are found in an inner query, a row from an employees table is returned.

2. Using LEFT JOIN

• The second method combines the records of the first table with those of the second table by applying the LEFT JOIN operation and then removes the records where there is no matching occurrence in the second table.
  
• This below method is useful for joining tables and identifying unmatched records.

SELECT e.*
FROM employees e
LEFT JOIN employees_details ed ON e.employee_id = ed.employee_id
WHERE ed.employee_id IS NULL;

Output:

employee_id | first_name | last_name | email | department_id | hire_date  | salary
------------|------------|-----------|-------|---------------|------------|--------
4           | Emily      | Brown     | [email protected] | 103 | 2022-05-05 | 62000.00

Explanation: The query first retrieves all records from the first table (employees) and then executes a LEFT JOIN to the employees_details table using the employee_id. The WHERE clause removes any rows that do not have an entry in the employees_details table (indicated by a NULL value in the joined column).

Conclusion

Overall, comparing data between tables in SQL Server to find records that don't exist in another table is a common and important task in database management. By using the NOT EXISTS clause or a LEFT JOIN operation, you can efficiently identify and manage such records, ensuring data integrity and consistency in your database.