• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / sql / UNION ALL in Oracle SQL – PLSQL

UNION ALL in Oracle SQL – PLSQL

October 26, 2012 by techhoneyadmin

As the name suggests UNION ALL in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, a UNION ALL does not 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 the UNION ALL to work.

Syntax for UNION ALL in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
UNION ALL
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 ALL, suppose we write our SQL query using UNION ALL as:

SELECT employee_id
FROM employee
UNION ALL
SELECT emp_id
FROM comm;

The result of the above query will be:

Emp_Id
101
102
102
103
103
104
104
105
105
106
107

Here we can see that the records for employee_id = ‘102’,’103,’104’ and ‘105’ appear twice in the result set because they are present in both (‘employee’ and ‘comm’) tables. Also note that all the ‘employee_id’ from both ‘employee’ and ‘comm’ tables are present in the result set.


Filed Under: sql Tagged With: how to use union all query in oracle database query, how to use union all query in oracle plsql, how to use union all query in oracle sql, syntax and example of union all query in oracle database query, syntax and example of union all query in oracle plsql, syntax and example of union all query in oracle sql, union all query in oracle plsql, union all query in oracle sql, UNIONALLPLSQL, using union all query in oracle database query, using union all query in oracle plsql, using union all query in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in