JOIN Syntax and Fundamentals
An INNER JOIN keeps a Member relationship only when the referenced Course and User rows are found.
Try it: JOIN Step by Step
How a JOIN combines rows from two tables by testing the ON condition for every pair (a nested loop), how LEFT and RIGHT JOIN keep unmatched rows by padding with NULL, and how a junction table links a many-to-many relationship with two joins.
How it works
- Take the first row of the outer (left) table.
- Compare its key with the key of every row of the inner table using the ON condition; every TRUE comparison produces one combined row. NULL = anything is NULL, never a match.
- INNER JOIN drops an outer row with no match; LEFT JOIN keeps it with the inner columns set to NULL. RIGHT JOIN is the mirrored LEFT JOIN; CROSS JOIN pairs every row with every row.
- Repeat for every outer row. A second JOIN treats the rows produced so far as its outer table — this is how student → member → course crosses a junction table.
Default run (18 steps): SELECT artist.name, album.title FROM artist JOIN album ON artist.id = album.artist_id. A nested loop: for each outer row, test the ON condition against every inner row. … Result: 3 joined rows (15 ON comparisons).
Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). The join is shown as a plain nested loop; real SQLite may reorder tables or build a temporary index, but the rows produced are the same. Result order here is outer row, then inner row (SQL promises no order without ORDER BY).
Loading the simulation…
Why One Relationship Needs Three Tables
Suppose a course can have many members, while one member can take many courses. This is a many-to-many relationship. Two tables alone cannot record every course-and-user pairing cleanly: a single user may need to be associated with several course IDs, and a single course may need to be associated with several user IDs. The solution is to store the two entity types separately and add a junction table between them.
A junction table is a table that records relationships between two entity tables by storing foreign keys that point to each entity's primary key.
Member does not describe a course or a user. Its purpose is to record which course_id values are paired with which user_id values. The Course and User tables hold the entity information; Member holds the links.
Following the First JOIN
A many-to-many query rebuilds the relationship in stages. Begin with Course and connect it to Member by matching Course.course_id with Member.course_id. This first JOIN attaches course information to each junction-table row whose course foreign key matches a Course primary key.
After the first JOIN, the query has course information and the relationship keys from Member. The Member row still contains the user_id needed for the second JOIN. The first JOIN does not directly discover the user's name; it prepares the relationship for the next match.
Completing the Second JOIN
The second JOIN connects Member to User by matching Member.user_id with User.user_id. When both key matches succeed, the database can combine columns from Course, Member, and User into one result row. The two JOINs therefore reconstruct a relationship that was stored across three separate tables.
Reading One Joined Row
Interpret a result row containing Course.course_id, Member.course_id, Member.user_id, and User.user_id.
- 1Check the course keys
If the Course.course_id value and Member.course_id value match, the row has connected a Course record to that Member relationship record.
- 2Check the user keys
If the Member.user_id value and User.user_id value match, the same relationship record has been connected to a User record.
- 3Read the complete pairing
The row now represents one course-and-user pairing recorded by Member, enriched with descriptive information from Course and User.
The keys make the connection visible: the row shows which course is paired with which user and demonstrates that both matches came through the junction table.
Reading the Flattened Result
The final result is a flattened view of the relationship. One output row combines information from all three tables, so each row represents one valid course-and-user pairing. If one user belongs to several courses, that user's information can appear on several output rows, once for each course pairing. This repeated information belongs to the temporary query result; it does not mean that the underlying User record was duplicated.
| Output part | What it demonstrates |
|---|---|
| Course.course_id | The selected course entity |
| Course.course_name | Descriptive information about that course |
| Member.course_id | The course key stored in the relationship record |
| Member.user_id | The user key stored in the relationship record |
| User.user_id | The user entity matched by the relationship key |
| User.user_name | Descriptive information about that user |
Keys and descriptive columns can appear together so the relationship can be both read and verified.
Keep the primary-key and foreign-key columns in the output when you are learning, debugging, or verifying a many-to-many query. Selecting only course and user names hides the evidence that the pairing came from the expected Member row. Visible keys make the result easier to check and provide stable references for later operations.
Mistakes in Many-to-Many JOINs
Common mistake Trying to store the many-to-many relationship only in Course and User
What it looks likePutting several course IDs into one User record
Why it failsA single entity row cannot cleanly represent every pairing without duplicating the user record or breaking the table structure.
Do this insteadUse Member as a junction table containing course_id and user_id pairs.
Common mistake Joining Course directly to User and skipping Member
What it looks likeAttempting to retrieve course and user details without the relationship table
Why it failsThe actual course-and-user pairings are recorded in Member, not in either entity table.
Do this insteadJoin Course to Member, then join Member to User.
Common mistake Selecting only descriptive names
What it looks likeReturning course_name and user_name while omitting the IDs
Why it failsThe result no longer visibly shows which keys established the relationship.
Do this insteadInclude the relevant primary and foreign keys while verifying or debugging the query.
Common mistake Assuming repeated user information means repeated stored users
What it looks likeSeeing one user's name on multiple result rows
Why it failsThe query result is a temporary denormalized view, so one user may appear once for each course pairing.
Do this insteadDistinguish the flattened output from the separate normalized tables used for storage.
Practice the Reconstruction
Write a SELECT query that starts with Course, joins Member using course_id, and then joins User using user_id. Include course_id, course_name, user_id, and user_name in the selected output so that the relationship remains visible.
Hints
- Use two JOIN clauses because three tables must be connected.
- The first match uses Course.course_id and Member.course_id.
- The second match uses Member.user_id and User.user_id.
- Keep the key columns in the SELECT list while checking your result.
To check your construction, trace one expected result row from the junction table outward. First identify its course_id and find the matching Course row. Then identify its user_id and find the matching User row. If both matches succeed, the final row should contain one course, one user, and the keys that show how Member connected them.
The Reusable Mental Model
- 1A many-to-many relationship uses two entity tables and a junction table.
- 2Member records
course_idanduser_idpairs; it is the bridge between Course and User. - 3Two sequential JOINs reconstruct the relationship by matching foreign keys to entity-table primary keys.
- 4Each successful output row represents one valid course-and-user pairing.
- 5The joined result is temporarily denormalized for display or analysis, while the underlying tables remain separate and normalized.
Key Takeaways
- Many-to-many data is stored across Course, User, and the Member junction table.
- The first JOIN matches Course.course_id to Member.course_id; the second matches Member.user_id to User.user_id.
- Member supplies the pairing, while Course and User supply the entity details.
- Visible primary and foreign keys let you verify the source of each output row.
- Repeated information in the joined result is temporary denormalization, not duplicated underlying storage.