SQL JOIN Clauses and Data Reconstruction
First, the row is inserted unless the database is instructed to ignore an insertion that conflicts with an existing row.
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…
Try it: Normalising into Lookup Tables
How normalisation removes repeated text: each distinct artist or album is stored once in a lookup table with an integer id (INSERT OR IGNORE, then SELECT id), the other table stores only that id as a foreign key, and a JOIN rebuilds the original rows. A roster preset links users and courses through a junction table.
How it works
- For each flat row, INSERT OR IGNORE the repeated text into its lookup table; the UNIQUE column makes a second copy be ignored.
- SELECT the text's id back — new or existing.
- Store that integer id (a foreign key) in the next table instead of the text.
- Many-to-many: a junction table stores (user_id, course_id) pairs plus the role; INSERT OR REPLACE keeps one row per pair.
- A JOIN that follows the ids rebuilds the same rows, while each distinct string is now stored once.
Default run (27 steps): A flat table: 6 rows × 3 text columns = 18 stored strings, many of them repeated. Split it into Artist, Album and Track. … Done: 18 stored strings became 12 (179 → 130 characters). The JOIN rebuilt 6 rows, the same rows as the flat table.
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). Follows the Python for Everybody tracks and roster programs on at most 8 rows; the stored-string count ignores the (small) space integers take.
Loading the simulation…
Why the Data Must Be Reassembled
A complete music record is distributed across three tables. Artist stores the artist row, Album stores an album and the artist_id that connects it to an Artist, and Track stores a track and the album_id that connects it to an Album. This separation avoids duplicating the same artist and album data. When you need to view a complete track record together with its album title and artist name, SQL must reconstruct that view by following the relationships between the tables.
The central pattern is insert first, retrieve the generated primary key immediately, and use that key as the foreign key in the next insert.
The Insert-and-Retrieve Sequence
INSERT OR IGNORE followed by SELECT is a two-step operation. First, the row is inserted unless the database is instructed to ignore an insertion that conflicts with an existing row. Immediately afterward, the program retrieves the primary key of the relevant row. That retrieved value becomes available for a later insert. The important idea is not merely that a row was stored; it is that the program now has the identifier needed to connect another row to it.
Following Foreign-Key Dependencies
The population order is Artist, then Album, then Track. An Album row needs an artist_id, so the related Artist must already exist and its primary key must have been retrieved. A Track row needs an album_id, so the related Album must already exist and its primary key must have been retrieved. The sequence is therefore determined by dependencies rather than by preference.
One Row Through the Dependency Chain
Process one input record that contains an artist, an album, and a track.
- 1Artist step
Read the artist name, perform INSERT OR IGNORE for the Artist row, and immediately select its primary key. Store that value as
artist_id. - 2Album step
Insert the Album using
artist_idas its foreign key. Immediately select the Album primary key and store it asalbum_id. - 3Track step
Insert the Track using
album_idso the Track is linked to its Album, which is already linked to its Artist.
The Track is connected indirectly to the Artist through Album: Track uses album_id, and Album uses artist_id.
Maintaining Related Rows
A foreign key is a primary key value from one table stored in another table. In this model, Album.artist_id stores the primary key of the Artist row to which the album belongs. Track.album_id stores the primary key of the Album row to which the track belongs. These values are identifiers, not copies of the artist's name or other descriptive data.
The Track operation differs slightly from the earlier operations: it uses INSERT OR REPLACE rather than INSERT OR IGNORE. This allows the program to update Track metadata such as rating, count, and length if the same track appears again in the CSV. The Track remains connected through album_id, so the relationship still leads from Track to Album and then from Album to Artist.
Reconstructing a Complete Record
JOIN clauses combine rows from multiple tables by matching primary-key and foreign-key values. To reconstruct album and artist information for a track, follow the stored relationships: the Track points to an Album through album_id, and the Album points to an Artist through artist_id. A JOIN between Album and Artist matches Album.artist_id with Artist.id, producing a result row that contains the album title and artist name together.
Reading a Joined Result
Explain what a reconstructed record contains when a Track row points to an Album row and that Album row points to an Artist row.
- 1Start with Track
Use the Track row's
album_idto identify the related Album row. - 2Continue through Album
Use the Album row's
artist_idto identify the related Artist row. - 3Read the combined result
The reconstructed result can present track information together with the album title and artist name.
JOIN does not duplicate the stored relationships; it uses matching primary and foreign key values to display related data together.
Mistakes in Relational Linking
Common mistake Inserting Album before Artist
What it looks likeAn Album insert is attempted before the related Artist primary key has been retrieved.
Why it failsAlbum needs
artist_id, and that value is not available until the Artist row exists and its key has been selected.Do this insteadInsert or ignore the Artist first, select its primary key, and use that value in the Album insert.
Common mistake Inserting Track before Album
What it looks likeA Track insert is attempted before the related Album row exists.
Why it failsTrack needs
album_id, which cannot be retrieved until the Album has been inserted or identified.Do this insteadComplete the Album insert and key-retrieval step before inserting the Track.
Common mistake Treating a foreign key as copied descriptive data
What it looks likeThinking that Album.artist_id stores the artist name.
Why it failsThe foreign key stores the Artist primary key, not the artist name.
Do this insteadUse the matching primary and foreign key values to connect the rows, and use a JOIN when the descriptive fields must be shown together.
Common mistake Stopping after the insert
What it looks likeInserting an Artist and proceeding without selecting its primary key.
Why it failsThe next Album insert still lacks the
artist_idneeded to establish the relationship.Do this insteadTreat INSERT OR IGNORE and the immediate SELECT as one two-step pattern.
Practice the Reconstruction Chain
Describe the correct operation order for one CSV record containing an artist name, an album title, and a track. Identify when artist_id is retrieved, where it is used, when album_id is retrieved, and where it is used. Then explain which matching values a JOIN follows to display the track together with its album and artist.
Hints
- Begin with the table that has no foreign-key dependency in this chain.
- The first retrieved key becomes a value in the Album row.
- The second retrieved key becomes a value in the Track row.
- A JOIN follows primary-key and foreign-key matches rather than copied names.
What do you think happens?
What would happen if the program attempted to insert a Track before inserting its Album?
Reveal answer
The database would reject the insert with a foreign key constraint error.
Track requires album_id, and that key cannot be retrieved until the related Album exists.
Key Takeaways
- 1Use INSERT OR IGNORE followed immediately by SELECT to obtain the primary key needed for a related insert.
- 2Populate the tables in dependency order: Artist, then Album, then Track.
- 3Album.artist_id points to Artist.id, while Track.album_id points to Album.id.
- 4JOIN clauses match primary and foreign keys to reconstruct related information from separate tables.
- 5Track can use INSERT OR REPLACE when repeated input should update metadata such as rating, count, or length.
At a glance
- The INSERT OR IGNORE and SELECT pattern retrieves a primary key for the next relational link.
- Foreign-key dependencies determine the order Artist, Album, and Track must be populated.
- Primary keys and foreign keys connect rows without copying descriptive data between tables.
- JOIN clauses reconstruct a complete record by matching those keys across tables.
- Repeated Track data can use INSERT OR REPLACE to update track metadata while preserving its album relationship.