Use CONCAT or CONCAT_WS in MySQL?

I. What the documentation says

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

CONCAT()

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

In other words, any value NULLthat goes into a CONCATnullifies all other values.

CONCAT_WS()

CONCAT_WS()does not skip empty strings. However, it ignores any value NULLafter 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 CONCATwith valuesNULL

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

That means, if we do an insert like this:

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

the column apellido_paternowill receive by default the value NULL.

If we use CONCATin 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érezwill be:

    datos
1   NULL

In other words, neither Pedro, nor Pérez, nor anything... just NULL. Truly terrible right?

Note that NULLand an empty string are not the same thing. Let's try another INSERT:

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

Since you are observant, you have seen that the column apellido_paternois receiving an empty string ''.

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

The result of the SELECTabove for Juan Ariaswill be:

    datos
3    Arias, Juan

The problem is that when no value is given explicitly, the column gets a value NULLand that's terrible if we use CONCAT.

(b) The behavior ofCONCAT_WS()

Let's see how he 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;

It would result in:

    datos
1   Pérez Pedro

And with Juan Arias?:

    datos
3    Arias Juan

This CONCAT_WSthen is almost a wonder!

According to its syntax, we have to put the separator at the beginning only, and then the list of columns or values ​​that we want to concatenate. If the spacer is the same that's great, but what if I need different spacers? .

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

Good, but... there is a blank space when some data is missing! That has a solution, it's up to you to find it.

III. conclusion

Since CONCAT():

• He is not a friend of valuesNULL
• Forces us to repeat the separator even in cases where the separator does not change

And seen that CONCAT_WS():

• He is a friend of valuesNULL
• It can be used as if CONCAT()in cases where the separator changes

We can conclude that it can be used CONCAT_WS()in all cases and if we want to use it CONCAT()we must be totally sure that all the values ​​involved are noneNULL .

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