CODEWITHSUNDEEP
mysql
Antonio Craveiro
Antonio Craveiro

Reputation: 121

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

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)

Upvotes: 1

Views: 237

Answers (2)

dognose
dognose

Reputation: 20889

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.

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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.

Upvotes: 3

Related Questions