PostgreSQL INTERSECT

In this section, we are going to understand the working of the PostgreSQL Intersect operator, which is used to merge the output of multiple commands.

And the examples of the PostgreSQL intersect operator with single, multiple Expression and with Order by clause.

What is a PostgreSQL Intersect operator?

In PostgreSQL, the INTERSECT operator retrieves any rows available in both the outputs, just like UNION and Except Operators.

In other words, we can say that the PostgreSQL intersect operator gets the intersection of multiple datasets.

If a record exists in both data sets, it will be involved in the INTERSECT outcomes and each dataset is defined by a SELECT command. But, if a record occurs in one data set and not in the other, it will be gone from the INTERSECT outcomes.

The below image represents the final output, which is produced by the INTERSECT operator.

PostgreSQL INTERSECT

In the above image, the blue area shows the intersects between circle P and circle Q.

Each SELECT command within the INTERSECT must have a similar number of output fields with similar data types.

Syntax of PostgreSQL Intersect Operator

The illustration of the PostgreSQL Intersect operator is as follows:

In the above syntax, we have the following parameters, which we discussed in the following table:

Parameter Description
expression1, expression2, expression_n These are the columns or calculations, which we want to retrieve.
Tables The tables which we want to get records from.
WHERE conditions These are the condition that must be happened for the values to be selected. And it is an optional parameter.

Note: To use the INTERSECT operator, the columns which appear in the SELECT command must follow the following rules:

  • There must be a matching number of expressions or the columns in both SELECT clause.
  • The equivalent expressions must have a similar data type in the SELECT command, like expression1 must be a similar data type in both the first and second SELECT command.

Example of PostgreSQL INTERSECT operator

To understand the working of the PostgreSQL intersect operator, we will take the top_rated_cars and most_reliable_cars tables created in the PostgreSQL UNION tutorial:

To see the data from a top_rated_cars table, we will use the SELECT command, as shown in the below command:

Output

After executing the above command, we will get the below result:

PostgreSQL INTERSECT

The following command returns the data from the most_reliable_cars table:

Output

On implementing the above command, we will get the below output:

PostgreSQL INTERSECT
  • Example of PostgreSQL Intersect with Single Expression

In the below example, we will use an INTERSECT operator, which contains one field with a similar data type:

Output

After executing the above command, we will get the following output:

PostgreSQL INTERSECT

Note: In the above example, if the Car_name appeared in both the top_rated_cars and most_reliable_cars table, it would appear in our output.

Now, if we want to add the WHERE clause to the INTERSECT command in the above example, it would be like this:

Output

After implementing the above command, we will get the below output, which displays those intersect values that we retrieve after using the where condition.

PostgreSQL INTERSECT

Note: In the above example, the WHERE clauses have been added to all the datasets.

  • The initial dataset has been filtered thus only records from the top_rated_cars table where the launch_yearis less than 2022 are returned.
  • The second dataset has been filtered; therefore, only records from the most_reliable_carstable are returned where the launch_year is equal to 2020.

Example of PostgreSQL Intersect with various Expression

We will now see one sample example to understand PostgreSQL INTERSECT operator's working with more than one column.

In the below example, we will use the INTERSECT operator in PostgreSQL to retrieve various columns.

For this, we are taking the employee and department table from the Organization database, which we created in the earlier section of the PostgreSQL tutorial.

Output

After executing the above command, we will get the below output, which displays the records from the employee table where the address and phone values match the department table's address and phone values.

PostgreSQL INTERSECT

In the above example, the WHERE conditions on each data set to added filter the output; therefore, only records from the employee table are returned where the employee_name is not ross.

And the records from the department table are retrieved where the address is not London.

  • PostgreSQL INTERSECT with ORDER BY clause

If we want to sort the INTERSECT operator's output, we place the ORDER BY at query list's final command.

Syntax of PostgreSQL Intersect with ORDER BY clause

The below illustration is used to represent the PostgreSQL intersect with the order by clause:

Example of PostgreSQL intersects with Order by clause

In the following example, we are using a similar table as employee and department to understand the usage of an INTERSECT operator with the ORDER BY clause:

Output

We will get the below result set after executing the above command, which displays the sorted the results by Address in ascending order, which is represented by the ORDER BY 1.

And the address column fields are in the place of #1 in the output.

PostgreSQL INTERSECT

Overview

In the PostgreSQL Intersect section, we have learned the following topics:

  • We have used the PostgreSQL Intersect Operator to merge the output of multiple commands.
  • We have also understood the working of PostgreSQL intersect operator with single and multiple
  • We have also used the PostgreSQL Intersect with ORDER By clause to order the command outputs.

Next TopicPostgreSQL Except




Contact US

Email:[email protected]

PostgreSQL Intersect
10/30