Introduction to INTERSECT in PostgreSQL
PostgreSQL INTERSECT will combine the result of two or more select statements, as like union and except operation in PostgreSQL and 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 specific condition of data.
- Asterisk (*): Retrieve all columns in the result set from the specified table.
How INTERSECT Operator works 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 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 have only duplicate data required from both the result set.
- INTERSECT only fetch those data which is available in both the result set, duplicate data fetched using intersect in PostgreSQL.
- We have not used 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.
- Each data set in INTERSECT will be defined by a select statement and the record 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 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 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 the 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 at the same time we have used intersect in PostgreSQL.
- This operator is very useful 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 very important 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.
Recommended Articles
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 –
