DbSchema Tutorial | SQL UNION OPERATOR

Publish on DbSchema Blog >>>

SQL (Structured Query Language) is the cornerstone of modern database operations. Among its numerous functionalities, the UNION operator stands out for its ability to combine the results of two or more SELECT statements. This guide delves deep into the intricacies of the SQL UNION operator, ensuring you not only grasp its fundamental concept but also its practical applications.

1. SQL UNION Operator

The SQL UNION operator is used to combine the result sets of two or more SELECT statements. It removes duplicate rows among the combined records.

Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Example:

Consider the following two tables, Students and Alumni.

Student_ID	Student_Name
1	Alice
2	Bob

Alumni_ID	Alumni_Name
1	Alice
3	Charlie

To combine the names from both tables:

SELECT Student_Name FROM Students
UNION
SELECT Alumni_Name FROM Alumni;

Result:

Name
Alice
Bob
Charlie

Explanation:

Note that Alice, although present in both tables, appears only once in the result due to the UNION’s default behavior of eliminating duplicates.

2. SQL UNION ALL Operator

UNION ALL is similar to UNION but it does not eliminate duplicate rows. It simply combines the results of multiple SELECT statements.

Syntax:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Example using the same tables:

SELECT Student_Name FROM Students
UNION ALL
SELECT Alumni_Name FROM Alumni;

Result:

Name
Alice
Bob
Alice
Charlie

Explanation:

Note the repetition of Alice in the results.

3. Difference between SQL UNION and SQL UNION ALL

While both UNION and UNION ALL are used to combine results from multiple SELECT statements, they differ in how they handle duplicates and performance.

Duplicates:

• UNION: It inherently removes duplicate rows from the combined result. This means that if a record appears in both tables, it will only appear once in the UNION result.
• UNION ALL: It allows duplicate rows. If a record appears in both tables, it will appear twice in the UNION ALL result.

Performance:

• UNION: Since UNION has to check for duplicates, it might be slower, especially when dealing with large datasets.
• UNION ALL: As it doesn’t perform a check for duplicates, it’s generally faster.

Practical Use Case:

Imagine you’re collecting feedback forms for a product from two different sources. Using UNION ensures each feedback appears only once, while UNION ALL would be useful if you wanted to see all feedbacks, even if some users submitted it multiple times.

4. Using UNION on a Single Field

Consider two tables: Current_Employees and Past_Employees.

Emp_ID	Emp_Name
1	John
2	Clara

PastEmp_ID	PastEmp_Name
1	John
3	Mike

To combine names from both tables:

SELECT Emp_Name FROM Current_Employees
UNION
SELECT PastEmp_Name FROM Past_Employees;

Result:

Name
John
Clara
Mike

Explanation

Note that John is listed once even though he appears in both tables.

5. Using UNION on Multiple Fields

To union multiple fields, ensure that:

1. The number and order of columns must be the same in both SELECT statements.
2. The data types must be compatible.

Example:

Students table:

Student_Name	Age
John	20
Alice	21
Bob	19

Alumni table:

Alumni_Name	Age
Jane	25
Steve	27
Alice	21

Considering we add an age column to both tables:

SELECT Student_Name, Age FROM Students
UNION
SELECT Alumni_Name, Age FROM Alumni;

The result should be a combination of both tables, but without any duplicate rows (because UNION removes duplicates).

Result:

Name	Age
John	20
Alice	21
Bob	19
Jane	25
Steve	27

Explanation:

The result set combines both the Students and Alumni tables. Since Alice appears in both tables with the same age (21), she appears only once in the result. This is due to the nature of the UNION operator, which eliminates duplicate rows. If we were to use UNION ALL, then duplicates would be retained.

6. Advanced UNION techniques

Using the WHERE Clause With the UNION Operator:

Let’s fetch names of employees who have an ID greater than 1 from both tables.

SELECT Emp_Name FROM Current_Employees WHERE Emp_ID > 1
UNION
SELECT PastEmp_Name FROM Past_Employees WHERE PastEmp_ID > 1;

Result:

Name
Clara
Mike

Explanation

This fetches Clara from Current_Employees and Mike from Past_Employees as both have IDs greater than 1.

Using UNION With Aliases:

Aliases can be used to rename columns in the final result set, making the results more readable.

SELECT Emp_Name AS Name FROM Current_Employees
UNION
SELECT PastEmp_Name AS Name FROM Past_Employees;

Result:

Name
John
Clara
Mike

Explanation

The use of aliases here doesn’t change the result but ensures that the column name in the output is “Name”.

UNION with JOINS:

Assuming there’s a third table, Departments, linking employees with their respective departments.

Dept_ID	Dept_Name
1	HR
2	Finance

Now, let’s UNION results from joined tables.

SELECT C.Emp_Name, D.Dept_Name 
FROM Current_Employees C 
JOIN Departments D ON C.Dept_ID = D.Dept_ID
UNION
SELECT P.PastEmp_Name, D.Dept_Name 
FROM Past_Employees P 
JOIN Departments D ON P.Dept_ID = D.Dept_ID;

Result:

Emp_Name	Dept_Name
John	HR
Clara	Finance
Mike	HR

Explanation

The result combines current and past employees with their respective departments.

7. Common Mistakes and FAQs

• Order and number of columns: Ensure that the SELECT statements you’re trying to UNION have the same order and number of columns.
• Data types: Ensure that the data types are compatible between SELECT statements.

FAQs

1. Can I order the final UNION result?
   Yes, simply append the ORDER BY clause at the end of the entire query.

2. Can I use aggregate functions with UNION?
   Yes, but ensure that each SELECT statement has the same number and order of aggregate functions.

8. Practice Questions

1. Combine names of students and alumni who are above 25 years.
2. Fetch distinct names from both students and alumni tables without using UNION. How would you do it?
3. How would you combine results of three tables using UNION?

9.Conclusion

By understanding and mastering the UNION operator, you can efficiently combine and manipulate data from multiple tables. As always, practice is key. Use the sample tables and examples provided here as a foundation and build upon it.

Happy querying!