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 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.
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:
Note: To use the INTERSECT operator, the columns which appear in the SELECT command must follow the following rules:
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:
After executing the above command, we will get the below result:
The following command returns the data from the most_reliable_cars table:
On implementing the above command, we will get the below output:
In the below example, we will use an INTERSECT operator, which contains one field with a similar data type:
After executing the above command, we will get the following output:
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:
After implementing the above command, we will get the below output, which displays those intersect values that we retrieve after using the where condition.
Note: In the above example, the WHERE clauses have been added to all the datasets.
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.
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.
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.
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:
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.
In the PostgreSQL Intersect section, we have learned the following topics: