Code Pumpkin

UNION Vs UNION ALL | Oracle SQL

May 28, 2017
Posted by Pumpkin
Subscribe

Though both UNION and 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.

UNION SQL Operator

UNION ALL SQL Operator

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 : 

Customer Table

Customer_ID Customer_Name Country
1 Dan Williams USA
2 Moses Tong China
3 Ritesh Vyas India

Supplier Table

Supplier_ID Supplier_Name Country
1 Ramesh Patel India
2 Ricardus W UK
3 Manish Khatri India
4 Cajun Delights USA

Download SQL Scripts »


Query 1) Find list of all Countries from where Customers and Suppliers belong?


SELECT country FROM Customer
UNION
SELECT country FROM Supplier;

Output:


   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;

Output:


   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;

Output:


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

Here UNION and 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 BLOB (or 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.

Differences

UNION UNION ALL
1 The UNION operator returns only distinct rows that appear in either result while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows
2 Slower than UNION ALL, as it uses a SELECT DISTINCT on the results set to remove duplicate rows. faster than UNION but may look slow because it returns more data which takes more time to travel via the network.
3 Can't be used with BLOB or CLOB datatypes. Can be used with BLOB and CLOB datatypes


Important Points:

  1. Each SELECT statement with the UNOIN and UNION ALL must have the same number of columns.
  2. Datatypes : The columns must also be in the same datatype group (such as numeric or character).
  • If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value.
  • If either or both of the queries select values of datatype VARCHAR2, then the returned values have datatype VARCHAR2.
  • If no query selects values of type BINARY_DOUBLE but any query selects values of type BINARY_FLOAT, then the returned values have datatype BINARY_FLOAT.
  • If all queries select values of type NUMBER, then the returned values have datatype NUMBER.
  • 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;
  1. Precedence :  All set operators i.e. UNIONUNION ALLINTERSECT, and MINUS have 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 INTERSECT operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the INTERSECT operator with other set operators.

  1. The UNION  and UNION ALL operators are not valid on LONG columns. Same is true for INTERSECT and MINUSas well.
  2. 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 order_by_clause.
  3. You cannot also specify the for_update_clause with the set operators.

  4. You cannot specify the order_by_clause in the subquery of these operators.

  5. You cannot use these operators in SELECT statements containing TABLE collection expressions.

That's all about UNION and UNION ALL.  If you have any doubt or any suggestions, please drop a comment. Thanks! yes

Happy Learning 🙂

Related Article : View vs Materialized View | Oracle SQL

Tags: , , , ,


Comments and Queries

If 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.

Total Post : 80
Subscribe
Contribute Your Articles

Interview Experiences

Related Books

Like Us On Facebook

Alexa Page Rank