Postgres offers multiple ways of comparing two tables, such as using EXCEPT Operator, UNION operator, OUTER JOIN, and so on. Among them, the EXCEPT operator is the most widely used approach, which retrieves the filtered result set of various queries based on the comparison.

Through practical examples, this blog post will explain how to compare tables using the EXCEPT operator in Postgres. So, let’s get started!

How to Compare Tables Using EXCEPT Operators in PostgreSQL?

The EXCEPT operator executes two SELECT queries and retrieves only those records that are not available in the second SELECT query.

Let’s dive into the practical implementation of the EXCEPT operator.

Example: Table Comparison Using EXCEPT Operator

First, we will create two tables, and then we will utilize the EXCEPT operator to perform a comparison:

CREATE TABLE books_info(
book_id SERIAL PRIMARY KEY,
book_name TEXT,
is_available BOOLEAN
);

The “book_details” table has been created successfully. Let’s utilize the INSERT command to insert the records into the targeted table:

INSERT INTO books_info(book_name, is_available)
VALUES ('Great Expectations', 't'),
('Wuthering Heights', 'f'),
('The Kite Runner', 't'),
('The Catcher in the Rye', 't'),
('The Lord of the Rings', 'f'),
('His Dark Materials', 't'),
('Think and Grow Rich', 't');

The desired records have been inserted into the “books_info” table. Use the SELECT command to fetch the content of the newly created table:

SELECT * FROM books_info;

Now create another sample table, let’s say “top_selling_books” with three columns: book_id, book_name, and is_available:

CREATE TABLE top_selling_books(
book_id INT PRIMARY KEY,
book_name TEXT,
is_available BOOLEAN
);

The table “top_selling_books” was created successfully. Use the INSERT INTO statement to add some data to the selected table:

INSERT INTO top_selling_books(book_id, book_name, is_available)
VALUES (2, 'Wuthering Heights', 't'),
(3, 'The Lord of the Rings', 't'),
(4, 'Think and Grow Rich', 't'),
(1, 'The Great Gatsby', 't'),
(5, 'To Kill a Mockingbird', 'f'),
(6, 'The Grapes of Wrath', 't'),
(7, 'Frankenstein', 'f');

The output shows that the desired records have been inserted into the “top_selling_books” table. Use the SELECT command to fetch the content of the targeted table:

SELECT * FROM top_selling_books;

Let’s utilize the EXCEPT operator to compare the “books_info” and “top_selling_books” tables:

SELECT book_name
FROM top_selling_books
EXCEPT
 SELECT book_name
 FROM books_info;

In this example program, we utilized the EXCEPT operator to compare the two tables: “books_info” and “top_selling_books”. As a result, the EXCEPT operator will retrieve only those records that are not present in the book_info table:

The EXCEPT operator retrieves those books that are not available in the books_info table.

That’s all from this Postgres guide.

Conclusion

In PostgreSQL, the EXCEPT operator is used to compare multiple tables. The EXCEPT operator executes two SELECT queries and retrieves only those records that are not available in the second SELECT query. This blog post has considered a practical example to compare two different tables using EXCEPT operators.