Transact SQL Other Articles Software Reviews
Using the Transact SQL Union Statement
The Union clause is a useful Transact SQL statement that allows two or more select queries to be bunched together in a single results set. It is a great function to be able to have in stored procedures that are used to return results sets for use in web applications and other database driven applications.
The Union clause is really easy to use - you just put the Union keyword between two SQL select statements, e.g.
select ProductID, ProductName from Products_In_Stock
select ProductID, ProductName from Products_Out_Of_Stock
The query above therefore returns a single results set containin ProductID and ProductName data columns.
There is no limit to the number of select queries that can be combined using the union operator.
The drawback with the Union clause is that it can only be used to combine two or more select queries that return the same number of data columns. The other consideration is that the Union function will also only work if the columns in the different queries have the same (or compatible) data types. If the data types differ then it might however be possible to make the Union join work if the convert function is used.
Problems can also occur when using union select statements where the columns to be combined have different numerical column data types, such as combining float columns and int columns. In this case, the SQL Server data precedence rules will apply.