Sub Query capability instead of using JOIN?

Question

Is it possible to write the code below using an auxiliary request and not use the 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 greatly appreciated.

Ben

Answer 1

You can do:

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

However, MySQL does not really optimize subqueries, and the connection is probably much faster. Benchmarking will tell you which one will win, but I think.

Answer 2

Yes, but 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 not an SQL expert, but I would expect the JOIN operation to be much faster than the subquery, which is likely to be performed on each line.