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.....

There should not be a performance issue. You can try any other approach but this query will give equal even better performance. — Akhilesh Mishra, Aug 30 '20 at 11:55
Some general performance tips for many:many tables: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table — Rick James, Aug 30 '20 at 18:11

score 1 · Accepted Answer · edited Aug 30 '20 at 12:14

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

Can sub-query be avoided in this query?

1 Answers1