Convert Sub Query to a Join

Question

Hi is it possible to convert this sub query to a join ?

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

For this to be efficient, consultant_no should be indexed and declared as NOT NULL.

If it's not, pick any column that is declared as NOT NULL in patient and replace consultant_no with this column in your WHERE clause.

See this article in my blog for comparison of three methods to do this query in MySQL:

• NOT IN vs. NOT EXISTS vs. 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