We’re asking MySQL for all songs whose album name matches an album name in the albums table. What if a song doesn’t have an album name?
select * from songs where album=""
There are five songs with no album. Let’s try and find one in the above 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
where song="Eddie Fisher" order by artist limit 10
Nothing. That’s because there is no album with no name in the albums table. The same thing will happen if we have some songs with an album name that does not match any album name in the albums table. If we want to see all songs, even ones with no matching album, we need to join the tables together with a left join. A left join is like a normal join, but it does not throw any records out on the left. In this case, the left table is “songs”, so a left join will show all songs, even ones with no album.
select song, songs.artist, songs.album, purchasedate, songs.rating/10 as "Song Rating", albums.rating as "Album Rating"
from songs left join albums on songs.album=albums.album
where song="Eddie Fisher" order by artist limit 10
Now, we find our Eddie Fisher record.
What if we wanted a list of all songs with no matching album? Notice that in the above example, purchasedate and Album Rating are both NULL for Eddie Fisher. That’s because those columns are in the albums table, and there is no matching record for Eddie Fisher in the albums table. We can use that to our advantage.
select song, songs.artist, songs.album
from songs left join albums on songs.album=albums.album
where albums.album is NULL order by artist
There are 1,198 songs that don’t have a matching album. If this were important data, we might want to figure out why. Most likely, some album names have been spelled differently in one table than they are in the other. Depending on the source of the data and the nature of the differences, you might make your fixes in MySQL using the update statement, or in the original source and re-import.
