USING GROUP_CONCAT LISTS DIRECTLY IN SQL

What is the GROUP_CONCAT function?

Ever wondered how to retrieve the values within a group when you perform a GROUP BY operation on your data? GROUP_CONCAT returns a delimited list of a field’s or expression’s values when grouped. It supports ordering of the values, delimiter is a parameter (SEPARATOR) and you can apply a DISTINCT filter, thus retrieving unique values. I am giving an example which is highly unlikely to be used in real-life but for the sake of demonstration, plus we see the power of prepared statements which provide the ability of dynamic SQL.

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

Caveat: the length of the generated list length is controlled by a variable group_concat_max_len.
The good news are that it can also be defined as a SESSION variable dynamically.

SET SESSION group_concat_max_len = 100000;

Default length in bytes is 1024.
If your GROUP_CONCAT result exceeds the permitted length, a warnings is generated and you can view it as usual with:

SHOW WARNINGS;

How can I use a list of IDs?

Let’s assume the common scenario that you have a table with a structure like:

CREATE TABLE test(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  fk_id_1 INT UNSIGNED,
  fk_id_2 INT UNSIGNED
);

CREATE TABLE fk_table_1(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
);

Executing this:

SET @LIST:='';
SELECT @LIST:=GROUP_CONCAT(DISTINCT fk_id_1 SEPARATOR ',') AS fk_id_1_list
FROM test 
WHERE fk_id_2 = 7293
GROUP BY fk_id_2;

Comma is the default separator, so the specification is redundant. We stored the list in session variable @LIST.

We can retrieve data from fk_table_1 using the list of IDs:

SET @FK_TABLE_STMT = CONCAT('SELECT id, name FROM 
                             fk_table_1 WHERE id IN(', 
                             @LIST, 
                             ')'
                           );
PREPARE fk_table_data FROM @FK_TABLE_STMT;
EXECUTE fk_table_data;

…and we retrieve the data from fk_table_1.
After we are done we remove the prepared statement by executing:

DEALLOCATE fk_table_data;