Back to list Standard SQL How to Combine the Results of Two Queries in SQL Database: SQL MySQL PostgreSQL SQL Server Oracle SQLite Operators:UNION ALL, UNION Problem: You’d like to display data from given columns (of a similar data type) from two tables in SQL. Example: There are two tables in our database: employee and customer. The employee table contains data in the following columns: id, first_name, last_name, and age. idfirst_namelast_nameage 1TomMiller22 2JohnSmith26 3LisaWilliams30 4CharlesDavis21 5JamesMoore22 The customer table contains data in the following columns: id, first_name, last_name, and age. idfirst_namelast_nameage 1MilanSmith45 2CharlesDavis21 3MarkBacker19 In one result set, let’s display the first name, last name, and age for all people in the database, both employees and customers. Solution 1: We’ll use UNION ALL to join data from columns in two tables. Here’s the query you’d write: SELECT first_name, last_name, age FROM employee UNION ALL SELECT first_name, last_name, age FROM customer; Here’s the result: first_namelast_nameage TomMiller22 JohnSmith26 LisaWilliams30 CharlesDavis21 JamesMoore28 MilanSmith45 CharlesDavis21 MarkBacker19 Discussion: Use the UNION ALL clause to join data from columns in two or more tables. In our example, we join data from the employee and customer tables. On the left of the UNION ALL keyword, put the first SELECT statement to get data from the first table (in our example, the table employee). On the right, use another SELECT statement to get data from the second table (in our example, customer). Remember that the selected data in both tables must be of the same data type in each column. For example, if the first column in the first SELECT is a string data type, the first column in the second SELECT must also be a string data type. If the second column in the first SELECT statement is an integer, the second column in the second table must also be an integer type. In the first query, we selected age (the age of the employee, which is an integer data type) for the third column. Therefore, the third column in the second SELECT is also an integer value; it’s the age of the customer. The second columns in both SELECT statements are the same data type. However, if the values are the same in both tables, they will be displayed multiple times; for example, ‘Charles Davis 21’ is shown twice in the result set. What if don’t want multiple identical records in the result table? In this case, use UNION. It is similar to UNION ALL, but it removes duplicate records. Look at the following example. Solution 2: Here’s the query that avoids duplicate records: SELECT first_name, last_name FROM employee UNION SELECT first_name, last_name FROM customer; Here’s the result of the above query: first_namelast_name MarkBacker JamesMoore JohnSmith CharlesDavis MilanSmith TomMiller LisaWilliams Note: UNION ALL is faster than UNION, but UNION removes duplicate rows. The choice depends on the result data we need. Recommended courses: SQL Basics in SQL Server SQL Practice Set Standard SQL Functions Recommended articles: 5 SQL Functions for Manipulating Strings 18 Useful Important SQL Functions to Learn ASAP See also: How to Order By Two Columns in SQL? How to Order Alphabetically in SQL How to Order by Count in SQL? Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.