SQL UNION
In SQL, the UNION operator is used to combine the results of two or more SELECT statements into a single result set. It is a powerful feature when you need to query data from multiple tables or retrieve distinct sets of records from different queries. Each SELECT statement within a UNION must have the same number of columns, and the corresponding columns must have compatible data types.
In this chapter, we will cover SQL UNION in depth, starting from the basic syntax, advancing to more complex use cases, and including examples with outputs to ensure a complete understanding. By the end, you will be able to confidently use UNION to combine multiple result sets effectively.
What is SQL UNION?
The UNION operator allows you to combine the result sets of two or more SELECT queries into a single result. It eliminates duplicate rows between the combined queries and ensures that each row in the result is unique unless you specifically allow duplicates (using the UNION ALL operator).
Basic Rules for Using UNION:
- Same number of columns: All
SELECTstatements must return the same number of columns. - Data types: The data types of each column in the result sets must be compatible.
- Order: You can apply
ORDER BYonly once at the end of the final query.
Syntax:
SELECT column_list FROM table1
UNION
SELECT column_list FROM table2;
SQL UNION vs. UNION ALL
Before diving deeper, it’s essential to understand the difference between UNION and UNION ALL:
- UNION: Removes duplicate rows and returns only unique records from all result sets.
- UNION ALL: Includes all rows from each result set, including duplicates.
Example 1: UNION vs. UNION ALL
Assume we have two tables, employees_us and employees_uk, containing employees from the US and the UK, respectively.
Table 1: employees_us
| employee_id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | David |
Table 2: employees_uk
| employee_id | name |
|---|---|
| 1 | Alice |
| 4 | John |
| 5 | Chris |
Query using UNION:
SELECT name FROM employees_us
UNION
SELECT name FROM employees_uk;
Output:
| name |
|---|
| Alice |
| Bob |
| David |
| John |
| Chris |
Explanation:
- The result set contains unique records from both tables. The name “Alice” appears only once even though she exists in both tables.
Query using UNION ALL:
SELECT name FROM employees_us
UNION ALL
SELECT name FROM employees_uk;
Output:
| name |
|---|
| Alice |
| Bob |
| David |
| Alice |
| John |
| Chris |
Explanation:
- The
UNION ALLresult set includes all rows, even duplicates like “Alice.”
SQL UNION: Matching Columns and Data Types
The UNION operator requires that both queries have the same number of columns and that these columns have compatible data types. Let’s consider an example where the data types must match:
Example 2: Matching Columns and Data Types
Suppose we have two tables, students and teachers, with different information.
Table 1: students
| student_id | name | grade |
|---|---|---|
| 1 | Mark | A |
| 2 | Sarah | B |
| 3 | John | A |
Table 2: teachers
| teacher_id | name | subject |
|---|---|---|
| 101 | Jane | Math |
| 102 | Paul | History |
| 103 | Adam | Physics |
You cannot directly combine these tables using UNION because the columns do not match in number or type.
Invalid Query:
SELECT student_id, name, grade FROM students
UNION
SELECT teacher_id, name, subject FROM teachers;
This will result in an error because grade and subject are not of the same data type, and there’s a mismatch in column count. To fix this, you could either adjust the number of columns or explicitly cast the data types.
Advanced Example: UNION with WHERE Clauses
You can also use the UNION operator with conditions (WHERE clauses) to combine filtered data from different queries.
Example 3: Filtering Results with WHERE Clauses
Let’s return to our employees_us and employees_uk tables. Now, we want to find employees whose names start with the letter “A” from both tables.
SELECT name FROM employees_us
WHERE name LIKE 'A%'
UNION
SELECT name FROM employees_uk
WHERE name LIKE 'A%';
Output:
| name |
|---|
| Alice |
Explanation:
- The query returns only employees whose names start with “A” from both tables. In this case, “Alice” appears only once even though she exists in both tables.
Sorting Results with UNION
When using UNION, you can apply ORDER BY to sort the final result set. However, ORDER BY can only be used at the end of the entire query, not after individual SELECT statements.
Example 4: Using ORDER BY with UNION
SELECT name FROM employees_us
UNION
SELECT name FROM employees_uk
ORDER BY name ASC;
Output:
| name |
|---|
| Alice |
| Bob |
| Chris |
| David |
| John |
Explanation:
- The
ORDER BYclause is applied to the final combined result, sorting all the names alphabetically.
UNION with Different Tables
A common use case for UNION is when you need to combine results from different tables that share some common structure, even though they store data in different contexts.
Example 5: Combining Employee and Customer Data
Suppose you have two tables, employees and customers, and you want to combine their names into a single result set.
Table 1: employees
| employee_id | name | department |
|---|---|---|
| 1 | Alice | Sales |
| 2 | Bob | HR |
Table 2: customers
| customer_id | name | country |
|---|---|---|
| 1 | John | USA |
| 2 | Chris | UK |
Query:
SELECT name FROM employees
UNION
SELECT name FROM customers;
Output:
| name |
|---|
| Alice |
| Bob |
| John |
| Chris |
Explanation:
- The query returns all the unique names from both the
employeesandcustomerstables.
UNION with Aggregation
You can also combine the results of aggregated queries using UNION. For example, you may want to calculate totals or averages for different groups and combine those results.
Example 6: UNION with Aggregates
Let’s assume we want to get the total number of employees and the total number of customers.
SELECT COUNT(*) AS total FROM employees
UNION
SELECT COUNT(*) AS total FROM customers;
Output:
| total |
|---|
| 2 |
| 2 |
Explanation:
- The query returns two rows: one showing the count of employees, and the other showing the count of customers.
The SQL UNION operator is a powerful feature that allows you to combine the results of multiple SELECT queries into a single result set. It is especially useful for querying different tables with similar structures or combining filtered results from the same table. Understanding the differences between UNION and UNION ALL, ensuring matching columns and data types, and utilizing filtering and sorting options makes the UNION operator a versatile tool in SQL.Happy coding !❤️