Intersect Operator in DBMS

INTERSECT Operator in DBMS

 

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

  • INTERSECT statement combines result-set of two or more select queries and returns only those values that are common in both the result set
  • 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
Intersection 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