SQL Join 和 Sub 查询混淆

Question

SELECT familyname 
FROM dtoohey.patient, DTOOHEY.account, dtoohey.doctor 
WHERE dtoohey.account.patientID = dtoohey.patient.patientid 
  AND dtoohey.doctor.providerno = DTOOHEY.account.providerno 
  AND dtoohey.doctor.name = 'Dr Brian';

SELECT familyname 
FROM dtoohey.patient 
WHERE patientid IN (
          SELECT patientid 
          FROM dtoohey.account 
          WHERE providerno IN (
              SELECT providerno 
              FROM dtoohey.doctor 
              WHERE name = 'Dr Brian'
          )
);

这两者有什么区别？两者给出不同的结果。

Answer 1

执行计划的主要区别。可能这个查询会更有效 -

SELECT familyname
FROM dtoohey.patient p
WHERE EXISTS(
    SELECT 1 
    FROM dtoohey.account a
    JOIN dtoohey.doctor d ON d.providerno = a.providerno
    WHERE a.patientID = p.patientid
        AND d.Name = 'Dr Brian'
)