SQL UNION Operator

Welcome to The Coding College, your go-to platform for learning programming concepts. In this guide, we’ll cover the SQL UNION Operator, a fundamental SQL tool for merging data from multiple queries.

What Is SQL UNION Operator?

The SQL UNION Operator is used to combine the result sets of two or more SELECT statements into a single result set. It eliminates duplicate rows by default, unless specified otherwise using the UNION ALL variant.

Syntax of SQL UNION

SELECT column1, column2, ...  
FROM table1  
UNION  
SELECT column1, column2, ...  
FROM table2;  

Key Points

1. Number of Columns: The number of columns in each SELECT statement must match.
2. Data Types: The data types of corresponding columns in each query must be compatible.
3. Order of Data: Use ORDER BY at the end of the last SELECT statement to sort the combined result.

Example Tables

Table: employees_usa

Table: employees_uk

Example: Using UNION

Query

SELECT name, location  
FROM employees_usa  
UNION  
SELECT name, location  
FROM employees_uk;  

Result

UNION vs. UNION ALL

Using UNION ALL

If you want to include duplicate rows in the result set, use UNION ALL instead of UNION.

SELECT name, location  
FROM employees_usa  
UNION ALL  
SELECT name, location  
FROM employees_uk;  

Key Differences

Practical Use Cases

1. Merging Regional Data: Combine employee records from different regions or branches.
2. Data Warehousing: Aggregate data from multiple sources into a single report.
3. Historical vs. Current Data: Merge historical and current datasets for analysis.

Using UNION with Filters

You can apply WHERE clauses in individual queries before combining them.

Example: Filter Data by Location

SELECT name, location  
FROM employees_usa  
WHERE location = 'New York'  
UNION  
SELECT name, location  
FROM employees_uk  
WHERE location = 'London';  

Result

Using ORDER BY with UNION

To sort the combined result, use ORDER BY after the final SELECT statement.

SELECT name, location  
FROM employees_usa  
UNION  
SELECT name, location  
FROM employees_uk  
ORDER BY location;  

Result

Combining More Than Two Tables

You can use UNION to combine results from more than two tables.

Example

SELECT name, location  
FROM employees_usa  
UNION  
SELECT name, location  
FROM employees_uk  
UNION  
SELECT name, location  
FROM employees_canada;  

Best Practices

1. Check Compatibility: Ensure that the columns and data types in the queries match.
2. Optimize Performance: Use UNION ALL if duplicates don’t matter to avoid the overhead of duplicate elimination.
3. Use Column Aliases: Assign meaningful aliases for clarity in the result set.

SELECT name AS employee_name, location AS office_location  
FROM employees_usa  
UNION  
SELECT name AS employee_name, location AS office_location  
FROM employees_uk;  

Common Errors

• Column Count Mismatch:

SELECT name, location  
FROM employees_usa  
UNION  
SELECT name  
FROM employees_uk;  

• This will throw an error because the number of columns doesn’t match.
• Data Type Mismatch: Ensure that corresponding columns have compatible data types.

Real-World Application

Merging Sales Data from Multiple Regions

SELECT sales_amount  
FROM usa_sales  
UNION  
SELECT sales_amount  
FROM uk_sales;  

Result

Conclusion

The SQL UNION Operator is a powerful tool for combining data from multiple queries into a single, unified dataset. By understanding its syntax, use cases, and best practices, you can effectively manage and analyze data from diverse sources.

Visit The Coding College for more SQL tutorials and programming insights to take your skills to the next level!