Except

--1997/1有下訂單但1997/2月無訂單的客戶
Select distinct CustomerID
from dbo.Orders
where year(OrderDate)=1997 and month(OrderDate)=1
except
Select distinct CustomerID
from dbo.Orders
where year(OrderDate)=1997 and month(OrderDate)=2

Intersect

/*
只包括 TABLE1 和 TABLE2 中都有的行並消除所有重複行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重複行。
*/

--1997/1及1997/2月均有下訂單的客戶
Select distinct CustomerID
from dbo.Orders
where year(OrderDate)=1997 and month(OrderDate)=1
intersect
Select distinct CustomerID
from dbo.Orders
where year(OrderDate)=1997 and month(OrderDate)=2

Union(多個結果集合併)

/*
Union ---不包含重複(Distinct)
Union All ---全部
1.各結果集欄位數須相同
2.同一欄位型別需相同
*/

Select Convert(char(9),EmployeeID) as 'EmployeeID',LastName,HireDate
from Northwind.dbo.Employees
union
select emp_id,lname,hire_date
From pubs.dbo.employee

--列出產品目錄'1'中庫存量不足或安全存量不足的產品
Select ProductID,ProductName,CategoryID,UnitsInStock,UnitsOnOrder,ReorderLevel
from dbo.Products
where CategoryID=1 and (UnitsInStock＜UnitsOnOrder or UnitsInStock＜ReorderLevel )
----Union
Select ProductID,ProductName,CategoryID,UnitsInStock,UnitsOnOrder,ReorderLevel
from dbo.Products
where CategoryID=1 and UnitsInStock＜UnitsOnOrder
union
Select ProductID,ProductName,CategoryID,UnitsInStock,UnitsOnOrder,ReorderLevel
from dbo.Products
where CategoryID=1 and UnitsInStock＜ReorderLevel
---Union All
Select distinct Country
from dbo.Employees
union all ---(可允許重複)
Select distinct Country
from dbo.Customers

Cross Join(交叉聯結)

/*
Cross Join(交叉聯結):將產生聯結所牽涉到的資料表的笛卡兒乘積。
*/

Use Northwind;

Select C.CompanyName as '客戶', S.CompanyName as '貨運'
from dbo.Customers C cross join dbo.Shippers S

Self Join

/*
自我TABLE JOIN 可用於:
1.Parent-Child Relationship
2.自我比對
*/

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

---1.Parent-Child Relationship
------列出全部員工ID及其主管ID
Select EmployeeID,ReportsTo
From dbo.Employees
------列出全部員工姓名及其主管姓名
Select A.LastName as '員工' ,B.LastName as '主管'
From dbo.Employees A left join dbo.Employees B on A.ReportsTo=B.EmployeeID
------沒有主管的就是'BOSS'
Select A.LastName as '員工' ,IsNull(B.LastName,'BOSS') as '主管'
From dbo.Employees A left join dbo.Employees B on A.ReportsTo=B.EmployeeID
------使用Case When 表示'BOSS'
Select A.LastName as '員工' ,
case when B.LastName is null then 'Boss'
else B.LastName
end as '主管'
From dbo.Employees A left join dbo.Employees B ---假設A是員工,B是主管
on A.ReportsTo=B.EmployeeID ---假設條件成立(員工的主管編號=主管的員工編號)

---2.自我比對
------相同年資的員工年齡比對(年紀大在前,年紀小在後)
Select A.LastName,A.BirthDate,Datediff(month,A.HireDate,getdate())/12,B.LastName,B.BirthDate
from dbo.Employees A join dbo.Employees B ---假設A是年紀大,B是年紀小
on A.BirthDate＜B.BirthDate and Datediff(month,A.HireDate,getdate())/12=Datediff(month,B.HireDate,getdate())/12
------訂單中買相同產品,不同售價比對(大在前)
Select A.OrderID,A.ProductID,A.UnitPrice,B.OrderID,B.UnitPrice
from dbo.[Order Details] A join dbo.[Order Details] B
on A.UnitPrice>B.UnitPrice and A.ProductID=B.ProductID
-----訂單中同一客戶買相同產品,不同售價比對(大在前)
With Allorders ---CTE
as( Select O.OrderID,CustomerID,OrderDate,ProductID,UnitPrice,Quantity
From dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
)
Select A.OrderID,A.OrderDate,A.CustomerID,A.ProductID,A.UnitPrice,B.OrderID,B.OrderDate,B.UnitPrice
from Allorders A join Allorders B
on A.UnitPrice>B.UnitPrice and A.CustomerID=B.CustomerID and A.ProductID=B.ProductID

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'

Inner Join

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

/*
使用INNER JOIN
*/

---客戶訂單
Select CustomerID,CompanyName, ---From dbo.Customers
OrderDate,RequiredDate,ShippedDate ---From dbo.Orders
from dbo.Customers inner join dbo.Orders
on CustomerID=CustomerID ---A.PK=B.FK

-設TABLE別名(AS)
Select C.CustomerID,CompanyName, ---From dbo.Customers
OrderDate,RequiredDate,ShippedDate ---From dbo.Orders
from dbo.Customers as C inner join dbo.Orders as O
on C.CustomerID=O.CustomerID

-----省略 AS , INNER
Select C.CustomerID,CompanyName, ---From dbo.Customers
OrderDate,RequiredDate,ShippedDate ---From dbo.Orders
from dbo.Customers C join dbo.Orders O
on C.CustomerID=O.CustomerID
------------------------------------------------------------------------------
Select O.OrderID,OrderDate, ---From dbo.Orders
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount ---From dbo.Order Details
from dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID

--找出售價低於目前訂價訂單及其明細
Select O.OrderID,OrderDate, ---From dbo.Orders
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount ---From dbo.Order Details
From dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID
where P.UnitPrice>D.UnitPrice
-------------------------------------------------------------------------
Select O.OrderID,OrderDate, ---From dbo.Orders
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount ---From dbo.Order Details
From dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
join dbo.Products P on P.ProductID=D.ProductID
where P.UnitPrice>D.UnitPriceOrder by 訂價 desc--只有Order by 可用欄位別名，Where不可用欄位別名
-------------------
Select O.OrderID,OrderDate, ---From dbo.Orders
C.CustomerID,CompanyName, ---From dbo.Customers
E.EmployeeID,FirstName+' '+LastName as '員工', ---from dbo.Employees
P.ProductID,ProductName,P.UnitPrice as '訂價', ---From dbo.Products
D.UnitPrice as '售價',Quantity,Discount, ---From dbo.Order Details
D.UnitPrice*Quantity*(1-Discount) as '小計'
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.Customers C on C.CustomerID=O.CustomerID
join dbo.Employees E on E.EmployeeID=O.EmployeeID