Combining Queries with UNION

Understanding Combined Queries

Most SQL queries contain a single SELECT statement that returns data from one or more tables. However, standard SQL also enables you to execute multiple separate SELECT statements and combine their output into a single, unified result set. These combined queries are generally referred to as compound queries or union queries.

You typically construct combined queries in two primary scenarios: when you need to return structurally similar data from multiple different tables in a single query, or when you want to execute multiple separate queries against a single table but return the data as one consolidated list.

The UNION Operator

To create a combined query, you use the UNION operator. The UNION keyword is placed directly between each individual SELECT statement you wish to combine. The database engine executes each query independently and then appends the results of the subsequent queries to the bottom of the first query's result set.

SELECT customer_name, contact_email
FROM customers
WHERE state = 'NY'
UNION
SELECT customer_name, contact_email
FROM customers
WHERE state = 'CA';

In this example, the database first retrieves the specified columns for customers in New York. It then retrieves the exact same columns for customers in California. Finally, it merges the two sets of records and presents them as a single output.

Rules for Combining Queries

Because the database engine must merge multiple independent result sets into a single grid, you must adhere to several strict structural rules when using the UNION operator.

• A UNION must consist of two or more SELECT statements, separated by the UNION keyword.
• Each query within the UNION must contain the exact same number of columns, calculated fields, or aggregate functions.
• The columns must be listed in the exact same order in every SELECT statement.
• The data types of the corresponding columns must be compatible. They do not need to be the exact same data type, but the database must be able to implicitly convert them.

Handling Duplicate Rows

When you combine queries using the standard UNION operator, the database engine automatically evaluates the final combined result set and removes any duplicate rows. If a row is returned by the first query and an identical row is returned by the second query, it will only appear once in your final output.

If you want the database engine to preserve and return every single row, including duplicates, you must modify the operator and use UNION ALL instead.

SELECT product_id, category
FROM current_inventory
UNION ALL
SELECT product_id, category
FROM archived_inventory;

The UNION ALL operator is the only standard SQL technique that allows you to deliberately retain duplicate rows across multiple queries. Because the database engine skips the computational step of identifying and removing duplicates, a UNION ALL operation generally executes faster than a standard UNION.

Sorting Combined Results

When combining queries, you cannot sort the individual result sets independently. You are only permitted to sort the final, combined result set.

To sort a UNION query, you place a single ORDER BY clause at the very end of the entire statement, immediately following the final SELECT query.

SELECT customer_name, contact_email
FROM customers
WHERE state = 'NY'
UNION
SELECT customer_name, contact_email
FROM customers
WHERE state = 'CA'
ORDER BY customer_name;

The database engine will interpret this single ORDER BY clause as an instruction to sort the complete, merged data. Placing an ORDER BY clause anywhere else within a compound query will result in a syntax error.

Apply these union techniques to safely and efficiently merge independent data sets into comprehensive reports.