The table joins in the SELECT statement in the simple SQL tutorial

To select data from two or more tables, we typically use table joins to implement this functionality.

This section describes the concept of join (join). For this we prepared two test tables: album (album table) and track (track table).

Album table: Contains an overview of 200 music CDs from Amazon.

Album (ASIN, title, artist, Price, release, label, Rank)

Track Table: The details of each album's tracks (because they are music CDs, so can also be called songs).

Track (album, DSK, Posn, song)

The SQL phrase from album join track on Album.asin=track.album represents the connection album and track tables.

Where Album.asin represents the unique identification number of the album, Track.album represents the album number associated with the album in the track table.

When you connect, you get a temporary table with a two-part field for each record in the temporary table.

In addition to the corresponding fields in the album Table album (title, Artist ...) also contains all the fields of the track table track (album, Disk, Posn and song).

With this temporary table, many queries are easy to implement.

Take a look at some concrete examples,

List name and author of the song named ' Alison '

SELECT title, artist
From album JOIN track
On (Album.asin=track.album)
WHERE song = ' Alison '

Clearly, the song name, album name, and author are in two tables, and you must use a table connection to complete the query.

Two, which artist recorded the song ' Exodus '

SELECT artist
From album JOIN track on (asin=album)
WHERE song = ' Exodus '

Two fields used as a connection asin,album is not necessarily prefixed with the table name because it is unique in all two tables.

But in order to facilitate understanding, we recommend using prefixes, such as: Album.asin=track.album

List all the songs that belong to the ' Blur ' album in the Repertoire table

SELECT Song
From album JOIN track on (asin=album)
WHERE title = ' Blur '

If we think of album join track on (asin=album) as a temporary table, the concept of join is well understood.