SQL高級查詢方法

  • 2020 年 2 月 18 日
  • 筆記

正文共：5024 字 2 圖 預計閱讀時間：14 分鐘

本文目錄：

  • 4.8 子查詢 subquery
  • 4.9 聯接 join
  • 4.10 UNION運算符
  • 4.11 EXCEPT和INTERSECT半聯接
  • 4.12 公用表表達式 WITH

4.8 子查詢 subquery

子查詢是一個嵌套在 SELECT、INSERT、UPDATE 或 DELETE 語句或其他子查詢中的查詢。任何允許使用表達式的地方都可以使用子查詢。

子查詢也稱為內部查詢或內部選擇，而包含子查詢的語句也稱為外部查詢或外部選擇。

有三種基本的子查詢。它們是：

  • 在通過 IN 或由 ANY 或 ALL 修改的比較運算符引入的列表上操作。WHERE expression [NOT] IN (subquery)
  • 通過未修改的比較運算符引入且必須返回單個值。WHERE expression comparison_operator [ANY | ALL] (subquery)
  • 通過 EXISTS 引入的存在測試。WHERE [NOT] EXISTS (subquery)

許多包含子查詢的 Transact-SQL 語句都可以改用聯接表示。其他問題只能通過子查詢提出。

在 Transact-SQL 中，包含子查詢的語句和語義上等效的不包含子查詢的語句（即聯接的方式）在性能上通常沒有差別。但是，在一些必須檢查存在性的情況中，使用聯接會產生更好的性能。否則，為確保消除重複值，必須為外部查詢的每個結果都處理嵌套查詢。所以在這些情況下，聯接方式會產生更好的效果。

子查詢的 SELECT 查詢總是使用圓括弧括起來。它不能包含 COMPUTE 或 FOR BROWSE 子句，如果同時指定了 TOP 子句，則只能包含 ORDER BY 子句。

子查詢受下列限制的制約：

  • 通過比較運算符引入的子查詢選擇列表只能包括一個表達式或列名稱（對 SELECT * 執行的 EXISTS 或對列表執行的 IN 子查詢除外）。
  • 如果外部查詢的 WHERE 子句包括列名稱，它必須與子查詢選擇列表中的列是聯接兼容的。
  • ntext、text 和 image 數據類型不能用在子查詢的選擇列表中。
  • 由於必須返回單個值，所以由未修改的比較運算符（即後面未跟關鍵字 ANY 或 ALL 的運算符）引入的子查詢不能包含 GROUP BY 和 HAVING 子句。
  • 包含 GROUP BY 的子查詢不能使用 DISTINCT 關鍵字。
  • 不能指定 COMPUTE 和 INTO 子句。
  • 只有指定了 TOP 時才能指定 ORDER BY。
  • 不能更新使用子查詢創建的視圖。
  • 按照慣例，由 EXISTS 引入的子查詢的選擇列表有一個星號 (*)，而不是單個列名。因為由 EXISTS 引入的子查詢創建了存在測試並返回 TRUE 或 FALSE 而非數據，所以其規則與標準選擇列表的規則相同。

子查詢的例子可以參考筆試題中的例子，SQL筆試50題（上），SQL筆試50題（下）

4.9 聯接 join

通過聯接，可以從兩個或多個表中根據各個表之間的邏輯關係來檢索數據。

聯接條件可通過以下方式定義兩個表在查詢中的關聯方式：

  • 指定每個表中要用於聯接的列。典型的聯接條件在一個表中指定一個外鍵，而在另一個表中指定與其關聯的鍵。
  • 指定用於比較各列的值的邏輯運算符（例如 = 或 <>）。

可以在 FROM 或 WHERE 子句中指定內部聯接；而只能在 FROM 子句中指定外部聯接。聯接條件與 WHERE 和 HAVING 搜索條件相結合，用於控制從 FROM 子句所引用的基表中選定的行。

