Outer Joins

outer_joins.sql

--Put the predicate that determines the join condition in the ON clause (ie. p.ProductId = od.ProductId)
--Put the predicate that specifies the outer row filter in the WHERE clause

--LEFT OUTER returns all rows from the left table that match the right table and also rows from the left table that do NOT match
SELECT 
  p.Name,
  od.SalesOrderDetailId,
  od.OrderQty

FROM
  Production.Product AS p --result set preserved
  
  LEFT JOIN Sales.SalesOrderDetail AS od
  ON 
    p.ProductId = od.ProductID
  AND
    od.OrderQty > 2

ORDER BY
  p.Name,
  od.SalesORderDetailId;

--RIGHT OUTER returns all rows from the right table that match the left table and also rows from the right table that do NOT match
--FULL OUTER returns all rows from the right and left table that match and also any unmatched rows from either table