How to Use UNION with SQL?

Welcome back to our SQL tutorial series! In this article, we’re going to explore the UNION operator, which is used to combine the results of two or more SELECT statements into a single result set. Understanding how to use UNION is essential for working with multiple queries and combining their results effectively.

Understanding the UNION Operator

The UNION operator is used to combine the result sets of two or more SELECT statements. By default, UNION removes duplicate rows from the combined result set. If you want to include duplicates, you can use UNION ALL.

Basic Syntax of UNION

The basic syntax of the UNION operator is as follows:

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

Basic Syntax of UNION ALL

The basic syntax of the UNION ALL operator is as follows:

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

Let’s look at some practical examples to understand how to use the UNION and UNION ALL operators effectively.

Example Tables: employees and contractors

We’ll use the following employees and contractors tables for our examples:

employees

contractors

Combining Results with UNION

To combine the names and positions of employees and contractors into a single result set, you use:

SELECT name, position FROM employees
UNION
SELECT name, position FROM contractors;

This command returns:

Combining Results with UNION ALL

If you want to include duplicates in the combined result set, you use UNION ALL:

SELECT name, position FROM employees
UNION ALL
SELECT name, position FROM contractors;

This command returns:

Notice that Sarah Wilson appears twice because UNION ALL includes duplicates.

Using UNION with Additional Columns

To combine the names and positions from both tables, along with a column indicating whether they are an employee or a contractor, you use:

SELECT name, position, 'Employee' AS type FROM employees
UNION
SELECT name, position, 'Contractor' AS type FROM contractors;

This command returns:

Using UNION with WHERE Clause

You can combine UNION with the WHERE clause to filter results. For example, to combine the names and positions of employees with a salary greater than $70,000 and contractors with an hourly rate greater than $70:

SELECT name, position FROM employees WHERE salary > 70000
UNION
SELECT name, position FROM contractors WHERE hourly_rate > 70;

This command returns:

Using UNION with ORDER BY

You can also use the ORDER BY clause to sort the combined result set. For example, to combine and sort the names and positions of employees and contractors alphabetically by name:

SELECT name, position FROM employees
UNION
SELECT name, position FROM contractors
ORDER BY name;

This command returns:

Conclusion

The UNION and UNION ALL operators are powerful tools for combining the results of multiple SELECT statements into a single result set. Whether you need to remove duplicates or include all results, understanding how to use these operators effectively is essential for working with multiple queries and analyzing data across multiple tables.

In our next tutorial, we’ll explore more advanced SQL topics and commands. Stay tuned and happy coding!