Beginner

689. JOIN with UNION ALL

Combine datasets with UNION ALL and then JOIN the combined result.

📘 Lesson 689 of 728 ⏱ 15 min 🎯 Advanced

UNION ALL vertically combines result sets without removing duplicates, allowing the combined data to participate in a JOIN.

UNION ALL is often more efficient than UNION when duplicate removal is not required.

Combine monthly transaction tables and JOIN them with account information.

SQL 
SELECT
    a.AccountNumber,
    t.TransactionID,
    t.TransactionAmount
FROM (
    SELECT AccountID, TransactionID, TransactionAmount
    FROM Transactions_2026_01

    UNION ALL

    SELECT AccountID, TransactionID, TransactionAmount
    FROM Transactions_2026_02
) t
INNER JOIN Accounts a
    ON t.AccountID = a.AccountID;

Used in partitioned data, historical tables, monthly files, and data warehouse reporting.

  • UNION ALL keeps duplicate rows.
  • It usually avoids the duplicate-removal step of UNION.
  • Source columns must be compatible.
  • Useful for combining partitions or monthly tables.
  • Duplicate handling becomes the responsibility of the query design.
Why use UNION ALL instead of UNION?

Use UNION ALL when duplicates should be preserved and duplicate removal is unnecessary.

Can UNION ALL results be JOINed?

Yes. The combined result can be treated as a derived table and JOINed.

Have you finished this lesson?

Introduction to SQL – All Lessons