MySQL query select rows from table according to specific key that is not in another table

Question

How to select all exams by its ID and title (from table 'exams'), that a student (in table 'grades') hasn't written yet?

TABLE: grades

+--------------+----+---- |student_number|e_id| ... +--------------+----+---- |888075 |1 | ... |888075 |2 | ... |888075 |4 | ... |637020 |2 | ... +--------------+----+----

TABLE: exams

+----+------+ |e_id|title | +----+------+ |1 |exam 1| |2 |exam 2| |3 |exam 3| |4 |exam 4| +----+------+

In this particular case I would expect the following output for student 888075:

+--+------+ |id|title | +--+------+ |3 |exam 3| +--+------+

I just need the inverse selection of this:

SELECT e.e_id as id, e.title as title FROM grades g LEFT JOIN exams e ON g.e_id = e.e_id WHERE g.student_number = '888075'

sgeddes · Accepted Answer · 2015-10-31 00:52:53Z

Your query was close -- just reverse the joins and check for null accordingly:

SELECT e.e_id as id, e.title as title FROM exams e LEFT JOIN grades g ON g.e_id = e.e_id AND g.student_number = '888075' WHERE g.e_id IS NULL

Kostas Mitsarakis · Accepted Answer · 2015-10-31 00:56:55Z

You need to select those e_id that are not in e_id's that student participated.

SELECT aa.e_id AS id, aa.title FROM exams AS aa WHERE e_id NOT IN ( SELECT e_id FROM grades WHERE student_number = '888075' GROUP BY e_id );

OR

SELECT aa.e_id AS id, aa.title FROM exams AS aa WHERE e_id NOT IN ( SELECT DISTINCT e_id FROM grades WHERE student_number = '888075' );

OR

SELECT aa.e_id AS id, aa.title FROM exams AS aa WHERE NOT EXISTS ( SELECT e_id FROM grades AS bb WHERE aa.e_id = bb.e_id AND bb.student_number = '888075' );

Not In is a perfectly good option, but I would guess using distinct or group by in the subquery would actually make it slower. Without that it should be equivalent to the outer join / null check. I actually generally prefer to use Not Exists, but unfortunately it doesn't perform as well in mysql -- explainextended.com/2009/09/18/…
Thank you that you explain optimizations and improvements! I have read something similar about Not Exists. After i read better the question I bet OP wanted exactly what you posted (+1).

Collectives™ on Stack Overflow

MySQL query select rows from table according to specific key that is not in another table

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related