SQL Server Power Search - Get More Relevant Results

SQL UNION

The SQL UNION statement allows the results from multiple SELECT statements to be combined.

 

The examples in this article will use the following tables:

Example1

Example2

IdCol1Col2
1AAA123
2BBB234
3CCC345
IdCol1Col2
2BBB234
4DDD456
6FFF678

To demonstrate the basic syntax, we'll start with a command to return all rows from both tables:

This returns:

IdCol1Col2
1AAA123
2BBB234
3CCC345
4DDD456
6FFF678

I have included additional line breaks in the SQL to attempt to separate the component parts. The 2 SELECT statements are executed first. These are then combined by the UNION statement and duplicate rows are eliminated. Finally the ORDER BY (which is optional) sorts the output.

The names of the columns in the result set produced by the query are taken from the first SELECT. The columns in the subsequent SELECT statements do not have to have the same names, although they must have compatible datatypes.

It may be that you don't mind seeing duplicate rows in the output, or you know that none will appear. The step of removing duplicates imposes additional overhead so the query will perform more quickly if this could be removed. In this case you would use the UNION ALL statement:

This returns:

IdCol1Col2
2BBB234
2BBB234
3CCC345
4DDD456

You are not limited to 2 SELECT statements. There is no limit to the number of SELECT statements that can be combined using multiple UNION or UNION ALL operators.

Please ensure that all the SELECT statements have the same number of columns, or you will get the following error message:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

If you attempt to combine 2 or more queries with different datatypes, it will run and perform type conversions as it goes. This is inefficient, and should be avoided if possible. If a type conversion fails during execution you will get an error and no rows will be returned. For example, a UNION of a varchar and a datetime column may fail with the following message:

Conversion failed when converting datetime from character string.

This short description of the SQL UNION operator should be enough to get you started. The next article describes the SQL ORDER BY clause, and is the last article on the SELECT statement.