在早期的 SQL Server 版本中就有提供 UNION 運算子,用以結合多個查詢結果。 直到 SQL2005 才又加入了 EXCEPT 和 INTERSECT 運算子,以取得多個查詢結果的差集和交集。
UNION
UNION 運算子:
- 用來結合多個 SELECT 取得的集合集。
- 要結合的結果集,必須欄位數相同,且對應欄位的資料型別必須相容。
- 結合後的結果集,欄位名稱是由第一個結果集的欄位名稱決定。
SELECT CONVERT(Char(8),Employees.EmployeeID) FROM Employees UNION SELECT Customers.CustomerID FROM Customers
其它注意事項:
- 結合後的結果集,其欄位名稱是由第一個結果集的欄位名稱決定。
- ORDER BY 必須置於最後一個 SELECT 陳述式之後。
- 要撰選資料,要在每一個 SELECT 陳述式之中加入 WHERE 子句。
- 使用 ALL 關鍵子可以回傳包含重複的列。
SELECT CONVERT(Char(8),Employees.EmployeeID) as AA FROM Employees WHERE EmployeeID>9 UNION ALL SELECT Customers.CustomerID as BB FROM Customers WHERE CustomerID like 'A%' ORDER BY AA desc
例
SELECT OrderID, ProductID FROM OrderDetails WHERE OrderID=10285 OR OrderID=10294
SELECT ProductID FROM OrderDetails WHERE OrderID=10285 UNION SELECT ProductID FROM OrderDetails WHERE OrderID=10294 SELECT ProductID FROM OrderDetails WHERE OrderID=10285 UNION ALL SELECT ProductID FROM OrderDetails WHERE OrderID=10294
EXCEPT and INTERSECT
You use the EXCEPT and INTERSECT operators to perform semi-joins and anti-joins.
- semi-joins:(反聯結,交集)returns rows from the first input if there is as least one matching row in the second input.
- anti-joins:(半聯結,差集)returns rows from the first input if there are no matching rows in the second input.
例
--Except SELECT ProductID FROM OrderDetails WHERE OrderID=10285 Except SELECT ProductID FROM OrderDetails WHERE OrderID=10294 --Intersect SELECT ProductID FROM OrderDetails WHERE OrderID=10285 Intersect SELECT ProductID FROM OrderDetails WHERE OrderID=10294
APPLY
APPLY 運算子主要用來將一個子查詢結果和一個資料表值函式(table-valued funtion)做 JOIN。 運算式會將每一筆查詢結果帶入資料表值函式,然後將子查詢結果會當成 left input ,資料表值函式則當成 right input ,再執行結合。
APPLY 運算子有二種用法: CROSS APPLY 和 OUTER APPLY 。這二個都是從 SQL2005 才提供的語法。若要使用 APPLY,資料庫相容性層級至少必須是 90。
- CROSS APPLY :相當於使用 Inner Join 與資料表值函式(table-value function)執行 JOIN 查詢。
- OUTER APPLY :相當於使用 Left Outter Join 與資料表值函式(table-value function)執行 JOIN 查詢。
建立 table-value 函式
CREATE FUNCTION funOrderAmount(@OrderID int) RETURNS TABLE RETURN ( SELECT O.OrderID, SUM(UnitPrice*Quantity) as Amount FROM Orders O INNER JOIN OrderDetails OD ON O.OrderID=OD.OrderID WHERE O.OrderID=@OrderID GROUP BY O.OrderID )
使用 CROSS APPLY 取得所有訂單的總金額
SELECT O.OrderID, O.CustomerID, OA.Amount FROM Orders O CROSS APPLY funOrderAmount(O.OrderID) OA
如果使用 OUTER APPLY ,若 right table 沒有結果,就會得到 NULL
SELECT O.OrderID, O.CustomerID, OA.Amount FROM Orders O CROSS APPLY funOrderAmount(O.OrderID) OA
沒有留言:
張貼留言