跳轉到內容

結構化查詢語言/SELECT: 子查詢

Add links
來自華夏公益教科書，開放的書籍，開放的世界
SELECT: CASE 表示式 結構化查詢語言
SELECT: 子查詢 		檢視



子查詢是一個完整的 SELECT 命令，用於另一個 SELECT、UPDATE、INSERT 或 DELETE 命令中。它與簡單的 SELECT 命令唯一的區別在於它被括在圓括號中。

分類

[編輯 | 編輯原始碼]

根據建立結果的型別，子查詢有三種類型

  • 標量值子查詢: 子查詢返回單個值，例如: (SELECT max(weight) FROM person)。
  • 行子查詢: 子查詢返回單個包含一個或多個值的行，例如: (SELECT min(weight), max(weight) FROM person)。
  • 表子查詢: 子查詢返回一個行的列表，即一個表，例如: (SELECT lastname, weight FROM person)。在分類中，結果列表中包含零行、一行或多行都沒有區別。表子查詢和行子查詢的區別在於 **可能** 出現多行。

每種型別都可以用於其所代表的型別可能出現的所有位置：標量值子查詢可以在單個值可能出現的位置使用，行子查詢可以在單個行可能出現的位置使用，表子查詢可以在表可能出現的位置使用。此外，表子查詢可以作為 EXISTS、IN、SOME、ANY 或 ALL 謂詞的引數。

獨立於這種分類，子查詢可以是 相關子查詢 或 非相關子查詢。相關子查詢與周圍的查詢相關聯，因為它們在子查詢中使用周圍查詢的值。非相關子查詢獨立於周圍的查詢。這種區別將在 下一章 中詳細說明，但也適用於其他兩個子查詢類。

由於相關子查詢使用由周圍查詢確定的值，這些值可能隨行而改變，因此子查詢在概念上被執行的次數與周圍查詢的查詢結果行數相同。這可能會導致效能問題。儘管如此，相關子查詢仍然是一個常用的結構。在許多情況下，存在使用 JOIN 的等效結構。哪一個表現出更好的效能高度依賴於 DBMS，以及涉及的行數、索引的存在以及更多變數。

標量值子查詢

[編輯 | 編輯原始碼]

第一個例子建立了一個姓氏、體重和所有人的平均體重的列表。

SELECT id,
       lastname,
       weight,
       (SELECT avg(weight) FROM person) AS 'avg_weight'  -- this is the subquery
FROM   person
ORDER BY lastname;

因為子查詢使用了 avg() 函式，所以 SQL 編譯器知道它將返回一個唯一的值。因此，它的型別是 標量值子查詢，可以在標量值可能出現的位置使用，例如 SELECT 和 FROM 之間的列表。

在下一個示例中，子查詢被用作 WHERE 子句中值的代理。

-- Persons who weigh more than the average of all persons
SELECT id, lastname, weight
FROM   person
WHERE  weight >= (SELECT avg(weight) FROM person)   -- another position for the subquery
ORDER BY lastname;

這兩個示例都使用了兩次 person 表。也可以使用不同的表。子查詢中的表名與周圍查詢中的表名之間沒有依賴關係。這適用於所有型別的相關和非相關子查詢。子查詢可以從任何其他表中檢索任何值，例如聯絡人的數量。

這兩個例子展示了非相關子查詢，這意味著子查詢獨立於它們嵌入的查詢。它們只執行一次。

但應用程式經常會遇到需要子查詢使用外部查詢的值（類似於使用引數的子例程）的情況。這種子查詢稱為相關子查詢。例如，下一個查詢列出了人員及其家庭的平均體重。

SELECT id, firstname, lastname, weight,
       (SELECT avg(weight)
        FROM   person sq                -- 'sq' is an arbitrary alias name for the table in the subquery
        WHERE  sq.lastname = p.lastname -- identify the inner and outer table by its alias names
       ) family_average                 -- an arbitrary alias name for the computed family average
FROM   person p                         -- 'p' is an arbitrary alias name for the table in the surrounding query
ORDER BY lastname, weight;

