Working with multiple tables 23

For the from portion of the statement, we have  from songs, 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 where 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.

Missing records

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, albums

where songs.album=albums.album and 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

When we do a left join, the relevant portion of the where part of the statement moves into an on

area. We are grabbing  from songs left join albums on songs.album=albums.album .

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