如何通过INNER JOIN/UNION ALL合并SELECT语句查询用户两剂疫苗名称

你之前的两种写法存在逻辑问题：

• 单次INNER JOIN写法：仅通过首剂疫苗ID关联了vaccine表，查询时两次选取同一个vaccine_name字段，没有关联第二剂疫苗的映射数据，所以两列都会返回首剂对应的Moderna。
• 两次INNER JOIN写法：没有给vaccine表设置独立别名，要求同一条vaccine记录的ID同时匹配首剂、第二剂两个不同的疫苗ID，逻辑上不可能成立，同时还存在字段名笔误（将vaccine_id写为vaccines_id），因此查询无结果。

方案1：双别名INNER JOIN（推荐） #

给vaccine表分配两个不同的别名，分别关联首剂、第二剂的疫苗ID，即可一次性取出两剂对应的疫苗名称：

SELECT
  v1.vaccine_name AS dose1_name,
  v2.vaccine_name AS dose2_name
FROM user u
INNER JOIN vaccine v1 
  ON u.first_dose = v1.vaccine_id
INNER JOIN vaccine v2 
  ON u.second_dose = v2.vaccine_id
WHERE u.user_id = 'A221H'

执行上述语句后会直接返回你需要的结果：dose1_name: Moderna、dose2_name: Pfizer。

方案2：UNION ALL实现 #

也可以通过UNION ALL分别查询两剂疫苗数据，再做聚合得到结果，写法如下：

SELECT
  MAX(CASE WHEN dose_type = 1 THEN vaccine_name END) AS dose1_name,
  MAX(CASE WHEN dose_type = 2 THEN vaccine_name END) AS dose2_name
FROM (
  SELECT 1 AS dose_type, v.vaccine_name
  FROM user u
  INNER JOIN vaccine v ON u.first_dose = v.vaccine_id
  WHERE u.user_id = 'A221H'
  UNION ALL
  SELECT 2 AS dose_type, v.vaccine_name
  FROM user u
  INNER JOIN vaccine v ON u.second_dose = v.vaccine_id
  WHERE u.user_id = 'A221H'
) t

该写法逻辑相对冗余，性能略低于双JOIN方案，更推荐优先使用第一种写法。

内容的提问来源于stack exchange，提问作者T R A C E ツ