SQL EXCEPT keyword in DB2 for i SQL

SQL EXCEPT keyword in DB2 for i SQL

EXCEPT keyword

The EXCEPT keyword returns the rows from the first sub-select query minus any matching rows from the second sub-select.

Syntax of EXCEPT keyword

SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
[Conditions] //optional
EXCEPT
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 EXCEPT query.

How SQL EXCEPT 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 in 2nd sub-select query gets excluded from the firs-subselect query and the remaining rows from the first sub-select query gets rturned.

Example using EXCEPT 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 EXCEPT query on both the table and check if both table have same data or not

select * from easyclass1.student
except                          
select * from qtemp.student     

No rows returned from Easyclass1/student table

       ROLLNO   NAME                      SUBJECTID  
********  End of data  ********                      

select * from qtemp.student
except                          
select * from easyclass1.student     

No rows returned from qtemp/student table

       ROLLNO   NAME                      SUBJECTID  
********  End of data  ********                      

This means both the table has same data. Nothing got changed.

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

INSERT query to insert one record in STUDENT table

INSERT INTO EASYCLASS1/STUDENT (ROLLNO, NAME, SUBJECTID) VALUES(11, 
'Inserted_Name', 98)                                                

Data in EASYCLASS1/STUDENT table

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

Let's run EXCEPT query both the ways:

select * from easyclass1.student  
except                            
select * from qtemp.student       

This will return the 1 row from the EASYCLASS1/STUDENT table i.e. not present in QTEMP/STUDENT table.

ROLLNO   NAME                      SUBJECTID
    11   Inserted_Name                    98

select * from qtemp.student
except                          
select * from easyclass1.student     

No rows returned from qtemp/student table

       ROLLNO   NAME                      SUBJECTID  
********  End of data  ********