Multiple table queries using UNION, EXCEPT, INTERSECT, and JOINs

So far in this book, we have seen queries that only retrieve data from a single table. However, in the real world, it is very unlikely that you will write queries that only refer to a single table. In practice, the requirement might be to retrieve data from multiple tables. SQL Server 2014 provides several options to create queries that return data from multiple tables. In this section, we will explore these options.

The UNION operator

The UNION operator is used to combine the result sets of two or more SELECT statements to generate a single result set. The following is the basic syntax for using the UNION operator:

select_statement UNION [ALL] select_statement
[UNION [ALL] select_statement […n]]

The key point to remember is that all statements combined using the UNION operator must have the same number of columns and must have compatible data types. The column names of the first SELECT statement are used as headings for the result...