Beginner

688. JOIN with UNION Result

Learn how the result of a UNION can be treated as a dataset and JOINed with another table.

📘 Lesson 688 of 728 ⏱ 15 min 🎯 Advanced

A UNION combines compatible result sets vertically, and the combined result can then be JOINed with another dataset.

This is useful when related records come from multiple sources or tables with similar structures.

Combine current and archived customers before JOINing them with sales data.

SQL 
SELECT
    c.CustomerName,
    s.TotalSales
FROM (
    SELECT CustomerID, CustomerName
    FROM CurrentCustomers

    UNION

    SELECT CustomerID, CustomerName
    FROM ArchivedCustomers
) c
INNER JOIN (
    SELECT
        CustomerID,
        SUM(OrderAmount) AS TotalSales
    FROM Orders
    GROUP BY CustomerID
) s
    ON c.CustomerID = s.CustomerID;

Used in historical systems, data migration, reporting, and consolidated datasets.

  • UNION combines compatible result sets.
  • The combined result can be JOINed.
  • Both UNION queries must have compatible columns.
  • UNION removes duplicate rows.
  • UNION ALL preserves duplicates.
Can a UNION result be JOINed?

Yes. A UNION result can be used as a derived table and JOINed with another dataset.

What is the difference between UNION and UNION ALL?

UNION removes duplicate rows, while UNION ALL retains them.

Have you finished this lesson?

Introduction to SQL – All Lessons