SQL UNION Operator - Complete Beginner Tutorial
Quick Answer
The SQL UNION operator combines the results of two or more SELECT queries into a single result set, removing duplicate rows. It requires the queries to have the same number of columns with compatible data types. UNION is useful for merging data from similar tables or queries.
Learning Objectives
- Understand the purpose and syntax of the SQL UNION operator.
- Learn how UNION removes duplicate rows from combined query results.
- Differentiate between UNION and UNION ALL operators.
Introduction
SQL set operators allow you to combine results from multiple queries into a single result set.
The UNION operator is one of the most commonly used set operators in SQL.
It merges the results of two or more SELECT statements and removes duplicate rows.
This tutorial will guide you through the syntax, usage, and examples of the UNION operator.
Combine data efficiently with SQL UNION.
What is the SQL UNION Operator?
The UNION operator combines the results of two or more SELECT queries into a single result set.
It automatically removes duplicate rows, ensuring each row in the result is unique.
UNION requires that all SELECT statements have the same number of columns and compatible data types in corresponding positions.
- Combines multiple SELECT query results.
- Removes duplicate rows by default.
- Requires matching column count and compatible data types.
- Returns a distinct set of rows.
SQL UNION Syntax
The basic syntax of the UNION operator is straightforward.
You write multiple SELECT statements separated by the UNION keyword.
| Syntax Element | Description |
|---|---|
| SELECT column_list FROM table1 | First query to select columns. |
| UNION | Combines results and removes duplicates. |
| SELECT column_list FROM table2 | Second query with matching columns. |
Example of SQL UNION
Here is a simple example combining two SELECT queries using UNION.
Example Query
Suppose you have two tables, Employees_US and Employees_UK, each with columns EmployeeID and Name.
SQL Code
The following query combines employee names from both tables without duplicates:
Difference Between UNION and UNION ALL
While UNION removes duplicate rows, UNION ALL includes all rows from the combined queries, including duplicates.
UNION ALL is faster because it does not perform the duplicate elimination step.
Use UNION when you want unique rows, and UNION ALL when duplicates are acceptable or desired.
- UNION removes duplicates; UNION ALL does not.
- UNION ALL performs better in large datasets.
- Choose based on whether duplicates matter in your result.
Common Errors and How to Fix Them
Errors often occur when the SELECT statements in a UNION have different numbers of columns or incompatible data types.
Ensure all SELECT queries have the same number of columns and matching data types in the same order.
Another common mistake is forgetting that UNION removes duplicates, which can affect expected results.
- Mismatch in column count causes syntax errors.
- Incompatible data types cause conversion errors.
- Unexpected removal of duplicates can confuse results.
Practical Example
This query combines employee records from two tables, removing any duplicate entries.
Examples
SELECT EmployeeID, Name FROM Employees_US
UNION
SELECT EmployeeID, Name FROM Employees_UK;This query combines employee records from two tables, removing any duplicate entries.
Best Practices
- Always ensure SELECT statements have the same number of columns and compatible data types.
- Use UNION when you need distinct results; use UNION ALL for performance when duplicates are acceptable.
- Order the columns consistently across all SELECT statements.
- Test queries individually before combining with UNION.
- Use ORDER BY at the end of the last SELECT to sort the combined results.
Common Mistakes
- Using different numbers of columns in SELECT statements.
- Mixing incompatible data types across SELECT statements.
- Expecting UNION to preserve duplicates (it removes them).
- Placing ORDER BY before UNION instead of after the last SELECT.
- Not aliasing columns consistently when necessary.
Hands-on Exercise
Combine Customer Lists
Write a SQL query using UNION to combine customer names from two tables: Customers_2023 and Customers_2024, ensuring no duplicates.
Expected output: A combined list of unique customer names from both tables.
Hint: Make sure both SELECT statements have the same columns and data types.
Compare UNION and UNION ALL Results
Write two queries: one using UNION and one using UNION ALL on the same tables. Observe and explain the difference in results.
Expected output: Understanding of how duplicates are handled by UNION vs UNION ALL.
Hint: Look for duplicate rows in the UNION ALL result.
Interview Questions
What is the difference between UNION and UNION ALL in SQL?
InterviewUNION removes duplicate rows from the combined result set, while UNION ALL includes all rows, including duplicates. UNION ALL is generally faster because it skips the duplicate elimination step.
Can you use ORDER BY with UNION?
InterviewYes, but ORDER BY should be applied only once at the end of the last SELECT statement to sort the entire combined result set.
What requirements must SELECT statements meet to be combined with UNION?
InterviewAll SELECT statements must have the same number of columns, and corresponding columns must have compatible data types.
MCQ Quiz
1. What is the best first step when learning UNION?
A. Understand the purpose and basic idea
B. Skip directly to advanced implementation
C. Ignore examples and practice
D. Memorize terms without context
Correct answer: A
Starting with the purpose and basic idea makes later examples and practice easier to understand.
2. Which activity helps reinforce UNION?
A. Reading once without practice
B. Building or writing a small practical example
C. Avoiding review questions
D. Skipping the summary
Correct answer: B
A small practical example helps connect the topic to real usage.
3. Which statement is most accurate about this topic?
A. The SQL UNION operator combines the results of two or more SELECT queries into a single result set, removing duplicate rows.
B. UNION never needs examples
C. UNION is unrelated to practical work
D. UNION should be learned without checking results
Correct answer: A
The correct option is based on the available topic explanation.
Key Takeaways
- UNION combines results from multiple SELECT queries into one result set.
- It removes duplicate rows by default, unlike UNION ALL.
- All SELECT statements in UNION must have the same number of columns and compatible data types.
- UNION is useful for consolidating data from similar tables or queries.
- Understanding UNION helps write more efficient and accurate SQL queries.
Summary
The SQL UNION operator is a powerful tool to combine results from multiple SELECT queries into a single distinct result set.
It requires matching column counts and compatible data types across queries.
UNION removes duplicates by default, unlike UNION ALL which includes all rows.
Proper use of UNION helps in data consolidation and reporting tasks.
Understanding its syntax and behavior is essential for writing effective SQL queries.
Frequently Asked Questions
Can UNION combine SELECT statements with different column names?
Yes, UNION combines results based on column positions, not names. However, the number and data types of columns must match.
Does UNION sort the result set?
No, UNION does not sort results by default. Use ORDER BY after the last SELECT statement to sort the combined results.
What happens if the data types differ between SELECT statements in UNION?
SQL will raise an error if data types are incompatible. You may need to cast or convert columns to compatible types.
