Welcome to the Treehouse Community
The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.
Harris Handoko3,394 Points
Question on order of joining tables
I keep getting confused which table to start off, which table to join next. I want to develop some kind of a simple rule of thumb. Based on these exercises, I seem to conclude that when it comes to joining tables, we should start off our SELECT query with the table that contains our column of interest (WHO/WHICH). Then we will join the table that expresses the first table's relationship (HAS/DOES) with the third table that provides the condition (THIS/THAT).
-- Which teacher teaches 7th grade science? (Who does (or is associated with) that?) SELECT T.ID, T.FIRST_NAME, T.LAST_NAME, S.NAME AS SUBJECT_NAME, S.GRADE FROM TEACHERS AS T JOIN CLASSES AS C ON T.ID = C.TEACHER_ID JOIN SUBJECTS AS S ON C.SUBJECT_ID = S.ID )
Is this a reliable rule of thumb? Or does it even matter?
ivana kantnerova13,688 Points
i think, It does not matter only the set of attributes that are in the relationship is created
spencer tintorri6,184 Points
Functionally, you can JOIN in either direction. Both of the below options return the correct result.
SELECT DISTINCT t.ID, t.FIRST_NAME, t.LAST_NAME FROM TEACHERS AS t JOIN CLASSES AS c ON t.ID = c.TEACHER_ID JOIN SUBJECTS AS s ON s.ID = c.SUBJECT_ID WHERE s.GRADE = 7 AND s.NAME = 'Science'; SELECT DISTINCT t.ID, t.FIRST_NAME, t.LAST_NAME FROM SUBJECTS AS s JOIN CLASSES AS c ON c.SUBJECT_ID = s.ID JOIN TEACHERS AS t ON t.ID = c.TEACHER_ID WHERE s.GRADE = 7 AND s.NAME = 'Science';
One direction may be a best practice, so it's more readable, but someone more knowledgeable would need to chime in on that.
I prefer starting with the table that has final piece of data I'm after. So in the above example, we're looking for teachers, so I'd prefer to with the the teachers table, and join onto it.