Let’s take another look at that query.
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
For the from portion of the statement, we have “from songs join albums”. This is the part that combines the two tables. However, we also need to restrict the combined table, because by default MySQL combines the tables in every possible way. If we had a table with the records “2” and “3”, and another table with the records “3” and “6”, a simple combine would give us four rows: “2, 3”, “2, 6”, “3, 3”, and “3, 6”.
So, to the on portion of the statement, we add a restriction that matches a column in one table to the same column in the other: “where songs.album=albums.album”.
Once we have more than one table, it is possible—often even likely—that one or more columns will have the same name. We differentiate between the two columns by prefacing the column name with the table name, and separating the two with a period. So, “songs.album” is the album column from the songs table, and “albums.album” is the album column from the albums table.
We do the same thing in the select portion of the statement. We have two choices for artist, and we choose the artist column from the songs table. Similarly for the album column. for the rating column, we choose to display each one (although we modify the rating column in songs to make it comparable to the rating column in albums) and give them each a special name.
