7.3. The UNION ALL Operation

UNION ALL works exactly like UNION, but does not expunge duplicates or sort the results. UNION ALL is more efficient in execution (because UNION ALL does not have to expunge the duplicates), and occasionally you may need to keep duplicates (just to keep all occurrences or records), in which case you can use UNION ALL.

The following is the same query previously shown for UNION, but using UNION ALL instead of UNION:

    SELECT sname
    FROM Student
    WHERE major = 'COSC'
      UNION ALL
    SELECT sname
    FROM Student
    WHERE major = 'MATH'

This query results in 17 unsorted rows, including one duplicate, Jake; using UNION produced 16 rows with no duplicates:

    sname
    --------------------
    Mary
    Zelda
    Brenda
    Lujack
    Elainie
    Jake
    Hillary
    Brad
    Alan
    Jerry
    Mario
    Kelly
    Reva
    Monica
    Sadie
    Stephanie
    Jake

    (17 row(s) affected)