MySQL - No result when joining tables using a NULL value

Question

Similar question here but this is slightly different...

I have two tables that I want to join:

location
---------------------------
| id | city    | state_id |
---------------------------
| 1  | Denver  |        6 |
| 2  | Phoenix |        2 |
| 3  | Seattle |     NULL |
---------------------------

state
-------------------
| id | name       |
-------------------
| 1  | Alabama    |
| 2  | Alaska     |
| 3  | Arizona    |
| 4  | Arkansas   |
| 5  | California |
| 6  | Colorado   |
-------------------

SELECT
    location.id,
    location.city,
    state.name
FROM
    location
JOIN
    state ON state.id = location.state_id;

However, in the case where location.state_id happens to be NULL (perhaps the person inputting the data forgot to select a state), the query would not return a result, but that doesn't mean the location doesn't exist.

How do I get around this problem and somehow display all the locations, even though the state_id might be NULL ?

Answer 1

Use a LEFT OUTER JOIN

SELECT
location.id,
location.city,
state.name
FROM
    location
LEFT OUTER JOIN
    state ON state.id = location.state_id;