Lesson 15. Combining Queries

You can combine the results set of more than one query without joining the tables together. The result sets of the tables have to have the same number of columns. The final column output will be defined by the column names in the first SQL statement. In actual practice, it is good to alias table and column names that are different so you can keep track of where things are going and what things are.

You can perform this magic by using the SQL UNION clause.

There are two kinds of UNION clauses.

UNION – Removes duplicates from the final result set.

UNION ALL – Does not remove duplicate records from the final result set.

The use case for this is difficult to model in a sample database. Usually, this need arises from data being generated by more than one highly complex SQL statement. This statement may be joining data across servers. The bottom line is, whatever the user is after, it could not be generated with a single SQL statement.

Examples

Example Usage Of Union And Union All

USE AdventureWorks2016

SELECT RevisionNumber
FROM Sales.SalesOrderHeader
UNION
SELECT SalesOrderID AS RevisionNumber
FROM Sales.SalesOrderHeader


SELECT RevisionNumber
FROM Sales.SalesOrderHeader
UNION ALL
SELECT SalesOrderID AS RevisionNumber
FROM Sales.SalesOrderHeader