Include NULL values in MySQL SELECT results

Question

I have the following MySQL tables:

tbl_pet_owners:

+----+--------+----------+--------+--------------+
| id | name   | pet      | city   | date_adopted |
+----+--------+----------+--------+--------------+
|  1 | jane   | cat      | Boston | 2017-07-11   |
|  2 | jane   | dog      | Boston | 2017-07-11   |
|  3 | jane   | cat      | Boston | 2017-06-11   |
|  4 | jack   | cat      | Boston | 2016-07-11   |
|  5 | jim    | snake    | Boston | 2017-07-11   |
|  6 | jim    | goldfish | Boston | 2017-07-11   |
|  7 | joseph | cat      | NYC    | 2016-07-11   |
|  8 | sam    | cat      | NYC    | 2017-07-11   |
|  9 | drew   | dog      | NYC    | 2016-07-11   |
| 10 | jack   | frog     | Boston | 2017-07-19   |
+----+--------+----------+--------+--------------+

tbl_pet_types:

+----------+-------------+
| pet      | type        |
+----------+-------------+
| cat      | mammal      |
| dog      | mammal      |
| goldfish | fish        |
| goldfish | seacreature |
| snake    | reptile     |
+----------+-------------+

I have the following SELECT statement

SELECT DISTINCT owners.name, owners.pet, owners.city,
group_concat(DISTINCT types.type separator ', ') AS type
FROM tbl_pet_owners owners
INNER JOIN tbl_pet_types types ON owners.pet = types.pet
WHERE owners.city = 'Boston' OR owners.city = 'NYC'
GROUP BY owners.name, owners.pet
ORDER BY owners.city

..which returns this result:

+--------+----------+--------+-------------------+
| name   | pet      | city   | type              |
+--------+----------+--------+-------------------+
| jack   | cat      | Boston | mammal            |
| jane   | cat      | Boston | mammal            |
| jane   | dog      | Boston | mammal            |
| jim    | goldfish | Boston | fish, seacreature |
| jim    | snake    | Boston | reptile           |
| drew   | dog      | NYC    | mammal            |
| joseph | cat      | NYC    | mammal            |
| sam    | cat      | NYC    | mammal            |
+--------+----------+--------+-------------------+

Unfortunately, jack's frog is omitted from the results because there is no entry for frog in tbl_pet_types. How can I edit my query to include jack's frog in the results (with type = NULL)?

Change your `INNER JOIN` to a `LEFT OUTER JOIN` and let 'er rip. `INNER JOIN` says "Only return results where the join condition is true". `LEFT OUTER JOIN` says "Return all results from the first table and only those results from the left-join'd table where the condition is true" — JNevill, Jul 19 '17 at 15:11
Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — JNevill, Jul 19 '17 at 15:13
@JNevill I think those conditions in the `WHERE` clause also need to be moved to the `ON` clause. — Tim Biegeleisen, Jul 19 '17 at 15:13
@TimBiegeleisen The WHERE clause conditions are all on the Left-Most/First table, so I believe they should stay. Putting them in the `ON` clause would be an odd move in this case. — JNevill, Jul 19 '17 at 15:16

score 2 · Answer 1 · answered Jul 19 '17 at 15:13

You're using an INNER JOIN so since there isn't a match on both tables, that row won't show up. Try using a LEFT JOIN so that all values in the left (first) table will show up, regardless of if there is an answer on the right (second) table.

You can visualize this with this image

Tim Biegeleisen · Answer 2 · 2017-07-19T15:18:16.070

I can suggest the following query, which is similar to what you had originally but with a few modifications.

SELECT
    owners.name,
    owners.pet,
    owners.city,
    GROUP_CONCAT(DISTINCT COALESCE(types.type, 'NA') separator ', ') AS type
FROM tbl_pet_owners owners
LEFT JOIN tbl_pet_types types
    ON owners.pet = types.pet
WHERE owners.city = 'Boston' OR owners.city = 'NYC'
GROUP BY owners.name, owners.pet, owners.city
ORDER BY owners.city

You don't need to use DISTINCT in your select clause, because GROUP BY will already achieve what you had in mind. The most major change was switching to a LEFT JOIN. This should prevent records from being dropped.

score 1 · Accepted Answer · answered Jul 19 '17 at 15:13

Use a LEFT JOIN to the tbl_pet_types instead of an INNER JOIN. What an INNER JOIN does is make sure you only see the records on both tables that are matching. In this particular case, you want all of the tbl_pet_owners data regardless if it's matching tbl_pet.

SELECT DISTINCT owners.name, owners.pet, owners.city,
group_concat(DISTINCT types.type separator ', ') AS type
FROM tbl_pet_owners owners
LEFT JOIN tbl_pet_types types ON owners.pet = types.pet
WHERE owners.city = 'Boston' OR owners.city = 'NYC'
GROUP BY owners.name, owners.pet
ORDER BY owners.city

No need to use `DISTINCT` in the select clause, and `owners.city` should possibly be included in the `GROUP BY` list. — Tim Biegeleisen, Jul 19 '17 at 15:27

Include NULL values in MySQL SELECT results

3 Answers3