When MySQL Left JOIN specifies that the NULL column returns a specific value for elaboration

  • 2020-05-27 07:21:24
  • OfStack

The coalesce function can take multiple arguments and will return the value of the first of these arguments that is not NULL, or NULL if all of the supplied arguments are NULL
The ifnull function and coalesce function are the same, only two parameters can be accepted
The if function takes three arguments and implements something like a 3 yuan judge (? :), that is, if the first parameter is not NULL and is not 0, the second parameter will be returned; otherwise, the third parameter will be returned

SELECT a.*,coalesce(t.cous,0) as count FROM brand as a 
left join (select brandid as bid,count(1) as cous from shopbrand group by brandid) t on a.brandid=t.bid 

The above syntax is to do a statistic, which itself will return NULL, but it is not a good practice to be empty, so coalesce is used to solve the problem

Related articles: