PostgreSQL: Trouble understanding Exists condition

Question

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.

Add a where clause to subquery.

Giorgi Nakeuri
– Giorgi Nakeuri

2016-05-18 14:30:44 +00:00
Commented May 18, 2016 at 14:30 — Giorgi Nakeuri
– Giorgi Nakeuri, Commented May 18, 2016 at 14:30

Gordon Linoff · Accepted Answer · 2016-05-18 14:36:11Z

2

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.

edited May 18, 2016 at 14:36

answered May 18, 2016 at 14:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Trouble understanding Exists condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related