La función concat(), se utiliza para concatenar dos cadenas y a si formar una tercera, se puede concatenar más de dos cadenas utilizando la misma función pero seria anidadas las cadenas. En caso que el operador de concatenación sea nula, el resultado devolverá como resultado nulo.

SELECT 'Esta es una cadena' || ' otra cadena' FROM dual;

Como resultado nos muestra la cadena de texto concatenada lo cual es = «Esta es una cadena otra cadena»

Utilizando la función concat quedaría de otro modo, solo vasta con poner el nombre de la función y adentro de los paréntecis pondremos los parametro. Para concatenar tres palabras, se utiliza dos beses la función una adentro de la otra.
Como funciona esto, cuando tenemos una función dentro de otra función, la función interior se ejecuta primero y segunda se ejecuta al ultimo, un claro ejemplo es el siguiente, que como resultado nos mostrara «uno dos tres».

SELECT CONCAT(CONCAT('uno',' dos'),' tres') FROM dual;
Sintaxis

CONCAT( String1, String2)

Argumentos

String1, String2:  Ambos argumentos pueden ser cadenas de caracteres o expresiones de longitud fija o variable.

Tipo de retorno

CONCAT devuelve una cadena. El tipo de datos de la cadena es el mismo tipo que los argumentos de entrada.

Fuente – Amazon Redshift