More SQL（Focus Subqueries、Join）

目录

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

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

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

特性	内连接（INNER JOIN）	外连接（OUTER JOIN）
匹配策略	仅保留两表中完全匹配的行	保留至少一个表的所有行，未匹配的部分用 NULL 填充
结果集大小	可能小于或等于参与连接的表的行数之和	可能等于或大于参与连接的表的行数之和
默认关键字	JOIN（等价于 INNER JOIN）	必须显式指定类型（LEFT/RIGHT/FULL）
数据完整性	丢弃未匹配的数据	保留所有数据（通过 NULL 表示无匹配）