← Back to Chapters

MySQL UNION ALL

? MySQL UNION ALL

? Quick Overview

The UNION ALL operator combines results from multiple SELECT queries and returns all rows, including duplicates. It’s useful when you want raw merged data without filtering unique values.

? Key Concepts

  • Returns all rows, including duplicates.
  • Faster than UNION since it skips the duplicate-removal step.
  • Each SELECT must return the same number of columns.
  • Column data types must be compatible.

? Syntax

? View Code Example 
// Basic UNION ALL syntax combining results
SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2;

? Example

? View Code Example 
// Creating tables and using UNION ALL
CREATE TABLE Sales_Q1 (Product VARCHAR(50), Amount INT);
CREATE TABLE Sales_Q2 (Product VARCHAR(50), Amount INT);
INSERT INTO Sales_Q1 VALUES ('Laptop', 1000), ('Mouse', 200), ('Laptop', 1000);
INSERT INTO Sales_Q2 VALUES ('Laptop', 1500), ('Keyboard', 300);
SELECT Product, Amount FROM Sales_Q1
UNION ALL
SELECT Product, Amount FROM Sales_Q2;

? Explanation

  • UNION ALL: Keeps duplicate rows in the final output.
  • Can merge quarterly sales, logs, or report fragments efficiently.
  • Use UNION instead if you want only unique rows.
  • Add ORDER BY only at the end of the entire query.

✅ Tips & Best Practices

  • Use UNION ALL for performance when duplicates don’t matter.
  • Ensure all SELECT statements have matching column counts.
  • Place ORDER BY after the final SELECT block.

? Try It Yourself

  • Create employee tables for two branches and merge using UNION ALL.
  • Compare results by switching UNION ALL with UNION.
  • Add ORDER BY Product to sort combined results.