CodesJava

Easy learning with example program codes

UNION in Oracle

ORACLE UNION
To combine the output sets of two or more Oracle SELECT statements, the Oracle UNION operator is used. During the combining process, the UNION operator removes the duplicate rows between the SELECT statements’ results.

There are however two mandatory conditions for using the UNION 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  
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: Fetching single field from two tables.
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	        Tom	        30
102	        Jerry	        25
103	        Brutus	        40

Query:

SELECT student_id  
FROM students
UNION  
SELECT teacher_id  
FROM teachers;

Output:

STUDENT_ID
1
2
3
4
5
101
102
103

Explanation:
The ‘students’ and the ‘teachers’ are already existing tables. After the UNION, a combination of student_id and teacher_id would appear, both being the columns of the same data type but of different tables. During this process, the duplicate sets will be removed.

Example 2: Fetching multiple fields from two tables.
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	        Tom	        30
102	        Jerry	        25
103	        Brutus	        40

Query:

SELECT student_id, student_age
FROM students
UNION  
SELECT teacher_id, teacher_age
FROM teachers;

Output:

STUDENT_ID	STUDENT_AGE
1	        10
2	        13
3	        11
4	        13
5	        10
101	        30
102	        25
103	        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 be removed from the corresponding columns’ result.

Example 3: Fetching multiple fields from two tables with conditions.
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	        Tom	        30
102	        Jerry	        25
103	        James	        40

Query:

SELECT student_id, student_age
FROM students
WHERE student_id > 2  
UNION  
SELECT teacher_id, teacher_age
FROM teachers
WHERE teacher_age >= 30;

Output:

STUDENT_ID	STUDENT_AGE
3	        11
4	        13
5	        10
101	        30
103	        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 be removed from the corresponding columns’ result. Here, the Union of the teacher_id column will be done only for the rows of the table ‘students’ with student_id greater than 2 and the Union of the student_age column will be done only for the rows of the table ‘teachers’ with teacher_age greater than 30.

Example 4: Fetching multiple fields from two tables with conditions and using ORDER BY clause.
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	        Tom	        30
102	        Jerry	        25
103	        James	        40

Query:

SELECT student_name, age
FROM students 
UNION  
SELECT teacher_name, age
FROM teachers
WHERE age >= 30 
ORDER BY age;

Output:

STUDENT_NAME	AGE
Joy	        10
Bond	        10
Happy	        11
James	        13
Smiley	        13
Tom	        30

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 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.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status