CODEWITHSUNDEEP
sqlpostgresqlexists
Dust009
Dust009

Reputation: 335

PostgreSQL: Trouble understanding Exists condition

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.

Description of the queries in the terminal

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions