How To Use Union In Oracle

This tutorial page is based on examples to make it easier to follow. Operators Oracle Union and Oracle Union ALL are connecting two SQL queries with same amount and type of columns. It is very important that all columns are in the same data type or Oracle Union operator will raise an error and your select will not return any row.

The Oracle Union syntax is:

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

Operator Oracle Union will remove duplicated lines from the SQL query and the first example shows how acts Union when two first dual tables have exactly the same values and the last one has slightly different ones.

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 output above shows that Oracle Union did return only two lines instead of three and the reason is in Oracle Union that does grouping.

The Union ALL operator works in the same way as Oracle Union with one exception Oracle Union ALL does not remove duplicated rows. The Oracle Union ALL syntax is:

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

The next example with Oracle Union ALL is using the same Oracle Selects as in the last example only this time we are using the UNION ALL operator instead of 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;

And in this time the query returned all three rows and ignored that two of them has duplicated values. Whenever you would need to do Union and you know that the lines are not duplicated use always UNION ALL operator that is quicker than UNION.

In addition, you should keep in mind that the UNION is removing duplicated lines and the UNION ALL is not doing it. The output can be different and it will affect counting or summarising lines. You need to understand the difference between them to get the correct output.

The second important thing to know is that Oracle Union will slow down big SQL queries. Use it only where Oracle Union ALL cannot be use, but do prefer to use Oracle Union ALL and Group By in inner queries.

For the following we will describe a few errors that may occur while you are writing Oracle Union statements. The first error is ORA-01790: expression must have same datatype as corresponding expression and it appears when column types are not matching in both queries that you are uniting. You can see in the following example that the lowest Oracle Select has a date type declared as the first column using function Oracle Sysdate.

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 sysdate AS first, 3 AS second, 4 AS third
  FROM DUAL;

Now the second most common mistake is to declare in one query more columns than others and Oracle Union operator will raise error: ORA-01789: query block has incorrect number of result columns. To fix this error you need to put to all united SQL queries the same amount of columns even if you need to set as NULL value inside it. Take a look at the following example where the lowest SQL query has the fourth column that is missing from other two queries.

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 1 AS first, 3 AS second, 4 AS third, 5 as fourth
  FROM DUAL;

See Also:
Oracle Group By Home