Outer Join

/*
使用Outer JOIN(外部連結)

CTE(Common Table Expression):暫存的檢視表，生命週期為只存在當下的Context中。
WITH (欄位名稱)
AS(
<查詢語法>
)
SELECT <欄位名稱>
FROM
*/

---使用北風資料庫
use Northwind

--列出從來沒下過訂單的客戶
--SubQuery
Select CustomerID,CompanyName
from dbo.Customers
where CustomerID not in ( Select CustomerID From dbo.Orders)
--Outer Join
Select C.CustomerID,CompanyName,OrderDate
from dbo.Customers C left join dbo.Orders O on C.CustomerID=O.CustomerID
where O.OrderDate is null

--列出1997年一月沒下訂單的客戶
--SubQuery
Select CustomerID,CompanyName
from dbo.Customers
where CustomerID not in (
Select CustomerID
From dbo.Orders
where Year(OrderDate)=1997 and Month(OrderDate)=1
)
--Outer Join
Select C.CustomerID,CompanyName
from dbo.Customers C left join dbo.Orders O
on C.CustomerID=O.CustomerID and Year(OrderDate)=1997 and Month(OrderDate)=1
where O.OrderDate is null

----列出1997年一月沒有訂單的產品
--SubQuery
select ProductName
from dbo.Products
where ProductID not in(
Select ProductID
from dbo.Orders O join dbo.[Order Details] D
on O.OrderID=D.OrderID and Year(OrderDate)=1997 and Month(OrderDate)=1
)
--Outer Join
Select ProductName
From dbo.Orders O join dbo.[Order Details] D
on O.OrderID=D.OrderID and Year(OrderDate)=1997 and Month(OrderDate)=1
right join dbo.Products P
on P.ProductID=D.ProductID
where OrderDate is null

----沒有買過已停產產品的客戶
select CompanyName
from dbo.Orders O join dbo.[Order Details] D
on O.OrderID=D.OrderID
join dbo.Products P
on P.ProductID=D.ProductID and Discontinued=1
right join dbo.Customers C
on C.CustomerID=O.CustomerID
where OrderDate is null
---使用TEMP TABLE
Select O.*
into #包含已停產產品訂單
from dbo.Orders O join dbo.[Order Details] D
on O.OrderID=D.OrderID
join dbo.Products P
on P.ProductID=D.ProductID and Discontinued=1
select CompanyName
from #包含已停產產品訂單 O right join dbo.Customers C
on C.CustomerID=O.CustomerID
where OrderDate is null
--使用CTE
with 包含已停產產品訂單
as(Select O.*
from dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID and Discontinued=1
)
select CompanyName
from 包含已停產產品訂單 O right join dbo.Customers C on C.CustomerID=O.CustomerID
where OrderDate is null

---列出沒買美國產品的美國客戶
select C.CompanyName
from dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID
join dbo.Suppliers S on S.SupplierID=P.SupplierID and S.Country='USA'
right join dbo.Customers C on C.CustomerID=O.CustomerID
where OrderDate is null and C.Country='USA'