Reputation: 335
I use PostgreSQL 9.5.1 in a terminal. I follow a tutorial. There are two tables: "is_called" with the id and the name of students, and "is_enrolled_on" with the courses the students are enrolled on. I tried to create the query that returns only students that are not enrolled on any course. For that, I used the "not exists" condition but I can't understand why the student with id 5 is not returned as he is not enrolled on any course.
Maybe it is my understanding of the "exists" condition that is wrong. For me, "exists" works like an intersection between the two relations.
Upvotes: 0
Views: 33
Reputation: 1271231
The problem is that the subquery is simply executed with no connection to the outer query. It returns rows; hence, NOT EXISTS
is false and no rows are returned.
To get a hang of this, start with NOT IN
:
select i.studentid
from is_called i
where i.studentid not in (select io.studentid from is_enrolled_on io);
Then convert this to NOT EXISTS
:
select i.studentid
from is_called i
where not exists (select 1 from is_enrolled_on io where io.studentid = i.studentid);
I should also note that select distinct
is not appropriate for either IN
or EXISTS
.
Upvotes: 2