0

Suppose we have tables Appointments, Patients, and Doctors, and every record in Appointments is related to Patients and Doctors by patient_id and doctor_id. What's the difference between these two queries. What are the advantages and disadvantages of either of them:

1:
SELECT Patients.first_name,Patients.last_name,Patients.patient_id,
       Doctors.first_name, Doctors.last_name,Doctors.doctor_id,
       Appointments.app_length,Appointments.app_datetime
FROM Appointments,Patients,Doctors
WHERE Appointments.doctor_id=Doctors.doctor_id 
      AND Appointments.patient_id=Patients.patient_id


2:
SELECT Patients.first_name,Patients.last_name,Patients.patient_id,
       Doctors.first_name, Doctors.last_name,Doctors.doctor_id,
       Appointments.app_length,Appointments.app_datetime

FROM Patients INNER JOIN Appointments ON Appointments.patient_id=Patients.patient_id
INNER JOIN Doctors ON Appointments.doctor_id=Doctors.doctor_id

It's good to know that both of them work and give the same results. But I want to know the difference and which one is more optimized on a huge amount of data.

Barbara Laird
  • 12,599
  • 2
  • 44
  • 57
Ehsan88
  • 3,569
  • 5
  • 29
  • 52
  • 3
    I once stated that the second style would perform better. Then I was challenged to prove it. When I ran my tests it turned out to not make any difference. – Dan Bracuk Jul 30 '13 at 22:44
  • 2
    possible duplicate of [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) – Filipe Silva Jul 30 '13 at 23:17
  • 1
    ... or [MySQL Performance: JOIN ON vs WHERE](http://stackoverflow.com/questions/5338920/mysql-performance-join-on-vs-where) – davidkonrad Aug 01 '13 at 08:38
  • possible duplicate of [Condition within JOIN or WHERE](http://stackoverflow.com/questions/1018952/condition-within-join-or-where) – Bill Karwin Aug 02 '13 at 23:58

0 Answers0