PostgreSQL中如何筛选内连接未出现ID/用户ID的表行？

嘿，我来帮你搞定这个需求！你要找的是joins_example表中那些**user_id没有匹配到任何行的id，或者id没有匹配到任何行的user_id**的记录——也就是完全没参与到x.user_id = y.id这个自内连接里的行。下面给你几种实用的实现方式：

方法1：用NOT EXISTS子查询（最直观） #

这种写法逻辑最清晰，直接判断当前行的user_id是否在整个表的id列里找不到对应值，或者当前行的id在user_id列里找不到对应值：

SELECT *
FROM joins_example j
WHERE 
    -- 该行的user_id没有出现在任何行的id中
    NOT EXISTS (SELECT 1 FROM joins_example j2 WHERE j.user_id = j2.id)
    -- 或者该行的id没有出现在任何行的user_id中
    OR NOT EXISTS (SELECT 1 FROM joins_example j2 WHERE j.id = j2.user_id);

执行后就能得到你要的基础形式结果：

user_id | price  | id | email
---------+--------+----+--------------------------
 7       | $20.00 |    |
         |        | 2  | fahir@example.com
(2 rows)

方法2：用LEFT JOIN筛选未匹配项 #

我们可以把原表和连接条件做左连接，然后筛选出没有匹配到的行：

SELECT j.*
FROM joins_example j
-- 左连接找user_id对应的id行
LEFT JOIN joins_example j_match_id ON j.user_id = j_match_id.id
-- 左连接找id对应的user_id行
LEFT JOIN joins_example j_match_user ON j.id = j_match_user.user_id
-- 只有当两个连接都没匹配到的时候，才是目标行
WHERE j_match_id.id IS NULL AND j_match_user.user_id IS NULL;

这个方法和方法1的结果完全一致，只是用连接的方式实现而已。

方法3：用集合运算（EXCEPT） #

如果你喜欢用集合的思路，可以先找出所有参与了自内连接的行，再用原表减去这些行：

-- 原表所有行
SELECT * FROM joins_example
-- 减去所有参与自连接的行（左表和右表的行都算）
EXCEPT
(
    SELECT DISTINCT j.* FROM joins_example j
    JOIN joins_example j2 ON j.user_id = j2.id
    UNION
    SELECT DISTINCT j2.* FROM joins_example j
    JOIN joins_example j2 ON j.user_id = j2.id
);

这里的UNION是把自连接中作为左表和右表的行合并去重，然后用原表减去这些行，剩下的就是未参与连接的记录。

转换成你要的形式一/形式二 #

如果需要把结果转换成你提到的形式一或形式二（补全空列），只需要在SELECT里手动补充对应的空字段就行，比如形式一的写法：

SELECT 
    j.user_id, j.price, j.id, j.email,
    NULL::integer AS user_id_2, 
    NULL::text AS price_2, 
    NULL::integer AS id_2, 
    NULL::text AS email_2
FROM joins_example j
WHERE 
    NOT EXISTS (SELECT 1 FROM joins_example j2 WHERE j.user_id = j2.id)
    OR NOT EXISTS (SELECT 1 FROM joins_example j2 WHERE j.id = j2.user_id);

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