Select from a table the ones that don't have a relationship with another table

Question

The specific problem is listing the names of the teachers that never graded.

I have 'teachers' table with the columns 'Name' and 'ID'.
And 'grades' table with the column 'IDTeacher' and 'Grade'.

Don't get why this doesn't work:

Select Name from teachers where not exists(Select * from grades, teachers)

possible duplicate of [SELECT \* WHERE NOT EXISTS](http://stackoverflow.com/questions/915643/select-where-not-exists) — leftiness, Nov 05 '14 at 21:36
Both Dognose & Gordon Linoff have the correct ways of doing it. The reason your doesn't work is that your inner select doesn't have any criteria to match it to the outer one as well as you are doing a implied full outer join in it, so the inner select will always return rows unless you don't have any rows in both grades and teachers. — Dijkgraaf, Nov 05 '14 at 21:37

dognose · Answer 1 · 2014-11-05T21:51:01.087

6

You can just join it with the grades table and use the ones where the join returns "null" for the right side:

SELECT
   name
from
   teachers t
LEFT JOIN
   grades g
on
   t.teacher = g.teacher
WHERE
   ISNULL(g.teacher)

edit: Thought about a right join instead, but no, the right join might not work, if the teacher has no entry in the grades table. (Then you would miss him completely, even if he is in the teacher table)

You could also use WHERE IN for this:

SELECT 
  name 
FROM 
  teachers 
WHERE 
  name 
NOT IN (SELECT name from grades)

BUT the MySQL Optimizer will rewrite this to exactly the correlated subquery @Gordon Linoff has written. Using WHERE NOT IN is just easier to read imho.

edited Nov 05 '14 at 21:51

answered Nov 05 '14 at 21:35

dognose

20,360
9
61
107

1

Maybe I look at this graphic more than I should. http://stackoverflow.com/a/16598900/3403036 :D – leftiness Nov 05 '14 at 21:40
Do you know how I could solve it with not exists? thanks it works – Antonio Craveiro Nov 05 '14 at 21:40
@AntonioCraveiro See Gordon Linoffs answer for a solution with `not exists` – dognose Nov 05 '14 at 21:45

score 3 · Accepted Answer · answered Nov 05 '14 at 21:39

Your query does work, it just doesn't do what you think it should. The subquery creates a cartesian product between the two tables. If both tables have rows, then the cartesian product has rows and the where clause will always be true.

You can take this approach, but you need a correlated subquery:

Select Name
from teachers t
where not exists (Select 1 from grades g where g.idteacherid = t.id);

Note that this query only has one table in the subquery.

There are other ways to write this query, but this seems to be the approach you are heading in. And, not exists is a very reasonable approach.

Select from a table the ones that don't have a relationship with another table

2 Answers2