[Solution] Why is this join not working?
Click here to view the question
Use a left join to preserve all the records from the all_platforms table. Also apply a coalesce function to fill in null values with 0 in the number_of_plays column.
select all_platforms.platform , coalesce(july_data.number_of_plays,0) as number_of_plays from all_platforms left join july_data on all_platforms.platform = july_data.platform
Concepts Covered: Left Joins, Join Logic, Coalesce
Joins by default are inner joins. In other words, the result will only contain records where all columns referenced in the join condition exist. In the original question, there are no rows for "iOS" or "android" in the july_data table. Therefore, these rows were omitted from the final result when using the default inner join.
To remedy this, use a left join. Left joins preserve all the values in the first table referenced (in the example above, all_platforms). If the value does not show up in the second table, any columns from the second table in the result will contain a null value.
Now, we want to fill in a 0 for all the null values. This is where coalesce comes into play. If the first argument is null, coalesce replaces it with the second argument. In the code above, we fill in nulls with the integer 0.