CODEWITHSUNDEEP
sqlmysql
Ben
Ben

Reputation: 69

Convert Sub Query to a Join

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);

Upvotes: 3

Views: 1638

Answers (2)

knittl
knittl

Reputation: 265171

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

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425331

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:

Upvotes: 7

Related Questions