3 versions of SQL query to find students with simultaneous classes

This is a toy example where we have 3 tables:

• Student(snum, sname)

• Enrolled(snum, cname)

• Class(cname, meets_at)

and we want to find the sname of the students that are enrolled in at least
two classes that meet at the same time.

Here are three attempts, which one is best and why ?

select sname, (count(distinct cname) - count(distinct meets_at))
from (select st.*, cl.*
      from Student st, Class cl, Enrolled en
      where st.snum = en.snum
      and cl.cname = en.cname)
group by sname
having count(distinct cname) - count(distinct meets_at) >= 1;

select S.sname
from Student S
where S.snum in (
                select E.snum
                from Enrolled E, Class C
                where E.cname = C.cname
                group by E.snum, C.meets_at
                having count(E.cname) >= 2
                );

select S.sname
from Student S
where S.snum in ( select E1.snum
                  from Enrolled E1, Enrolled E2, Class C1, Class C2
                  where E1.snum = E2.snum and E1.cname <> E2.cname and
                  E1.cname = C1.cname and E2.cname = C2.cname
                  and C1.meets_at = C2.meets_at);

They are all messy. And please don't use old style joins.

Two is the least messy as it goes at it more directly.

But you can do it with joins

select st.sname, cl.meets_at 
from Student st 
join Enrolled en 
  on en.snum = st.snum
join Class cl 
  on cl.cname = en.cname 
group by st.sname, cl.meets_at 
having count(*) > 1

That will return more than one row per sname if they have more than one class that has overlapping times. But that is the most direct approach to the logic (in my opinion).

This would be a clean way to go at single sname

select st.sname 
from Student st 
where exists ( select 1  
               from Enrolled en     
               join Class cl 
                 on cl.cname = en.cname 
               where en.snun = st.snum
               group by cl.meets_at
               having count(*) > 1 
             )

another - this might be the best efficiency

select st.sname 
from Student st 
join  ( select cl.snum  
        from Enrolled en     
        join Class cl 
          on cl.cname = en.cname 
        group by cl.snum 
        having count(*) > 1 
      ) cl2 
 on st.snum = cl.snum

StackExchange Code Review Q#157186, answer score: 3