Aggregate Functions and GROUP BY Clauses

A Row Reconstructed from Several Tables

A database can store related information in separate tables. An Artist table can store artists, while an Album table stores albums. A JOIN reconstructs related information by combining rows from those tables. The ON clause tells the database which values establish the relationship.

Primary Keys as Anchors

Every table has a primary key: a unique identifier assigned to each row as data is parsed into the database. In the Artist table, an artist's id identifies that artist's row. In the Album table, each album has its own id, and each album row also contains artist_id. That artist_id is a foreign key because it points to a specific row in the Artist table.

The foreign key is the bridge between the tables. When an Album row contains artist_id equal to an Artist row's id, the database can reconstruct which artist is associated with that album. The matching values, rather than the physical position of either row, establish the connection.

Tracing a One-to-Many Match

A one-to-many relationship means that one parent record can connect to multiple child records. One artist can have many albums. Each album row points to exactly one artist through its foreign key, while the same artist's primary-key value can appear in multiple Album rows. Consequently, a JOIN produces multiple result rows for that artist when several albums are connected to it.

Writing the Matching Query

The basic JOIN pattern names the columns to return, names the two tables, and then states the relationship in ON. In the common parent-child arrangement, the child table supplies the foreign key and the parent table supplies the primary key.

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

SELECT Artist.id, Album.id, Album.artist_id
FROM Artist
JOIN Album
  ON Album.artist_id = Artist.id;

This query follows the relational connection rather than guessing from table order. For every Album row, the ON condition looks for an Artist row whose id equals that album's artist_id. If one artist is connected to several albums, that artist contributes several joined result rows.

Following Two Relationships

The same reasoning can be extended across three tables. In the Artist, Album, and Track scenario, one artist has many albums and one album has many tracks. A query that JOINs all three tables follows both connections. Each resulting row represents one track while also carrying the associated album information and artist information.

• Start with the Track row that supplies the track-level record.
• Follow the relationship from the track to its Album row.
• Follow the relationship from that album to its Artist row.
• Read the final joined row as one track with its album and artist context.

Mistakes in Relational Matching

• Treating the table names as sufficient to create a relationship.

  The database needs an ON condition to determine which rows from the different tables should be combined.

  Fix: State the relationship explicitly by matching Album.artist_id to Artist.id.

• Expecting one result row for each parent row.

  A one-to-many relationship produces one joined result row for each connected child record.

  Fix: Interpret repeated parent information as evidence of multiple matching child rows.

• Confusing a child row's own primary key with its relationship key.

  Album.id identifies the album, while artist_id points to the related Artist row.

  Fix: Use the foreign-key column that stores the matching primary-key value from the parent table.

• Ignoring key design and access support.

  The source material identifies foreign-key indexes as important for locating matches efficiently and primary-key uniqueness as essential for correct JOIN behavior.

  Fix: Use indexes on foreign keys and uniqueness constraints on primary keys when constructing the relationships.

Reliable Relationship Design

When constructing related tables, make the primary key unique and provide an index on the foreign key. A uniqueness constraint ensures that primary-key values are truly unique, which is essential for correct JOIN behavior. An index lets the database locate rows matching the ON condition without scanning the entire table, which is important for JOIN performance on large tables.

Practice the Trace