Updated March 30, 2023
Introduction to PL/SQL UNION
PL/SQL UNION operator is used in order to combine the result set of two or more SELECT statements and remove the duplicate records. UNION and UNION ALL operators work almost similarly in Oracle, the only difference being that UNION ALL displays all the duplicate records as well to the user. Therefore, the ‘UNION’ clause is applied between the 2 SELECT statements to see the query’s combined result set. One advantage of using the UNION operator is that, instead of showing unnecessary repeated records to the user, it shows only the relevant, unique data to the user.
Syntax of PL/SQL UNION
Given below is the syntax of using the UNION operator in PL/SQL code:
SELECT expr1, expr2, expr3, ... expr_n
FROM table1
[WHERE conditions]
UNION
SELECT expr1, expr2, expr3, ... expr_n
FROM table2
[WHERE conditions];Where,
- expr1, expr2, expr3, … expr_n: These are the columns that need to be retrieved from table1 and table 2 respectively.
- table1: It is the first table from which the data needs to be extracted and displayed using the SELECT statement.
- table2: It is the second table from which the data needs to be extracted and displayed using the SELECT statement.
- UNION: It is an operator that will be applied to the result of both the SELECT statements in order to combine their result by removing duplicates from the resultset.
- WHERE: It is an optional clause which is used to extract the records from the tables using some conditions on it.
How does UNION Operator Work in PL/SQL?
There are some important points mentioned below describing how the union operator works in PL/SQL:
1. In Oracle, the UNION operator is used to give the result set by combining the result of 2 or more SELECT statements.
2. The UNION operator combines the result set of the SELECT statement but removes the duplicate records from it.
3. In order to view the duplicate records, Oracle provides the UNION ALL operator, which works similar to the UNION operator, except it displays the duplicate records as well.
4. In order to apply the UNION operator in the database, the following conditions need to be met:
- The same number of columns should be retrieved from each SELECT statement which is to be combined using the UNION operator.
- Columns retrieved from each SELECT statement must be of the same data type.
- The UNION operator returns the unique fields or records from the resultset.
5. Combining the result set using the UNION operator is quite helpful, especially when merging the remote tables using the database links.
6. UNION operator works great when there are too many duplicate records returned by combining the result set, and the user wants to see only the relevant data on the screen instead of showing all the duplicate records and unnecessary creating chaos on the screen.
7. Though the UNION operator is quite fast and reliable, it is somewhat slower than UNION ALL, especially when too many records are in the resultset; time is wasted in sorting out the duplicate records before displaying them to the user.
8. ORDER BY clause can be used in the UNION resultset in order to perform the sorting of recording on the basis of certain criteria.
Examples of PL/SQL UNION
Given below are the examples of PL/SQL UNION:
Consider the two tables with the name ‘Teacher’ and ‘Student’ having the fields of their id’s and name’s and college_names respectively.
There can be some teachers who are studying as well. So their id must be present in both the TEACHER and STUDENT table.
Teacher:
| teach_id | teach_name |
college_name |
| T_001 | Arush | xyz |
| T_002 | Akash | xyz |
| T_003 | Amitansh | abc |
| T_004 | Gourang | bcd |
| T_005 | Manish | efg |
Student:
|
stud_id |
stud_name |
college_name |
| S_006 | Akansha | fgh |
| T_001 | Arush | xyz |
| T_002 | Akash | xyz |
| S_007 | Utkarsh | fgh |
| S_008 | Akshat | qwe |
Example #1
Code:
SELECT teach_id, teach_name FROM TEACHER UNION SELECT stud_id, stud_name FROM STUDENT ;Output:
Explanation:
- In the above example, teach_id and teach_name are retrieved from the table TEACHER, and stud_id and stud_name are retrieved from the table STUDENT.
- Only the two columns of both the tables, STUDENT and TEACHER, are used. The UNION operator is then applied to the extracted result of both SELECT statements, and the output is displayed to the user on the console by removing the duplicate records.
Example #2
Code:
SELECT teach_id, teach_name FROM TEACHER WHERE college_name = 'xyz' or college_name ='bcd' UNION SELECT stud_id, stud_name FROM STUDENT WHERE college_name = 'xyz' or college_name ='fgh' ;Output:
Explanation:
- In the above example, teach_id and teach_name are retrieved from the table TEACHER by applying the condition on the college name. Thus, only those teach_id and teach_name would be retrieved whose college name is either ‘xyz’ or ‘bcd’. And stud_id and stud_name is retrieved from the table STUDENT on the basis of whose college name is either ‘xyz’ or ‘fgh’.
- The UNION operator is then applied for combining the extracted result of both SELECT statements (on the basis of the condition using WHERE clause), which will further remove the duplicate records as well and displays only the relevant data to the user.
Example #3
Code:
SELECT teach_id, teach_name FROM TEACHER UNION SELECT stud_id, stud_name FROM STUDENT ORDER BY teach_name desc;Output:
Explanation:
- In the above example, teach_id and teach_name are retrieved from the table TEACHER, and stud_id and stud_name are retrieved from the table STUDENT. The UNION operator is then applied to the extracted result of both the SELECT statements for combining their resultset. Since the UNION removes the duplicate records, the duplicate data is removed and displayed only once in the final result.
- As mentioned before, that ORDER BY clause can be used with the UNION operator in order to perform the sorting on any basis. So, the above resultset is sorted on the basis of teach_name in decreasing order.
Conclusion
The above description clearly explains what the union operator is and how it works in PL/SQL code. UNION operator is mainly used to combine the result set of the queries and display the result accordingly. It is important for the programmer to understand these small concepts clearly because they are the building blocks and are quite helpful when working on real databases.
Recommended Articles
We hope that this EDUCBA information on “PL/SQL UNION” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
