EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL INTERSECT

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL INTERSECT

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

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.
  • 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:

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');

Popular Course in this category
PostgreSQL Course (2 Courses, 1 Project)2 Online Courses | 1 Hands-on Project | 7+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (2,948 ratings)
Course Price

View Course

Related Courses

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 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:

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.
  • 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:

operator using specific column

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.

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 –

  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

PostgreSQL Course (2 Courses, 1 Project)

2 Online Courses

1 Hands-on Project

7+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL log_statement
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

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

EDUCBA

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

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

Special Offer - PostgreSQL Course (2 Courses, 1 Project) Learn More