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.

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

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

2 Answers

ivana kantnerova
ivana kantnerova
13,688 Points
ivana kantnerova
ivana kantnerova
13,688 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.