子查詢在每次從周圍的 SELECT 中獲取一行作為引數時，名稱為 'p'。在子查詢中，行 'p' 的所有列都是已知的，並且可以使用。這裡，來自外部行的姓氏在子查詢中用於查詢家族中的所有人以及家族成員的平均體重。

注意：相關子查詢為周圍查詢的每一行執行一次。因此，它們的成本遠高於非相關子查詢。可能存在使用 JOIN 或 GROUP BY 的等效解決方案，該解決方案具有更好的效能。DBMS 的查詢最佳化器可能會在內部將給定的子查詢重新排列為其中一種等效形式。但這並不適用於所有情況。

相關和非相關子查詢之間的區別是通用的。它也適用於其他子查詢類。

行子查詢

[編輯 | 編輯原始碼]

這個示例檢索一個或多個人員，他們的名字是所有名字中最小（按照詞典順序）的，他們的姓氏是所有姓氏中最小的那一個。由於 AND 條件的存在，可能沒有找到任何人員。

-- One resulting row: Elias Baker
SELECT *
FROM   person
WHERE  (firstname, lastname) = (SELECT MIN(firstname), MIN(lastname) FROM person);

在子查詢中，檢索到最小的名字和姓氏。min() 函式的使用保證不會產生超過包含兩列的一行，因此它是行子查詢。在周圍的查詢中，這個中間結果與完整表 person 的每一行進行比較，或者，如果存在，則使用索引。

幸運的是，命令檢索到一行。在大多數情況下，最小的名字和姓氏來自不同的人。但即使在這些情況下，命令在語法上也是正確的，也不會丟擲任何異常。

在下一個示例中，檢索到每個家族中名字和姓氏最小的那些人。要做到這一點，需要使用相關行子查詢。

-- 7 rows, one per family
SELECT *
FROM   person p
WHERE (firstname, lastname) =
  (SELECT MIN(firstname), MIN(lastname) FROM person sq where p.lastname = sq.lastname);

同樣，person 表有兩個化身，一個在周圍的查詢中別名為 'p'，另一個在子查詢中別名為 'sq'。子查詢為周圍查詢的每一行結果執行一次，因為 'p.lastname' 可能會隨著 'p' 的每一行而改變。

每個家族中至少有一個滿足條件的人，也可能有多個人滿足條件。

表子查詢

[編輯 | 編輯原始碼]

下一個例子檢索擁有聯絡人的那些人。子查詢的類別是：非相關表子查詢（用作 IN 謂詞中的條件）。

SELECT * 
FROM   person 
WHERE  id IN 
  (SELECT person_id FROM contact);  -- the subquery

子查詢建立多行，每行包含一列。這構成了一個新的中間表。因此，這個例子是表子查詢。

IN 運算子能夠作用於這個中間表。相反，不能對這種中間結果使用像 '=' 或 '>' 這樣的運算子。在這種情況下，SQL 編譯器會識別出語法錯誤。

下一個示例是第一個示例的擴充套件。它透過請求電子郵件地址中的姓氏來新增查詢和子查詢之間的相關性標準。

-- A correlated table subquery, looking for lastnames within e-mail-addresses
SELECT * 
FROM   person p
WHERE  id IN 
  (SELECT person_id 
   FROM   contact c
   WHERE  c.contact_type = 'email'
   AND    UPPER(c.contact_value) LIKE CONCAT(CONCAT('%', UPPER(p.lastname)), '%'));

AND 之後的最後一個比較有點複雜。它使用 CONCAT() 和 UPPER() 函式以及 LIKE 謂詞，但這與當前主題 '子查詢' 無關。重要的是子查詢引用了周圍查詢的 'p.lastname'。只有 Goldstein 先生滿足了當兩列不區分大小寫進行比較時，他的電子郵件地址包含他的姓氏的標準。

備註：CONCAT() 連線兩個字串。UPPER() 將字串轉換為大寫。LIKE 與 '%' 符號一起使用來查詢另一個字串中的一個字串。


