Writing JOIN Queries to Retrieve Data Across Multiple Tables
A multi-table relational schema organizes data across separate tables to eliminate redundancy and improve data integrity. The Artist, Album, and Track tables in this example demonstrate how to structure a music library database.
From One Flat Record to Three Tables
A music-library CSV record can contain a track title, artist name, album title, track length, rating, and play count all on one line. A normalized database does not keep every value together in one repeated row. Instead, it separates the information into Artist, Album, and Track tables, then connects those tables with references. The result reduces repeated data and improves data integrity.
The Three-Table Schema
Each table represents a different kind of entity. Artist stores unique artist names. Album stores album titles and the artist_id that identifies the artist who created each album. Track stores individual songs and metadata such as duration, rating, and play count. A track does not store the artist name directly. It stores album_id, which points to its album.
A foreign key is a value in one table that references the id value of a row in another table. In this schema, Album.artist_id references Artist, and Track.album_id references Album.
The relationships form a chain: Track to Album through album_id, then Album to Artist through artist_id. To find a track's artist, follow both references.
Following Keys Through a JOIN
A JOIN retrieves related information by matching the key values that connect rows. Begin with a Track row. Its album_id identifies the related Album row. The Album row then supplies artist_id, which identifies the related Artist row. The resulting retrieval can present track, album, and artist information together even though the database stores those facts in separate tables.
Tracing One Track to Its Artist
Suppose a Track row contains an album_id that identifies one Album row. That Album row contains an artist_id that identifies one Artist row. Trace the retrieval path.
Start with Track: Read the track's album_id value. This value is the link to the related Album row.
Reach Album: Use album_id to retrieve the album title and the album's artist_id.
Reach Artist: Use artist_id from the Album row to retrieve the artist name.
Combine the result: The related track, album, and artist values can now be returned together, although they remain stored in their respective tables.
The JOIN follows the chain Track.album_id to Album, then Album.artist_id to Artist.
Loading CSV Data Safely
The tracks_csv.py application reads a CSV file exported from Dr. Chuck's iTunes library. Each line contains the track title, artist name, album title, track length, rating, and play count. Because this is flat, denormalized input, the application must distribute the values into the normalized tables.
- Check whether the artist already exists in the Artist table.
- If the artist is absent, insert it and retrieve its new id.
- Check whether the album exists using the artist_id that was obtained.
- If the album is absent, insert it and retrieve its album id.
- Insert the track and store its album_id as the link to the Album table.
Normalization and Data Integrity
Normalization is the process of organizing data to minimize redundancy and improve data integrity. In this schema, each piece of information is stored once when possible, and foreign keys connect the related records.
If Queen releases a new album, the normalized design adds one row to Album and multiple rows to Track. It does not create a new copy of the artist record for every track. This reduces repetition and makes updates simpler because the artist information is maintained in one place.
Normalization separates artist, album, and track facts while foreign keys preserve the relationships between them. A JOIN temporarily brings related values together for retrieval without undoing that organization.
UNIQUE Constraints
The UNIQUE keyword prevents duplicate entries in the column where it is applied. In this schema, Artist.name is marked TEXT UNIQUE. The same constraint is applied to the title column in Album and Track. If an insert attempts to create a duplicate value in one of those constrained columns, the database rejects that duplicate.
| Table | Constrained column | Purpose |
|---|---|---|
| Artist | name | Prevents duplicate artist names |
| Album | title | Prevents duplicate album titles |
| Track | title | Prevents duplicate track titles |
Using UNIQUE is more concise than creating separate indexes manually. It both prevents duplicates and enables efficient lookups through one keyword in the table definition.
Mistakes with Relationships
Expecting Album.artist_id to contain the artist's name
The Album table stores the id value from Artist, not the artist name itself.
Fix:
Use the artist_id reference to reach the related Artist row and retrieve its name.Looking for an artist directly in Track
The Track table links to Album, and Album links to Artist.
Fix:
Follow the chain from Track to Album through album_id, then from Album to Artist through artist_id.Treating repeated CSV rows as separate artist records
That creates redundancy and can violate the UNIQUE constraint on Artist.name.
Fix:
Check whether the artist exists, reuse its id when it does, and insert it only when it is absent.Ignoring duplicate handling during CSV loading
The database can reject duplicate values when the relevant column is UNIQUE.
Fix:
Use the uniqueness constraints together with INSERT IGNORE so duplicate inserts are skipped gracefully.
Practice the Retrieval Path
A track row contains album_id. The related album row contains artist_id. Explain, in order, which tables you follow to retrieve the track title, album title, and artist name together.
Hints
- Begin with the Track table.
- Use album_id to reach Album.
- Use artist_id from Album to reach Artist.
A CSV file contains several tracks by the same artist. Describe how the loader should avoid creating repeated artist records while still inserting the tracks.
Hints
- Check Artist before inserting.
- Reuse the existing artist id when the artist is already present.
- Use the album id when inserting each track.
Key Takeaways
- Artist, Album, and Track separate different kinds of information in a normalized music-library schema.
- Album.artist_id references Artist, while Track.album_id references Album.
- A JOIN retrieves related values by following those matching key references.
- UNIQUE prevents duplicate artist, album, and track values in the constrained columns.
- CSV loading maps one flat record into related rows and uses existing ids when records are already present.
Key Takeaways
- A normalized music library stores artists, albums, and tracks in separate tables.
- Foreign keys create the path from Track to Album to Artist.
- JOIN retrieval combines related values by matching those references.
- UNIQUE constraints protect the database from duplicate names and titles.
- CSV input is split into normalized records as the loader checks or creates artists, albums, and tracks.