Intersect - (PostgreSQL Advance)

The INTERSECT operator in PostgreSQL is used to combine two or more SELECT statements and returns only those rows that are common to all SELECT statements. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of the INTERSECT operator in PostgreSQL.

Syntax

SELECT column1, column2, ... FROM table1
INTERSECT
SELECT column1, column2, ... FROM table2

Example

Let's take an example of using the INTERSECT operator in PostgreSQL.

SELECT id, name, age
FROM users
WHERE age > 25

INTERSECT

SELECT id, name, age
FROM users
WHERE age < 40

The output of the above query will only contain those rows which are common to the results of both SELECT statements.

Output

The output of the INTERSECT operator in PostgreSQL is a result set that contains only the rows that are common to all SELECT statements.

Explanation

In the above example, we combined two SELECT statements using the INTERSECT operator. The first SELECT statement returns all the users who are above 25 years old, while the second SELECT statement returns all the users who are below 40 years old. By combining these two SELECT statements using the INTERSECT operator, we get only those users who are common to both statements.

Use

The INTERSECT operator in PostgreSQL is used to combine two or more SELECT statements and returns only those rows that are common to all statements. This is useful when you want to check for common data between two or more tables.

Important Points

• The INTERSECT operator only returns distinct rows that are common to all SELECT statements.
• The number and order of columns in each SELECT statement must be the same.
• The data types of the columns in each SELECT statement must be compatible.

Summary

In this tutorial, we discussed the INTERSECT operator in PostgreSQL. We covered the syntax, example, output, explanation, use, and important points of using the INTERSECT operator. With this knowledge, you can now use the INTERSECT operator in PostgreSQL to combine multiple SELECT statements and retrieve only the rows that are common to all SELECT statements.