子查询

本章我们将讨论如何使用 PostgreSQL 子查询来构建复杂的查询。同时会学习 IN、EXISTS、ANY、SOME、ALL 等操作。

子查询简介

假设我们想找到租金高于平均值的电影，可以分两步：

• 使用 SELECT 语句和 AVG() 函数计算出平均值
• 使用第二个 SELECT 语句，并使用前一个语句计算的平均值来查找符合条件的电影。

这种方法不太优雅，利用子查询可以将其合并成一条 SQL 语句，一步实现：

SELECT
	film_id, title, rental_rate 
FROM 
	film
WHERE
	rental_rate > (
    	SELECT AVG(rental_rate) FROM film
    );

括号内的查询称为子查询，包含子查询的查询称为外部查询。PostgreSQL 按以下步骤执行包含子查询的查询：

• 首先，执行子查询
• 其它，获取子查询的结果，并将其传递给外部查询
• 第三，执行外部查询

带有 IN 运算符的子查询

比如，要查询 2005-05-29 ~ 2005-05-30 的电影，可以使用以下查询：

SELECT
	inventory.film_id
FROM
	rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE
	return_date BETWEEN '2005-05-29'
AND '2005-05-30';

它的结果我们可以用作 WHERE 子句的子查询：

带有 EXISTS 运算符的子查询

EXISTS 的语法：

EXISTS 子查询

• 如果子查询返回一行及以上，则 EXISTS 运算符的结果为 true；否则为 false
• EXISTS 只关心子查询返回的行数，而不关心内容，所以它的常见编码约定是：EXISTS (SELECT 1 FROM 表名 WHERE 条件);

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	EXISTS (
		SELECT
			1
		FROM
			payment
		WHERE
			payment.customer_id = customer.customer_id
	);

ANY和SOME 运算符

ANY 运算符将一个值 与子查询返回的一组值进行比较：

表达式 运算符 ANY(子查询)

• 子查询必须有且只有一行结果返回。如果子查询不返回任何行，则整个查询将返回空结果集。
• ANY 前面必须有以下比较运算符之一：=、<=、>、<、>= 或 <>
• 如果子查询满足其中任意条件，ANY 运算符的结果为 true；否则为 false

SOME 是 ANY 的同义词，可以在使用 SQL 语句中使用 SOME 替换 ANY

SOME 是 ANY 的同义词，可以在使用 SQL 语句中使用 SOME 替换 ANY

以下示例返回按影片类别分组的影响的最大片长：

SELECT
    MAX( length )
FROM
    film
INNER JOIN film_category
        USING(film_id)
GROUP BY
    category_id;

你可以将上面的查询作为子查询，查询片长大于或等于最大片长的影片：

SELECT title
FROM film
WHERE length >= ANY(
    SELECT MAX( length )
    FROM film
    INNER JOIN film_category USING(film_id)
    GROUP BY  category_id );

ANY 与 IN

= ANY 相当于 IN 运算符。

比如，要查询分类为 Action 或 Drama 的电影，使用 = ANY：

SELECT
    title,
    category_id
FROM
    film
INNER JOIN film_category
        USING(film_id)
WHERE
    category_id = ANY(
        SELECT
            category_id
        FROM
            category
        WHERE
            NAME = 'Action'
            OR NAME = 'Drama'
    );

它和 IN 的结果一样：

SELECT
    title,
    category_id
FROM
    film
INNER JOIN film_category
        USING(film_id)
WHERE
    category_id IN(
        SELECT
            category_id
        FROM
            category
        WHERE
            NAME = 'Action'
            OR NAME = 'Drama'
    );

<> ANY 和 NOT IN 并不一样

而 NOT IN ：

x NOT IN (a,b,c)
-- 相当于
x <> a AND x <> b AND x <> c

ALL 运算符

ALL 运算符的语法和ANY 运算符的语法类似，它的工作原理如下：

• 表达式 > ALL (子查询)：如果表达式 某个值大于子查询返回的最大值，则结果为 true
• 表达式 >= ALL (子查询)：如果表达式某个值大于等于子查询返回的最大值，则结果为 true
• 表达式 < ALL (子查询)：如果表达式某个值小于子查询返回的最小值，则结果为 true
• 表达式 <= ALL (子查询)：如果表达式某个值小于等于子查询返回的最小值，则结果为 true
• 表达式 = ALL (子查询)：如果表达式某个值等于子查询返回的任何值，则结果为 true
• 表达式 <> ALL (子查询)：如果表达式某个值不等于子查询返回的任何值，则结果为 true
• 如果子查询不返回任何行，则 ALL 的计算结果永远为 true

以下查询返回按电影评级分组的所有电影的平均时长：

SELECT
    ROUND(AVG(length), 2) avg_length
FROM
    film
GROUP BY
    rating
ORDER BY
    avg_length DESC;

要查询时长大于上述平均时长的电影，可以使用 > ALL：

SELECT
    film_id,
    title,
    length
FROM
    film
WHERE
    length > ALL (
            SELECT
                ROUND(AVG (length),2)
            FROM
                film
            GROUP BY
                rating
    )
ORDER BY
    length;