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.
Recommended Articles
Top courses in SQL
SQL students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.