INTERSECT in Oracle SQL – PLSQL

An INTERSECT in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, an INTERSECT fetches only the common rows returned by SQL SELECT statements.
Also, each SELECT statement must have the same number of column(s)/field(s) with similar data-types for the INTERSECT to work.

Syntax for INTERSECT in Oracle SQL / PLSQL is:
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
INTERSECT
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s);

Suppose have a table named ‘employee’ in the database as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Also we have one more table ‘comm’ as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s take an example to understand INTERSECT, suppose we write our SQL query using INTERSECT as:

SELECT employee_id
FROM employee
INTERSECT
SELECT emp_id
FROM comm;

The result of the above query will be:

Employee_Id
102
103
104
105

Here we can see that the records for employee_id = ‘102’,’103,’104’ and ‘105’ only appear in the result set because they are present in both (‘employee’ and ‘comm’) tables.


Tagged , , , , , , , , , , , . Bookmark the permalink.