SQL ISNULL()、NVL()、IFNULL()和COALESCE函数怎么理解？

您好，提供几个参考范例

1. SQL Server/MS Access用ISNULL()函数：
例：SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitOnOrder,0)) FROM Products;

2. Oracle用NVL()函数：
例：SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitOnOrder,0)) FROM Products;

3. MySQL用IFNULL()函数：
例：SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitOnOrder,0))  FROM Products;

或者：SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitOnOrder,0)) FROM Products;

mysql> select * from books;
+------+--------+------+------+
| name | author | jb   | id   |
+------+--------+------+------+
| ldk  | NULL   | NULL | NULL |
| xig  | sdds   | NULL | NULL |
+------+--------+------+------+
2 rows in set (0.00 sec)

mysql> select * from books where author is  null;
+------+--------+------+------+
| name | author | jb   | id   |
+------+--------+------+------+
| ldk  | NULL   | NULL | NULL |
+------+--------+------+------+
1 row in set (0.00 sec)

mysql> select * from books where author is not null;
+------+--------+------+------+
| name | author | jb   | id   |
+------+--------+------+------+
| xig  | sdds   | NULL | NULL |
+------+--------+------+------+
1 row in set (0.00 sec)