Home > Articles > Transact SQL Programming Articles

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

union

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.

Useful Links

• The SQL Documentation Tool automates the process of writing technical documentation for your Microsoft SQL Server databases. A free trial version is available for download.

Useful Development Tools

SQL Documentation Tool

The SQL Documentation Tool creates technical documentation for Microsoft SQL Server 7.0 and 2000 databases. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced. The SQL Documentation Tool documents SQL Server Tables, Views, Stored Procedures, Triggers, Table Relationships and Dependencies.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (10.3Mb ZIP file).

ASP Documentation Tool™

Automatically creates developer documentation for ASP 2.0 and 3.0 web applications written in VBScript and JScript. Documentation for Microsoft Access, SQL Server 7/2000 databases and Visual Basic 6.0 components associated with the web application can also be incorporated into the reports. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5.2Mb ZIP file).

.NET Documentation Tool

Automatically creates technical documentation for .NET Framework Windows and ASP.NET applications written in C# or VB.NET and SQL Server 7/2000/2005 or Microsoft Access databases associated with the application. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5Mb ZIP file).

VB Documentation Tool

The VB Documentation Tool creates technical documentation for Microsoft Visual Basic 6.0 projects. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1Mb ZIP file).

Indexing Service Companion

The Indexing Service Companion is a Windows application that extends the functionality of the Microsoft Windows Indexing Service so that it is able to index content from remote websites and also from ODBC databases. As such it can be used as a low cost alternative to Sharepoint Portal Search Services.

Try Sample Search Facility.     Download Trial Version (1.7Mb ZIP file).

The Website Utility

The Website Utility examines websites for errors and areas that need to be optimised for search engines by using a built in web crawling engine. Errors checked for include broken or moved hyperlinks, missing page titles and missing meta tags. It also generates HTML for use in creating website site maps (table of contents pages - like this one), and is able to create both client-side JavaScript Search Engines and server-side ASP Search Engines for a website.

View Sample Output (HTML Format).     Download Trial Version (3Mb ZIP file).

PHP Documentation Tool™

Automatically creates developer documentation for PHP web applications. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1.0Mb ZIP file).