Convert Sub Query to Group

Question

Hi, is it possible to convert this extra request into a connection?

SELECT staff_no
FROM doctor
WHERE NOT EXISTS (SELECT *
                  FROM patient
                  WHERE staff_no = consultant_no);

Answer 1

SELECT  staff_no
FROM    doctor
LEFT JOIN
        patient
ON      staff_no = consultant_no
WHERE   consultant_no IS NULL

To be effective, consultant_noindex and declare how NOT NULL.

If this is not the case, select any column declared as NOT NULLa patient’s and replace it consultant_nowith this column in the section WHERE.

See this blog post for a comparison of the three methods for this query in MySQL:

• NOT INvs. NOT EXISTSvs. LEFT JOIN / IS NULL:MySQL

Answer 2

   SELECT staff_no
     FROM doctor
LEFT JOIN patient
       ON staff_no = consultant_no
    WHERE consultant_no IS NULL