[MS SQL]子查詢與查詢多個資料來源

子查詢

子查詢意指巢狀結構存在於SELECT、INSERT、UPDATE、DELETE敘述中的SELECT查詢。子查詢可在巢狀子查詢，子查詢為join關聯資料子查詢與無關聯資料使用in(某段SELECT資料來源)，在效能表現上關連子查詢會較無關聯子查詢表現較優。

-- 使用子查詢來檢查是否符合
--關連查詢
SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
WHERE EXISTS ( SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1000 AND 2000 AND
SalesOrderID = s.SalesOrderID)

--無關連子查詢
SELECT SalesPersonID,
SalesQuota CurrentSalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota IN
(SELECT MAX(SalesQuota)
FROM Sales.SalesPerson)

查詢多個資料來源(JOIN)

TIP:盡量避免join子句使用資料累行轉換例:CONVERT、CAST。替代方案為修改資料表SCHEMA，使用資料相符或事先在獨立資料表、暫存資料表、資料表變數、一般料表運算式(Common Table Expression,CTE)中做資料轉換。
INNER JOIN
INNER JOIN 為連結資料表雙方連結欄位均有資料時才會出現資料，若A表連結欄位有資料則B表無資料，則INNER JOIN不會顯示該筆資料。下例為連結用法:

-- INNER 聯結的用法

SELECT p.Name,
s.DiscountPct
FROM Sales.SpecialOffer s
INNER JOIN Sales.SpecialOfferProduct o ON
s.SpecialOfferID = o.SpecialOfferID
INNER JOIN Production.Product p ON
o.ProductID = p.ProductID
WHERE p.Name = 'All-Purpose Bike Stand'

OUTER JOIN
INNER JOIN須雙方連結欄位均有資料才會顯示，若想顯示無資料連結則需使用OUTER JOIN連結資料OUTER分LEFT與RIGHT 當A表LEFT JOIN B表，A表有資料B表無資料時則會顯示A表資料B表資料已NULL顯示若A表無資料B表有資料時則跟筆資料不會顯示，以此類推RIGHT JOIN亦如上述僅連結方向性不同。

-- OUTER 聯結的用法

SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
INNER JOIN Sales.SalesTaxRate t ON
s.StateProvinceID = t.StateProvinceID

SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
LEFT OUTER JOIN Sales.SalesTaxRate t ON
s.StateProvinceID = t.StateProvinceID

交叉(CORSS JOIN)用法
沒有WHERE子句的CROSS JOIN，傳回結果就是所謂的笛卡兒乘積，這連結不依據任何邏輯資料，純粹以數學方式一一組合。 Person.StateProvince資料表有資料181列乘上Sales.SalesTaxRate資料表29列兩者乘積為5249列。

-- 交叉(CROSS)聯結的用法

SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
CROSS JOIN Sales.SalesTaxRate t

SELF JOIN
有時你必須將一張資料表是為兩張不同的資料表個體。起因在於資料表內涵巢狀資料結構(例如員工有須向上呈報的主管)。使用自體連結必須將資料表取別名利用別名參考來做連結，靠著別名SQL SERVER會將他們視為不同個體。如下例:

-- 執行自體聯結
SELECT e.EmployeeID,
e.Title,
m.Title AS ManagerTitle
FROM HumanResources.Employee e
LEFT OUTER JOIN HumanResources.Employee m ON
e.ManagerID = m.EmployeeID

衍生資料表的使用
衍生資料表，指在FROM子劇中被當成資料表看待的SELECT敘述，衍生資料表的性能有時會優於暫存資料表。如下例:

--衍生(derived)資料表的使用

SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
INNER JOIN (SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1000 AND 2000) d ON
s.SalesOrderID = d.SalesOrderID

使用聯集(UNION)組合查詢結果
聯集(UNION)可將兩個以上的SELECT敘述聯集成單一查詢結果。每個被併入的SELECT敘述都必須擁有相同數量的欄位，而且相同順序欄位的資料型態必須一致或彼此相容。請記住UNION運算子與社會消除重複資料，如不需消除重複資料就須在UNION 後加上ALL關鍵字。如下例:

-- 使用聯集(UNION)組合查詢結果

SELECT SalesPersonID, GETDATE() QuotaDate, SalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota > 0
UNION
SELECT SalesPersonID, QuotaDate, SalesQuota
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota > 0
ORDER BY SalesPersonID DESC, QuotaDate DESC

CROSS APPLY用法

CROSS APPLY與INNERT JOIN相似唯一不同之處是CROSS APPLY不需以ON指定連結條件，而是改在函式名稱後面填入參數篩選條件。用此篩選作與INNER JOIN相似的資料篩選。

-- CROSS APPLY 的用法

CREATE FUNCTION dbo.fn_WorkOrderRouting
(@WorkOrderID int) RETURNS TABLE
AS
RETURN
SELECT WorkOrderID,
ProductID,
OperationSequence,
LocationID
FROM Production.WorkOrderRouting
WHERE WorkOrderID = @WorkOrderID
GO

SELECT w.WorkOrderID,
w.OrderQty,
r.ProductID,
r.OperationSequence
FROM Production.WorkOrder w
CROSS APPLY dbo.fn_WorkOrderRouting
(w.WorkOrderID) AS r
ORDER BY w.WorkOrderID,
w.OrderQty,
r.ProductID

OUTER APPLY用法
OUTER APPLY與OUTER JOIN相似，與CROSS APPLY相同不需以ON為指定連結條件，無法連結資料鑿使用NULL呈現。例:1.新增一筆資料造成無法對應。
2.使用CROSS APPLY因資料無法對應故無法新增資料。 3.使用OUTER APPLY，可新增資料無法對應欄位資料已NULL呈現。

--OUTER APPLY 的用法

INSERT INTO [AdventureWorks].[Production].[WorkOrder]
([ProductID]
,[OrderQty]
,[ScrappedQty]
,[StartDate]
,[EndDate]
,[DueDate]
,[ScrapReasonID]
,[ModifiedDate])
VALUES
(1,
1,
1,
GETDATE(),
GETDATE(),
GETDATE(),
1,
GETDATE())

SELECT w.WorkOrderID,
w.OrderQty,
r.ProductID,
r.OperationSequence
FROM Production.WorkOrder AS w
CROSS APPLY dbo.fn_WorkOrderRouting
(w.WorkOrderID) AS r
WHERE w.WorkOrderID IN
(SELECT MAX(WorkOrderID)
FROM Production.WorkOrder)

SELECT w.WorkOrderID,
w.OrderQty,
r.ProductID,
r.OperationSequence
FROM Production.WorkOrder AS w
OUTER APPLY dbo.fn_WorkOrderRouting
(w.WorkOrderID) AS r
WHERE w.WorkOrderID IN
(SELECT MAX(WorkOrderID)
FROM Production.WorkOrder)