How to Select Rows That Don’t Exist in Other Table?
When working with databases, it is common to encounter situations where we need to compare data across tables and find records that are missing in one table but present in another.
This can be challenging, especially when dealing with large datasets. However, PostgreSQL provides several methods to solve this problem efficiently. Below are the method that is used to select rows that don’t exist in another table in PostgreSQL:
- Using NOT IN Clause
- Using NOT EXISTS Clause
- Using LEFT OUTER JOIN and IS NULL
- Using EXCEPT Operator
Let’s set up an Environment
To understand How to select rows that don’t exist in another table in PostgreSQL we need a 2 table on which we will perform various operations and queries. Here we will consider a table called professor and hod. Here the professor consists of id, consistsname, and dept. Also, the hod consists, of id, name and dept.
CREATE TABLE professor (
id INTEGER,
name VARCHAR(50),
dept VARCHAR(50)
);
INSERT INTO professor VALUES (1, 'Anil', 'Physics');
INSERT INTO professor VALUES (2, 'Rajesh', 'Chemistry');
INSERT INTO professor VALUES (3, 'Prakash', 'Physics');
INSERT INTO professor VALUES (4, 'Pawan', 'Chemistry');
INSERT INTO professor VALUES (5, 'Suraj', 'Maths');
INSERT INTO professor VALUES (6, 'Zenith', 'Maths');
CREATE TABLE hod (
id INTEGER,
name VARCHAR(50),
dept VARCHAR(50)
);
INSERT INTO hod VALUES (1, 'Anil', 'Physics');
INSERT INTO hod VALUES (2, 'Pawan', 'Chemistry');
INSERT INTO hod VALUES (3, 'Akash', 'Maths');
After Inserting some records Our professor table looks like:
Professor table data
After Inserting some records Our hod table looks like:
HOD table data
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. By the end, you will have a clear understanding of how to efficiently select rows that don’t exist in another table which help to improve your data management skills in PostgreSQL.