SQL Set Operator INTERSECT and INTERSECT ALL / Supported Set Operators in various DBMSs / INTERSECT and INTERSECT ALL Set Operators with Examples
Let us use the following tables
for explaining the concept of the INTERSECT set operator;
Regno
|
Name
|
Phone
|
10BS0123
|
Madhavan
|
9965235412
|
10BC0234
|
Jerry
|
8569326541
|
11BM0023
|
Malar
|
9658236542
|
11BC0003
|
Kumar
|
9653268954
|
Table 1 - UG_Student
Regno
|
Name
|
Phone
|
10MS0434
|
Noel
|
9856452314
|
10MS0012
|
Kumar
|
9653268954
|
11MC0070
|
Ramkumar
|
9965235412
|
Table 2 - PG_Student
INTERSECT operator is used to join two or more
tables vertically, and displays only the records which are common for all the tables. That is, the INTERSECT operator filters the records which can be commonly found in all the tables according to the given expressions.
For example, consider the query given below;
(SELECT Name,
Phone FROM UG_Student) INTERSECT (SELECT Name, Phone FROM PG_Student);
----- Query 1
The result of this query will be;
Related Articles
Name
|
Phone
|
Kumar
|
9653268954
|
Table 3 - Result of "(SELECT Name,
Phone FROM UG_Student) INTERSECT (SELECT Name, Phone FROM PG_Student);"
The result shows the only common
record according to the given expressions 1 (SELECT Name,
Phone FROM UG_Student) and 2 (SELECT Name, Phone FROM PG_Student) of Query 1.
The INTERSECT operator can be
used in two ways;
1. INTERSECT [Distinct] – Here Distinct
keyword need not be specified.
2. INTERSECT ALL – Removes the
duplicate tuples (records) from the result set. For intersect all, we will get the result as Table 4.
Name
|
Phone
|
Kumar
|
9653268954
|
Kumar
|
9653268954
|
Table 4 - Result of "(SELECT Name,
Phone FROM UG_Student) INTERSECT ALL (SELECT Name, Phone FROM PG_Student);"
Support for SQL Set Operator INTERSECT in Various DBMSs:
INTERSECT operator in MySQL
MySQL does not support INTERSECT. It can be achieved through other ways using the keywords IN or EXISTS. Query 1 is rewritten using the keyword IN as follows to achieve intersection in MySQL.
SELECT Name,
Phone FROM UG_Student WHERE (Name, Phone) IN (SELECT Name, Phone FROM PG_Student);
INTERSECT operator in Oracle
Oracle supports INTERSECT operator as
discussed above. The above example (Query 1) can be treated as example for Oracle INTERSECT
operator.
INTERSECT operator in DB2
DB2 supports INTERSECT operator.
Above examples can be treated as examples for DB2 too.