How to Use SQL Union Query
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
- 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.
- 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.
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'
First Name Source Abraham Author ... … Ann Employee Ann Author ... … Maria Employee Maria Employee … … Yoshi Employee
The SQL Union query is a very uesful way to bring together data from different tables into one dataset.
Microsoft SQL Pattern Matching Links
26 May 2016