UNION AND UNION ALL

This lesson on UNION AND UNION ALL is hands-on and example-driven. You will learn how to combine the results of multiple SELECT statements vertically into a single output table. You will be able to use UNION to stack rows while removing duplicates, or UNION ALL to include every row from every source query.

What You'll Be Able To Do

• Combine result sets from two different tables using the UNION operator.
• Write queries that preserve all rows, including duplicates, using UNION ALL.
• Ensure that the number and data types of columns match across all combined queries.
• Apply an ORDER BY clause correctly to sort the final, combined result set.
• Add a static label column to identify the source query for each returned row.

Detailed Concept Walkthrough

1. UNION Operator (Distinct)

UNION stacks the rows from multiple queries into one result set. By default, it performs a distinct operation, removing any rows that are identical across the combined output.

• Mechanism: The database executes all component SELECT statements first, then merges the results into a temporary set.
• Under the Hood: A sorting and comparison process is run on the temporary set to identify and discard duplicate rows, which adds significant overhead.
• Best Practice: Use UNION only when duplicate removal is necessary, as the distinct operation is computationally expensive.

SELECT first_name, last_name FROM employees 
UNION 
SELECT first_name, last_name FROM contractors;

Key Takeaway: UNION combines rows and automatically enforces uniqueness across the entire result set.

2. UNION ALL (Preserving Duplicates)

UNION ALL stacks rows from multiple queries without checking for or removing duplicates. This is the fastest way to combine result sets vertically.

• Mechanism: Rows from the second query are simply appended directly to the rows returned by the first query.
• Under the Hood: No sorting or comparison is required, making UNION ALL much faster and less resource-intensive than standard UNION.
• Syntax Rule: The ALL keyword must immediately follow UNION to explicitly request the inclusion of duplicate rows.

SELECT product_id, price FROM sales_q1 
UNION ALL 
SELECT product_id, price FROM sales_q2;

Key Takeaway: Use UNION ALL when performance is critical and duplicate rows are acceptable or expected.

3. Structural Requirements and Ordering

To combine queries, the structure of the columns must be identical. Sorting is applied only once, at the very end of the combined statement.

• Syntax Rule: All SELECT statements in a UNION operation must return the exact same number of columns.
• Syntax Rule: The data types of corresponding columns must be compatible (e.g., string with string, integer with integer).
• Mechanism: The column names of the final result set are determined by the column names defined in the first SELECT statement.
• Best Practice: Place the ORDER BY clause only after the final SELECT statement to sort the entire combined output.

SELECT id, name FROM table_a 
UNION ALL 
SELECT id, name FROM table_b 
ORDER BY name DESC;

Key Takeaway: Column structure must match exactly, and ORDER BY applies globally at the end.

4. Labeling Source Queries

You can add a static string value as an extra column to identify which source query contributed a specific row to the final result set.

• Mechanism: A literal string value is included in the SELECT list of each component query, usually aliased to a descriptive column name.
• Best Practice: Ensure this new label column is included in all component queries to maintain the required matching column count.
• Syntax Rule: The label string must be enclosed in single quotes (e.g., 'Source A').
• Execution Flow: The database treats the static string as a valid column value during the combination process.

SELECT name, 'Q1' AS source FROM sales_q1 
UNION ALL 
SELECT name, 'Q2' AS source FROM sales_q2;

Key Takeaway: Use static string aliases to track the origin of rows in complex combined results.

Topics Covered in UNION AND UNION ALL

1. UNION Operator (0:03 - 2:06) — The UNION operator combines result sets vertically and automatically removes duplicate rows.
2. UNION vs DISTINCT (2:07 - 2:34) — Standard UNION behaves identically to UNION DISTINCT by default, enforcing uniqueness.
3. UNION ALL (2:35 - 2:56) — UNION ALL combines result sets without performing duplicate removal, resulting in faster execution.
4. Labeling Source Rows (3:23 - 3:39) — Static values can be added as columns to identify which source query contributed a specific row.
5. Ordering Combined Results (6:12 - 6:33) — The ORDER BY clause must be placed at the end of the entire combined statement to sort the final output.

SQL Cheat Sheet

• SELECT ... UNION SELECT ... — Combines results vertically, removing duplicate rows

  SELECT id FROM A UNION SELECT id FROM B;
  

• SELECT ... UNION ALL SELECT ... — Combines results vertically, keeping all duplicate rows

  SELECT id FROM A UNION ALL SELECT id FROM B;
  

• ORDER BY column_name — Sorts the entire combined result set globally

  SELECT id FROM A UNION SELECT id FROM B ORDER BY id;
  

• 'label' AS column_name — Adds a static column to identify the source query

  SELECT name, 'Emp' AS type FROM employees;
  

• Column Count Match — Requires the same number of columns in all queries

Comparison Table

Common Pitfalls

• Mistake: Using different column names or aliases in the component queries. Avoid: The final result set uses the column names from the first query.
• Mistake: The number of columns or data types do not match. Avoid: Ensure column count and data types are identical across all SELECT lists.
• Mistake: Placing ORDER BY inside one of the component queries. Avoid: ORDER BY must be placed once, after the final SELECT statement.

FAQs

• Does UNION combine columns or rows? UNION combines rows (stacks them vertically), unlike JOIN which combines columns (horizontally).
• Why is UNION ALL generally preferred over UNION? UNION ALL is significantly faster because it skips the resource-intensive step of checking for and removing duplicate rows.
• Can I use WHERE clauses with UNION? Yes, you can apply WHERE clauses to filter data within each individual SELECT statement before the results are combined.