Union SQL

How do you combine the results of two or more SELECT statements?

The UNION operator in SQL combines the result sets of multiple SELECT statements into a single result set. It's crucial for aggregating data from different sources or filtering results in a single query. Crucially, the columns in the SELECT statements must be compatible.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

The UNION operator is a powerful tool in SQL for combining the results of multiple SELECT statements. Imagine you have two tables: one containing customer orders and another containing customer returns. You might want to see a complete list of all items ordered or returned by a customer. UNION allows you to achieve this in a single query. It takes the output of multiple SELECT statements and merges them into a single result set. A key aspect of UNION is that the columns in the SELECT statements must have compatible data types and the same order. This ensures that the combined result set is semantically meaningful. For example, if one SELECT statement returns a customer ID and order amount, the other must also return a customer ID and an amount (e.g., return amount). The UNION operator automatically eliminates duplicate rows, ensuring that each row in the final result set is unique. This is often a desired outcome, but if you need to preserve all rows, use UNION ALL instead.

Why Union SQL is important

UNION is essential for combining data from different tables or views. It simplifies complex queries, allowing you to retrieve and analyze data from multiple sources in a single step, improving efficiency and reducing the need for multiple queries. This is crucial for data analysis and reporting.

Union SQL Example Usage


CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderAmount DECIMAL(10, 2)
);

CREATE TABLE Returns (
    ReturnID INT PRIMARY KEY,
    CustomerID INT,
    ReturnAmount DECIMAL(10, 2)
);

INSERT INTO Orders (OrderID, CustomerID, OrderAmount) VALUES
(1, 101, 100.00),
(2, 102, 50.00);

INSERT INTO Returns (ReturnID, CustomerID, ReturnAmount) VALUES
(1, 101, 25.00),
(2, 101, 15.00);

SELECT CustomerID, OrderAmount
FROM Orders
WHERE CustomerID = 101
UNION
SELECT CustomerID, ReturnAmount
FROM Returns
WHERE CustomerID = 101;

Union SQL Syntax

Common Mistakes

Forgetting to ensure the data types and order of columns in the SELECT statements are compatible.
Using UNION when UNION ALL is appropriate to avoid unintended removal of duplicate rows.
Incorrectly using the ORDER BY clause; it applies to the entire result set, not to individual SELECT statements.

Frequently Asked Questions (FAQs)

When should I use UNION versus UNION ALL in SQL?

Use UNION when you want a combined result set that automatically removes duplicate rows. If you need every row preserved—including duplicates—opt for UNION ALL. The blog post highlights that UNION eliminates duplicates by default, which is convenient for clean, deduplicated reports but can hide legitimate repeat events such as multiple identical returns.

Do the SELECT statements in a UNION need identical columns and data types?

Yes. Each SELECT clause in a UNION must return the same number of columns in the same order, and those columns need compatible data types. For example, if the first SELECT outputs customer_id (integer) and amount (numeric), the second must also output an integer followed by a numeric value. This requirement ensures the merged rows remain semantically meaningful and prevents runtime errors.

How can Galaxy’s AI copilot accelerate writing UNION queries?

Galaxy’s context-aware AI copilot can autocomplete compatible column lists, flag data-type mismatches, and even suggest converting UNION to UNION ALL when it detects you might want to keep duplicates. This saves developers time, reduces syntax errors, and lets teams quickly share endorsed UNION queries inside Galaxy Collections instead of pasting SQL in Slack.