PostgreSQL - Query to list all students having atleast one mark different than 0 in SQL ?

Yogesh Kumar
04-May-2022
0

To find all students that have at least one non-zero mark, I would use an EXISTS condition:

 select stu.*
from student stu
where exists (select * 
              from mark mrk
              where mrk.student_id = stu.id
                and mrk.mark <> 0);

Finding students that only have zero marks is a bit more tricky and requires aggregation.

 select stu.*
from student stu
where exists (select mrk.student_id
              from mark mrk
              where mrk.student_id = stu.id
              group by mrk.student_id
              having bool_and(mrk.mark = 0));

Logically, the group by is not needed in the sub-query as it will only deal with a single student, but the SQL syntax rules require it.

This could also be expressed as a join against a derived table which might actually be a bit more efficient:

 select stu.*
from student stu
  join (
    select mrk.student_id, bool_and(mrk.mark = 0) as all_marks_are_zero
    from mark mrk
    group by mrk.student_id
  ) m on m.student_id = stu.id and m.all_marks_are_zero