如何实现存在报告时选取审批日期，同时保留所有订单数据？

需求：从RAD_RESULT_VIEW、RAD_IMAGE_HEADER、RAD_REPORT_RESULT、MR_FILE_DETAILS四张表中查询订单日期、扫描日期及报告日期，要求无论订单是否有对应报告，都返回订单基础数据；当存在报告时，同时返回R.APPROVING_DATE（发布日期）和R.approving_time（发布时间）。

原查询语句（内连接，仅返回有报告的订单） #

SELECT   H.PATIENT_NO,
         TO_CHAR(TO_DATE(H.SERV_REQUEST_DATE,'yyyymmdd'),'YYYY-MM-DD') AS "Order Date",
         (TO_CHAR(TO_DATE(LPAD(TO_CHAR(H.SERV_REQUEST_TIME),4,'0'),'HH24MI'),'HH24:MI')) AS "Order Time" , 
         TO_CHAR(TO_DATE(G.DATE_IMAGE_TAKEN,'yyyymmdd'),'YYYY-MM-DD') AS "Scan Date",
         (TO_CHAR(TO_DATE(LPAD(TO_CHAR(G.TIME_IMAGE_TAKEN),4,'0'),'HH24MI'),'HH24:MI')) AS "Scan Time" , 
         TO_CHAR(TO_DATE(R.APPROVING_DATE,'yyyymmdd'),'YYYY-MM-DD') AS "Release Date",
         (TO_CHAR(TO_DATE(LPAD(TO_CHAR(r.approving_time),4,'0'),'HH24MI'),'HH24:MI')) AS "Release Time" , 
          H.PATIENT_SOURCE_IND,
          H.PROVIDING_RESOURCE                 
     FROM   RAD_RESULT_VIEW H,RAD_IMAGE_HEADER G, RAD_REPORT_RESULT R , MR_FILE_DETAILS A
     WHERE  H.HOSPITAL_NO = G.HOSPITAL_NO
            AND H.HOSPITAL_NO = M.HOSPITAL_NO -- 此处存在笔误，M应为A
            AND H.PATIENT_NO = A.PATIENT_NO
            AND H.RAD_ORDER_NO = G.RAD_ORDER_NO
            AND (G.IMAGE_NO = R.IMAGE_NO)

修改后的查询语句（左连接，返回所有订单） #

SELECT   H.PATIENT_NO,
         TO_CHAR(TO_DATE(H.SERV_REQUEST_DATE,'yyyymmdd'),'YYYY-MM-DD') AS "Order Date",
         TO_CHAR(TO_DATE(LPAD(TO_CHAR(H.SERV_REQUEST_TIME),4,'0'),'HH24MI'),'HH24:MI') AS "Order Time", 
         TO_CHAR(TO_DATE(G.DATE_IMAGE_TAKEN,'yyyymmdd'),'YYYY-MM-DD') AS "Scan Date",
         TO_CHAR(TO_DATE(LPAD(TO_CHAR(G.TIME_IMAGE_TAKEN),4,'0'),'HH24MI'),'HH24:MI') AS "Scan Time", 
         -- 无报告时自动返回NULL，符合需求
         TO_CHAR(TO_DATE(R.APPROVING_DATE,'yyyymmdd'),'YYYY-MM-DD') AS "Release Date",
         TO_CHAR(TO_DATE(LPAD(TO_CHAR(R.approving_time),4,'0'),'HH24MI'),'HH24:MI') AS "Release Time", 
         H.PATIENT_SOURCE_IND,
         H.PROVIDING_RESOURCE                 
FROM RAD_RESULT_VIEW H
JOIN RAD_IMAGE_HEADER G 
  ON H.HOSPITAL_NO = G.HOSPITAL_NO 
  AND H.RAD_ORDER_NO = G.RAD_ORDER_NO
JOIN MR_FILE_DETAILS A 
  ON H.HOSPITAL_NO = A.HOSPITAL_NO 
  AND H.PATIENT_NO = A.PATIENT_NO
-- 左连接RAD_REPORT_RESULT，确保无报告的订单也被返回
LEFT JOIN RAD_REPORT_RESULT R 
  ON G.IMAGE_NO = R.IMAGE_NO

关键修改说明 #

• 改用显式JOIN语法替代旧的隐式逗号分隔写法，逻辑更清晰易维护
• 对RAD_REPORT_RESULT使用LEFT JOIN，保证即使没有匹配的报告记录，订单的基础数据依然会被返回
• 修正原语句中的笔误：将H.HOSPITAL_NO = M.HOSPITAL_NO改为H.HOSPITAL_NO = A.HOSPITAL_NO（表MR_FILE_DETAILS的别名是A）
• 日期时间格式化逻辑保持不变，当无对应报告时，Release Date和Release Time会自动显示为NULL，与期望输出一致

期望输出示例 #

PATIENT_NO      Order Date          Scan Date        Release Date 
  1               2024-05-15        2024-05-15
  2               2024-05-15        2024-05-15       2024-05-15
  3               2024-05-15        2024-05-15       
  4               2024-05-14        2024-05-15
  5               2024-05-14        2024-05-15       2024-05-15

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