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
– JNevill, Commented Jul 19, 2017 at 15:11
Possible duplicate of What is the difference between "INNER JOIN" and "OUTER JOIN"? — JNevill
– JNevill, Commented Jul 19, 2017 at 15:13
@JNevill I think those conditions in the WHERE clause also need to be moved to the ON clause. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 19, 2017 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
– JNevill, Commented Jul 19, 2017 at 15:16

Colin Harrison · Accepted Answer · 2017-07-19 15:13:41Z

2

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

answered Jul 19, 2017 at 15:13

Colin Harrison

1942 silver badges19 bronze badges

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

Comments

Isaiah3015 · Accepted Answer · 2017-07-19 15:13:50Z

1

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

answered Jul 19, 2017 at 15:13

Isaiah3015

4913 silver badges11 bronze badges

1 Comment

Tim Biegeleisen Over a year ago

No need to use DISTINCT in the select clause, and owners.city should possibly be included in the GROUP BY list.

Tim Biegeleisen · Accepted Answer · 2017-07-19 15:18:16Z

1

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.

edited Jul 19, 2017 at 15:18

answered Jul 19, 2017 at 15:13

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

Include NULL values in MySQL SELECT results

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related