Intersect Operator in DBMS

Intersect Operator

Intersect statement combines result-set of two or more select queries and returns only those values that are common in both the result set.

In this article, we will learn about Intersect Operator in DBMS.

intersect operator in DBMS

INTERSECT Operator in DBMS

Simply INTERSECT returns the common rows are values common in both the data sets and will not consider duplicate values

Syntax for INTERSECT 

SELECT column name(s) FROM table1
INTERSECT
SELECT column name(s) FROM table2;

Each SELECT statement within INTERSECT must have

  • The same number of columns
  • The columns must also have similar data types
  • The columns in each SELECT statement must also be in the same order
Intersect Operator in DBMS

Example of INTERSECT

The First table,
ID NAME
1 rishi
2 trish
3 mahi
The Second table,
ID NAME
2 trish
3 Chester
4 mahi
INTERSECT query will be,
SELECT name FROM First
INTERSECT
SELECT name FROM Second;
The result set table will look like
Name
trish
mahi

Another example of intersect 

Whenever intersect is applied on the select queries which contain more than one column then the combination of both the values is considered as one tuple that is if both the values in the row are same am then only it is considered as common value
SELECT id, name FROM First
INTERSECT
SELECT id, name FROM Second;
O/P
2 Trish
In our example combination of [2, Trish] is considered as one value while returning the common tuples

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription