Concatenate with NULL Values in SQL

1. Overview

In SQL, the concatenation of a string is an essential and frequently used operation that joins many columns into a new string. However, we might find NULL values in some columns, affecting the concatenation result. Hence, it is very important to use the string concatenation operator in any SQL server cautiously.

In this tutorial, we’ll look at various approaches to handle NULL values in the concatenation of columns for different SQL database servers.

2. Database Initialization

Before we move forward, to check the string concatenation of NULL values in the table. As an example, we’ll use a table Department using the SQL statements contained in the Baeldung University schema. Since this data doesn’t contain null values, let’s update the few existing entries in the Department table by NULL value:

update Department SET code=NULL where id=2;
update Department SET name=NULL where id=4;

Now, this table contains both the valid and NULL values.

3. Handling NULL in MySQL

In SQL servers, the CONCAT function is used to concate 2 or more strings into a string. Moreover, this function is widely supported by all the different databases, including Oracle, SQL Server, and MySQL. CONCAT function can combine column values, literals, and variables in queries as its primary task is to contact multiple strings into one.

To understand the problem of CONCAT function with null values, let’s look at the query:

SELECT id, CONCAT(name, ' (', code, ')') AS department_info
    -> FROM Department;
+----+-----------------------------+
| id | department_info             |
+----+-----------------------------+
|  1 | Computer Science (CS)       |
|  2 | NULL                        |
|  3 | Mechanical Engineering (ME) |
|  4 | NULL                        |
|  5 | Mathematics (MA)            |
+----+-----------------------------+
5 rows in set (0.01 sec)

In the above result, we can see that due to code values null for id 2 and name value null for id 4, the resulting string is also null for complete department info.

3.1. Using CONCAT_WS

SQL uses the CONCAT_WS function “Concatenate With Separator” to concatenate multiple string values using a designated separator. MySQL databases and other databases support this capability. The primary benefit of CONCAT_WS over CONCAT is its capacity to place the separator solely between non-NULL entries by ignoring NULL values:

SELECT id, CONCAT_WS(' ', name, CONCAT('(', code, ')')) AS department_info
FROM Department;
+----+--------------------------------+
| id | department_info                |
+----+--------------------------------+
|  1 | Computer Science (CS)          |
|  2 | Electronics and Communications |
|  3 | Mechanical Engineering (ME)    |
|  4 | (CE)                           |
|  5 | Mathematics (MA)               |
+----+--------------------------------+
5 rows in set (0.01 sec)

In the above query, we can see that for id 2 code is null but the result appends it properly while ignoring null values.

3.2. Using COALESCE

We can also use COALESCE to handle the null values in SQL queries. All the main SQL databases, such as MySQL, PostgreSQL, SQL Server, and Oracle, support it. To handle null values, COALESCE is primarily used as its main function is to provide default values to null fields and then perform the other operations. To illustrate, let’s look at the select query:

SELECT id, CONCAT(COALESCE(name, ''), ' (', COALESCE(code, 'NULL'), ')') AS department_info
FROM Department;
+----+---------------------------------------+
| id | department_info                       |
+----+---------------------------------------+
|  1 | Computer Science (CS)                 |
|  2 | Electronics and Communications (NULL) |
|  3 | Mechanical Engineering (ME)           |
|  4 |  (CE)                                 |
|  5 | Mathematics (MA)                      |
+----+---------------------------------------+
5 rows in set (0.00 sec)

In the above result, we can see that concatenation respects the null values and provides the desired result as per our use case.

4. Using || in PostgreSQL and SQLite

In PostgreSQL and SQLite, we can use the || operator to perform the concatenation of strings and handle NULL values using the COALESCE function. The query works similarly to the concat and COALESCE functions, as we saw in the MySQL example. To demonstrate, let’s look at the example to perform this:

baeldung=# SELECT id, COALESCE(name, '') || ' (' || COALESCE(code, '') || ')' AS department_info
FROM Department;
 id |          department_info          
----+-----------------------------------
  1 | Computer Science (CS)
  3 | Mechanical Engineering (ME)
  5 | Mathematics (MA)
  2 | Electronics and Communications ()
  4 |  (CE)
(5 rows)

The above query concates name and code into department_info with handling the null values using the COALESCE where null values will be replaced by an empty string.

5. Conclusion

In this article, we looked into various solutions to handle NULL values cautiously with string concatenation. To achieve the desired result, we need to understand how different functions work on the SQL server. First, we looked at the CONCAT_WS and COALESCE functions in MySQL and the || operator with COALESCE for PostgreSQL and SQLite databases.