[Solution] Why is this join not working?

Click here to view the question


Solution:

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

Explanation:

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.

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like Follow
  • 1 yr agoLast active
  • 498Views
  • 1 Following