Difference Between Union And Union All in Oracle Database

In this SQL Tutorial, We will explore about the Difference between Union and Union All in Oracle.

Before we will go ahead in the post, We have to know about the Set Operators in Oracle SQL.

In Oracle SQL, Set Operators divided into four category.

1. Union
2. Union ALL
3. Intersect
4. Minus

Today, we will cover difference between Union and Union ALL in Oracle database.

What is Union in Oracle SQL?

The Union is a set operator in Oracle SQL. It is use to display result from multiple tables after removing the matched row.

Syntax  of Union Set Operator in Oracle SQL

SELECT COLUMN1,COLUMN2 FROM TABLE_NAME  --YOUR TABLE NAME--
UNION
SELECT COLUMN1,COLUMN2 FROM TABLE_NAME  ---YOUR TABLE NAME--

Example of Union Set Operator in Oracle SQL

SELECT EMPNO, ENAME FROM EMP1
UNION 
SELECT DEPTNO, DNAME FROM DEPT1

Before we go to the understand above example, let see our database table structure and inserted data then you will be understand better.

See the following table structure with the screenshot:

See the following existing inserted data with the screenshot:

After executing the above query, see the below output screenshot that will help you for the better understanding.

We have find 5 records after the execution of the query because Union Set operator have property to remove duplicate rows.

What is Union ALL in Oracle SQL?

The Union ALL is a set operator in Oracle SQL. It is use to display result from multiple tables without removing the matched rows.

Syntax  of Union Set Operator in Oracle SQL

SELECT COLUMN1,COLUMN2 FROM TABLE_NAME  --YOUR TABLE NAME--
UNION ALL
SELECT COLUMN1,COLUMN2 FROM TABLE_NAME  ---YOUR TABLE NAME--

Example of Union Set Operator in Oracle SQL

SELECT EMPNO, ENAME FROM EMP1
UNION ALL 
SELECT DEPTNO, DNAME FROM DEPT1

we will use above table for executing UNION ALL SQL statement. Let's see the following output  after the use of UNION ALL set operator.

Now you can see in the above screenshot that we are finding 6 records because of UNION ALL set operator property.

 Unique Difference between Union and Union All in Oracle

1. Union removes duplicate data before the return result while UNION ALL do not remove duplicate data before the result.
2. Union is sort display result after the removing the matched rows while Union ALL  is not sort display result.
3. Union is retrieved slow data because for filter the data before return result while Union All is retrieved fast data.

Rules:-

1. Whenever you have to use union or union all set operator, No. of columns should be same.
2. Whenever you have to use union or union all set operator, The columns data types should be same.
3. Order by will be use in the end of the query.

Now we will take some examples which will help to understand above points.

Example 1:-

Select empno,ename from EMP1
union all
Select deptno  from DEPT1;

After running the above statement, Error occurs (ORA-01789: query block has incorrect number of result columns).

Example 2:-

Select ename from EMP1
union all
Select deptno  from DEPT1;

After running the above statement, Error occurs that (ORA-01790: expression must have same datatype as corresponding expression).

Example 3:- Use of Order by

Select empno,ename from EMP1
union 
select deptno,dept_name from DEPT1
order by EMPNO;

That's all for today, I think (Difference between Union and Union ALL in Oracle Database) post will help you out for the better understanding. 

If you like the post then please share your valuable comment in the comment box section.