EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL INTERSECT

PostgreSQL INTERSECT

Priya Pedamkar
Article byPriya Pedamkar

Updated May 3, 2023

PostgreSQL INTERSECT

Introduction to INTERSECT in PostgreSQL

PostgreSQL INTERSECT will combine the result of two or more select statements, like union and except operation. 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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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?

  • One can merge multiple select statements into one query and collect corresponding rows from the dataset using it.
  • Below is the picture representation of INTERSECT as follows:

postgreSQL INTERSECT 1

  • 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.
  • We will define each data set in INTERSECT by using a select statement that includes the shared record to create the resultant set.

Examples

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

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:

emp_test1

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:

postgreSQL INTERSECT 3

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:

postgreSQL INTERSECT 4JPG

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:

postgreSQL INTERSECT 7JPG

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

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:

postgreSQL INTERSECT 9JPG

select * from emp_test1;

Output:

postgreSQL INTERSECT 10JPG

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:

postgreSQL INTERSECT 11JPG

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:

postgreSQL INTERSECT 12JPG

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:

postgreSQL INTERSECT 13JPG

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:

postgreSQL INTERSECT 14JPG

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:

postgreSQL INTERSECT 15JPG

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:

postgreSQL INTERSECT 16JPG

select * from emp_test2;

Output:

select * from emp_test2;

Please find below an example of INTERSECT operator :

Example #1

Intersect operator using all columns from both the table:

  • We must retrieve data from every column in the example below. All data is then combined with the tables emp_test1 and emp_test2 in this scenario.
  • In this scenario, all records will be retrieved from both tables.
select * from Emp_Test1 INTERSECT select * from Emp_Test2;

Output:

using all column

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.
  • To retrieve duplicate records from two tables in PostgreSQL, you can use the intersect function.
  • When using “INTERSECT” in PostgreSQL, only the matching records from both tables will be retrieved.
select emp_name, emp_address from Employee_Test1 INTERSECT select emp_name, emp_address from Employee_Test2;

Output:

operator using specific column

Rules and Regulation

  • 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.
  • In PostgreSQL, the “INNER JOIN” operator returns only those rows that are available in both datasets.

Conclusion

It is essential in PostgreSQL. To retrieve matching data from two or more datasets simultaneously in PostgreSQL, you can use the INTERSECT operator. 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

We hope that this EDUCBA information on “PostgreSQL INTERSECT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Set Operators
  2. Joins in Oracle
  3. SQL Set Operators
  4. SQL Right Join
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW