UNION IN ORACLE - Online Tech Support

The Oracle operators Oracle Union and Union ALL are to unite two SELECT statements with same amount columns. Also all columns should be in the same data type in both queries or the operator Oracle Union will raise an error and the select wouldn’t return any row.

The syntax of oracle union is:

SELECT <values>
  FROM <table_1>
UNION
SELECT <values>
  FROM <table_2>;

Operator Union in Oracle will remove duplicated lines from the queries. The following example shows how both dual tables have the same values and the last table has different ones. The Oracle Union will return to lines instead of three.

SELECT 1 AS first, 2 AS second, 3 AS third
  FROM DUAL
UNION
SELECT 1 AS first, 2 AS second, 3 AS third
  FROM DUAL
UNION
SELECT 2 AS first, 3 AS second, 4 AS third
  FROM DUAL;

The operator Union ALL in Oracle has the same purpose as Oracle Union with only one difference the Union ALL does not remove duplicated rows. The Oracle Union ALL syntax is following:

SELECT <values>
  FROM <table_1>
UNION ALL
SELECT <values>
  FROM <table_2>;

The second example is using the same Oracle Selects as above only in this time we are using the Union ALL operator instead of Oracle Union:

SELECT 1 AS first, 2 AS second, 3 AS third
  FROM DUAL
UNION ALL
SELECT 1 AS first, 2 AS second, 3 AS third
  FROM DUAL
UNION ALL
SELECT 2 AS first, 3 AS second, 4 AS third
  FROM DUAL;

The previous query returned all three rows and ignored that two of them are duplicated.

You can use Oracle Union and Union ALL in the same Select statement, but you should keep in mind the first comparing the rows and removing duplicated lines and the second one does not.

To demonstrate the differences between UNION ALL and UNION we will use the following Select statement. And to understand better the Oracle Union example outputs take a look at the following SQL query.

SELECT 1 AS nr
   FROM dual
    CONNECT BY rownum < 11;

The SQL query above is returning 10 rows with value number “1” and on the next example we will unite two exact queries as the last one using Oracle Union.

SELECT 1 AS nr
   FROM dual
    CONNECT BY rownum < 11
  UNION
 SELECT 1 AS nr
   FROM dual
    CONNECT BY rownum < 11;

The Oracle Union operator returned only one line out of 20 because the Union operator does group the values and returns only rows with unique values. The next example is using the same Select statements but they are united with the Union ALL operator.

SELECT 1 AS nr
   FROM dual
    CONNECT BY rownum < 11
  UNION ALL
 SELECT 1 AS nr
   FROM dual
    CONNECT BY rownum < 11;

As you see from the output all 20 rows are there and none of them is missing. Oracle Union ALL does not remove duplicated lines and it does keep both SQL query outputs as is.

The second important thing to know is that the Oracle Union is quite slow with many rows. Use it only where Oracle Union ALL cannot be use, but prefer to use the Oracle Union ALL operator and do Group By in inner queries where it is needed.