How to Use SQL Union Query

How to Use SQL Union Query

Background

Where is the SQL Union query used? Well, one of our regular SQL support topics is where an organisation’s data is spread across different data sources and there is a need to combine the results into a single report. A supplementary issue is to identify in the results set just where the data came from. A variation of this theme is where two or more completely different results sets are required from the same data source and they need to be tagged to identify their reason for inclusion in the results.

SQL Union Query

A SQL Union Query is the solution for this type of problem. It is has a few rules but they are very reasonable.

Multiple select queries on different data sources can be merged together by using the UNION keyword between each query. For example:

Select column1, column2
From table1
Where condition_x
UNION
Select Column7, column9
From table3
Wherecondition_y
UNION
….etc

The Rules

• Each select query must return the same number of columns.
• Each column in each Select Query must be of the same data type as that column in the other Select Queries. Use explicit data type conversion if necessary.
• The ORDER BY clause, if used, must not appear in the individual Select Queries but it must appear at end.
• Any duplicate output is suppressed unless UNION ALL is used.

Notes

• Think of the first Select Query as defining the rules that the subsequent Select Queries must follow.
• If using column aliases then only those in the first Select Query are observed.
• Column aliases in the other Select Queries may only serve to confuse.
• If using ORDER BY then I would suggest using the column aliases to avoid confusion.

Tagging the Results

By including a character description in the SELECT Clause of each Select Query then it becomes possible to identify the individual rows in the results as having come from a particular data source and / or is the result of a specific WHERE condition.

Example:

use pubs
go

select fname as 'First Name', 'Employee' as 'Source'
from employee
union ALL
select au_fname, 'Author'
from authors
order by 'First Name'

Results in:

First Name	Source
Abraham	Author
...	…
Ann	Employee
Ann	Author
...	…
Maria	Employee
Maria	Employee
…	…
Yoshi	Employee

Conclusion

The SQL Union query is a very uesful way to bring together data from different tables into one dataset.