dark

How to Use UNION in SQL with Examples

How to Use UNION in SQL with Examples

If you want to combine the result sets of two or more SELECT statements, you can use the UNION operator in SQL. The UNION operator removes duplicate rows and sorts the result set in ascending order by default. In this tutorial, you will learn how to use UNION in SQL with examples.

Syntax

The basic syntax of UNION is as follows:

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

The number of columns and their data types must be the same in all the SELECT statements. The column names in the first SELECT statement determine the column names in the result set.

Example

Suppose you have two tables named “employees” and “customers” with the following data:

employees
+----+--------+---------+
| id | name   | salary  |
+----+--------+---------+
| 1  | Alice  | 50000   |
| 2  | Bob    | 60000   |
| 3  | Charlie| 70000   |
+----+--------+---------+

customers
+----+--------+----------+
| id | name   | order_id |
+----+--------+----------+
| 1  | Dave   | 100      |
| 2  | Eve    | 200      |
| 3  | Frank  | 300      |
| 4  | Alice  | 400      |
+----+--------+----------+

To combine the result sets of two SELECT statements that retrieve the names of employees and customers, you can use UNION as follows:

SELECT name FROM employees
UNION
SELECT name FROM customers;

The result set would be:

+--------+
| name   |
+--------+
| Alice  |
| Bob    |
| Charlie|
| Dave   |
| Eve    |
| Frank  |
+--------+

Note that the UNION operator removed the duplicate name “Alice” from the result set.

UNION ALL

If you want to include duplicate rows in the result set, you can use the UNION ALL operator instead of UNION. The syntax is the same as UNION:

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

Example

Suppose you want to combine the result sets of two SELECT statements that retrieve the names and salaries of employees and customers. To include duplicate names in the result set, you can use UNION ALL as follows:

SELECT name, salary FROM employees
UNION ALL
SELECT name, order_id FROM customers;

The result set would be:

+--------+---------+
| name   | salary  |
+--------+---------+
| Alice  | 50000   |
| Bob    | 60000   |
| Charlie| 70000   |
| Dave   | 100     |
| Eve    | 200     |
| Frank  | 300     |
| Alice  | 400     |
+--------+---------+

Troubleshooting Tips

  • Make sure the number of columns and their data types are the same in all the SELECT statements.
  • Make sure the column names in the first SELECT statement are meaningful and unique.
  • If you use UNION ALL and want to sort the result set, you need to use ORDER BY explicitly.
  • If you use UNION and want to sort the result set in descending order instead of ascending order, you can use ORDER BY with DESC.

Conclusion

The UNION operator in SQL is a powerful tool for combining the result sets of two or more SELECT statements. With the knowledge you have gained in this tutorial, you should be able to use UNION with confidence and efficiency in your SQL queries.

Related tutorials