How to Select All Records from One Table That Do Not Exist in Another Table in SQL?
Last Updated : 11 Dec, 2024
When working with SQL databases, a common requirement is to find records from one table that do not exist in another table. This can be achieved using various SQL techniques like LEFT JOIN, NOT IN, or NOT EXISTS. In this detailed guide, we will explain how to accomplish this using SQL queries and Laravel's query builder. The article will also include examples, outputs, and optimized queries for improved database performance.
Why Find Records That Don’t Exist in Another Table?
- Finding records that don’t exist in another table is a critical operation in data analysis and reporting. It helps identify gaps and discrepancies in datasets, ensuring accurate insights and maintaining data integrity.
- Common use cases include detecting unprocessed orders by comparing an
orders table with a processed_orders table, identifying inactive users by analyzing differences between users and active_users tables, or validating relationships between related tables to ensure consistent and reliable data.
- This operation is essential for maintaining efficient workflows and ensuring robust database management.
SQL Query to Find Records in One Table But Not in Another
When managing relational databases, it is often necessary to identify records that exist in one table but not in another. This guide demonstrates how to achieve this with practical examples, including table creation, data insertion, and queries to fetch unmatched records.
1. Creating Tables
Consider two tables, employee_details and resigned_employees:
CREATE TABLE employee_details(
emp_id VARCHAR(8),
emp_name VARCHAR(20),
emp_designation VARCHAR(20),
emp_age INT);
CREATE TABLE employee_resigned(
emp_id VARCHAR(8),
emp_name VARCHAR(20),
emp_designation VARCHAR(20),
emp_age INT);
2. Inserting data into the Table
Next, insert records into the employee_details and employee_resigned tables using the following queries.
INSERT INTO employee_details VALUES
('E40001','PRADEEP','H.R',36),
('E40002','ASHOK','MANAGER',28),
('E40003','PAVAN KUMAR','ASST MANAGER',28),
('E40004','SANTHOSH','STORE MANAGER',25),
('E40005','THAMAN','GENERAL MANAGER',26),
('E40006','HARSH',' ANALYST',25),
('E40007','SAMHITH','GENERAL MANAGER',26),
('E40008','SAMEER','SENIOR ANALYST',25),
('E40009','RISABH','BUSINESS ANALYST',26);
INSERT INTO employee_resigned VALUES('E40001','PRADEEP','H.R',36),
('E40004','SANTHOSH','STORE MANAGER',25),
('E40005','THAMAN','GENERAL MANAGER',26);3. Verifying the inserted data :
To confirm that the data has been inserted correctly, use the following queries:
View Data in employee_details:
SELECT* FROM employee_details;
Output
employee_detailsView Data in employee_resigned:
SELECT* FROM employee_resigned;
Output
employee_resignedMethod 1: Using LEFT JOIN with WHERE NULL
The LEFT JOIN technique is one of the most effective ways to find unmatched records between two tables. It retrieves all records from the left table and only matching records from the right table. The WHERE NULL condition then filters out the rows where no match exists in the right table, leaving only unmatched records.
Query:
SELECT e.*
FROM employee_details e
LEFT JOIN employee_resigned r
ON e.emp_id = r.emp_id
WHERE r.emp_id IS NULL;
Output
| emp_id | emp_name | emp_designation | emp_age |
|---|
| E40002 | ASHOK | MANAGER | 28 |
| E40003 | PAVAN KUMAR | ASST MANAGER | 28 |
| E40006 | HARSH | ANALYST | 25 |
| E40007 | SAMHITH | GENERAL MANAGER | 26 |
| E40008 | SAMEER | SENIOR ANALYST | 25 |
| E40009 | RISABH | BUSINESS ANALYST | 26 |
Explanation:
The output contains details of employees in employee_details who are not present in employee_resigned. These are the employees who have not resigned.
Method 2: Using NOT EXISTS
The NOT EXISTS clause is a highly efficient method for finding unmatched records, especially when working with large datasets. It checks for the absence of a matching row in the subquery and returns records from the main query that do not satisfy the condition in the subquery.
Query:
SELECT emp_id,emp_name
FROM employee_details
WHERE NOT EXISTS
(SELECT *
FROM employee_resigned
WHERE employee_details.emp_id = employee_resigned.emp_id);
Output
| emp_id | emp_name |
|---|
| E40002 | ASHOK |
| E40003 | PAVAN KUMAR |
| E40006 | HARSH |
| E40007 | SAMHITH |
| E40008 | SAMEER |
| E40009 | RISABH |
Explanation:
The output lists the employee IDs and names of employees who have not resigned. This method efficiently identifies active employees by excluding those found in the employee_resigned table.
Method 3: Using NOT IN
The NOT IN clause is a straightforward way to find unmatched records by filtering rows from one table based on the absence of corresponding values in another table. It is particularly simple to use when dealing with small or moderately sized datasets.
Query:
SELECT *
FROM employee_details
WHERE emp_id NOT IN
(SELECT emp_id
FROM employee_resigned)
Output
| emp_id | emp_name | emp_designation | emp_age |
|---|
| E40002 | ASHOK | MANAGER | 28 |
| E40003 | PAVAN KUMAR | ASST MANAGER | 28 |
| E40006 | HARSH | ANALYST | 25 |
| E40007 | SAMHITH | GENERAL MANAGER | 26 |
| E40008 | SAMEER | SENIOR ANALYST | 25 |
| E40009 | RISABH | BUSINESS ANALYST | 26 |
Explanation:
The output includes the complete details of employees who have not resigned, such as their ID, name, designation, and age. By using NOT IN, we can quickly identify records that are exclusive to one table, making it a practical choice for straightforward queries.
Conclusion
Finding records in one table that do not exist in another table is a fundamental operation in SQL and Laravel. Use LEFT JOIN, NOT IN, or NOT EXISTS, depending on our specific use case and dataset size. Laravel provides convenient methods to achieve the same using Eloquent ORM or query builder. By applying the techniques and performance tips shared in this guide, we can efficiently handle unmatched records in our database.
Similar Reads
How to Select Rows from a Table that are Not in Another Table?
In MySQL, the ability to select rows from one table that do not exist in another is crucial for comparing and managing data across multiple tables. This article explores the methods to perform such a selection, providing insights into the main concepts, syntax, and practical examples. Understanding
3 min read
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
4 min read
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
3 min read
How to Find Records From One Table Which Don't Exist in Another MySQL
MySQL is a free and open-source relational database management system written in C and C++ that is extremely popular among developers. Like other relational database management systems, MySQL provides a variety of rich features to create databases and tables, insert data in them, and further manipul
5 min read
How to Select Rows that Don't Exist in Other Table in PostgreSQL?
In PostgreSQL, there are times when we need to find records in one table that do not exist in another table. This can be useful for various data manipulation tasks and ensuring data integrity. In this article, we will explore different approaches along with examples to achieve this using PostgreSQL.
5 min read
How to Select Rows with no Matching Entry in Another Table in SQLite?
In database management, selecting rows from one table that does not have matching entries in another table means returning the rows that are present in one table but do not have the same entry in any other table. This scenario often arises in various data validation and analysis processes. In this a
4 min read
How to Append Two Tables and Put the Result in a Table in SQL?
SQL provides the UNION and UNION ALL operators to combine data from two tables into a new table. These operators are used to merge rows from multiple tables into a single result set. The UNION operator removes duplicate rows from the combined result, while UNION ALL includes all rows, including dupl
4 min read
How to Copy Rows from One Table to Another in SQL?
In SQL, copying rows from one table to another is a common operation that simplifies data migration and duplication tasks. Whether creating backup tables, transferring data for analysis, or setting up a new schema, the ability to efficiently replicate data across tables is invaluable. This operation
3 min read
How to Update From One Table to Another Based on an ID Match in SQL
In SQL, updating data between tables is a common operation used to maintain data consistency and accuracy across related datasets. Whether we need to synchronize records, update fields, or correct discrepancies, SQL provides efficient methods to achieve this. In this article, we will explain how to
4 min read
SQL Query to Select all Records From Employee Table Where Name is Not Specified
In SQL, filtering records based on specific criteria is a fundamental task when working with relational databases. One common scenario is selecting all records from a table while excluding certain values, such as specific names or designations. This article demonstrates how to use SQL queries effect
4 min read