如何实现带过滤条件的右连接，仅保留符合要求的未匹配公民数据

你可以使用UNION ALL拼接两个符合要求的结果集实现需求，第一个结果集保留你原本查询的pregnant_consult = 1的预约关联记录，第二个结果集补充查询所有没有对应有效预约的怀孕公民，对应预约相关字段赋值为NULL即可：

SELECT 
  a.citizen_id, 
  a.date, 
  a.pregnant_consult, 
  c.id, 
  c.name, 
  c.pregnant
FROM appointment a
LEFT JOIN citizen c ON a.citizen_id = c.id
WHERE a.pregnant_consult = 1

UNION ALL

SELECT 
  NULL AS citizen_id, 
  NULL AS date, 
  NULL AS pregnant_consult, 
  c.id, 
  c.name, 
  c.pregnant
FROM citizen c
WHERE 
  c.pregnant = 1
  AND NOT EXISTS (
    SELECT 1 FROM appointment a 
    WHERE a.citizen_id = c.id AND a.pregnant_consult = 1
  )

执行后就会得到你期望的结果：既包含所有pregnant_consult = 1的预约关联记录，也额外补充了怀孕但没有对应有效预约的公民Sandra的记录，不会出现pregnant为0的Suzy。

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