1 record(s) selected.

7.1.8  String concatenation and NULL values

The ANSI92 standard states that if you concatenate a NULL value onto an 

existing string, the result set is NULL. Example 7 13 shows you the behavior of 

MySQL.

Example 7 13   How MySQL concatenates strings and NULL values

mysql> create table t2 (col1 char(2));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2 values(NULL);

Query OK, 1 row affected (0.07 sec)

mysql> select concat( abc ,col1) as nullstring from t2;

+                    +

| nullstring |

+                    +

| NULL               |

+                    +

1 row in set (0.05 sec)

mysql> select concat( abc , coalesce(col1,  )) as nullstring from t2;

+                                  +

| nullstring |

+                                  +

| abc                              |

+                                  +

1 row in set (0.00 sec)

As shown, MySQL behaves as ANSI 92 compliant, and therefore gives you the 

same result sets as Example 7 14 for DB2.

Example 7 14   DB2 handles string and NULL concatenation similar

db2 => create table t2 (col1 char(2))

DB20000I  The SQL command completed successfully.

db2 => insert into t2 values(NULL)

DB20000I  The SQL command completed successfully.

db2 => select concat( abc , col1) as nullstring from t2

NULLSTRING

166 

MySQL to DB2 UDB Conversion Guide