OUTER JOIN

Outer joins are used to return all the data in one table, plus any matching rows in the other table. In the left outer join, the entire left table is returned along with any matching rows from the right table. If there is no matching row on the right, null values will be returned for these columns:

Building on the preceding example, there are some rows in the Product table that are not currently in inventory; therefore, there are no rows with these product IDs in the ProductInventory table. To return all the products, whether they have matching rows in the ProductInventory table or not, a left outer join can be used.

In this case, rows with no inventory will return NULL for the Quantity column:

SELECT Name AS ProductName, Quantity
FROM Production.Product
LEFT OUTER JOIN Production.ProductInventory ON Product.ProductID = ProductInventory.ProductID;

So, following on from what was covered previously in the SELECT clause section, NULL can be replaced by zeros in the results, if desired, by using an expression:

SELECT Name AS ProductName, ISNULL(Quantity, 0) AS Quantity
FROM Production.Product
LEFT OUTER JOIN Production.ProductInventory ON Product.ProductID = ProductInventory.ProductID;

In a right outer join, all the rows from the right table are returned along with any matching rows from the left table (and NULL for the left columns if no match exists):

If there are products in the ProductInventory table that are not in the Products table for some reason, a right outer join would return all the quantities whether they have a corresponding name or not, and a NULL value for the Name column if no matching row exists in the Products table:

SELECT Name AS ProductName, Quantity
FROM Production.Product
RIGHT OUTER JOIN Production.ProductInventory ON Product.ProductID = ProductInventory.ProductID;

In this case, it might be good to display the ProductID column if the Name column is null:

SELECT ISNULL(Name, ProductInventory.ProductID) AS ProductName, Quantity
FROM Production.Product
RIGHT OUTER JOIN Production.ProductInventory ON Product.ProductID = ProductInventory.ProductID;

For full outer joins, all rows are returned from both tables; if rows are matched, they are combined into a single row in the results:

Using a full outer join, all the rows from both the Product and the ProductInventory tables will be returned. The Name column will be NULL for rows that appear only in the ProductInventory table, and the Quantity column will be NULL for rows that appear only in the Product table:

SELECT ISNULL(Name, ProductInventory.ProductID) AS ProductName, ISNULL(Quantity, 0) AS Quantity
FROM Production.Product
FULL OUTER JOIN Production.ProductInventory ON Product.ProductID = ProductInventory.ProductID;