Difference between GROUP_CONCAT() and CONCAT_WS()?

Question

I have searched unsuccessfully for a satisfactory explanation on the difference between GROUP_CONCAT() and CONCAT_WS().

Are they as closely related as I think they are?

What are the differences in usage, speed, etc. between these two functions?

Solution 1

GROUP_CONCAT is used when you want to have non-NULL values from different column rows in a single row. For this you need to have GROUP BY to work.

CONCAT_WS is to join two or more strings.

Example,

GROUP_CONCAT(CONCAT_WS(' ', firstname, lastname) ORDER BY id ASC SEPARATOR ',');

Outputs something like,

John Doe,Blah Blah,Tom Cruise,Lorem Ipsum

here space between the name is because of CONCAT_WS, while whole result in one row is because of GROUP_CONCAT

Solution 2

Pictorial difference:

http://www.w3resource.com/mysql/string-functions/mysql-concat_ws-function.php

GROUP_CONCAT below

http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

Comments

[ONOZ]

Also, if you want to use a separator with GROUP_CONCAT, you can use the SEPARATOR statement like this: GROUP_CONCAT(lastname SEPARATOR '-')

[François]

CONCAT_WS() can actually join an arbitrary number of values, not just two. The PHP equivalent is implode().