In PostgreSQL, the INTERSECT operator is used to combine the result set of at least two queries. The INTERSECT operator retrieves only those records that are common in all the targeted tables. While working with the INTERSECT operator, you must follow some rules, such as the number of columns and their order being the same, and data types should be compatible.

This post will present an in-depth overview of the INTERSECT Operator with examples. So, let’s begin.

How to Use INTERSECT Operator in PostgreSQL?

The below snippet depicts the basic syntax of the INTERSECT operator:

SELECT col_list
FROM tab_1
INTERSECT
SELECT col_list
FROM tab_2;

The SELECT query will fetch the records from the respective tables, while the INTERSECT operator will combine the common records.

While working with INTERSECT operator, several rules must be followed:

- Columns specified in the SELECT statement must follow the same order.

- The number of columns should be the same.

- Columns type must be compatible.

Example #1: How Does the INTERSECT Operator Work in PostgreSQL?

We have already created two tables in our database, i.e., article_details and recommended_articles. Let’s run the SELECT statement to see the details of each table:

SELECT * FROM article_details;

The article_details table has twelve records. Let’s run the SELECT query to fetch the data of the recommended_articles table:

SELECT * FROM recommended_articles;

The recommended_article table has three records. Let’s execute the below statement to understand the working of the INTERSECT operator:

SELECT *
FROM article_details
INTERSECT
SELECT *
FROM recommended_articles;

There is only one common record in the article_details and recommended_articles tables. Therefore, the INTERSECT operator returned only one record.

Example #2: How to Use INTERSECT Operator on Specific Columns?

We can use the INTERSECT operator on some specific columns to combine the common records of different tables' columns:

SELECT article_title
FROM article_details
INTERSECT
SELECT article_title
FROM recommended_articles;

The output shows that there are two common articles in the article_details and recommended_articles tables.

Example #3: How to Use INTERSECT Operator With ORDER BY Clause?

Let’s fetch the common records from the targeted tables based on article_title and article_id columns:

SELECT article_title, article_id
FROM article_details
INTERSECT
SELECT article_title, article_id
FROM recommended_articles;

Suppose we want to sort the result set in ascending order concerning the article_id column. For this purpose, we can utilize the ORDER BY clause with the INTERSECT operator:

SELECT article_title, article_id
FROM article_details
INTERSECT
SELECT article_title, article_id
FROM recommended_articles
ORDER BY article_id ASC;

This time the INTERSECT Operator retrieved the result set in ascending order.

Conclusion

In PostgreSQL, the INTERSECT operator combines the result set of at least two queries. The INTERSECT operator retrieves only common records from the targeted tables. While working with the INTERSECT operator, you need to follow a couple of rules, such as the same number of columns, the same columns’ order, and the data types should be compatible. This post taught us how to use the INTERSECT operator in PostgreSQL with suitable examples.