UNION in Oracle SQL

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:

SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
UNION
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s);

Suppose have a table named ‘employee’ in the database as shown below:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Also we have one more table ‘comm’ as shown below:

Emp_Id	Commission_Percent
102	20
103	20
104
105	10
106	15
107	25

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:

Employee_Id
101
102
103
104
105
106
107

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.