So we have a song table with song information, and an album table with album information. Suppose we want to combine the two on our display? We need to have a field that joins the two tables. In this case, the item that is the same is likely to be the album name.
select song, songs.artist, songs.album, purchasedate, songs.rating/10 as "Song Rating", albums.rating as "Album Rating"
from songs join albums on songs.album=albums.album order by artist limit 10
I’ve chosen to “limit 10” so that I only see ten rows. Otherwise, the screen would fill up with several thousand rows. When you are testing queries, it is useful to limit the number of records you ask for.
This is how you join tables: the main table on the left, the word join, the table you want to combine it with on the right, and a description of what you want to join it on. Here, we’re joining it on “when the album column in the songs table is equal to the album column in the albums table.” The “on” part of the query often ends up looking a lot like a “where” section.