接下來，有一個非相關表子查詢被連線操作的例子。

-- Persons plus maximum weight of their family
SELECT *
FROM   person p
JOIN   (SELECT lastname, max(weight) max_fam_weight
        FROM   person
        GROUP BY lastname
       ) AS sq ON p.lastname = sq.lastname   -- join criterion between subquery table 'sq' and table 'p'
;

另一個例子

[編輯 | 編輯原始碼]

這個例子展示瞭解決一個常見問題的方案。有時，有一些行描述了實體的過時狀態。這些行——針對同一個邏輯實體——在某些列中彼此不同，並且還有一個額外的 version 列來跟蹤時間流。

這裡有 booking 示例表及其資料。

-- The table holds actual and historical values
CREATE TABLE booking (
  -- identifying columns
  id             DECIMAL      NOT NULL,
  booking_number DECIMAL      NOT NULL,
  version        DECIMAL      NOT NULL,
  -- describing columns
  state          CHAR(10)     NOT NULL,
  enter_ts       TIMESTAMP    NOT NULL,
  enter_by       CHAR(20)     NOT NULL,
  -- ...
  -- select one of the defined columns as the Primary Key
  CONSTRAINT booking_pk PRIMARY KEY (id),
  -- forbid duplicate recordings
  CONSTRAINT booking_unique UNIQUE (booking_number, version)
);

-- Add data
INSERT INTO booking VALUES (1, 4711, 1, 'created',   TIMESTAMP'2014-02-02 10:01:01', 'Emily');
INSERT INTO booking VALUES (2, 4711, 2, 'modified',  TIMESTAMP'2014-02-03 11:10:01', 'Emily');
INSERT INTO booking VALUES (3, 4711, 3, 'canceled',  TIMESTAMP'2014-02-10 09:01:01', 'John');
--
INSERT INTO booking VALUES (4, 4712, 1, 'created',   TIMESTAMP'2014-03-10 12:12:12', 'Emily');
INSERT INTO booking VALUES (5, 4712, 2, 'delivered', TIMESTAMP'2014-03-12 06:01:00', 'Charles');
--
INSERT INTO booking VALUES (6, 4713, 1, 'created',   TIMESTAMP'2014-03-11 08:50:02', 'Emily');
INSERT INTO booking VALUES (7, 4713, 2, 'canceled',  TIMESTAMP'2014-03-12 08:40:12', 'Emily');
INSERT INTO booking VALUES (8, 4713, 3, 'reopend',   TIMESTAMP'2014-03-13 10:04:32', 'Jack');
INSERT INTO booking VALUES (9, 4713, 4, 'delivered', TIMESTAMP'2014-03-15 06:40:12', 'Jack');
--
COMMIT;


問題是檢索所有 實際 行，即每個預訂中版本號最高的那些行。如果預訂具有相同的預訂號，則認為預訂是相同的。

第一個解決方案使用非相關表子查詢。

SELECT *
FROM   booking b
WHERE  (booking_number, version) IN
  (SELECT booking_number, MAX(version) FROM booking sq GROUP BY booking_number) -- the subquery
ORDER BY booking_number;

子查詢建立了一個包含預訂號及其最高版本的列表。這個列表被周圍的查詢用於檢索具有所有列的所需行。


第二個解決方案使用相關標量值子查詢。

