MySQL AVG() return 0 if NULL

Question

I have 3 tables, shown below:

mysql> select * from Raccoon;
+----+------------------+----------------------------------------------------------------------------------------------------+
| id | name             | image_url                                                                                          |
+----+------------------+----------------------------------------------------------------------------------------------------+
|  3 | Jesse Coon James | http://www.pawfun.com/wp/wp-content/uploads/2010/01/rabbid.png                                     |
|  4 | Bobby Coon       | https://c2.staticflickr.com/6/5242/5370143072_dee60d0ce2_n.jpg                                     |
|  5 | Doc Raccoon      | http://images.encyclopedia.com/utility/image.aspx?id=2801690&imagetype=Manual&height=300&width=300 |
|  6 | Eddie the Rac    | http://www.felid.org/jpg/EDDIE%20THE%20RACCOON.jpg                                                 |
+----+------------------+----------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from Review;
+----+------------+-------------+---------------------------------------------+--------+
| id | raccoon_id | reviewer_id | review                                      | rating |
+----+------------+-------------+---------------------------------------------+--------+
|  1 |          3 |           1 | This raccoon was a fine raccoon indeed.     |      5 |
|  2 |          5 |           2 | This raccoon did not do much for me at all. |      2 |
|  3 |          3 |           1 | asdfsadfsadf                                |      5 |
|  4 |          5 |           2 | asdfsadf                                    |      1 |
+----+------------+-------------+---------------------------------------------+--------+
4 rows in set (0.00 sec)

mysql> select * from Reviewer;
+----+---------------+
| id | reviewer_name |
+----+---------------+
|  1 | Kane Charles  |
|  2 | Cameron Foale |
+----+---------------+
2 rows in set (0.00 sec)

I'm trying to build a select query that will return all of the columns in Raccoon as well as an extra column which grabs an average of Review.rating (grouped by id). The problem I face is that there is no guarantee that there will be rows present in the Review table for every single Raccoon (as determined by the FK, raccoon_id which references Raccoon.id. In situations where there are zero rows present in the Review table (for a given Raccoon.id, ie Review.raccoon_id) I'd like the query to return 0 as the average for that Raccoon.

Below is the current query I'm using:

mysql> SELECT *, (SELECT IFNULL(AVG(rating),0) FROM Review WHERE raccoon_id=Raccoon.id GROUP BY raccoon_id) AS "AVG" FROM Raccoon ORDER BY "AVG" ASC;
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
| id | name             | image_url                                                                                          | AVG    |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
|  3 | Jesse Coon James | http://www.pawfun.com/wp/wp-content/uploads/2010/01/rabbid.png                                     | 5.0000 |
|  4 | Bobby Coon       | https://c2.staticflickr.com/6/5242/5370143072_dee60d0ce2_n.jpg                                     |   NULL |
|  5 | Doc Raccoon      | http://images.encyclopedia.com/utility/image.aspx?id=2801690&imagetype=Manual&height=300&width=300 | 1.5000 |
|  6 | Eddie the Rac    | http://www.felid.org/jpg/EDDIE%20THE%20RACCOON.jpg                                                 |   NULL |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
4 rows in set (0.00 sec)

As you can see above, the query isn't returning 0 for Raccoons with id of 4 and 6, it is simply returning NULL. I need it to return something like the following (note the ordering, sorted by lowest average review first):

+----+------------------+----------------------------------------------------------------------------------------------------+--------+
| id | name             | image_url                                                                                          | AVG    |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
|  4 | Bobby Coon       | https://c2.staticflickr.com/6/5242/5370143072_dee60d0ce2_n.jpg                                     | 0.0000 |
|  6 | Eddie the Rac    | http://www.felid.org/jpg/EDDIE%20THE%20RACCOON.jpg                                                 | 0.0000 |
|  5 | Doc Raccoon      | http://images.encyclopedia.com/utility/image.aspx?id=2801690&imagetype=Manual&height=300&width=300 | 1.5000 |
|  3 | Jesse Coon James | http://www.pawfun.com/wp/wp-content/uploads/2010/01/rabbid.png                                     | 5.0000 |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+

Answer 1

use IFNULL outside your subquery since it will return null it there is not match on the outer table,

IFNULL((SELECT AVG(rating) FROM Review WHERE raccoon_id=Raccoon.id GROUP BY raccoon_id), 0) AS "AVG"

Or you can also use LEFT JOIN,

SELECT  ra.id, ra.name, ra.image_url,
        IFNULL(AVG(rv.rating),0)AS "AVG" 
FROM    Raccoon ra
        LEFT JOIN Review rv
            ON rv.raccoon_id = ra.id
GROUP   BY ra.id, ra.name, ra.image_url  
ORDER   BY "AVG" ASC;

Answer 2

You don't want a group by in the subquery. This is dangerous because it could return more than one row (although the where prevents this). More importantly, with no group by, the subquery is an aggregation query that always returns one row. So, you can put the logic in the subquery:

SELECT r.*,
       (SELECT COALESCE(AVG(rev.rating),0)
        FROM Review rev
        WHERE rev.raccoon_id = r.id
       ) AS "AVG"
FROM Raccoon r
ORDER BY "AVG" ASC;

Also: always use qualified column names when you have a correlated subquery. This is a good practice to prevent problems in the future.