比如下列聯接因為是內部聯接，因此也可以改寫為在WHERE條件中指定聯接。

 1-- FROM中指定聯接（首選）   2SELECT pv.ProductID, v.BusinessEntityID, v.Name   3FROM Purchasing.ProductVendor AS pv   4JOIN Purchasing.Vendor AS v   5    ON (pv.BusinessEntityID = v.BusinessEntityID)   6WHERE StandardPrice > 10   7    AND Name LIKE N'F%';   8-- WHERE中指定聯接   9SELECT pv.ProductID, v.BusinessEntityID, v.Name  10FROM Purchasing.ProductVendor AS pv, Purchasing.Vendor AS v  11WHERE pv.VendorID = v.VendorID  12    AND StandardPrice > 10  13    AND Name LIKE N'F%';

在 FROM 子句中指定聯接條件有助於將這些聯接條件與 WHERE 子句中可能指定的其他任何搜索條件分開，建議用這種方法來指定聯接。簡化的 ISO FROM 子句聯接語法如下：

1FROM first_table  2join_type  3second_table  4[ON (join_condition)]

join_type 指定要執行的聯接類型

  • 內部聯接（典型的聯接運算，使用類似於 = 或 <> 的比較運算符）。內部聯接包括同等聯接和自然聯接。
  • 外部聯接。外部聯接可以是左向外部聯接、右向外部聯接或完整外部聯接。 在 FROM 子句中可以用下列某一組關鍵字來指定外部聯接：
    • LEFT JOIN 或 LEFT OUTER JOIN。 左向外部聯接的結果集包括 LEFT OUTER 子句中指定的左表的所有行，而不僅僅是聯接列所匹配的行。如果左表的某一行在右表中沒有匹配行，則在關聯的結果集行中，來自右表的所有選擇列表列均為空值。
    • RIGHT JOIN 或 RIGHT OUTER JOIN 右向外部聯接是左向外部聯接的反向聯接。將返回右表的所有行。如果右表的某一行在左表中沒有匹配行，則將為左表返回空值。
    • FULL JOIN 或 FULL OUTER JOIN 完整外部聯接將返回左表和右表中的所有行。當某一行在另一個表中沒有匹配行時，另一個表的選擇列表列將包含空值。如果表之間有匹配行，則整個結果集行包含基表的數據值。
  • 交叉聯接 交叉聯接將返回左表中的所有行。左表中的每一行均與右表中的所有行組合。交叉聯接也稱作笛卡爾積。

join_condition 定義用於對每一對聯接行進行求值的謂詞（比較運算符或關係運算符）。

當 SQL Server 處理聯接時，查詢引擎會從多種可行的方法中選擇最有效的方法來處理聯接。由於各種聯接的實際執行過程會採用多種不同的優化，因此無法可靠地預測。

聯接的例子可以參考筆試題中的例子，SQL筆試50題（上），SQL筆試50題（下），在筆試題中有大量的內聯接和左聯接的例子。

4.10 UNION運算符

UNION 運算符可以將兩個或多個 SELECT 語句的結果組合成一個結果集。

UNION 的結果集列名與 UNION 運算符中第一個 SELECT 語句的結果集中的列名相同。另一個 SELECT 語句的結果集列名將被忽略。

默認情況下，UNION 運算符將從結果集中刪除重複的行。如果使用 ALL （即UNION ALL）關鍵字，那麼結果中將包含所有行而不刪除重複的行。

