UNION Vs.UNION ALL(SET Operators)

What is the difference between UNION and UNION ALL?

UNION and UNION ALL are set operators used to combine result set from different set of queries. Let's understand the difference between each of them.

UNION

UNION combines the result set of two or more queries into a single result set. This result set includes all the rows that belong to all queries in the UNION.

The following points need to be considered when using the UNION operator:

• The number of columns and sequence of columns must be the same in all queries
• The data types must be compatible.

UNION ALL

UNION ALL is very similar to UNION. It also includes duplicate rows in the result set.

Behaviour of UNION / UNION ALL operator with Duplicate Data

UNION always returns distinct rows. In other words, it eliminates duplicate rows from the result set.

·       UNION and UNION ALL used to combine (set operation) two or more query results.  UNION will eliminate duplicate rows and UNION ALL will display all rows.

·       There is a performance hit when using UNION vs UNION ALL, since the Database must do additional work to remove the duplicate rows and then sort the result. Therefore, it is recommended to use UNION ALL if in case you don’t want your result set to be unique.

·       A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results. When comparing the execution plan of UNION ALL and UNION it is also quite clear that UNION ALL is way less expensive than UNION as it does not have DISTINCT SORT operation.

Example:
Table 1: A, B, C, D, E

Table 2: A, B, E, F

Result of UNION and UNION ALL

UNION- A, B, C, D, E, F

UNION ALL- A, B, C, D, E, A, B, E, F

 

SQL> Select * From Table_A;

No
–
1
2
2

SQL> Select * From Table_B;

No
—–
2
3

SQL> Select * From Table_A UNION Select * From Table_B;

No
—
1
2
3

SQL> Select * From Table_A UNION ALL Select * From Table_B;

No
—
1
2
2
2
3

 

Things to remember writing UNION queries

• Each SELECT statement within the UNION must have the same number of columns.

             Example: - Select col1, col2 from TableA UNION Select col1 from TableB; — This will not work; 
             Instead you can replace column with null clause to match the number of columns

• The columns must have similar or compatible data types.

              Example: - Select ‘A’ from TableA UNION Select 1 from TableB; — This will not work;

• The columns in each SELECT statement must be in the same order.
• If the columns across the table have different column names then in general, the column names of the first query are used.

• For large data set queries UNION might have performance issues. So, use it very carefully.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.