When MySQLLeftJOIN is used, the specified NULL column returns a specific value.

Sometimes we have such an application. In the SQL leftjoin, we need to make a column with a NULL value not return NULL, but a specific value, such as 0. At this time, is_null is used.

Sometimes we have such an application. In SQL left join, we need to make a column with a NULL value not return NULL, but a specific value, such as 0. In this case, is_null is used.

Sometimes we have such an application. In SQL left join, we need to make a column with a NULL value not return NULL, but a specific value, such as 0.

At this time, using is_null (field, 0) will not work, and an error will be reported. It can be implemented using ifnull, but COALESE seems to be more compliant with the standard.

The coalesce function can accept multiple parameters and returns the first non-NULL value of these parameters. If all the provided parameters are NULL, NULL is returned.

The ifnull function is the same as the coalesce function, but only two parameters can be accepted.

The if function accepts three parameters, which are similar to the Trielement identifier (? :) Function, that is, if the first parameter is not NULL and is not 0, the second parameter is returned. Otherwise, the third parameter is returned.

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

The above syntax is a statistical statement, which returns NULL, but it is not a good practice if it is NULL, so coalesce is used to solve the problem.