Can sub-query be avoided in this query?

Question

Three of the tables in my DB are as follows:

• Teacher's table
  teachers: teacher_id, teacher_name
• Student's table
  students: student_id, student_name
• Teachers-Students have a Many-to-Many relationship and hence they a dedicated table as per this answer:
  students_volunteers: teacher_id (FK), student_id(FK)

Now let's say a teacher can't take a class so he/she posts a substitute_request, so we want to send a notification to only those teachers who have same students in common. So I have written the SQL query for the same:

SELECT DISTINCT students_volunteers.v_id_sv 
FROM   students_volunteers 
WHERE  students_volunteers.s_id_sv IN (SELECT students_volunteers.s_id_sv 
                                       FROM   students_volunteers 
                                       WHERE  v_id_sv = 3) 
       AND students_volunteers.v_id_sv <> 3 

And it works fine except it has a sub-query, which due to performance issues is not ideal.
So I want to know if we can use some JOIN or something else for the same to improve performance.....

Answer 1

You could try with not exists:

SELECT DISTINCT teacher_id
FROM students_volunteers sv
WHERE 
    teacher_id <> 3
    AND EXISTS (
        SELECT 1
        FROM students_volunteers sv1
        WHERE sv1.teacher_id = 3 AND sv1.student_id = sv.student_id
    )

Alternatively, here is an approach with a self-join:

SELECT DISTINCT sv.teacher_id
FROM students_volunteers sv
INNER JOIN students_volunteers sv1
    ON sv1.teacher_id <> sv.teacher_id
    AND sv1.student_id = sv.student_id
WHERE sv1.teacher_id = 3