List Of Topics

# UNION, INTERSECT, AND EXCEPT

A UNION, EXCEPT, or INTERCEPT expression combines sets of columns into new sets of columns. An illustration of what each operation does with a given set of data is shown below:

Examples of Union, Except, and Intersect

`         R1       R1      R1         R1        R1         R1        UNION    UNION  INTERSECT  INTERSECT   EXCEPT    EXCEPT         R2      ALL       R2         ALL       R2        ALL R1  R2           R2                   R2                  R2 --  --  -----  -----   ---------    -----     ------    ------  A   A    A       A        A          A          E        A  A   A    B       A        B          A                   C  A   B    C       A        C          B                   C  B   B    D       A                   B                   E  B   B    E       A                   C  C   C            B  C   D            B  C                B  E                B                   B                   C                   C                   C                   C                   D                   E`

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".

Syntax Diagram

Union, Except, and Intersect syntax

Sample Views

`CREATE VIEW R1 (R1)AS VALUES ('A'),('A'),('A'),('B'),('B'),('C'),('C'),('C'),('E');CREATE VIEW R2 (R2)AS VALUES ('A'),('A'),('B'),('B'),('B'),('C'),('D');        ANSWER                                                            ======SELECT R1                                                    R1 R2FROM R1                                                      -- --ORDER BY R1;                                                  A A                                                              A ASELECT R2                                                     A BFROM R2                                                       B BORDER BY R2;                                                  B B                                                              C C                                                              C D                                                              C                                                              E`

Usage Notes

Union & Union All

A UNION operation combines two sets of columns and removes duplicates. The UNION ALL expression does the same but does not remove the duplicates.

Union and Union All SQL

`SELECT    R1               R1 R2 UNION UNION ALLFROM      R1               -- -- ===== =========UNION                      A  A    A      ASELECT    R2               A  A    B      AFROM      R2               A  B    C      AORDER BY  1;               B  B    D      A                           B  B    E      A                           C  C           BSELECT    R1               C  D           BFROM      R1               C              BUNION     ALL              E              BSELECT    R2                              BFROM      R2                              CORDER BY  1;                              C                                          C                                          C                                          D                                          E`

NOTE: Recursive SQL requires that there be a UNION ALL phrase between the two main parts of the statement. The UNION ALL, unlike the UNION, allows for duplicate output rows which is what often comes out of recursive processing.

Intersect & Intersect All

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.

Intersect and Intersect All SQL

`SELECT    R1                         R1  R2 INTERSECT INTERSECT ALLFROM      R1                         --  -- ========= =============INTERSECT                             A   A      A         ASELECT    R2                          A   A      B         AFROM      R2                          A   B      C         BORDER BY  1;                          B   B                B                                      B   B                CSELECT    R1                          C   CFROM      R1                          C   DINTERSECT ALL                         CSELECT    R2                          EFROM      R2ORDER BY  1;`

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 row, not 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.

Except & Except All

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

Except and Except All SQL (R1 on top)

`SELECT   R1                     R1     R1FROM     R1                   EXCEPT EXCEPT ALLEXCEPT                  R1 R2   R2     R2SELECT   R2             -- -- ===== ==========FROM     R2              A  A    E      AORDER BY 1;              A  A           C                         A  B           CSELECT   R1              B  B           EFROM     R1              B  BEXCEPT   ALL             C  CSELECT   R2              C  DFROM     R2              CORDER BY 1;              E`

Because the EXCEPT operation is not commutative, using it in the reverse direction (i.e. R2 to R1 instead of R1 to R2) will give a different result:

Except and Except All SQL (R2 on top)

`SELECT   R2                       R2      R2FROM     R2                     EXCEPT  EXCEPT ALLEXCEPT                    R1 R2   R1      R1SELECT   R1               -- -- =====  ==========FROM     R1               A  A     D      BORDER BY 1;               A  A            D                          A  BSELECT   R2               B  BFROM     R2               B  BEXCEPT   ALL              C  CSELECT   R1               C  DFROM     R1               CORDER BY 1;               E`

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 bottom.

Precedence Rules

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.

The next example illustrates how parenthesis can be used change the processing order:

Use of parenthesis in Union

`SELECT   R1     (SELECT   R1    SELECT    R1   R1 R2FROM     R1      FROM     R1    FROM      R1   -- --UNION            UNION          UNION           A ASELECT   R2      SELECT   R2   (SELECT    R2    A AFROM     R2      FROM     R2    FROM      R2    A BEXCEPT          )EXCEPT         EXCEPT          B BSELECT   R2      SELECT   R2    SELECT    R2    B BFROM     R2      FROM     R2    FROM      R2    C CORDER BY 1;      ORDER BY 1;   )ORDER BY  1;    C D                                                C                                                E         ANSWER      ANSWER      ANSWER         ======      ======      ======          E            E            A                                    B                                    C                                    E`

Unions and Views

Imagine that one has a series of tables that track sales data, with one table for each year. One can define a view that is the UNION ALL of these tables, so that a user would see them as a single object. Such a view can support inserts, updates, and deletes, as long as each table in the view has a constraint that distinguishes it from all the others. Below is an example:

Insert, update, and delete using view

`CREATE TABLE sales_data_2002(sales_date      DATE      NOT NULL,daily_seq#      INTEGER   NOT NULL,cust_id         INTEGER   NOT NULL,amount          DEC(10,2) NOT NULL,invoice#        INTEGER   NOT NULL,sales_rep       CHAR(10)  NOT NULL,CONSTRAINT C CHECK (YEAR(sales_date) = 2002),PRIMARY KEY (sales_date, daily_seq#));CREATE TABLE sales_data_2003(sales_date      DATE      NOT NULL,daily_seq#      INTEGER   NOT NULL,cust_id         INTEGER   NOT NULL,amount          DEC(10,2) NOT NULL,invoice#        INTEGER   NOT NULL,sales_rep       CHAR(10)  NOT NULL,CONSTRAINT C CHECK (YEAR(sales_date) = 2003),PRIMARY KEY (sales_date, daily_seq#));CREATE VIEW sales_data ASSELECT *FROM sales_data_2002UNION ALLSELECT *FROM sales_data_2003;Define view to combine yearly tablesBelow is some SQL that changes the contents of the above view:INSERT INTO sales_data VALUES ('2002-11-22',1,123,100.10,996,'SUE')                               ,('2002-11-22',2,123,100.10,997,'JOHN')                               ,('2003-01-01',1,123,100.10,998,'FRED')                               ,('2003-01-01',2,123,100.10,999,'FRED');UPDATE sales_dataSET amount = amount / 2WHERE sales_rep = 'JOHN';DELETEFROM sales_dataWHERE sales_date = '2003-01-01'AND daily_seq# = 2;`

Below is the view contents, after the above is run:

View contents after insert, update, delete

`SALES_DATE DAILY_SEQ# CUST_ID AMOUNT INVOICE# SALES_REP---------- ---------- ------- ------ -------- ---------01/01/2003    1        123    100.10   998     FRED11/22/2002    1        123    100.10   996     SUE11/22/2002    2        123    50.05    997     JOHN`

 e-University Search   Related Jobs SQL DBA, Sybase DBA & DB2 DBA at US based Investment Bank!! (4 - 10 yrs.) Webscape Technologies Private Limited- DB Developer/db2 & Ms-sql (4-6 yrs.) (4 - 6 yrs.) Human Network hiring for A French multinational corporation-