UNION & UNION ALL

Think about those times you have copied multiple worksheets’ data from various Microsoft Excel files into one worksheet and continuous data range to make a master workbook or worksheet. You started with one worksheet and simply copied and pasted the other worksheets’ data, excluding the headers, below the original worksheet’s data. Fundamentally, that is how SQL’s UNION and UNION ALL operators work. The UNION and UNION ALL operators allow you to vertically append the results of multiple SELECT statements (i.e., queries) together. Let us review the basic syntax and rules for these operators.

In the code block below, I highlight the basic syntax required. Notice the position of the UNION ALL operator. When used, it is required to follow immediately after a SELECT statement and immediately before another SELECT statement. The ALL keyword is optional. UNION ALL will combine the results of both SELECT statements without checking for and subsequently removing duplicates while the UNION operator will. If duplicates are not of concern or you know in advance that the two queries cannot return duplicate rows, then I suggest using UNION ALL because the process of checking for duplicates and removing them is not computationally cheap.

SELECT
	expression_1,
	expression_2,
	expression_n
FROM
	table
UNION ALL
SELECT
	expression_1,
	expression_2,
	expression_n
FROM
	table;

There are two other things to take note of regarding the operators. One, each SELECT statement must have the same number of attributes and those attributes should be within the same order. This is entirely sensible given that the result of vertically appending data that is not properly aligned is less than useful. If the number of attributes do not match naturally, then you can use dummy attributes. I will discuss the use of dummy attributes later in this discussion. The second thing to take note of is that each attribute’s datatype must match the corresponding attribute’s data type in the other SELECT statements or an implicit conversion must be possible by the relational database management system (RDBMS).

Before reading further, consider reviewing the Example Data section below to familiarize yourself with the data being used throughout the examples on this page. If you are already familiar with the data, then skip to the UNION ALL section.

Example Data
The tables and corresponding data used in the example on this page can be found below. To more easily follow the examples covered on this page, consider first reading the table descriptions below and reviewing each table’s contents. To review the data maintained in each table, click on the icon next to the table name. To hide the data, click the icon again.

EXEC DBMS_RANDOM.SEED(42);

CREATE TABLE north_america_sale AS
    WITH north_america_sale (report_date, product_category, total_sale) AS (
        SELECT
            daily_sale.report_date,
            data_template.product_category,
            daily_sale.total_sale
        FROM (
            SELECT
               'Software' AS category_1,
               'Hardware' AS category_2,
               'Office Supplies' AS category_3
            FROM
                dual
        )
        UNPIVOT (
            product_category FOR category_column IN (
                "CATEGORY_1", "CATEGORY_2", "CATEGORY_3"
            )
        ) data_template
        CROSS JOIN (
        SELECT
            TO_DATE('2023-01-01', 'YYYY-MM-DD') AS report_date,
            FLOOR(DBMS_RANDOM.VALUE(100000, 500000)) AS total_sale
        FROM
            dual
        ) daily_sale
        UNION ALL
        SELECT
            north_america_sale.report_date + INTERVAL '1' DAY,
            north_america_sale.product_category,
            FLOOR(DBMS_RANDOM.VALUE(100000, 500000))
        FROM
            north_america_sale
        WHERE
            north_america_sale.report_date < TO_DATE('2023-12-31', 'YYYY-MM-DD')
    )
    SELECT
        north_america_sale.report_date,
        north_america_sale.product_category,
        north_america_sale.total_sale
    FROM
        north_america_sale;