You are reading the article How Does Union Operator Work In Pl/Sql? updated in October 2023 on the website Phuhoabeautyspa.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How Does Union Operator Work In Pl/Sql?Introduction to PL/SQL UNION
Web development, programming languages, Software testing & others
Syntax of PL/SQL UNIONSELECT expr1, expr2, expr3, ... expr_n FROM table1 [WHERE conditions] UNION SELECT expr1, expr2, expr3, ... expr_n FROM table2 [WHERE conditions];
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.
T_001 Arush xyz
T_002 Akash xyz
T_003 Amitansh abc
T_004 Gourang bcd
T_005 Manish efg
S_006 Akansha fgh
T_001 Arush xyz
T_002 Akash xyz
S_007 Utkarsh fgh
S_008 Akshat qweExample #1
Code:SELECT teach_id, teach_name FROM TEACHER UNION SELECT stud_id, stud_name FROM STUDENT ;
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 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' ;
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;
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.
You're reading How Does Union Operator Work In Pl/Sql?
Update the detailed information about How Does Union Operator Work In Pl/Sql? on the Phuhoabeautyspa.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!