使用 UNION 運算符時需遵循下列準則：

  • 在用 UNION 運算符組合的語句中，所有選擇列表中的表達式（如列名稱、算術表達式、聚合函數等）數目必須相同。
  • 用 UNION 組合的結果集中的對應列或各個查詢中所使用的任何部分列都必須具有相同的數據類型，並且可以在兩種數據類型之間進行隱式數據轉換，或者可以提供顯式轉換。例如，datetime 數據類型的列和 binary 數據類型的列之間的 UNION 運算符將不執行運算，直到進行了顯式轉換。但是，money 數據類型的列和 int 數據類型的列之間的 UNION 運算符將執行運算，因為它們可以進行隱式轉換。
  • 用 UNION 運算符組合的各語句中對應結果集列的順序必須相同，因為 UNION 運算符按照各個查詢中給定的順序一對一地比較各列。
  • 表中通過 UNION 運算所得到的列名稱是從 UNION 語句中的第一個單獨查詢得到的。若要用新名稱引用結果集中的某列（例如在 ORDER BY 子句中），必須按第一個 SELECT 語句中的方式引用該列 SELECT city AS Cities FROM stores_west UNION SELECT city FROM stores_east ORDER BY city

4.11 EXCEPT和INTERSECT半聯接

使用 EXCEPT 和 INTERSECT 運算符可以比較兩個或更多 SELECT 語句的結果並返回非重複值。

  • EXCEPT 運算符返回由 EXCEPT 運算符左側的查詢返回、而又不包含在右側查詢所返回的值中的所有非重複值。（左邊結果與 左右兩邊結果的交集的差集 A-A∩B）
  • INTERSECT 返回由 INTERSECT 運算符左側和右側的查詢都返回的所有非重複值。（兩個查詢結果的並集然後去重後的結果，A∪B）

使用 EXCEPT 或 INTERSECT 比較的結果集必須具有相同的結構。它們的列數必須相同，並且相應的結果集列的數據類型必須兼容。

INTERSECT 運算符優先於 EXCEPT。

4.12 公用表表達式 WITH

公用表表達式 (CTE) 可以認為是在單個 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句的執行範圍內定義的臨時結果集。CTE 與派生表類似，具體表現在不存儲為對象，並且只在查詢期間有效。與派生表的不同之處在於，CTE 可自引用，還可在同一查詢中引用多次。

CTE 可用於：

  • 創建遞歸查詢。
  • 在不需要常規使用視圖時替換視圖，也就是說，不必將定義存儲在元數據中。
  • 啟用按從標量嵌套 select 語句派生的列進行分組，或者按不確定性函數或有外部訪問的函數進行分組。
  • 在同一語句中多次引用生成的表。

使用 CTE 可以獲得提高可讀性和輕鬆維護複雜查詢的優點。查詢可以分為單獨塊、簡單塊、邏輯生成塊。之後，這些簡單塊可用於生成更複雜的臨時 CTE，直到生成最終結果集。

可以在用戶定義的常式（如函數、存儲過程、觸發器或視圖）中定義 CTE。

CTE 由表示 CTE 的表達式名稱、可選列列表和定義 CTE 的查詢組成。定義 CTE 後，可以在 SELECT、INSERT、UPDATE 或 DELETE 語句中對其進行引用，就像引用表或視圖一樣。CTE 也可用於 CREATE VIEW 語句，作為定義 SELECT 語句的一部分。

CTE 的基本語法結構如下：

1WITH expression_name [ ( column_name [,...n] ) ]  2AS  3( CTE_query_definition )  4-- 運行 CTE 的語句  5SELECT <column_list>  6FROM expression_name;
 1-- 定義 CTE 查詢別名和列名稱   2WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)   3AS   4-- 定義CTE查詢的結果集   5(   6    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear   7    FROM Sales.SalesOrderHeader   8    WHERE SalesPersonID IS NOT NULL   9)  10-- 使用CTE查詢的結果進行進一步的查詢  11SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear  12FROM Sales_CTE  13GROUP BY SalesYear, SalesPersonID  14ORDER BY SalesPersonID, SalesYear;

本文項目地址：

https://github.com/firewang/sql50

(喜歡的話，Star一下)

閱讀原文，或者訪問該鏈接可以在線觀看（該系列將更新至GitHub,並且託管到read the docs）

https://sql50.readthedocs.io/zh_CN/latest/

參考網址：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms175995(v=sql.105)

VirMach 便宜 VPS

QNews

QNews