SQL UNION

In this tutorial, we will learn how to use UNION or UNION ALL opertator and combine the result set of two or more SELECT statement into a single result set using SQL.

SQL UNION Operator

• SQL UNION Clause/Operator is used to combine the result set of two or more SELECT statements and only returned unique records/rows.
• For using UNION Clause/Operator, there are some basic rules as follow:
• Each SELECT statement/queries within UNION Clause must have the same number of columns.
• Columns must also have similar datatypes.
• The order of the columns must be the same in all SELECT statements/queries.

SELECT column1,column2,...columnN FROM tableName1
UNION
SELECT column1,column2,...columnN FROM tableName2;

SQL UNION ALL

• SQL UNION ALL Clause/Operator is used to combine the result set of two or more SELECT statements and returned all the duplicate records/rows also.
• The same rules that apply to the UNION clause/operator will apply to the UNION ALL clause/operator.

SELECT column1,column2,...columnN FROM tableName1
UNION ALL
SELECT column1,column2,...columnN FROM tableName2;

UNION vs UNION ALL

UNION AND UNION ALL Both used to combine the result-set of two or more SELECT statements. The Only difference between both UNION and UNION ALL is that UNION  returns distinct records while UNION ALL returns all the records.

Example:

Let us consider these two tables "Customer" and "Suppliers" for records.

Table Name: Customer

Table Name: Suppliers

Example of UNION Clause/Operator

Select the records(ID, FirstName, City) from both the tables (Customer and Suppliers) using UNION clause,  a query will be:-

SELECT ID,FirstName,City FROM Customer
UNION
select ID,FirstName,City FROM Suppliers;

Example of UNION ALL Clause/Operator

Select the records(ID, FirstName, City) from both the tables (Customer and Suppliers) using UNION ALL clause,  a query will be:

SELECT ID,FirstName,City FROM Customer
UNION ALL
select ID,FirstName,City FROM Suppliers;