使用SQL关联Medicine与Medication表查询开具Aspirine的医生信息问题

现有写法的问题 #

你当前使用的是SQL隐式内连接语法，语法本身是被数据库支持的，但存在两个核心错误和优化点：

• 缺失关键关联条件：未建立Medication表和Medicine表的外键关联，正常业务表设计中Medication会携带MedicineID字段关联Medicine表的主键ID，缺失该条件会产生笛卡尔积，返回大量错误的冗余结果
• 逻辑分层不清晰：所有关联条件和业务过滤条件都混在WHERE子句中，表数量增多后极易漏写条件、难以维护，行业目前普遍推荐使用显式INNER JOIN语法实现表关联，关联逻辑和过滤逻辑分离可读性更强
• 需求匹配不全：你需要查询专科信息，但SELECT子句中未包含Physician.Specialty字段，查询结果不会返回专科内容

修正后的推荐写法 #

使用显式INNER JOIN的优化版本如下：

SELECT DISTINCT 
    Physician.FirstName, 
    Physician.LastName,
    Physician.Specialty -- 补充需求要求的专科信息字段
FROM Physician
INNER JOIN Consultation 
    ON Physician.ID = Consultation.PhysicianID
INNER JOIN Medication 
    ON Consultation.ConsultationID = Medication.ConsultationID
INNER JOIN Medicine 
    ON Medication.MedicineID = Medicine.ID -- 补充缺失的药品关联条件
WHERE 
    Physician.Specialty = 'Geriatrics' -- 可按需删除该条件，返回所有科室开了Aspirine的医生
    AND Medicine.Name = 'Aspirine';

补充说明 #

• 如果你仍然想使用隐式内连接的写法，只需要在原脚本的WHERE子句中补充AND Medication.MedicineID = Medicine.ID，同时在SELECT中添加Physician.Specialty字段即可得到正确结果
• 语句中DISTINCT的使用是合理的，可以避免同一个医生多次开具Aspirine时返回重复的医生记录

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