DbSchema Database Designer

DbSchema Tutorial | SQL UNION OPERATOR



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.

SQL UNION alt >

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.

SQL UNION ALL alt >

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!

DbSchema → Your Trusted Partner in Database Design

Simplify complex database workflows and improve productivity with DbSchema's advanced design and management tools

Visual Design & Modeling
Visual Design & Schema Layout

➤ Create and manage your database schema visually through a user-friendly graphical interface.

➤ Easily arrange tables, columns, and foreign keys to simplify complex database structures, ensuring clarity and accessibility.

GIT & Collaboration
Version Control & Collaboration

➤ Manage schema changes through version control with built-in Git integration, ensuring every update is tracked and backed up.

➤ Collaborate efficiently with your team to maintain data integrity and streamline your workflow for accurate, consistent results.

Data Explorer & Query Builder
Relational Data & Query Builder

➤ Seamlessly navigate and visually explore your database, inspecting tables and their relationships.

➤ Build complex SQL queries using an intuitive drag-and-drop interface, providing instant results for quick, actionable insights.

Interactive Documentation & Reporting
HTML5 Documentation & Reporting

➤ Generate HTML5 documentation that provides an interactive view of your database schema.

➤ Include comments for columns, use tags for better organization, and create visually reports.