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.

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:

1. Using NOT IN Clause
   
2. Using NOT EXISTS Clause
   
3. Using LEFT OUTER JOIN and IS NULL
   
4. 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:

After Inserting some records Our hod table looks like:

1. Using NOT IN Clause

A subquery is a query that uses the result of another query to get the final result. There are multiple ways through which we can use subquery to get the our output. In the below query, we first select the name of professors that are present in the hod table and then use NOT IN clause to exclude them in the outer query.

SELECT * FROM professor
WHERE name NOT IN 
(
  SELECT name FROM hod
);

Output:

Explanation: This above query selects all records from the "professor" table where the name does not exist in the "hod" table.

2. Using NOT EXISTS Clause

In the following query, we first select only those records in the inner query that are present in both the tables by comparing the name in the WHERE clause. Later we discard these records from the professor table by utilising the NOT EXISTS clause.

SELECT * FROM professor
WHERE NOT EXISTS 
(
  SELECT * FROM hod
  WHERE professor.name=hod.name
);

Output:

Explanation: This above query selects all records from the "professor" table where there is no corresponding record in the "hod" table with the same name.

3. Using LEFT OUTER JOIN and IS NULL

The left join returns all the records in the left table whether they are matched or not. Using this, we can filter out the records for which the fields from the right table are NULL.

The following query employs the same to filter out the records for which hod.name is NULL.

SELECT professor.* FROM professor
LEFT OUTER JOIN hod
ON professor.name=hod.name
WHERE hod.name IS NULL;

Output:

Explanation: The output consists of employee records from the professor table where the name does not match any name in the hod table, using a LEFT OUTER JOIN and filtering for NULL values in the hod table.

4. Using EXCEPT Operator

The EXCEPT operator is used to retrieve distinct records from one table which are not present in the other table. We can make use of except operator to retrieve the rows which are not present in the other table. The following query does the same:

SELECT * FROM professor
WHERE name in (
  SELECT name FROM professor
  EXCEPT
  SELECT name FROM hod
);

Output:

Explanation: This above query selects all records from the "professor" table where the name exists in the "professor" table but does not exist in the "hod" table.

Conclusion

Overall, the PostgreSQL offers several efficient methods to select rows that don't exist in another table, which can be important for data management and ensuring data integrity. By using the NOT IN clause, NOT EXISTS clause, LEFT OUTER JOIN and IS NULL, and the EXCEPT operator, you can effectively compare data across tables and identify missing records. These methods provide valuable tools for database administrators and developers working with PostgreSQL, allowing them to manipulate data efficiently and maintain the integrity of their databases.