SELECT *
FROM   booking b
WHERE  version =
  (SELECT max(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
ORDER BY booking_number;

周圍的查詢檢索表中的所有行。對於其中的每一行，它呼叫子查詢，該子查詢檢索此預訂號內的最高版本。在大多數情況下，此最高版本與實際行的版本不同，並且由於 '=' 運算子的存在，這些行不是結果的一部分。只有那些其版本等於子查詢中確定的值（並且其預訂號與子查詢中使用的預訂號相同）的行是最終結果的一部分。


介紹問題的另一種方式可能是隻檢索一個特定預訂的歷史行（所有版本，除了最高版本）。

SELECT *
FROM   booking b
WHERE  version !=
  (SELECT max(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
AND    booking_number = 4711
ORDER BY version;

周圍的查詢將行限制為一個特定預訂的那些行。子查詢只為這些行呼叫。


很容易陷入陷阱

-- Unexpected result!
SELECT *
FROM   booking b
WHERE  version != (SELECT max(version) FROM booking)
AND    booking_number = 4711
ORDER BY version;

上面的查詢返回了所有版本號為 4711 的預訂，包括當前版本！為了獲得預期的結果，需要將周圍的查詢和子查詢關聯起來。

練習

[編輯 | 編輯原始碼]

查詢擁有最多版本的預訂。

點選檢視解決方案
-- The subselect return exactly ONE single value. Therefor it's a (non-correlated) single value subquery.
-- But this is only a intermediate result. The final result may contain several rows, which is not the case in our example database!
SELECT *
FROM   booking
WHERE  version = (SELECT MAX(version) FROM booking);

查詢所有已取消的預訂（在最新版本中）。

點選檢視解決方案
-- It's necessary to link the subquery with the surrounding query.
SELECT *
FROM   booking b
WHERE  version =
  (SELECT MAX(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
AND    state = 'canceled';

-- Additionally within the resulting rows there must be a correlation between the version and the state.
-- This is accomplished with the AND keyword at the level of the surrounding query. If the AND works within
-- the subquery, the result does not meet the expectations.
SELECT *
FROM   booking b
WHERE  version =
  (SELECT MAX(version) FROM booking sq WHERE sq.booking_number = b.booking_number AND state = 'canceled');

建立所有人員列表。對於每個人，請包含與該人生在同一城市的其他人數量。

點選檢視解決方案
-- The subselect uses the place_of_birth of the outside row. Therefore it's a correlated subquery.
SELECT firstname,
       lastname,
       place_of_birth,
       (SELECT COUNT(*) FROM person sq WHERE p.place_of_birth = sq.place_of_birth) cnt -- an arbitrary name for the additional column
FROM   person p;

建立所有人員列表以及他們的聯絡資訊數量。

點選檢視解決方案
-- The subselect uses the ID of the outside row. Therefore it's a correlated subquery.
SELECT firstname,
       lastname,
       (SELECT COUNT(*) FROM contact c WHERE p.id = c.person_id) cnt -- an arbitrary name for the additional column
FROM   person p;

建立所有人員列表以及他們的電子郵件地址數量。

點選檢視解決方案
SELECT firstname,
       lastname,
       (SELECT COUNT(*)
        FROM   contact c
        WHERE  p.id = c.person_id
        AND    contact_type = 'email'  -- The subselect is a complete SELECT. Therefor all elements of
                                       -- a 'regular' SELECT may be used: Join, functions, ... and: SUBSELECT
       ) cnt                           -- an arbitrary name of the additional column
FROM   person p;

建立所有人員列表以及他們的聯絡資訊數量。（與上一個問題相同。）
用 JOIN 結構替換子查詢。

點選檢視解決方案
-- Step 1 (for demonstration purpose only): To retrieve ALL persons, it's necessary to use an OUTER JOIN
SELECT firstname,
       lastname,
       c.contact_type
FROM   person p
LEFT OUTER JOIN contact c ON p.id = c.person_id;
-- 
-- Step 2 (complete solution): Add the counter. To do so, the result must be grouped.
SELECT firstname,
       lastname,
       count(c.contact_type)
FROM   person p
LEFT OUTER JOIN contact c ON p.id = c.person_id
GROUP BY firstname, lastname;

哪些人員沒有聯絡資訊？

點選檢視解決方案
-- The subquery returns more than one row. Therefore it's a table subquery.
SELECT firstname, lastname
FROM   person
WHERE  id NOT IN (SELECT person_id FROM contact);  -- the subquery


SELECT: CASE 表示式 結構化查詢語言
SELECT: 子查詢 		檢視
檢索自 "https://wikibook.tw/w/index.php?title=Structured_Query_Language/SELECT:_Subquery&oldid=3819559"
華夏公益教科書