PostgreSQL多表关联查询：单语句跨表检索及有效性疑问

嘿，我来帮你搞定这个问题！

一、单条SQL实现需求的几种方式 #

你可以通过子查询、CTE（公共表表达式）或者直接关联users表来实现用邮箱获取user_id后关联其他表的需求，这里给你几个实用的写法：

1. 用CTE（最清晰易读）

先把目标用户的user_id查出来存为临时结果，再用它过滤关联表：

WITH target_user AS (
    SELECT user_id FROM users WHERE email = 'bob@example.com'
)
SELECT * 
FROM table1 
JOIN table2 ON table1.user_id = table2.user_id
WHERE table1.user_id IN (SELECT user_id FROM target_user)
   OR table2.user_id IN (SELECT user_id FROM target_user);

2. 直接关联users表（避免重复子查询）

这种方式更高效，直接把users表和后续表关联，一次获取所有数据：

SELECT t1.*, t2.*
FROM users u
JOIN table1 t1 ON u.user_id = t1.user_id
JOIN table2 t2 ON u.user_id = t2.user_id OR t1.user_id = t2.user_id
WHERE u.email = 'bob@example.com';

3. 简单子查询写法

如果你的数据库版本不支持CTE，用子查询也能搞定：

SELECT * 
FROM table1 
JOIN table2 ON table1.user_id = table2.user_id
WHERE table1.user_id = (SELECT user_id FROM users WHERE email = 'bob@example.com')
   OR table2.user_id = (SELECT user_id FROM users WHERE email = 'bob@example.com');

二、关于JOIN执行的疑问 #

你提到的JOIN默认是INNER JOIN，它的逻辑是只保留两个表中同时满足连接条件的行。如果目标user_id不存在于table1中：

• 用当前的INNER JOIN table1 ON table1.user_id = table2.user_id语句，会因为table1里没有匹配的行，导致最终返回空结果。

如果想要即使table1里没有这个user_id，也能返回table2中对应的行，你需要改成RIGHT JOIN（保留table2的所有匹配行）：

WITH target_user AS (
    SELECT user_id FROM users WHERE email = 'bob@example.com'
)
SELECT * 
FROM table1 
RIGHT JOIN table2 ON table1.user_id = table2.user_id
WHERE table2.user_id IN (SELECT user_id FROM target_user);

同理，如果要保留table1的行（即使table2里没有匹配），用LEFT JOIN；如果两边的行都要保留（不管有没有匹配），可以用FULL OUTER JOIN（注意：不是所有数据库都支持FULL OUTER JOIN，比如MySQL就需要用UNION模拟）。

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