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.
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:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];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:
SELECT * FROM top_rated_cars; Output
After executing the above command, we will get the below result:
The following command returns the data from the most_reliable_cars table:
SELECT * FROM most_reliable_cars; Output
On implementing the above command, we will get the below output:
- 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:
SELECT Car_name
FROM top_rated_cars
INTERSECT
SELECT Car_name
FROM most_reliable_cars;Output
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:
SELECT Car_name
FROM top_rated_cars
WHERE launch_year< 2020
INTERSECT
SELECT Car_name
FROM most_reliable_cars
Where launch_year=2020;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.
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.
SELECT address, phone
FROM employee
WHERE employee_name<> 'ross'
INTERSECT
SELECT address, phone
FROM department
WHERE address<>'London'; 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.
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:
SELECT select_list
FROM table1
INTERSECT
SELECT select_list
FROM table2
ORDER BY sort_expression; 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:
SELECT address, phone
FROM employee
WHERE employee_name<> 'ross'
INTERSECT
SELECT address, phone
FROM department
WHERE address<>'London'
ORDER BY 1;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.
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.
Leave a Reply