As the name suggests UNION in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, a UNION excludes the duplicate rows returned by SQL SELECT statements.
Also, each SELECT statement must have the same number of column(s)/field(s) with similar data-types for UNION to work.
Syntax for UNION in Oracle SQL / PLSQL is:
Suppose have a table named ‘employee’ in the database as shown below:
Also we have one more table ‘comm’ as shown below:
Let’s take an example to understand UNION , suppose we write our SQL query using UNION as:
SELECT employee_id FROM employee UNION SELECT emp_id FROM comm;
The result of the above query will be:
Here we can see that the records for employee_id = ‘102’,’103,’104’ and ‘105’ appear only once in the result set thought they are present in ‘employee’ and ‘comm’ tables. Also note that all the ‘employee_id’ from both ‘employee’ and ‘comm’ tables are present in the result set.