Joins
The types of joins that SQL Server supports are listed in Table 4-3.
| Type of join | Description |
| Inner joins | Retrieves only data that exists in both tables based on the JOIN. INNER JOIN is frequently shorteded to just JOIN. |
| Outer Joins | Inner Joins allow us to pull data that exists in both tables, but sometime we want to pull all the data from one of the tables, in addition to what matches in both tables. These a-re referred as to as Outer Joins |
| Left outer joins | Retrieves all data in left table, and only data that exists in the right table based on the JOIN. |
| Right outer join | Retrieves all data in right table, and only data that exists in the left table based on the JOIN. |
| Full join (full outer join) | Retrieves all data in both tables. |
| Cross joins | Creates a Cartesian product of the tables. Used to create sample databases with large volumes of data without too much effort. |
| Self-join | Joining a table to itself. This is sometimes required when retrieving data from a table that references the table (such as ManagerID of an Employee also being in the Employee table as EmployeeID). |
INNER JOINS
SELECT Production.Product.Name, Production.Product.ProductNumber, Production.ProductModel.Name as 'Model Name' FROM Production.ProductModel Inner join Production.Product ON Production.ProductModel.ProductModelID=Production.Product.ProductModelID
OUTER JOINS
Once more than two tables are involved in the query, things get a bit more complicated. When a table is joined to the RIGHT table, a LEFT OUTER JOIN must be used. That is because the NULL rows from the RIGHT table will not match any rows on the new table. An INNER JOIN causes the non-matching rows to be eliminated from the results. If the Sales.SalesOrderDetail table is joined to the Sales.SalesOrderHeader table and an INNER JOIN is used, none of the customers without orders will show up. NULL cannot be joined to any value, not even NULL.
To illustrate this point, when I add the Sales.SalesOrderDetail table to one of the previous queries that checked for customers without orders, I get back no rows at all.
SELECT c.CustomerID, s.SalesOrderID, d.SalesOrderDetailID FROM Sales.Customer c LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID INNER JOIN Sales.SalesOrderDetail d ON s.SalesOrderID = d.SalesOrderID WHERE s.SalesOrderID IS NULL
To get correct results, change the INNER JOIN to a LEFT JOIN.
SELECT c.CustomerID, s.SalesOrderID, d.SalesOrderDetailID FROM Sales.Customer c LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID LEFT OUTER JOIN Sales.SalesOrderDetail d ON s.SalesOrderID = d.SalesOrderID WHERE s.SalesOrderID IS NULL
What about additional tables joined to Sales.Customer, the table on the left? Must outer joins be used? If it is possible that there are some rows without matches, it must be an outer join to guarantee that no results are lost. The Sales.Customer table has a foreign key pointing to the Sales.SalesTerritory table. Every customer’s territory ID must match a valid value in Sales.SalesTerritory. This query returns 66 rows as expected because it is impossible to eliminate any customers by joining to Sales.SalesTerritory:
SELECT c.CustomerID, s.SalesOrderID, t.Name FROM Sales.Customer c LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID INNER JOIN Sales.SalesTerritory t ON c.TerritoryID = t.TerritoryID WHERE SalesOrderID IS NULL
Sales.SalesTerritory is the primary key table; every customer must match a valid territory. If you wanted to write a query that listed all territories, even those that had no customers, an outer join will be used. This time, Sales.Customers is on the right side of the join.
SELECT t.Name, CustomerID FROM Sales.SalesTerritory t LEFT OUTER JOIN Sales.Customer c ON t.TerritoryID = c.TerritoryID
Cross Join
Many SQL books and tutorials recommend that you “avoid cross joins” or “beware of Cartesian products” when writing your SELECT statements, which occur when you don’t express joins between your tables.
SELECT S.Store, P.Product FROM Stores S CROSS JOIN Products P
Self Join
A table can be joined to itself in a self-join. For example, you can use a self-join to find the products that are supplied by more than one vendor.
USE AdventureWorks;
GO
SELECT DISTINCT pv1.ProductID, pv1.VendorID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2 ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID <> pv2.VendorID
ORDER BY pv1.ProductID
Tags: Cross Join, Inner Join, Joins, Left Outer Join, Outer Join, Self Join