20. Combining Results with UNION

Core Idea: UNION concatenates the result sets of two or more SELECT queries into a single combined output dataset. By default, UNION automatically deduplicates records, while UNION ALL preserves all duplicate rows.

Syntax Shape

            SELECT City, Country FROM Customers
            UNION
            SELECT City, Country FROM Suppliers;
        

UNION ALL (High Performance)

Use UNION ALL when duplicates are acceptable or known not to exist; it avoids an expensive internal distinct sort operation:

            SELECT 'Domestic' AS MarketType, ProductName, Revenue FROM DomesticSales
            UNION ALL
            SELECT 'International' AS MarketType, ProductName, Revenue FROM GlobalSales;
        

Set Operators: INTERSECT & EXCEPT

INTERSECT returns only rows common to both query results. EXCEPT returns rows from the first query that do not exist in the second query.

            SELECT CustomerID FROM Orders2023
            INTERSECT
            SELECT CustomerID FROM Orders2024;
        

Advanced Deep Dive ADVANCED

Column Requirements: Each query in a UNION must contain the exact same number of columns in the same order, with compatible or implicitly convertible data types.

Column Naming: Column names in the combined result set are taken exclusively from the first SELECT query statement.

ORDER BY Placement: Only a single global ORDER BY clause is permitted, placed at the very end of the final query in the union chain.

Performance Insight: UNION executes a Sort or Hash Distinct operator to eradicate duplicate records. Prefer UNION ALL whenever uniqueness is already guaranteed or unnecessary.