How to Use UNION ALL in SQL

How to Use UNION ALL in SQL

If you need to combine data from multiple tables in SQL, you can use the UNION ALL keyword. This allows you to merge data from two or more tables and present it as a single result set. In this tutorial, you’ll learn how to use UNION ALL in SQL.


Before you begin, you should have a basic understanding of SQL and have access to a SQL database server. You should also have some knowledge of the tables you want to combine and the columns you want to retrieve.


The syntax for using UNION ALL in SQL is:

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

The SELECT statement retrieves the columns you want to display from each table. The keyword UNION ALL combines the results of multiple SELECT statements into a single result set.


Let’s say you have two tables, employees and customers, and you want to combine the data from both tables into a single result set. Here’s an example SQL query:

SELECT first_name, last_name, 'employee' AS source FROM employees
SELECT first_name, last_name, 'customer' AS source FROM customers

This SQL query will retrieve the first and last names of all employees and customers, along with a column indicating the source of the data (’employee’ for the employees table and ‘customer’ for the customers table). The UNION ALL keyword combines the two result sets into a single result set.


The output of the above query might look something like this:

first_name | last_name | source
John       | Smith     | employee
Jane       | Doe       | employee
Bob        | Johnson   | employee
Alice      | Smith     | customer
Chris      | Brown     | customer

Troubleshooting Tips

If you receive an error message when using UNION ALL in SQL, check the following:

  • Make sure the number and data type of columns selected in each SELECT statement match up
  • Ensure that the column alias (if used) is the same for each table
  • Verify that the tables you are selecting from exist and are spelled correctly


Using UNION ALL in SQL is a simple way to merge data from multiple tables into a single result set. By understanding the syntax and following best practices, you can use UNION ALL to create complex queries and gain valuable insights from your data.