Use CONCAT or CONCAT_WS in MySQL?

I. What the documentation says

Let's first see what the documentation on both functions explains:

CONCAT()

  

CONCAT() returns NULL if any of its arguments is NULL .

In other words, any value NULL that enters a CONCAT cancels the other values.

CONCAT_WS()

  

CONCAT_WS() does not ignore empty strings. However, omit any   value NULL after the separator argument.

II. Determining which one to use in a real scenario

Suppose a table layout like this:

CREATE TABLE  IF NOT EXISTS persona 
    (
      persona_id          INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      nombre              VARCHAR(50) NOT NULL,
      apellido_paterno    VARCHAR(50),
      apellido_materno    VARCHAR(50) NOT NULL
    );

(a) The problem of CONCAT with the values NULL

The table layout shown above is a common design, in fact, any table could have columns such as apellido_paterno , that is, columns that support NULL values. And not only that, in any insert that does not assign values to those columns, its default value will be NULL .

That means that, if we make an insertion similar to this:

INSERT INTO persona (nombre, apellido_materno) 
            VALUES  ('Pedro','Pérez');

the apellido_paterno column will default to the value NULL .

If we use CONCAT in our table persona :

-- Tratamiento de NULL con CONCAT ... ¡terrible!

SELECT CONCAT(
              apellido_paterno, ' ', apellido_materno, ', ', nombre
             ) datos FROM persona;

The result, in the case of Pedro Pérez will be:

    datos
1   NULL

That is, neither Pedro, nor Pérez, nor anything ... only NULL . Truly terrible, right?

Note that NULL and one empty string are not the same. Let's try another INSERT :

INSERT INTO persona (nombre, apellido_paterno, apellido_materno) 
            VALUES  ('Juan', '', 'Arias');

As an observer, you have seen that the apellido_paterno column is receiving an empty string '' .

And we'll see that CONCAT() is a friend of empty strings ... It's that CONCAT() has its preferences ... it's not fair, no! :)

The result of SELECT above for Juan Arias will be:

    datos
3    Arias, Juan

The problem is that when no value is explicitly specified, the column acquires a value NULL and that is terrible if we use CONCAT .

(b) The behavior of CONCAT_WS()

Let's see how it behaves CONCAT_WS() with our friend Pedro Pérez :

-- Tratamiento de NULL con CONCAT_WS ... ¡genial +!

SELECT CONCAT_WS(
                  ' ', apellido_paterno, apellido_materno, nombre
                ) datos 
        FROM persona;

Would result in:

    datos
1   Pérez Pedro

And with Juan Arias ?:

    datos
3    Arias Juan

This CONCAT_WS is then almost a wonder!

According to its syntax, it is necessary to put the separator at the beginning only, and then the list of columns or values that we want to concatenate. If the separator is the same, it's wonderful, but What if I need different separators? .

We can do it! , putting an empty string as a separator and concatenating the columns and values as we do with CONCAT() , that is, handmade :

-- Tratamiento de NULL con CONCAT_WS y varios separadores ... ¡genial ++!

SELECT CONCAT_WS(
                  '', apellido_paterno, ' ', apellido_materno, ', ' ,nombre
                ) datos 
        FROM persona;

The result would be:

    datos
1    Pérez, Pedro
2   Ruiz García, Santiago
3    Arias, Juan

Well, but ... there is a blank space when some data is missing! That has a solution, it's your turn to look for it.

III. Conclusion

Given that CONCAT() :

• Not a friend of the values NULL
• Forces us to repeat the separator even in cases where the separator does not change

And seen that CONCAT_WS() :

• Is a friend of the values NULL
• Can be used as if CONCAT() in cases where the separator changes

We can conclude that you can use CONCAT_WS() in all cases and if you want to use CONCAT() we must be totally safe that all the values involved < strong> none is NULL .

A full demo of the code used in the response can be found here .