MySQL COALESCE function

The COALESCE function returns the first non-null value from a list of values and replaces it with 0 when it encounters a null value. COALESCE (Str1,str2 ...);

e.g. All employees who are lower than the ' WARD ' Commission (COMM) are required to be identified in the table, and the employee who made the null is included. (Personal opinion, if the default value of the Database Commission field is not a value of 0, it is certainly the fault of developing that).

Select Ename,comm from emp where COALESCE (comm,0) < (select comm from emp whrer ename = "WARD");

Results:

+--------+------+| ename  | Comm |+--------+------+| SMITH  | NULL | | ALLEN  |  300 | | JONES  | NULL | | BLAKE  | NULL | | CLARK  | NULL | | SCOTT  | NULL | | KING   | NULL | | TURNER |    0 | | JAMES  | NULL | | MILLER | NULL | | ADAMS  | NULL | | FORD   | NULL |+--------+------+12 rows in Set

　　

Returns a non-null value:

Select Ename, Comm,coalesce (comm,0) from the EMP where COALESCE (comm,0) < (select comm from emp where ename = ' WARD ');

　　

+--------+------+------------------+| ename  | comm | COALESCE (comm,0) |+--------+------+------------------+| SMITH  | NULL |                0 | | ALLEN  |  |              300 | | JONES  | NULL |                0 | | BLAKE  | NULL |                0 | | CLARK  | NULL |                0 | | SCOTT  | NULL |                0 | | KING   | NULL |                0 | | TURNER |    0 |                0 | | JAMES  | NULL |                0 | | MILLER | NULL |                0 | | ADAMS  | NULL |                0 | | FORD   | NULL |                0 |+--------+------+------------------+12 rows in Set

　　

MySQL COALESCE function