SQL INTERSECT keyword in DB2 for i SQL

SQL INTERSECT keyword in DB2 for i SQL

INTERSECT keyword

The INTERSECT keyword returns the rows that are same between the result set of two or more tables.

Syntax of INTERSECT keyword

SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
[Conditions] //optional
INTERSECT
SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
[Conditions] //optional

Note: The number and order of columns in both SELECT statements must be the same to run INTERSECT query.

How SQL INTERSECT keyword works?

First SUB-SELECT query gets processed and result set retun in an intermediate table.

Second SUB-SELECT query gets processed and result set return in an intermediate table.

Now, all the matching rows present will get selected from both the result set..

Example using INTERSECT keyword

Refer the Structure, data of STUDENT from here.

Data in STUDENT table

ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201
     2   Rahul                           202
     3   Raman                           203
     4   Vimal                           204
     5   Samar                           290

Let's copy this table in another library QTEMP.

CPYF FROMFILE(EASYCLASS1/STUDENT)
     TOFILE(QTEMP/STUDENT)       
     CRTFILE(*YES)               

Above CPYF command will create a copy of STUDENT table from library EASYCLASS1 in QTEMP library and will contain same data as STUDENT in EASYCLASS1.

We can run INTERSECT query on both the table and return the identical rows

select * from easyclass1.student
intersect                          
select * from qtemp.student     

Or

select * from qtemp.student
intersect                          
select * from easyclass1.student     

Identical rows returned from Easyclass1/student table

ROLLNO   NAME                      SUBJECTID
     5   Samar                           290
     3   Raman                           203
     2   Rahul                           202
     1   Ankur                           201
     4   Vimal                           204

Let's delete some records in STUDENT table in library EASYCLASS1.

DELETE FROM EASYCLASS1/STUDENT WHERE ROLLNO > 3

Data in EASYCLASS1/STUDENT table

ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201
     2   Rahul                           202
     3   Raman                           203

Let's run INTERSECT query:

select * from easyclass1.student  
INTERSECT                            
select * from qtemp.student       

Or

select * from qtemp.student
INTERSECT                          
select * from easyclass1.student     

Identical rows returned from qtemp/student table

ROLLNO   NAME                      SUBJECTID
     3   Raman                           203
     2   Rahul                           202
     1   Ankur                           201