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 ?

Biff MaGriff · Accepted Answer · 2010-09-23 17:07:54Z

5

Use a LEFT OUTER JOIN

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

answered Sep 23, 2010 at 17:07

Biff MaGriff

8,2619 gold badges65 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jake Wilson Over a year ago

Never used that one before. Thanks!

Collectives™ on Stack Overflow

MySQL - No result when joining tables using a NULL value

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related