Transact SQL 101 - Lesson 05_03_SQL.txt

USE adventureworkslt
GO

SELECT * FROM saleslt.salesorderheader
SELECT * FROM saleslt.customer 

--OUTER JOIN (Example 1)
SELECT 
    c.customerid, c.companyname, c.emailaddress, s.salesorderid, s.totaldue
FROM saleslt.customer c
  INNER JOIN saleslt.salesorderheader s ON c.customerid = s.customerid
  LEFT OUTER JOIN saleslt.salesorderheader s ON c.customerid = s.customerid
  
--look at all productcategories and the products associated with them...
--LEFT OUTER JOIN (Example 2)
SELECT 
    c.productcategoryid, c.name AS 'Category Name', p.productid, p.name, p.listprice
FROM saleslt.productcategory c
  LEFT OUTER JOIN saleslt.product p ON c.productcategoryid = p.productcategoryid

--RIGHT OUTER JOIN (Example 3)
SELECT p.productid, p.productnumber, p.listprice, m.productmodelid, m.name AS ModelName
FROM saleslt.product p
  RIGHT OUTER JOIN saleslt.productmodel m ON p.productmodelid = m.productmodelid
ORDER BY p.productid

  
--FULL OUTER JOIN (Example 4)
SELECT c.companyname, c.emailaddress, s.salesorderid, s.orderdate, s.totaldue, s.customerid
FROM saleslt.customer c
  FULL OUTER JOIN saleslt.salesorderheader s ON c.customerid = s.customerid
--WHERE s.salesorderid IS NULL

--OUTER JOIN SYNTAX - No Longer Supported (Example 5)
--LEFT OUTER JOIN
SELECT c.customerid, c.companyname, c.emailaddress, s.salesorderid, s.totaldue
FROM saleslt.customer c 
  LEFT OUTER JOIN saleslt.salesorderheader s ON c.customerid = s.customerid

SELECT c.customerid, c.companyname, c.emailaddress, s.salesorderid, s.totaldue
FROM saleslt.customer c, saleslt.salesorderhearder s
WHERE c.customerid *= s.customerid

--RIGHT OUTER JOIN
SELECT c.customerid, c.companyname, c.emailaddress, s.salesorderid, s.totaldue
FROM saleslt.customer c 
  RIGHT OUTER JOIN saleslt.salesorderheader s ON c.customerid = s.customerid

SELECT c.customerid, c.companyname, c.emailaddress, s.salesorderid, s.totaldue
FROM saleslt.customer c, saleslt.salesorderheader s 
WHERE c.customerid =* s.customerid