Sub Query possibility instead of using JOIN ?

Question

Hi,

Is it possible to write the code below using a sub query and not use JOIN statement?

SELECT Nurse_no, Nurse.Name, Auxilary_No, Patient.Name
FROM Nurse LEFT OUTER JOIN Patient
ON Nurse_no = Agency_Nurse_No;

Any help is grately appreciated.

Ben

Answer 1

Yes, but the JOIN will probably be faster.

SELECT Nurse_no, Nurse.Name, Auxilary_No, (select Name from Patient where agency_nurse_no = Nurse.Nurse_no) as PatientName
FROM Nurse;

I'm no SQL expert, but I would expect the JOIN operation to execute much faster than the subquery which probably will execute per row.

Answer 2

You could do:

select
   nurse_no,
   nurse.name,
   auxiliary_no,
   (select name from patient where agency_nurse_no = nurse_no) as PatientName
from
    nurse

However, MySQL doesn't optimize subqueries very well, and the join is probably much faster. Benchmarking will tell you which one wins, but that's my guess.