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 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 set.
Tip
By default, the UNION operator removes duplicate rows from the result set. If you do not want to remove duplicate rows, specify the ALL keyword. UNION ALL is faster than UNION because it requires less backend processing for the union operation. This is because the UNION clause also adds an additional sorting operation to remove duplicate rows from two or more SELECT statements. Therefore, it is better to use UNION ALL, where possible.
Have a look at the following Venn diagram to get a better understanding of UNION and UNION ALL operators:
For example, the following query returns all products associated with a purchase order and all products associated with a sales order:
SELECT [ProductID],
[UnitPrice],
[OrderQty]
FROM [Purchasing].[PurchaseOrderDetail]
UNION
SELECT [ProductID],
[UnitPrice],
[OrderQty]
FROM [Sales].[SalesOrderDetail];We will run the following query to keep duplicates:
SELECT [ProductID] ,
[UnitPrice] ,
[OrderQty]
FROM [Purchasing].[PurchaseOrderDetail]
UNION ALL
SELECT [ProductID] ,
[UnitPrice] ,
[OrderQty]
FROM [Sales].[SalesOrderDetail];The EXCEPT operator compares the results of two SELECT statements and returns only distinct rows from the first SELECT statement result set that do not exist in the second SELECT statement result set. The following is the basic syntax for using the EXCEPT operator:
select_statement EXCEPT select_statement
Like the UNION operator, all statements combined using the EXCEPT operator must have compatible data types and the same number of columns. Have a look at the following diagram:
For example, let's rewrite the preceding query to return only distinct ProductID, UnitPrice, and OrderQty values of those products associated with the purchase orders that do not have an associated sales order. Have a look at the following code:
SELECT [ProductID] ,
[UnitPrice] ,
[OrderQty]
FROM [Purchasing].[PurchaseOrderDetail]
EXCEPT
SELECT [ProductID] ,
[UnitPrice] ,
[OrderQty]
FROM [Sales].[SalesOrderDetail];The INTERSECT operator compares the results of two SELECT statements and only returns distinct rows from the first SELECT statement result set that also exist in the second SELECT statement result set. The general syntax for the INTERSECT statement is as follows:
select_statement INTERSECT select_statement
Similar to the UNION and EXCEPT operators, the INTERSECT operator has the same SELECT list restrictions. The following diagram will help you understand the INTERSECT operator:
For example, to find out which currency codes exist in both the Sales.Currency table and the Sales.CurrencyRate table, we need to retrieve the distinct list of currency codes, for which we will run the following query:
SELECT [CurrencyCode] FROM [Sales].[Currency] INTERSECT SELECT [ToCurrencyCode] FROM [Sales].[CurrencyRate];
The JOIN operator is used to join columns from multiple tables and return them in a single result set. The JOINs often use foreign key relationships to join multiple related tables. The basic syntax for the JOIN operator is as follows:
SELECT select_list FROM table_or_view [INNER | LEFT | RIGHT | FULL | OUTER] JOIN table_or_view ON (join_condition)
The following are the four main types of joins: inner joins, outer joins, cross joins, and self joins. In this section, we will discuss these join types.
Include only those records in the result set that satisfy the join condition. Therefore, inner joins are also called equi-joins. For example, the following query uses INNER JOIN to retrieve all the employees' names, titles, and current department information:
SELECT p.[Title] , p.[FirstName] + SPACE(1) + p.[MiddleName] + SPACE(1) + p.[LastName] AS [FullName] , e.[JobTitle] , d.[Name] AS [Department] , d.[GroupName] , dhist.[StartDate] FROM [HumanResources].[Employee] e INNER JOIN [HumanResources].[EmployeeDepartmentHistory] dhist ON e.[BusinessEntityID] = dhist.[BusinessEntityID] AND dhist.[EndDate] IS NULL INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] INNER JOIN [HumanResources].[Department] d ON dhist.[DepartmentID] = d.[DepartmentID];
The outer joins return all rows, whether or not they satisfy the join conditions. There are three basic outer join types: LEFT OUTER JOIN (left join), RIGHT OUTER JOIN (right join), and FULL OUTER JOIN (full join).
The LEFT OUTER JOIN operator returns all rows from the left table named in the LEFT OUTER JOIN clause. If there is no matching row in the table to the right, SQL Server displays the values of the right table as NULLs. For example, the following query uses LEFT OUTER JOIN to retrieve an employee's name and title, regardless of whether they have a phone, an e-mail ID, and additional contact information:
SELECT p.[Title] , p.[FirstName] + SPACE(1) + p.[MiddleName] + SPACE(1) + p.[LastName] AS [FullName] , pp.[PhoneNumber] , pt.[Name] AS [PhoneNumberType] , ea.[EmailAddress] , p.[AdditionalContactInfo] FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] LEFT OUTER JOIN [Person].[EmailAddress] ea ON p.[BusinessEntityID] = ea.[BusinessEntityID] LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.[BusinessEntityID] = p.[BusinessEntityID] LEFT OUTER JOIN [Person].[PhoneNumberType] pt ON pp.[PhoneNumberTypeID] = pt.[PhoneNumberTypeID];
The RIGHT OUTER JOIN operator returns all rows from the right table named in the RIGHT OUTER JOIN clause. If there is no matching row in the left table, SQL Server displays the values of the left table as NULLs. For example, the following query uses RIGHT OUTER JOIN to return the list of all products, regardless of whether there is a special discount associated with the product:
SELECT p.[ProductID] , p.[Name] , so.[SpecialOfferID] , so.[Description] , so.[DiscountPct] , so.[Type] , so.[Category] , so.[StartDate] , so.[EndDate] , so.[MinQty] , so.[MaxQty] FROM [Sales].[SpecialOfferProduct] sop RIGHT OUTER JOIN [Sales].[SpecialOffer] so ON so.[SpecialOfferID] = sop.[SpecialOfferID] RIGHT OUTER JOIN [Production].[Product] p ON p.[ProductID] = sop.[ProductID];
The FULL OUTER JOIN operator returns all rows from both tables. If there are no matches between the right and left rows, SQL Server displays the missing values as NULLs. In other words, FULL OUTER JOIN acts as a combination of LEFT OUTER JOIN and RIGHT OUTER JOIN.
The CROSS JOIN operator returns all rows from the table to the left. Each row in the table to the left is combined with all rows in the table to the right. This is also known as a Cartesian product. The cross join does not have an ON clause. The following is an example of a cross join:
SELECT c.* ,
crc.*
FROM [Sales].[Currency] c
CROSS JOIN [Sales].[CountryRegionCurrency] crc;In self join, you join a table to itself in order to find the rows in a table that have values in common with other rows of the table. Self joins are rarely used in a normalized database. Create a table alias to reference the table multiple times in the same query. We can also use a WHERE clause to eliminate cases where a row matches itself.