SQL UNION vs UNION ALL – Similarities and Differences

Which operator do you use if you have to combine the result sets of two or more queries and display all the rows returned by each of the queries as one single result set? The solution is to use the UNION set operator. Except in MySQl, the set operators UNION and UNION ALL are supported by most database platforms like DB2, Oracle, SQL server, and Postgre SQL.

When using UNION remember that the order, number, and datatype of columns should be the same in all queries. The datatypes need not be identical, but they should be compatible. For example, CHAR and VARCHAR are compatible datatypes. If you want to recollect information on tables, SQL database operations, and programming then SQL Database for Beginners and Learn SQL in 6 days are great refresher courses. If you need further guidance on writing SQL queries and learning how to build applications or generate business reports, then refer Introduction to SQL Training.

Comparison Between UNION and UNION ALL

UNION

The UNION command is used to select related information from two tables, much like the JOIN command. With UNION, only distinct values are selected by default. A UNION statement effectively does a SELECT DISTINCT on the result set.

UNION Statement in SQL Server

The UNION operator is used to combine the result set of two or more SELECT statements.

Here are some of the simple rules of using UNION in SQL:

• Each SELECT statement within the UNION must have the same number of columns and the columns must have similar or compatible data types.
• The columns in each SELECT statement must be in the same order.
• If the columns sizes of the two tables vary, then while returning data, SQL server uses the larger of the two columns. Thus if a SELECT….UNION statement has a CHAR (5) and CHAR (10) column, then it will display output data of both the columns as a CHAR (10) column.
• If the columns across the table have different column names then in general, the column names of the first query are used.

SQL UNION Syntax

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

UNION ALL

The UNION ALL command is similar to the UNION command, except that UNION ALL selects all values. So with UNION ALL duplicate rows are not eliminated, rather they are included. This operator just pulls all rows from all tables which satisfy the query and combines them into a table. If you are sure that all the records returned from a UNION operation are unique, then using UNION ALL is a better option as it gives faster results. The results from a UNION ALL are unsorted. So after getting the results, you can sort them by using the ORDER BY clause. A ORDER BY should be inserted with the last SELECT statement.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

Example 

To compare UNION and UNION ALL let us take the following table ‘Contacts’ containing columns for City, State, and Zip.

City               State          Zip

                Nashville          TN                   37235

Lawrence           KS                   66049

Corvallis            OR                  97333

UNION ALL SQL Statement

If we apply UNION ALL SQL statement, then we will combine the two queries which will retrieve and combine records from Tennessee (‘TN’) twice.

Syntax:

SELECT City, State, Zip FROM Contacts WHERE State IN (‘KS’, ‘TN’)

UNION ALL

SELECT City, State, Zip FROM Contacts WHERE IN (‘OR’ ‘TN’)

 Result of UNION ALL syntax:

City                  State           Zip

                Nashville              TN                   37235

Lawrence              KS                   66049

Nashville              TN                   37235

Corvallis               OR                  97333

With UNION ALL syntax the TN record appears twice, since both the SELECT statements retrieve TN records.

UNION SQL Statement

We now use the SQL UNION command by writing the syntax:

SELECT City, State, Zip FROM Contacts WHERE State IN (‘KS’, ‘TN’)

UNION

SELECT City, State, Zip FROM Contacts WHERE IN (‘OR’ ‘TN’)

The result of the UNION query will be as follows:

City               State          Zip

                Corvallis            OR                  97333

Lawrence           KS                   66049

Nashville          TN                   37235

Notice that the TN record appears only once, even though both the SELECT statements retrieve TN records. The UNION syntax automatically eliminates the duplicate records between the two SQL statements and also sorts the results. The resultant displayed records are sorted alphabetically and so the Corvallis record appears first even though it is from the second SELECT statement. A GROUP BY clause can be added at the end to sort the list.

A UNION query, by definition, eliminates all duplicate rows and compared to UNION ALL it is slower as it does a sorting operation. To do this in SQL Server, a UNION statement must build a temporary index on all the columns returned by both queries. If the index cannot be build for the queries, then you will get a SQL error. In such cases it is best to use the UNION ALL statement.

Application in Other Database Severs

In Oracle

Oracle does not support UNION or UNION ALL on queries under these circumstances:

• Queries containing columns of LONG, BLOB, CLOB, BFILE, or VARRAY datatype.
• Queries containing a FOR UPDATE clause or a TABLE collection expression.

In DB2

In DB2 database system, you can use both UNION and UNION ALL along with the VALUES clause.

Conclusion

If you remember the simple rules to use UNION and UNION ALL and the syntax for the same, then combining tables as per your requirement becomes an easy task. If you want to explore basic SQL queries and other code statements, then it is a good idea to check out SQL Queries 101. If you want to gather knowledge on Oracle SQL and Oracle PL/SQL Programming then it may be worth to peek into Introduction to Oracle SQL, or Oracle PL/SQL Tutorial – A Comprehensive Training Course.