PostgreSQL查询中新增字段判断ID是否存在于另一表的实现方法

现有三张PostgreSQL表结构如下：

• announcement表：| id | author_id | date | group_id | ... |
• author表：| id | name | email | ... |
• chapter表：| id | announcement_id | ... |

已实现announcement与author的左连接查询，原SQL语句为：

select announcement.id, announcement.date, author.id as publisher_id, author.name as publisher_name
from announcement 
left outer join author 
on announcement.author_id = author.id
where announcement.group_id = 123 and announcement.date >= '2022-06-01'::date;

现在需要新增with_chapters字段，用于判断当前announcement的id是否在chapter表的announcement_id列中存在（只要有至少一条匹配就返回true，否则返回false），修改后的SQL语句如下：

select 
    announcement.id, 
    announcement.date, 
    author.id as publisher_id, 
    author.name as publisher_name,
    EXISTS (
        SELECT 1 
        FROM chapter 
        WHERE chapter.announcement_id = announcement.id
    ) as with_chapters
from announcement 
left outer join author 
on announcement.author_id = author.id
where announcement.group_id = 123 and announcement.date >= '2022-06-01'::date;

期望输出 #

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