子查询

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

子查询简介

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

-- 计算出平均值 
SELECT AVG(rental_rate) FROM film;
/* 结果
2.98
*/

-- 查找符合条件的电影
SELECT film_id, title, rental_rate FROM film WHERE rental_rate > 2.98;

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

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

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

带有 IN 运算符的子查询

如果子查询可能返回零行或多行，请使用 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 子句的子查询：

SELECT
	film_id,
	title
FROM
	film
WHERE
	film_id IN (
		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'
	);

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(子查询)

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'
    );

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 并不一样

<> ANY：

x <> ANY (a,b,c)
-- 相当于
x <> a OR x <> b OR x <> c

而 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;