目錄
Subqueries
Subqueries That Return One Tuple
Subqueries and Self Connection
The IN Operator
The Exists Operator
The Operator ANY
The Operator ALL
Union, Intersection, and Difference（交并差）
Bag Semantics
Controlling Duplicate Elimination
Inner Joins
Outer Joins
Subqueries
-
A parenthesized SELECT-FROM-WHERE statement (subquery ) can be used as a value in a number of places, including FROM and WHERE clauses.（將子查詢的結果作為一張表進行查詢）
-
Example: in place of a relation in the FROM clause, we can use a subquery and then query its result.
-
Must use a tuple-variable to name tuples of the result.（必須為子查詢的這張臨時表命名）
Subqueries That Return One Tuple
-
If a subquery is guaranteed to produce one tuple, then the subquery can be used as a value.
-
Usually, the tuple has one component.
-
A run-time error occurs if there is no tuple or more than one tuple.
例題：
??
子查詢的優先級比父查詢的優先級更高
Subqueries and Self Connection
思考題：是不是所有的額子查詢都能夠寫成連接形式，又是不是所有的連接都能寫成子查詢形式？并且思考兩者的效率如何？
The IN Operator
-
<tuple> IN (<subquery>) is true if and only if the tuple is a member of the relation produced by the subquery.（IN表達式正確當且僅當元組在子查詢返回的結果中）
-
Opposite: <tuple> NOT IN (<subquery>).
-
IN-expressions can appear in WHERE clauses.
The Exists Operator
-
EXISTS(<subquery>) is true if and only if the subquery result is not empty.
-
Example: From Beers(name, manf) , find those beers that are the unique beer by their manufacturer.
SELECT name
FROM Beers b1
WHERE NOT EXISTS (SELECT *FROM BeersWHERE manf = b1.manf ANDname <> b1.name);The Operator ANY
-
x = ANY(<subquery>) is a boolean condition that is true if x equals at least one tuple in the subquery result.（ANY語句為真當且僅當x與其中的至少一個元組相同）
= could be any comparison operator.（=可以是其他的運算符號）
Example: x >= ANY(<subquery>) means x is not the uniquely smallest tuple produced by the subquery.
-
Note tuples must have one component only.
The Operator ALL
- x <> ALL(<subquery>) is true if?for every tuple t in the relation, x is not equal to t.
- That is, x is not in the subquery result.
<> can be any comparison operator.Example: x >= ALL(<subquery>) means there is no tuple larger than x in the subquery result.
Union, Intersection, and Difference（交并差）
Union, intersection, and difference of relations are expressed by the following forms, each involving subqueries:
-
(<subquery>) UNION (<subquery>)
-
(<subquery>) INTERSECT (<subquery>)
-
(<subquery>) EXCEPT (<subquery>)
要注意的是：UNION產生的結果是一個集合，集合是不允許出現重復元素的，但如果寫法是（UNION ALL那么產生的結果是一個包，包中是允許重復元素的）
深入思考：交、并、差運算的前提應該都是排序，所以說在排序之后便直接產生了集合
Bag Semantics
- Although the SELECT-FROM-WHERE statement uses bag semantics, the default for union, intersection, and difference is set semantics.
- That is, duplicates are eliminated as the operation is applied.?
Controlling Duplicate Elimination
- Force the result to be a set by SELECT DISTINCT . . .
- Force the result to be a bag (i.e., don’t eliminate duplicates) by ALL, as in . . . UNION ALL . . .
Inner Joins
SELECT buyer_name, sales.buyer_id, qty
FROM buyers INNER JOIN sales
ON buyers.buyer_id = sales.buyer_id
Outer Joins
SELECT buyer_name, sales.buyer_id, qty
FROM buyers LEFT OUTER JOIN sales
ON buyers.buyer_id = sales.buyer_id
SELECT buyer_name, sales.buyer_id, qty
FROM sales RIGHT OUTER JOIN buyers
ON buyers.buyer_id = sales.buyer_id
內連接和外連接的核心區別在于對于未匹配行的處理：?
| 特性 | 內連接（INNER JOIN） | 外連接（OUTER JOIN） |
|---|---|---|
| 匹配策略 | 僅保留兩表中完全匹配的行 | 保留至少一個表的所有行，未匹配的部分用?NULL?填充 |
| 結果集大小 | 可能小于或等于參與連接的表的行數之和 | 可能等于或大于參與連接的表的行數之和 |
| 默認關鍵字 | JOIN（等價于?INNER JOIN） | 必須顯式指定類型（LEFT/RIGHT/FULL） |
| 數據完整性 | 丟棄未匹配的數據 | 保留所有數據（通過?NULL?表示無匹配） |
》)
)
