EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

UNION in Oracle

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Oracle Tutorial » UNION in Oracle

UNION in Oracle

What is UNION Operator in Oracle?

The UNION operator in oracle database combines the result set which is returned by two or more Oracle SELECT statements into a single result set by removing the duplicate rows between various SELECT statement that are under the UNION operator and also the SELECT statement that is present within the UNION operator also should have the same number of fields with same data types in the result set returned by the SELECT statement.

Points of Concentration:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • The queries are all executed independently, but their output is merged.
  • Only the final query ends with a semicolon ‘;’

Syntax:

SELECT col_1, col_2, ..., col_n FROM TableName WHERE condition(s)
UNION
SELECT col_1, col_2, ..., col_n FROM TableName WHERE condition(s) ;

Description:

  • Col_1/2/n: The column(s) or calculation as per your requirement.
  • Table Name: As per your requirement
  • WHERE: It’s optional, depends on your requirement

Example:

SELECT empno, ename, deptno FROM Emp WHERE deptno=10
UNION
SELECT empno, ename, deptno FROM Emp WHERE deptno=30
ORDER BY 1;

Output:

UNION in Oracle-1.1

Rules and Restrictions of Oracle UNION Operator

Before implementing of UNION operator, one must know some important rules and restrictions of the UNION operator which are listed below:

  • The result sets of the queries must have the same number of columns.
  • The data type of each column in the second result set must match the data type of its corresponding column in the first result set.
  • The two SELECT statements may not contain an ORDER BY clause, but the final result of the UNION operation can be ordered.
  • The column used for ordering can be defined through the column number.
  • To sort the final result set, the SELECT statement can contain an ORDER BY clause but the last SELECT statement only can contain the ORDER BY clause.

Implementations of UNION Operator with Examples

In this section, we’ll see the implementation of a UNION operator and its behavior. For that, we will use the below table (Emp) with 14 records throughout the examples to understand the UNION operator behavior.

Query:

SELECT * from Emp;

Output:

UNION in Oracle-1.2

1. UNION Operator without any Condition

Query:

SELECT empno, ename, deptno FROM Emp
UNION
SELECT empno, ename, deptno FROM emp;

Output:

UNION in Oracle-1.3

In this SQL SELECT statement with UNION operator, we got the same 14 records in which the EMP table consists because each SELECT statement generated an individual set of results but during the merge of the result sets, the UNION operator eliminated the duplicate records.

2. UNION Operator with WHERE Clause

Query:

SELECT ename, deptno FROM Emp WHERE deptno = 10
UNION
SELECT ename, deptno FROM Emp WHERE deptno = 30;

Output:

UNION in Oracle-1.4

In this example, both the SELECT statements created a separate result set based on condition respectively and the UNION operator merged both the sets and displayed a final result with nine records in a single set which falls under deptno 10 & 30.

Popular Course in this category
Sale
Oracle Training (14 Courses, 8+ Projects)14 Online Courses | 8 Hands-on Projects | 120+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,641 ratings)
Course Price

View Course

Related Courses
Oracle DBA Database Management System Training (2 Courses)All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects

3. UNION Operator with Different Number of Columns

Query:

SELECT empno, ename, deptno FROM Emp WHERE deptno = 10
UNION
SELECT ename, deptno FROM Emp WHERE deptno = 30;

Output:

UNION in Oracle-1.5

As UNION operator Rules and Restrictions says that the result sets of all SELECT statements must have the same NUMBER of columns. But in this example first SELECT statement having three columns but the second SELECT statement having two columns. That’s why OUTPUT showing an incorrect number of result columns error.

4. UNION Operator with Invalid Column Name

Query:

SELECT dname, ename, deptno FROM Emp WHERE deptno = 10
UNION
SELECT ename, deptno FROM Emp WHERE deptno = 30;

Output:

Invalid Column Name output

Because the column name “Dname” does not exist in the Emp Table. That’s why got an invalid identifier.

Note: This example proves that each SELECT statement creates a separate result set.

5. UNION Operator with Mismatch Data Type

Query:

SELECT ename, hiredate, deptno FROM Emp WHERE deptno = 10
UNION
SELECT ename, job, deptno FROM Emp WHERE deptno = 30;

Output:

Mismatch Data Type output

UNION operator Rules and Restrictions says that the data type of each column in the second result set must match the data type of its corresponding column in the first result set. But in this example, column numbers are the same in both the result sets but the data type is mismatching with the result sets. First SELECT statement result set consisting column “Hiredate” which is DATE data type but the second result set consisting column “Job” which is VARCHAR2 data type it’s not matching. That’s why OUTPUT showing data type error.

6. UNION Operator with ORDER BY Clause

Query:

SELECT empno, ename, deptno FROM Emp WHERE deptno = 10
ORDER BY   empno
UNION
SELECT empno, ename, deptno FROM Emp WHERE deptno = 30 ;

Output:

ORDER BY Clause output

In this example output throwing an ERROR even if the column number and data type criteria is matching with both the result sets. WHY THE ERROR?

In this example ORDER BY clause used by the first SELECT statement to sort the result set. But we can only use the OREDER BY clause in the last SELECT statement.

Query:

SELECT empno, ename, deptno FROM Emp WHERE deptno = 10
UNION
SELECT empno, ename, deptno FROM Emp WHERE deptno = 30
ORDER BY   empno;

Output:

UNION in Oracle-1.9

In this example OREDER BY clause used by the last SELECT statement to sort the result without any error.

TIP: In the ORDER BY clause, you can also use column position number instead of the column name. In some cases, UNION operation is costlier than other operations (join, subquery, etc.)

7. UNION Operator with NULL Column

Query:

SELECT Deptno, SUM(Sal) SalSum FROM Emp GROUP BY Deptno
UNION
SELECT NULL, SUM(Sal) FROM Emp;

Output:

Note: NULL doesn’t have any data type so ORACLE considered it according to the corresponding column Data type.

Conclusion

UNION Operator performs VERTICAL Join and eliminates duplicate records. If you are looking for unique records, use UNION but column(s) number and data type must be the same.

Recommended Articles

This is a guide to the UNION operator in Oracle. Here we discuss rules and restrictions along with various examples of UNION operators. You may also look at the following articles to learn more –

  1. Oracle Interview Questions
  2. Understanding MySQL Schema
  3. Uses OF SQL in details
  4. Top Ethical Hacking Tools and their Features
  5. Guide to Different Oracle Versions
  6. Implementation of  GROUP BY in Oracle

Oracle Training (14 Courses, 8+ Projects)

14 Online Courses

8 Hands-on Projects

120+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

1 Shares
Share
Tweet
Share
Primary Sidebar
Oracle Tutorial
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Oracle Training (14 Courses, 8+ Projects) Learn More