Mysql COALESCE
From w3cyberlearnings
Contents |
MySQL COALESCE Function
Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
Syntax COALESCE
- Va1: value 1
- Va2: value 2
- Va3: value 3
COALESCE(Va1,Va2,Va3...);
Example 1
mysql> SELECT COALESCE(3,40); +----------------+ | COALESCE(3,40) | +----------------+ | 3 | +----------------+ 1 row in set (0.00 sec)
Example 2
mysql> SELECT COALESCE(NULL,40); +-------------------+ | COALESCE(NULL,40) | +-------------------+ | 40 | +-------------------+ 1 row in set (0.00 sec)
Exampl 3
mysql> SELECT * FROM student;
+------------+------+------+
| student_id | name | age |
+------------+------+------+
| 1 | John | 28 |
| 2 | Bob | 30 |
| 3 | Kat | 28 |
| 4 | Pher | 24 |
| 5 | Jim | 21 |
+------------+------+------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM student
-> WHERE student_id=COALESCE(NULL,3,5,2);
+------------+------+------+
| student_id | name | age |
+------------+------+------+
| 3 | Kat | 28 |
+------------+------+------+
1 row in set (0.00 sec)
Related Links
- BETWEEN ... AND ...
- COALESCE
- <=>
- =
- >=
- >
