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 trial

Databases SQL Reporting by Example Day 1: Joining Tables 7th Grade Science

Harris Handoko
Harris Handoko
3,932 Points
Posted by Harris Handoko
Harris Handoko
3,932 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?

2 Answers

ivana kantnerova
ivana kantnerova
15,932 Points
ivana kantnerova
ivana kantnerova
15,932 Points

i think, It does not matter only the set of attributes that are in the relationship is created

spencer tintorri
spencer tintorri
6,184 Points
spencer tintorri
spencer tintorri
6,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.