Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 6 SET Operators
  5. 6.3 UNION ALL

6.3 UNION ALL

The UNION operator returns only distinct rows. However, the UNION ALL operator returns all rows from each component query and does not eliminate any duplicate rows.

SELECT 1 AS Value  FROM DUAL UNION ALL
SELECT 2 AS Value2 FROM DUAL UNION ALL
SELECT 3 AS Value3 FROM DUAL UNION ALL
SELECT 2 AS Value4 FROM DUAL UNION ALL
SELECT 1 AS Value5 FROM DUAL UNION ALL
SELECT 5 AS Value6 FROM DUAL UNION ALL
SELECT 4 AS Value7 FROM DUAL UNION ALL
SELECT 1 FROM DUAL
ORDER BY 1;

     VALUE
----------
	 1
	 1
	 1
	 2
	 2
	 3
	 4
	 5

8 rows selected.

--1st query
SELECT Ename FROM Emp WHERE Deptno = 20;

ENAME
----------
JONES
SCOTT
FORD
SMITH
ADAMS


--2nd query
SELECT Ename FROM Emp WHERE Job = 'CLERK';

ENAME
----------
SMITH
ADAMS
JAMES
MILLER
SELECT Ename FROM Emp WHERE Deptno = 20
UNION ALL
SELECT Ename FROM Emp WHERE Job = 'CLERK';

ENAME
----------
JONES
SCOTT
FORD
SMITH
ADAMS
SMITH
ADAMS
JAMES
MILLER

9 rows selected.

The removal of duplicates by UNION operator requires extra processing, so you should consider using UNION ALL where ever possible.

Was this article helpful to you? Yes No

How can we help?