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.
Sign up for the latest in SQL knowledge from the Galaxy Team!
Description
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.
Example Usage
```sql
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;
```
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.
.avif){kind=logo}
