More SQL（Focus Subqueries、Join）

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

1. <tuple> IN (<subquery>) is true if and only if the tuple is a member of the relation produced by the subquery.（IN表达式正确当且仅当元组在子查询返回的结果中）
2. Opposite: <tuple> NOT IN (<subquery>).
3. 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 Beers
    WHERE manf = b1.manf AND
    name <> 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

内连接和外连接的核心区别在于对于未匹配行的处理：