When you build a system based on normalized table structures, you must join the tables back together to see the data in a useable format. For example, if you have separated customers, orders, and order details, you need to join these tables in a query to see the name of the customer who placed an order for a particular item. Several types of joins are available. They include inner joins, outer joins, full joins, and self-joins. The sections that follow cover each of these join types.
Using Inner Joins
n inner join is the most common type of join. When you use an inner join, only rows on the one side of the relationship that have matching rows on the many side of the relationship are included in the output. Here's an example:
SELECT Customers.CustomerID,
Customers.CompanyName, Orders.OrderID,
Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
This example includes only those customers who have orders.
By the Way
The word OUTER is assumed in the LEFT JOIN clause used when building a left outer join.At times you need to join more than two tables in a SQL statement. The most common syntax is
FROM table1 JOIN table2 ON condition1 JOIN table3 ON condition2
The following example joins the Customers, Orders, and OrderDetails tables:
SELECT Customers. CustomerID, Customers.CompanyName,
Orders.OrderID, Orders.OrderDate
FROM (Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
In the example, the order of the joins is unimportant. The exception to this is when you combine inner and outer joins. When you combine inner and outer joins, the SQL Server engine applies two specific rules. First, the nonpreserved table in an outer join cannot participate in an inner join. The nonpreserved table is the one whose rows may not appear. In the case of a left outer join from Customers to Orders, the Orders table is considered the nonpreserved table. Therefore, it cannot participate in an inner join with OrderDetails. The second rule is that the nonpreserved table in an outer join cannot participate with another nonpreserved table in another outer join.
Creating Outer Joins
An outer join enables you to include rows from one side of the join in the output, regardless of whether matching rows exist on the other side of the join. Two types of outer joins exist: left outer joins and right outer joins. With a left outer join, SQL Server includes in the output all rows in the first table specified in the SELECT statement. Here's an example:
SELECT Customers.CustomerID,
Customers.CompanyName, Orders.OrderID,
Orders.OrderDate
FROM Customers
LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
In the previous example, customers are included regardless of whether they have orders. With the right outer join shown next, orders are included whether or not they have associated customers. If you have properly enforced referential integrity, this scenario should never exist.
SELECT Customers.CustomerID,
Customers.CompanyName, Orders.OrderID,
Orders.OrderDate
FROM Customers
RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Utilizing Full Joins
A full join combines the behavior of the left and right outer joins. It looks like this:
SELECT Customers.CustomerID,
Customers.CompanyName, Orders.OrderID,
Orders.OrderDate
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID
In this example, all customers appear in the output regardless of whether they have orders, and all orders appear in the output whether or not they are associated with customers.
Taking Advantage of Self-Joins
A self-join involves joining a table to itself. Although it is not the most common type of join, this join type is very valuable. Imagine the scenario in which an Employee table contains a field called EmployeeID and another field called ReportsTo. The ReportsTo field must contain a valid EmployeeID. It would not make sense to have separate Employee and Supervisor tables because supervisors are employees. This is where the self-join comes in. A self-join looks like this:
SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
Supervisors.EmployeeID as SupervisorID,
Supervisors.LastName as SupervisorLastName,
Supervisors.FirstName as SupervisorFirstName
FROM Employees INNER JOIN Employees as Supervisors
ON Employees.ReportsTo = Supervisors.EmployeeID
In this example, the EmployeeID from the Employees table is joined to an alias of the ReportsTo field of an alias of the Employees table (called Supervisors).