3
\$\begingroup\$

This is a toy example where we have 3 tables:

  1. Student(snum, sname)
  2. Enrolled(snum, cname)
  3. 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);
\$\endgroup\$
  • 1
    \$\begingroup\$ Have you compared the query plans (using EXPLAIN SELECT or similar)? \$\endgroup\$ – 200_success Mar 7 '17 at 19:21
  • \$\begingroup\$ @200_success I've never heard of query plans \$\endgroup\$ – Julien__ Mar 7 '17 at 19:53
  • 1
    \$\begingroup\$ Then it's time to get familiar with them, because it's hard to speculate about these things. It greatly depends on indexes and statistics (table and index metrics) which query plan an RDBMS deems best. Also, it matters which database provider you're using. Just for the record, I thing both in versions are not efficient because the subqueries do far more than necessary and can be prevented. Rewrite them to exist, bringing S.snum into the subquery. \$\endgroup\$ – Gert Arnold Mar 7 '17 at 20:06
3
\$\begingroup\$

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
| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.