PostgreSQL INTERSECT

Introduction to INTERSECT in PostgreSQL

PostgreSQL INTERSECT will combine the result of two or more select statements, like union and except operation in PostgreSQL. It will combine the result of two or more select statements. It will combine two or more select statement results set into a single result set.

This operator returns rows that were available in both the result set. We can combine one or more select query results set into a single result set by using this operator, and it is more useful and important in PostgreSQL.

Syntax 1

Select Column_name1, Column_name2, …, Column_nameN from table1
INTERSECT
Select Column_name1, Column_name2, …, Column_nameN from table2
Where condition

Syntax 2

Select expression1, expression2, …, expressionN from table1
INTERSECT
Select expression 1, expression 2, …, expressionN from table2
Where condition

Syntax 3

elect * from table1
INTERSECT
Select * from table2
Where condition

Below is the parameter description of the above syntax are as follows:

• Select: Select statement is used to select no of the column from tables.
• Column1 to ColumnN: Column used in the select statement to fetch results using
• intersect in PostgreSQL.
• Expression1 to ExpressionN: Column used in the select statement to fetch results using
• intersect in PostgreSQL.
• .Table1 and Table2: Table used to retrieve data using the operator in PostgreSQL.
• Intersect: This operator used to combine the result of two or more select queries into a single result set.
• From: Keyword to define table from which we have fetching data.
• Where condition: Where the condition is used to fetch a specific condition of data.
• Asterisk (*): Retrieve all columns in the result set from the specified table.

How does the INTERSECT Operator work in PostgreSQL?

• It will be used to combine two or more select statements into a single query and retrieve matching rows from both the data set.
• Below is the picture representation of INTERSECT as follows:

• The above figure shows the INTERSECT of table 1 and table 2. The result of the INTERSECT of table 1 and table 2 was combined with a single result set.
• The operator is only used in condition if we only have duplicate data from both the result set.
• INTERSECT only fetch those available data in both the result set, duplicate data fetched using intersect in PostgreSQL.
• We have not used the order by clause in the INTERSECT operator to fetch data in ascending or descending order.
• This operator will return the intersection of two or more data set into the single resultant data set.
• A select statement will define each data set in INTERSECT, and the record that exists in each data set will be included in the resultant set.

Examples of PostgreSQL INTERSECT Operator

To use the INTERSECT operator in PostgreSQL, we have using the emp_test1 and emp_test2 table to describe INTERSECT in PostgreSQL.

Table1: emp_test1

CREATE TABLE emp_test1 ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL, PRIMARY KEY (emp_id));

Output:

INSERT INTO emp_test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (1, 'ABC', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

INSERT INTO emp_test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (2, 'PQR', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

INSERT INTO emp_test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (3, 'XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');

Output:

INSERT INTO emp_test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (4, 'BBS', 'Mumbai', '1234567890', 45000, '02-01-2020');

Output:

INSERT INTO emp_test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (5, 'RBS', 'Delhi', '1234567890', 50000, '03-01-2020');

Output:

select * from emp_test1;

Output:

Table 2: emp_Test2

CREATE TABLE emp_test2 ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL, PRIMARY KEY (emp_id));

Output:

INSERT INTO emp_test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (6, 'PQR', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

INSERT INTO emp_test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (7, 'XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');

Output:

INSERT INTO emp_test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (8, 'BBS', 'Mumbai', '1234567890', 45000, '02-01-2020');

Output:

INSERT INTO emp_test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (9, 'RBS', 'Delhi', '1234567890', 50000, '03-01-2020');

Output:

INSERT INTO emp_test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (10, 'ABC', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

select * from emp_test2;

Output:

Please find below an example of INTERSECT operator in PostgreSQL:

Example #1

Intersect operator using all columns from both the table:

• In the below example, we have to retrieve data from all the columns. In such a case, all data is combined with emp_test1 and emp_test2 tables.
• In this scenario, all records will be retrieved from both tables.

select * from Emp_Test1 INTERSECT select * from Emp_Test2;

Output:

Example #2

Intersect operator using a specific column from both the table:

• In the below example, we have to retrieve data from specific columns. In such a case, only matching records from both the tables are fetched.
• Duplicate records from both tables are fetched using intersect in PostgreSQL.
• In this scenario, only matching records will be retrieved from both the tables using intersect in PostgreSQL.

select emp_name, emp_address from Employee_Test1 INTERSECT select emp_name, emp_address from Employee_Test2;

Output:

Rules and Regulation for INTERSECT in PostgreSQL

• Intersect in PostgreSQL will return those rows which were common from both the datasets.
• Intersect operator in PostgreSQL doesn’t manage data in ascending or descending order.
• It only retrieves matching records from both the dataset.
• If we only required a matching record from two different tables simultaneously, we have used intersect in PostgreSQL.
• This operator is handy and important in PostgreSQL to retrieve matching data from two or more data sets.
• This operator returns those rows which are available from both the data set in PostgreSQL.

Conclusion

It is essential in PostgreSQL. Retrieve matching data from two or more data sets at the same time PostgreSQL intersect operator is used. This operator only retrieves matching records from both the dataset. If we required matching records data set at the same time, we used intersect in PostgreSQL.

This has been a guide to PostgreSQL INTERSECT. Here we discuss the introduction, how this operator works in PostgreSQL with examples. You may also have a look at the following articles to learn more –

1. SQL Set Operators
2. Joins in Oracle
3. SQL Set Operators
4. SQL Right Join
5. PostgreSQL Views (Examples)
6. PostgreSQL FETCH with Examples