sql subquery In function

Question

here still got lots to learn thanks for the help in advance. *bows tried several time cant seems to get it to work hope to get some help from more experienced coders

I want to show staff that are not teaching anyone

enter image description here original table

Staff_ID    Mentor_ID   First_Name                Last_Name                
----------- ----------- ------------------------- -------------------------               
101         NULL        May                       Ou                     
102         101         Io                        Rush                   
103         102         Ley                       Have                     
104         103         Stephanie                 Soon                    
105         101         Iat                       Raiz                     
106         101         Rina                      shion  

--notes

• not having a mentor id they are not learning from anyone
• having a mentor id learning that that person e,g 102 staff "Io Rush" is learning from staff 101 "May Ou" therefore means staff 101 is teaching 102 as well.
• therefore if staff id of a staff did not appear in mentor id they are not teaching anyone e.g. staff 104, 105 & 106

--basic key points

• staff 101 is not learning from any one but is teaching other people
• staff 102,103,104,105,106 are all learning from someone
• staff 101,102,103 are the only staff that are teaching other staff
• staff 104,105,106 are the only ones not teaching anyone

--my codings:

SELECT * 
FROM staff
WHERE Staff_ID  IN (SELECT Mentor_ID FROM staff)

--Result enter image description here

Staff_ID    Mentor_ID   First_Name                Last_Name                
----------- ----------- ------------------------- -------------------------                   
101         NULL        May                       Ou                     
102         101         Io                        Rush                   
103         102         Ley                       Have                   

-- this show the complete opposite of what i am going for as this show all staff that is teaching someone

-- so i tought of an idea an since its the opposite should be just adding a NOT will give me what i one so i type the following code

--coding 2 :

SELECT * 
FROM staff
WHERE Staff_ID NOT IN (SELECT Mentor_ID FROM staff)

--result: blank....

no idea what else i can do --" kinda stuck now, btw i am still a newbie to mysql so there may be i function of stuff that i dont know of please bear with me*bows yup so that is a detailed description of my problem, i thank all those in advance for taking your time to read my problem all advice is appreciated, hope to hear from you guys soon *waves waves :D

Answer 1

Try this one:

SELECT * FROM staff s WHERE NOT EXISTS (SELECT 1 FROM staff m WHERE m.Mentor_ID=s.Staff_ID )

Your last query was in a way similar, but you fell into a common pitfall with the NOT IN construct. It works fine as long as there are no null values in your list you are comparing against. But unfortunately there is a null value. The above form with EXISTS will always work.

I changed your data for a little fiddle (using 0 instead of null) and you can see that both versions work there.

Answer 2

EXISTS is much faster than IN when the subquery results is very large. IN is faster than EXISTS when the subquery results is very small.

This will help you Difference between EXISTS and IN in SQL?