Esta función nos es muy útil por ejemplo cuando queremos mostrar dos o más datos de una tabla ya concatenados. Por ejemplo.

SELECT CONCAT(nombres,' ',apellidopaterno,' ',apellidomaterno) AS nombrecompleto FROM alumno

Y también podemos agregar algún texto o alguna etiqueta html por si estamos trabajando una aplicación web como muestra este ejemplo.

SELECT 
	idproducto,
	descripcion,
	CONCAT('<label class="label label-success">',codigo,'</label>') AS codigo
FROM producto

.o por ejemplo.

SELECT 
	idproducto,
	codigo,
	descripcion,
	IF(estado='ACTIVO',
		CONCAT('<label class="label label-success">',estado,'</label>'),
		CONCAT('<label class="label label-danger">',estado,'</label>')
	) AS estado
FROM producto

pues sí que nos ahora trabajo.