Did you notice that our previous query only showed us Jim Smith. We have a record in the database to Bob Rocker, right?

mysql> select names.lastName, addresses.zip from names, addresses where names.id = addresses.nameId;
+----------+-------+
| lastName | zip   |
+----------+-------+
| Smith    | 90000 |
+----------+-------+
1 row in set (0.00 sec)

This happens because of the type of join we made. There are different types of joins and each one produces slightly different results.

We want a query that will show us all names and all known addresses.

This is where a left join comes into play.

mysql> select names.lastName, addresses.zip from names left join addresses on names.id = addresses.nameId;
+----------+-------+
| lastName | zip   |
+----------+-------+
| Smith    | 90000 |
| Rocker   | NULL  |
+----------+-------+
2 rows in set (0.00 sec)

Now you'll see a null in the zip column. This basically lets us know that there wasn't any data for that column or that row.

What happens when we insert another row into addresses for Jim Smith?

insert into addresses set nameId=1, street="4321 Good St", city="Los Angeles", state="CA", zip="90001";

mysql> select names.lastName, addresses.zip from names, addresses where names.id = addresses.nameId;
+----------+-------+
| lastName | zip   |
+----------+-------+
| Smith    | 90001 |
| Smith    | 90000 |
+----------+-------+
2 rows in set (0.00 sec)

And using a left join:

mysql> select names.lastName, addresses.zip from names left join addresses on names.id = addresses.nameId;
+----------+-------+
| lastName | zip   |
+----------+-------+
| Smith    | 90000 |
| Smith    | 90001 |
| Rocker   | NULL  |
+----------+-------+
3 rows in set (0.00 sec)

What changed here?

We removed where from our query, but we added the keywords, left join and on. We're using left join to create a relationship between names and addresses. We then use the keyword on to specifically say that we want this condition to be met in our join, aka names.id = addresses.nameId, aka our join condition.

You should also notice that we have more than one record for Jim Smith. This is what makes SQL unique with it's relational system. You can have multiple entries in one table and one entry in another, and still be able to relate to one another. This is because the nameId in addresses links back to id in names.