UNION Vs UNION ALL | Oracle SQL
UNION ALL is used to combine results of two
SELECT queries, the main difference between them is that
UNION doesn't include duplicate records, but
UNION ALL does.
Another difference between them is that
UNION ALL is faster than
UNION , but may look slow because it returns more data which takes more time to travel via the network.
Lets understand this by example :
Query 1) Find list of all Countries from where Customers and Suppliers belong?
SELECT country FROM Customer UNION SELECT country FROM Supplier;
COUNTRY ------------------------------ China India UK USA
As we have used
UNION set operator here, It has not printed duplicate country names from both the tables. Lets see what will happen if we use
UNION ALL in above query.
SELECT country FROM Customer UNION ALL SELECT country FROM Supplier;
COUNTRY ------------------------------ USA China India India UK India USA
Query 2) Find detailed list of all Customers and Suppliers?
SELECT * FROM Customer UNION ALL SELECT * FROM Supplier;
CUSTOMER_ID CUSTOMER_NAME COUNTRY ----------- ------------------------------ ------------------------------ 1 Dan Williams USA 1 Ramesh Patel India 2 Moses Tong China 2 Ricardus W UK 3 Manish Khatri India 3 Ritesh Vyas India 4 Cajun Delights USA
UNION ALL both will give the same result set, but
UNION ALL gives faster output as
UNION statement does additional work to remove the duplicate rows.
We should use use
UNION ALL , if we are sure that all the records returned are unique from your union (as in our query 2), it gives faster results.
Another thing to keep in mind is amount data returned by
UNION ALL. If your database server is quite far away and you have limited bandwidth,
UNION ALL may appear slower than
UNION because of number of duplicates it returned. Cost of transferring duplicate row can exceed the query execution benefits in many cases.
Union with BLOB datatype in Oracle
In Oracle Database:
UNION does not support
CLOB) column types,
UNION ALL does.
If we try to use
UNION operator on the BLOB columns, It will give following error message:
Error report: SQL Error: ORA-00932: inconsistent datatypes: expected - got BLOB
The reason behind this is that,
UNION statement uses a
SELECT DISTINCT on the results set to remove duplicate rows which is not supported for BLOB datatype.
UNION operator returns only distinct rows that appear in either result
ALL operator returns all rows. The
ALL operator does not eliminate duplicate selected rows
UNION ALL, as it uses a
SELECT DISTINCT on the results set to remove duplicate rows.
UNION but may look slow because it returns more data which takes more time to travel via the network.
|Can't be used with BLOB or CLOB datatypes.
|Can be used with BLOB and CLOB datatypes
SELECTstatement with the
UNION ALLmust have the same number of columns.
- Datatypes : The columns must also be in the same datatype group (such as numeric or character).
If the queries select values of
CHARwith different lengths, then the returned value is
VARCHAR2with the length of the larger
If either or both of the queries select values of datatype
VARCHAR2, then the returned values have datatype
If no query selects values of type
BINARY_DOUBLEbut any query selects values of type
BINARY_FLOAT, then the returned values have datatype
If all queries select values of type
NUMBER, then the returned values have datatype
- Oracle does not perform implicit conversion across datatype groups. e.g. below query will give error:
SELECT '3' FROM DUAL UNION SELECT 3f FROM DUAL;
Precedence : All set operators i.e.
MINUShave equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.
Note : As per the Oracle Documentation,
To comply with emerging SQL standards, a future release of Oracle will give the
INTERSECToperator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the
INTERSECToperator with other set operators.
UNION ALLoperators are not valid on
LONGcolumns. Same is true for
If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the
You cannot also specify the
for_update_clausewith the set operators.
You cannot specify the
subqueryof these operators.
You cannot use these operators in
That's all for this topic. If you guys have any suggestions or queries, feel free to drop a comment. We would be happy to add that in our post. You can also contribute your articles by creating contributor account here.
Happy Learning 🙂
If you like the content on CodePumpkin and if you wish to do something for the community and the planet Earth, you can donate to our campaign for planting more trees at CodePumpkin Cauvery Calling Campaign.
We may not get time to plant a tree, but we can definitely donate ₹42 per Tree.
About the Author
Comments and QueriesIf you want someone to read your code, please put the code inside <pre><code> and </code></pre> tags. For example:
<pre><code class="java"> String foo = "bar"; </code></pre>For more information on supported HTML tags in disqus comment, click here.