使用JOIN、类型转换与WHERE子句时查询结果丢失记录的问题

编辑补充 #

发布后不久，我想到了如下解决方案：

select
    c.id as c_id,
    c.category_name as category_name,
    case
        when p.id = 1 and cs.person_id is not null
            then true::text
        else false::text
    end is_subscribed
from category c
    full join category_subscription cs
      on c.id = cs.category_id
    full join person p
      on p.id = cs.person_id

该方案似乎有效，但仍欢迎更多建议

原问题 #

我在构造查询语句以返回全部记录时遇到问题，当前仅能得到匹配is not null条件的记录，尝试了所有可能的JOIN方式仍未解决。

表结构 #

create table person(
  id serial primary key,
  person_name varchar(55)
);
create table category(
  id serial primary key,
  category_name varchar(55)
);
create table category_subscription(
  id serial primary key,
  category_id bigint references category(id),
  person_id bigint references person(id)
);

测试数据 #

insert into person values (1, 'george');
insert into category values (10, 'homework'), (20, 'promotion');
insert into category_subscription values (100, 10, 1);

初始查询与问题 #

初始查询语句如下：

select
    c.id as c_id,
    c.category_name as category_name,
    (p.id is not null)::text as is_subscribed
from category c
full outer join category_subscription cs on c.id = cs.category_id
full outer join person p on p.id = cs.person_id

该语句可以正常返回所有记录，is_subscribed列的值为true或false。但添加WHERE子句后，仅能得到is_subscribed为true的记录：

. . .
where p.person_name = 'george'

我需要获取用户george的所有category_subscription相关记录，同时显示所有category记录并标记是否订阅。例如，当category_subscription表为空时，查询结果应如下：

[
    {
        "c_id": 10,
        "category_name": "homework",
        "is_subscribed": false
    },
    {
        "c_id": 20,
        "category_name": "promotion",
        "is_subscribed": false
    }
]

问题原因与修复方案 #

原因 #

添加WHERE p.person_name = 'george'后，会过滤掉所有p.person_name为NULL的记录——也就是那些没有被george订阅的分类。因为这些分类在全外连接后，对应的person表字段都是NULL，WHERE条件会直接排除这些行，最终只保留有订阅记录的分类。

修复方案 #

方案1：将用户筛选条件移至JOIN的ON子句（推荐）

以category为主表，通过LEFT JOIN关联指定用户的订阅记录，这样能保留所有分类，同时判断是否存在订阅：

SELECT
    c.id AS c_id,
    c.category_name AS category_name,
    CASE WHEN cs.category_id IS NOT NULL THEN 'true' ELSE 'false' END AS is_subscribed
FROM category c
LEFT JOIN category_subscription cs 
    ON c.id = cs.category_id 
    AND cs.person_id = (SELECT id FROM person WHERE person_name = 'george');

方案2：使用EXISTS子查询判断订阅状态

通过子查询检查当前分类是否存在george的订阅记录，逻辑更直观：

SELECT
    c.id AS c_id,
    c.category_name AS category_name,
    EXISTS (
        SELECT 1 
        FROM category_subscription cs
        JOIN person p ON cs.person_id = p.id
        WHERE cs.category_id = c.id AND p.person_name = 'george'
    )::TEXT AS is_subscribed
FROM category c;

对编辑补充方案的优化

你提供的方案可以正常工作，但硬编码p.id = 1不够灵活，建议改为通过person_name关联，避免依赖固定ID：

select
    c.id as c_id,
    c.category_name as category_name,
    case
        when p.person_name = 'george' and cs.person_id is not null
            then true::text
        else false::text
    end is_subscribed
from category c
    full join category_subscription cs
      on c.id = cs.category_id
    full join person p
      on p.id = cs.person_id

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