SQL SELECT statement table join

I will introduce the concept of join. For this reason, I have prepared two trial tables: album (album table) and track (track table ).

Album: contains the summary of 200 music CDs from Amazon.

Album (asin, title, artist, price, release, label, rank)

Track table: the detailed information of each track in each album (because it is a music CD, it can also be called a song.

Track (album, dsk, posn, song)

SQL phrase FROM album JOIN track ON album. asin = track. album indicates connecting album and track tables.

Among them, album. asin indicates the unique ID of the album, and track. album indicates the album number associated with the album in the track table.

After the connection, a temporary table is obtained. The fields in each record in the temporary table are composed of two parts,

In addition to the corresponding field album (title, artist...) in the album table, it also contains all the fields track (album, disk, posn and song) in the track table ).

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

Take a look at some specific instances,

1. List the album names and authors whose names are 'alison'
Copy codeThe Code is as follows:
SELECT title, artist
FROM album JOIN track
ON (album. asin = track. album)
WHERE song = 'alison'


Obviously, the song name, album name, and author are in two tables respectively. You must use table join to complete this query.

2. Which artist has recorded the 'exodus' song'

SELECT artist
FROM album JOIN track ON (asin = album)
WHERE song = 'exodus'

The two asin fields used for connection. Because album is unique in both tables, you do not have to add the table name as the prefix.

We recommend that you use a prefix, for example, album. asin = track. album.

Or if the table name is too long, it can be replaced by as, for example, FROM album as a JOIN track as t ON a. asin = t. album

3. list all the songs of the 'blur' album in the track table
Copy codeThe Code is as follows:
SELECT song
FROM album JOIN track ON (asin = album)
WHERE title = 'blur'

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