Text copied!
UNION ALL clause
SQL clause is the specific part of a SQL statement which is used to perform various operations. It can be combined to create more complex queries to retrieve and manipulate data.
UNION ALL clause :
UNION ALL clause is used to combine/merge the result of multiple SELECT statements into a single result by keeping duplicate rows.
While using UNION ALL clause, ensure that column_name(s) and their data types should be in the same order across all SELECT statements.
The syntax of the UNION ALL clause generally looks like this :
SELECT column_name(s)
FROM table1
UNION ALL
SELECT column_name(s)
FROM table2;
• Specify the column(s) name after the 'SELECT' keyword.
• Specify Asterisk (*) symbol to selects all columns from the table after the 'SELECT' keyword.
• Specify the table name after the 'FROM' keyword.
• 'table1' and 'table2' refer to the two tables being merged together.
• 'UNION ALL' keyword combines the result set of multiple SELECT statements into a single result by keeping duplicate rows.
Here's an example of how you might use the UNION ALL clause :
Let's suppose we have two tables :
[a] Employees Table :
→ It contains columns [Employee_Id], [Employee_Name], [Gender] and [Salary].
[b] Employees_2 Table :
→ It contains all columns same as '[Employees] table' with same data types and order. Except '[Age]' column.
1. Please ensure that each column_name(s) and their data types are in the same order across all SELECT statements.
For example, let's see what happens if we run the below statement :
SELECT *
FROM [Employees]
UNION ALL
SELECT *
FROM [Employees_2];
An error is thrown indicating that while performing 'UNION ALL' operation, number of columns must be the same in all SELECT statements.
The error occurred because we specified 'Asterisk' (*) symbol that selects all columns from the table where [Employees_2] table contains '[Age]' column which is absent in [Employees] table. Therefore, we need to specify column_name(s) explicitly in all SELECT statements.
2. Please run the following SQL statement :
SELECT Employee_Id, Employee_Name, Gender, Salary
FROM [Employees]
UNION ALL
SELECT Employee_Id, Employee_Name, Gender, Salary
FROM [Employees_2];
3. Above statement merges the result sets of both employee tables into a single result set for all selected columns. It doesn't eliminate duplicate rows. Hence all 7 rows are returned.
That's it! You have successfully merged data using 'UNION ALL' clause.