Summary -

In this topic, we described about the UNION ALL Operator with detailed example.

UNION ALL operator is used to combine two or more SELECT statements of different tables to get all rows in result set like duplicate rows also from all tables. In UNION ALL operator also the number of columns and DATA TYPE of all columns should be same. By using UNION ALL operator, we get first SELECT statement column names in result set.

Syntax -

SELECT Expression1 [AS Alias_name], Expressio2,…
 FROM table1 [WHERE condition]  
UNION ALL
SELECT Expression1, Expressio2,… FROM table2 [WHERE condition];

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000
010 Employee6 Analyst Employee5 2019-07-11 27000.00 1000
011 Employee8 Salesman Employee5 2019-09-09 10000.00 1000

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2
3000 Office Location1

Scenario – Getting all rows of multiple columns from existing two tables by using UNION ALL.

Requirement - Getting all rows of multiple columns from existing two employee_details and department_details tables by using UNION ALL. The query was as follows – 

SELECT emp_name, designation FROM employee_details
UNION ALL
SELECT dept_name, dept_location FROM department_details;

By executing above query we can get all rows of emp_name, designation, dept_name, dept_location columns from both employee_details and department_details tables by using UNION ALL operator. The output was as follows –

emp_name designation
Employee1 Director
Employee2 Director
Employee3 Manager
Employee4 Manager
Employee5 Analyst
Employee6 Analyst
Employee7 Clerk
Employee8 Salesman
Employee9 Salesman
Employee6 Analyst
Employee8 Salesman
Office Location1
Sales Location2
Office Location1

Scenario – Getting single column with alias name by using UNION ALL from existing tables.

Requirement – Getting single column values with alias name from both employee_details and department_details tables by using UNION ALL operator. The query was as follows – 

SELECT emp_name AS NAME FROM employee_details
UNION ALL
SELECT dept_name FROM department_details;

By executing above query we can get single column values like emp_name, dept_name from both employee_details and department_details tables by using UNION ALL operator. The output was as follows –

NAME
Employee1
Employee2
Employee3
Employee4
Employee5
Employee6
Employee7
Employee8
Employee9
Employee6
Employee8
Office
Sales
Office

Scenario – Getting all rows from existing tables by using UNION ALL operator with WHERE condition.

Requirement - Getting all rows from existing employee_details and department_details tables by using UNION ALL operator with WHERE condition. The query was as follows -

SELECT emp_name,designation FROM employee_details WHERE dept_id = 1000
 UNION ALL
SELECT dept_name,dept_location FROM department_details WHERE dept_id = 1000;

By executing above query we can get all rows from both employee_details and department_details tables by using UNION ALL operator whose dept_id is 100.The output was as follows -

emp_name designation
Employee1 Director
Employee3 Manager
Employee5 Analyst
Employee6 Analyst
Employee7 Clerk
Employee6 Analyst
Employee8 Salesman
Office Location1