SQL query without COUNT() to get all the students taking all the classes available-CodePudding

Is there a way I could get only the students that take all the classes available in CLASS without using COUNT() or any other aggregate functions? It's just a question out of curiosity, just to know if there is an alternative to COUNT(). The DBMS is PostgreSQL. The tables look like this:

STUDENT
student_id | student_name
-----------¦-------------
int        | varchar(30)

CLASS
class_id | class_name
---------¦-----------
int      | varchar(30)

REGISTRATION
student_id | class_id
-----------¦---------
int        | int

CodePudding user response：

This will show the students who are registered in all classes:

select * from student
where student_id not in
(
    select student_id from 
    (
        select 
        s.student_id, c.class_id
        from student s cross join class c
        except
        select student_id, class_id
        from registration
    ) classmissed
)

explanation:

first a cross join between student and classes will return all possible combinations of student/clasess then remove the actual registrations, which will leave the students that are NOT registered in all classes finally, the NOT IN that set will give us the students that are registered in all classes.

db-fiddle

https://www.db-fiddle.com/f/gjuScoNeABT91aj1eTWC82/0

CodePudding user response：

A double NOT EXISTS can also get the students that registered for all classes.

It returns students that are not among those that haven't registered all classes.

select *
from student as st1
where not exists (
    select 1
    from student as st2
    cross join class as cl
    where st2.student_id = st1.student_id
      and not exists (
        select 1
        from registration as re
        where re.student_id = st2.student_id
          and re.class_id = cl.class_id
      )
)

Or an EXCEPT to get all students, except for those that aren't registered for all classes.

select *
from student
except
select distinct st.*
from student as st
cross join class as cl
left join registration as re 
    using(student_id, class_id) 
where re.class_id is null