COALESCE()
- Description
- Syntax
- Arguments
- Examples

COALESCE()

Description

The COALESCE() function returns the first non-null value in a list.

Syntax

1. > COALESCE(val1, val2, ...., val_n)

Arguments

Arguments	Description
val1, val2, val_n	Required. The values to test

Examples

Example: Calculate

1. mysql> SELECT COALESCE(1)+COALESCE(1);
2. +---------------------------+
3. | coalesce(1) + coalesce(1) |
4. +---------------------------+
5. |                         2 |
6. +---------------------------+

Example: Comparison

1. drop table if exists t2;
2. create table t2(a float, b datetime);
3. insert into t2 values (12.345, '2022-02-20 10:10:10.999999');
4. insert into t2 values (3.45646, NULL);
5. insert into t2 values(NULL, '2023-04-03 22:10:29.999999');
6. insert into t2 values (NULL, NULL);

8. mysql> select * from t2;
9. +---------+---------------------+
10. | a       | b                   |
11. +---------+---------------------+
12. |  12.345 | 2022-02-20 10:10:11 |
13. | 3.45646 | NULL                |
14. |    NULL | 2023-04-03 22:10:30 |
15. |    NULL | NULL                |
16. +---------+---------------------+
17. mysql> select coalesce(a, 1.0) from t2;
18. +--------------------+
19. | coalesce(a, 1.0)   |
20. +--------------------+
21. | 12.345000267028809 |
22. | 3.4564599990844727 |
23. |                  1 |
24. |                  1 |
25. +--------------------+
26. mysql> select coalesce(a, 1) from t2;
27. +--------------------+
28. | coalesce(a, 1)     |
29. +--------------------+
30. | 12.345000267028809 |
31. | 3.4564599990844727 |
32. |                  1 |
33. |                  1 |
34. +--------------------+
35. mysql> select coalesce(b, 2022-01-01) from t2;
36. +---------------------------+
37. | coalesce(b, 2022 - 1 - 1) |
38. +---------------------------+
39. | 2022-02-20 10:10:11       |
40. |                           |
41. | 2023-04-03 22:10:30       |
42. |                           |
43. +---------------------------+

Example: ORDER BY Clause

1. CREATE TABLE t1 ( a INTEGER, b varchar(255) );
2. INSERT INTO t1 VALUES (1,'z');
3. INSERT INTO t1 VALUES (2,'y');
4. INSERT INTO t1 VALUES (3,'x');

6. mysql> SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(MIN(b), 'a');
7. +-------+
8. | min_b |
9. +-------+
10. | x     |
11. | y     |
12. | z     |
13. +-------+

15. mysql> SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(min_b, 'a');
16. +-------+
17. | min_b |
18. +-------+
19. | x     |
20. | y     |
21. | z     |
22. +-------+

24. mysql> SELECT MIN(b) AS min_b FROM t1 GROUP BY a ORDER BY COALESCE(MIN(b), 'a') DESC;
25. +-------+
26. | min_b |
27. +-------+
28. | z     |
29. | y     |
30. | x     |
31. +-------+

Example: Case When Clause

1. mysql> select if(1, cast(1111111111111111111 as unsigned), 1) i,case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, coalesce(cast(1111111111111111111 as unsigned), 1) co;
2. +---------------------+---------------------+---------------------+
3. | i                   | c                   | co                  |
4. +---------------------+---------------------+---------------------+
5. | 1111111111111111111 | 1111111111111111111 | 1111111111111111111 |
6. +---------------------+---------------------+---------------------+

Example: IN Subquery

1. CREATE TABLE ot (col_int_nokey int(11), col_varchar_nokey varchar(1));
2. INSERT INTO ot VALUES (1,'x');
3. CREATE TABLE it (col_int_key int(11), col_varchar_key varchar(1));
4. INSERT INTO it VALUES (NULL,'x'), (NULL,'f');

6. mysql> SELECT col_int_nokey FROM ot WHERE col_varchar_nokey IN(SELECT col_varchar_key FROM it WHERE coalesce(col_int_nokey, 1) );
7. +---------------+
8. | col_int_nokey |
9. +---------------+
10. |             1 |
11. +---------------+

Example: WHERE

1. CREATE TABLE ot1(a INT);
2. CREATE TABLE ot2(a INT);
3. CREATE TABLE ot3(a INT);
4. CREATE TABLE it1(a INT);
5. CREATE TABLE it2(a INT);
6. CREATE TABLE it3(a INT);
7. INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
8. INSERT INTO ot2 VALUES(0),(2),(4),(6);
9. INSERT INTO ot3 VALUES(0),(3),(6);
10. INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
11. INSERT INTO it2 VALUES(0),(2),(4),(6);
12. INSERT INTO it3 VALUES(0),(3),(6);

14. mysql> SELECT * FROM ot1 LEFT JOIN ot2 ON ot1.a=ot2.a WHERE COALESCE(ot2.a,0) IN (SELECT a FROM it3);
15. +------+------+
16. | a    | a    |
17. +------+------+
18. |    0 |    0 |
19. |    1 | NULL |
20. |    3 | NULL |
21. |    5 | NULL |
22. |    6 |    6 |
23. |    7 | NULL |
24. +------+------+

Example: HAVING

1. drop table if exists t1;
2. create table t1(a datetime);
3. INSERT INTO t1 VALUES (NULL), ('2001-01-01 00:00:00.12'), ('2002-01-01 00:00:00.567');

5. mysql> select a from t1 group by a having COALESCE(a)<"2002-01-01";
6. +---------------------+
7. | a                   |
8. +---------------------+
9. | 2001-01-01 00:00:00 |
10. +---------------------+

Example: ON CONDITION

1. drop table if exists t1;
2. drop table if exists t2;
3. create table t1(a INT,  b varchar(255));
4. create table t2(a INT,  b varchar(255));
5. insert into t1 values(1, "你好"), (3, "再见");
6. insert into t2 values(2, "日期时间"), (4, "明天");
7. > SELECT t1.a, t2.a FROM t1 JOIN t2 ON (length(COALESCE(t1.b)) = length(COALESCE(t2.b)));
8. +------+------+
9. | a    | a    |
10. +------+------+
11. |    1 |    4 |
12. |    3 |    4 |
13. +------+------+