INTERSECT and EXCEPT use in SQL Queries

An INTERSECT operation retrieves the matching set of distinct values (not rows) from two columns. The INTERSECT ALL returns the set of matching individual rows. An INTERSECT and/or EXCEPT operation is done by matching ALL of the columns in the top and bottom result sets. In other words, these are rows, not the column, operations. It is not possible to only match on the keys, yet at the same time, also fetch non-key columns. To do this, one needs to use a sub-query. The UNION operation is a logical OR, INTERSECT is a logical AND. When multiple operations are done in the same SQL statement, there are precedence rules:

• Operations in parenthesis are done first.
• INTERSECT operations are done before either UNION or EXCEPT.
• Operations of equal worth are done from top to bottom.

SQL treats NULLS differently in these operations than it does when handing NULLS in joins and scans. SQL treats set operations NULL values as equal to each other; that is, the evaluation expression (NULL = NULL) produces the result TRUE whereas, in JOIN or SCAN, NULL equality expression evaluates to UNKNOWN

INTERSECT Syntax

 SELECT column1 [, column2 ]
 FROM table1 [, table2 ]
 [WHERE condition]
 INTERSECT/ INTERSECT ALL
 SELECT column1 [, column2 ]
 FROM table1 [, table2 ]
 [WHERE condition]
 
Let’s consider below example 
 
Table R1                             Table R2
 R1                                   R2
 Abhi                                 Abhi
 Abhi                                 Abhi
 Abhi                                 Baby
 Baby                                 Baby
 Baby                                 Baby
 Cat                                  Cat
 Cat                                  Dan
 Cat
 Elie
 
 SELECT R1
 FROM R1
 INTERSECT/ INTERSECT ALL 
 SELECT R2
 FROM R2
 ORDER BY 1;
 
 INTERSECT          INTERSECT ALL
 Abhi               Abhi      
 Baby               Abhi      
 Cat                Baby
                    Baby
                    Cat

EXCEPT & EXCEPT ALL

An EXCEPT operation retrieves the set of distinct data values (not rows) that exist in the first table but not in the second. The EXCEPT ALL returns the set of individual rows that exist only in the first table.

NOTE: Only the EXCEPT operation is not commutative. Both the UNION and the INTERSECT operations work the same regardless of which table is on top or on the bottom.

Example:
Table R1                             Table R2
 R1                                   R2
 Abhi                                 Abhi
 Abhi                                 Abhi
 Abhi                                 Baby
 Baby                                 Baby
 Baby                                 Baby
 Cat                                  Cat
 Cat                                  Dan
 Cat
 Elie 

 SELECT R1
 FROM R1
 EXCEPT/ EXCEPT ALL 
 SELECT R2
 FROM R2
 ORDER BY 1;
 
 EXCEPT             EXCEPT ALL
 Elie               Abhi      
                    Cat
                    Cat
                    Elie

WARNING: Unlike the UNION and INTERSECT operations, the EXCEPT statement is not commutative. This means that “A EXCEPT B” is not the same as “B EXCEPT A”. So if you use the EXCEPT operation in the reverse direction (i.e. R2 to R1 instead of R1 to R2) will give a different result:

 SELECT R2
 FROM R2
 EXCEPT/ EXCEPT ALL 
 SELECT R1
 FROM R1
 ORDER BY 1;

 EXCEPT             EXCEPT ALL
 Dan                Baby      
                    Dan

Read DB2 blogs : Click Here IBM DB2 Manual : Click Here