Writing Efficient SQL Queries

JOIN and ON clauses reconstruct data across multiple tables by following relational connections between foreign keys and primary keys.

Programming

Reconstructing Related Data

A relational database may store connected information in separate tables. An Artist table can store artist records, while an Album table stores album records. A JOIN reconstructs related information by following the connection between a primary key in one table and a foreign key in another.

The JOIN clause combines rows. The ON clause tells the database which rows are related and therefore may be combined.

How does a JOIN use the ON condition to match rows from two separate tables and produce one combined result row?

Following a One-to-Many Link

A one-to-many relationship has one parent record connected to multiple child records. In the source example, Artist is the parent table and Album is the child table. Each album row has a foreign key pointing to one artist, but one artist can be referenced by several album rows.

One Artist, Several Albums

Suppose an Artist row has primary-key value 7. Three Album rows contain artist_id value 7.

Find the parent key: The Artist row contributes the primary-key value 7.

Inspect child keys: Each of the three Album rows contains artist_id value 7, so each points to the same Artist row.

Trace the JOIN result: The JOIN produces one result row for each matching Album row. The same artist information can therefore appear in three result rows, once alongside each related album.

One parent Artist row produces three JOIN result rows because it matches three child Album rows.

Why does one row in a parent table correspond to multiple rows in the JOIN result when it matches several related child rows?

Primary Keys as Connection Points

A primary key is a unique identifier assigned to each row in a table. In the Album table, each album has its own id. The Album table also contains artist_id, a foreign key that points to a specific row in the Artist table.

The foreign key is the bridge between the tables. Its value comes from the primary-key values of the related table. When the database compares Album.artist_id with Artist.id, a matching value identifies the Artist row connected to that Album row.

What contains what, and how does a foreign-key value in one table point to the matching primary-key row in another table?

Reading the JOIN Structure

A basic JOIN query names the columns to return, identifies the first table, names the table to join, and then states the matching condition. The ON condition should compare the foreign key in one table with the primary key in the related table.

sql

SELECT columns
FROM table1
JOIN table2
ON table1.foreign_key = table2.primary_key

SELECT columns
FROM table1
JOIN table2
ON table1.foreign_key = table2.primary_key

Query part	Role
SELECT columns	Chooses which columns appear in the result
FROM table1	Names the starting table
JOIN table2	Adds rows from another table
ON table1.foreign_key = table2.primary_key	Defines which rows are related

The parts of the basic JOIN pattern

Following Multiple Relationships

The same reasoning extends across more than two tables. With Artist, Album, and Track tables, one Artist can have many Albums, and one Album can have many Tracks. A query that joins all three tables follows both relationships.

Reconstructing Track Context

Trace the meaning of one result row when Artist, Album, and Track are joined.

Start with a Track row: The result represents one track record at the most detailed level of this relationship chain.

Connect the Track to an Album: The Album relationship supplies the album information associated with that track.

Connect the Album to an Artist: The Artist relationship supplies the artist information associated with the album.

Each result row represents one track while also carrying the related album and artist context.

This is data reconstruction: information that was stored in normalized tables is brought together in the query result by following each key-based connection. Because an album can have multiple tracks, the artist and album information may recur across several result rows, once for each related track.

Supporting Efficient Matching

Correct relationships and efficient execution depend on how the tables are constructed. Primary keys should have uniqueness constraints so that each primary-key value identifies one row. Foreign keys should have indexes so the database can quickly locate rows that match the ON condition instead of scanning the entire table.

Database feature	How it supports a JOIN
Uniqueness constraint on a primary key	Ensures the primary key is truly unique, which is essential for correct matching
Index on a foreign key	Helps the database quickly locate rows that match the ON condition

Common JOIN Mistakes

Treating a one-to-many relationship as if it must produce one result row
A JOIN produces one result row for each matching child record.
Fix: Count the matching child rows and expect the parent information to recur once per connected child.
Matching unrelated columns in the ON condition
The ON condition determines which rows are combined, so an incorrect comparison does not follow the stored relationship.
Fix: Compare the foreign key from one table with the primary key of the related table.
Assuming the foreign key contains the complete parent record
The foreign key stores the primary-key value that points to the related Artist row; it is the bridge, not the complete parent record.
Fix: Use a JOIN to bring the related parent-table columns into the result.
Ignoring table structure when considering performance
The database may need to scan the entire table to find rows matching the ON condition.
Fix: Use indexes on foreign keys and uniqueness constraints on primary keys when constructing the relationships.

Practice the Relationship Trace

MEDIUM

Write the structure of a JOIN between Artist and Album. Use the Album foreign key artist_id and the Artist primary key id in the ON condition. Then explain how many result rows one Artist row can produce when three Album rows reference it.

Hints

Begin with SELECT, FROM, JOIN, and ON.
The matching condition compares Album.artist_id with Artist.id.
A one-to-many relationship produces one result row for each matching child record.

Your attempt (not saved)

What do you think happens?

An Artist row is referenced by four Album rows. How many JOIN result rows represent that artist?

One
Four
One for every Artist row in the table

Reveal answer

Answer: Four

A one-to-many JOIN produces one result row for each matching child record. Four Album rows connected to the Artist row therefore produce four result rows for that artist.

Key Takeaways

JOIN combines rows from separate tables, while ON defines which rows match.
A foreign key stores the primary-key value that connects a child row to a parent row.
A one-to-many relationship produces multiple result rows when one parent matches multiple child records.
Joining Artist, Album, and Track follows multiple relationships to reconstruct the full context of each track.
Uniqueness constraints on primary keys support correct matching, and indexes on foreign keys support efficient matching.

Key Takeaways

JOIN and ON clauses reconstruct related data from separate tables.
Foreign keys point to primary keys and provide the bridge between tables.
One parent row can appear in multiple JOIN result rows when it has multiple related child rows.
Multiple JOIN clauses can follow a chain such as Artist to Album to Track.
Indexes on foreign keys and uniqueness constraints on primary keys support efficient and correct joins.