# UNION ALL in Oracle

ORACLE UNION ALL
To combine the output sets of two or more Oracle SELECT statements, the Oracle UNION ALL operator is used. During the combining process, the UNION ALL operator does not remove the duplicate rows between the SELECT statements’ results, but returns all of them and this feature makes it different from the Oracle UNION operator.

There are however two mandatory conditions for using the UNION ALL operator in Oracle.

• Each SELECT statement must have the same number of expressions.
• Each corresponding expression in the different SELECT statement should be of the same data type.

Syntax:

SELECT expr_1, expr_2, ... expr_n
FROM table1
WHERE conditions
UNION  ALL
SELECT expr_1, expr_2, ... expr_n
FROM table2
WHERE conditions;


Parameters:
expr_1, expr_2, … expr_n: It is used to specify the columns of the table which needs to be retrieved.
table1, table2: It is used to specify the name of the tables from which the records need to be retrieved.
conditions: It is used to specify the conditions to be strictly followed for selection.

Example 1:
Students Table:

 STUDENT_ID STUDENT_NAME STUDENT_AGE 1 Joy 10 2 Smiley 13 3 Happy 11 4 James 13 5 Bond 10

Teachers Table:

 TEACHER_ID TEACHER_NAME TEACHER_AGE 101 James 30 102 Bond 25 103 Smith 40

Query:

SELECT student_id as ID, student_age as AGE FROM students WHERE student_id &gt; 2 UNION ALL SELECT teacher_id as ID, teacher_age as AGE FROM teachers WHERE teacher_age &gt;= 30;

Output:

 ID AGE 3 11 4 13 5 10 101 30 103 40

Explanation:
The ‘students’ and the ‘teachers’ are already existing tables. After the union, the result of the UNION ALL operator will be same as that of UNION operator, since there are no duplicate rows between the selected corresponding fields.

Example 2:
Students Table:

 STUDENT_ID STUDENT_NAME AGE 1 Joy 10 2 Smiley 13 3 Happy 11 4 James 13 5 Bond 10

Teachers Table:

 TEACHER_ID TEACHER_NAME AGE 101 James 30 102 Bond 25 103 Smith 40

Query:

SELECT student_name as NAME, age FROM students UNION SELECT teacher_name as NAME, age FROM teachers WHERE age &gt;= 30 ORDER BY age;

Output:

 NAME AGE Joy 10 Bond 10 Happy 11 James 13 Smiley 13 Tom 30 James 40

Explanation:
The ‘students’ and the ‘teachers’ are already existing tables. After the union, a group of the combination of student_id and teacher_id would appear along with another group of the combination of student_age and teacher_age. The corresponding column of the two tables is of the same data type. During this process, the duplicate sets will not be removed from the corresponding columns’ result. Here, the Union of the student_age column will be done only for the rows of the table ‘teachers’ with age greater than 30, while there is no condition for the union of teacher_name and student_name. The result will be finally available in the sorted order in the ascending sequence of the ages.