PostgreSQL中如何获取appointment与booking两张表的全部行记录

你提供的期望结果表头存在字段名重复问题，实际左侧三列对应appointment表的字段，右侧三列对应booking表的字段，以下SQL会给字段加别名避免冲突，返回结果和你给出的示例数据完全匹配：

SELECT 
  a.idappointment AS a_idappointment,
  a.idday AS a_idday,
  a.worktime AS a_worktime,
  NULL AS b_idbooking,
  NULL AS b_idappointment,
  NULL AS b_date
FROM appointment a
UNION ALL
SELECT 
  NULL AS a_idappointment,
  NULL AS a_idday,
  NULL AS a_worktime,
  b.idbooking AS b_idbooking,
  b.idappointment AS b_idappointment,
  b.date AS b_date
FROM booking b
UNION ALL
SELECT 
  a.idappointment AS a_idappointment,
  a.idday AS a_idday,
  a.worktime AS a_worktime,
  b.idbooking AS b_idbooking,
  b.idappointment AS b_idappointment,
  b.date AS b_date
FROM appointment a
INNER JOIN booking b ON a.idappointment = b.idappointment;

实现逻辑是用UNION ALL拼接三类数据：

• 所有appointment表的时段数据，右侧booking相关字段全部返回空
• 所有booking表的预订数据，左侧appointment相关字段全部返回空
• 两张表通过idappointment关联匹配成功的数据

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