PostgreSQL中含连接与多表WHERE条件的CTE使用问题及替代方案咨询

嘿，刚好对PostgreSQL的这些用法比较熟，来给你拆解下你的问题：

1. 能不能在CTE中用多表WHERE条件替代INNER JOIN？ #

完全可以！从逻辑上来说，用WHERE子句连接多表的等值条件，和INNER JOIN的效果是完全等价的，PostgreSQL的查询优化器通常会把这两种写法优化成相同的执行计划。比如下面这两个CTE的效果是一模一样的：

用INNER JOIN的写法：

WITH user_orders AS (
    SELECT u.id, u.name, o.order_date, o.amount
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE o.amount > 100
)
SELECT * FROM user_orders;

用WHERE条件替代的写法：

WITH user_orders AS (
    SELECT u.id, u.name, o.order_date, o.amount
    FROM users u, orders o
    WHERE u.id = o.user_id AND o.amount > 100
)
SELECT * FROM user_orders;

不过要注意两个点：

• 这种替代只适用于INNER JOIN，如果是LEFT/RIGHT JOIN，用WHERE条件过滤会把不匹配的行直接过滤掉，等同于INNER JOIN的效果，这时候就不能替代了。
• 从可读性和维护性来说，优先用JOIN语法，尤其是当关联条件多、表数量多的时候，JOIN能更清晰地表达表之间的关联关系，不容易出错。

2. 除了CTE，还有哪些保存多表查询结果的方式？ #

你说的没错，PostgreSQL里的变量（比如SELECT ... INTO variable）只能存储单行单值，确实满足不了多表结果的存储需求。这里给你几个常用的替代方案：

临时表（TEMP TABLE） #

临时表是会话级别的，只会在当前数据库连接中存在，关闭连接后自动销毁，适合存储临时的多表查询结果，而且可以多次复用：

-- 创建临时表存储查询结果
CREATE TEMP TABLE temp_user_orders AS
SELECT u.id, u.name, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

-- 后续可以多次查询这个临时表
SELECT * FROM temp_user_orders WHERE order_date > '2024-01-01';

物化视图（MATERIALIZED VIEW） #

如果你的查询结果不需要实时更新，只是用来做统计、报表这类场景，物化视图是个不错的选择——它会把查询结果物理存储在磁盘上，比每次重新查询更快。需要更新的时候手动刷新即可：

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_user_orders AS
SELECT u.id, u.name, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

-- 查询物化视图
SELECT * FROM mv_user_orders;

-- 当源表数据变化后，刷新物化视图
REFRESH MATERIALIZED VIEW mv_user_orders;

子查询（Subquery） #

如果只是一次性使用查询结果，不需要重复引用，子查询是最简单的方式，虽然它不能像CTE或临时表那样复用，但写法简洁：

SELECT *
FROM (
    SELECT u.id, u.name, o.order_date, o.amount
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE o.amount > 100
) AS user_orders
WHERE order_date > '2024-01-01';

另外补充一点：PostgreSQL 12及以上版本中，CTE默认是“内联”的（也就是优化器会把CTE的逻辑合并到主查询中），如果你想强制CTE物化存储结果，可以用MATERIALIZED关键字：

WITH user_orders AS MATERIALIZED (
    SELECT u.id, u.name, o.order_date, o.amount
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE o.amount > 100
)
SELECT * FROM user_orders;

内容的提问来源于stack exchange，提问作者mercury