Databases SQL Reporting by Example Day 2: Advanced Selecting A Smelly Issue

Stamos Bolovinos
Stamos Bolovinos
4,320 Points

Why joining the students table?

There is no need to join the students table.

SELECT COUNT(*) 
FROM classes
JOIN schedule ON id = class_id
WHERE subject_id IN (SELECT id FROM subjects WHERE name = 'Physical Education')
AND period_id = 1;

will accomplish the same as the teachers example

SELECT COUNT(1) FROM students
JOIN schedule ON students.id = schedule.student_id
JOIN classes ON classes.id = schedule.class_id
JOIN subjects ON subjects.id = classes.subject_id
WHERE period_id = 1 AND name = 'Physical Education'

3 Answers

Steven Parker
Steven Parker
203,694 Points

You could also just eliminate that table from the original example, and reduce it to:

SELECT COUNT(1) FROM schedule
JOIN classes ON classes.id = schedule.class_id
JOIN subjects ON subjects.id = classes.subject_id
WHERE period_id = 1 AND name = 'Physical Education'

I can think of one reason it might be desirable to do it the original way, in case the classes table potentially contained records for students who were no longer current, and you needed the students table to limit the count to only current students. But I agree it doesn't seem to be needed here.

If you feel this is a bug, you could report it to Support. I'm also tagging Ben Deitch in case he might like to comment directly.

SELECT COUNT(*)
FROM STUDENTS
    INNER JOIN SCHEDULE
      ON STUDENTS.ID = SCHEDULE.STUDENT_ID
    INNER JOIN CLASSES 
      ON CLASSES.ID = SCHEDULE.CLASS_ID
WHERE CLASSES.SUBJECT_ID IN(19, 25, 31)  --phys ed 6th, 7th, and 8th
 /*WHERE SUBJECT_ID IN (
SELECT ID        
FROM SUBJECTS
 WHERE NAME = 'Physical Education')*/
 AND PERIOD_ID = 1;

So it should be obvious from reading the code you are counting students ( in case you or a co-worker wants to grab this from the repo and use this as a subquery ). Mostly wanted to share my answer :P

Paul Brubaker
Paul Brubaker
9,925 Points

That's a nice catch. It probably just felt natural to start with the students table, since it is students being counted. I went about the task in the exact same way Ben did, line for line, also starting with the students table, and would never have noticed it wasn't necessary if no one had pointed it out. Maybe it's just our human bias at work; it makes more sense to us when we think of a list of people to start with the table that includes their names and not just their ID numbers. Not necessarily a bad thing, but I guess it cost us a little performance on this one.