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

Supplier Table

Download Complete SQL Scripts from GitHub  »

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

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;

3. Precedence :  All set operators i.e. UNION, UNION ALL, INTERSECT, 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.

4. The UNION  and UNION ALL operators are not valid on LONG columns. Same is true for INTERSECT and MINUSas well.
5. 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.

6. You cannot also specify the for_update_clause with the set operators.

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

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