Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trialHarris Handoko
3,932 PointsQuestion 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?
2 Answers
ivana kantnerova
15,932 Pointsi think, It does not matter only the set of attributes that are in the relationship is created
spencer tintorri
6,184 PointsFunctionally, 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.