Which operator is used to combine the result set of two OR more SELECT statements?

Except in MySQl, the set operator UNION or UNION ALL is supported by most platforms like DB2, Oracle, SQL server, and Postgre SQL. 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.

UNION Statement – What’s it About?

The UNION command is used to select related information from two tables, similar to the JOIN command, but different in the fact that JOIN combines columns from two tables. With UNION, only distinct values are selected by default.A UNION statement effectively does a SELECT DISTINCT on the result set.

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.

UNION Statement in SQL Server

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

Here are some 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 Statement in SQL Server

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 with the last SELECT statement.

SQL UNION ALL Syntax:

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

UNION and UNION ALL 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.

Examples of UNION and UNION ALL Statements

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.

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.

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.

Which operator is used to combine result sets of two or more SELECT statements and it does not remove duplicate rows between various SELECT statements?

What would be used to combine the results from two queries?

Which statement is used to combine rows from two queries?

Can you use 2 SELECT statements in SQL?