To join several rows into one string per group in MySQL, use GROUP_CONCAT() with GROUP BY. The order and the separator go inside the brackets: GROUP_CONCAT(subject ORDER BY subject SEPARATOR ', '). Add DISTINCT straight after the opening bracket to drop duplicates. Two things catch people out: NULL values are skipped silently, and the result is cut off at group_concat_max_len, which is 1,024 bytes by default, with only a warning to tell you.
I ran every query in this post on MySQL 8.4.6 on 11 October 2026, with the default sql_mode (which includes ONLY_FULL_GROUP_BY), and pasted the real output under each one. Everything below is copy-and-paste ready: create the two tables, insert the rows and run each query in order. If you run your queries from PHP, my guide to PDO fetch() and fetchAll() shows how to read the results back.
The examples use two small tables: students and the subjects they’ve taken, with an optional score. I’ve deliberately included a duplicate (Chloe took Biology twice), a NULL score and a row with a NULL subject, because that’s where GROUP_CONCAT behaves in surprising ways.
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(40), class VARCHAR(10));
CREATE TABLE subjects (student_id INT, subject VARCHAR(40), score INT NULL);
INSERT INTO students VALUES
(1,'Ayesha','10A'), (2,'Ben','10A'), (3,'Chloe','10B'), (4,'Dev','10B');
INSERT INTO subjects VALUES
(1,'Maths',91), (1,'Physics',84), (1,'English',77),
(2,'History',68), (2,'Maths',73),
(3,'Biology',88), (3,'Maths',NULL), (3,'Biology',90),
(4,NULL,NULL);
GROUP_CONCAT() is an aggregate function, like COUNT() or SUM(). The MySQL aggregate function reference describes it simply as “return a concatenated string”. You group the rows, and it glues each group’s values together.
SELECT s.name, GROUP_CONCAT(sub.subject) AS subjects
FROM students s
JOIN subjects sub ON sub.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;
+--------+-----------------------+
| name | subjects |
+--------+-----------------------+
| Ayesha | Maths,Physics,English |
| Ben | History,Maths |
| Chloe | Biology,Maths,Biology |
| Dev | NULL |
+--------+-----------------------+
Notice three things. The default separator is a comma with no space. The order inside each list isn’t guaranteed; here it happens to follow insertion order, but you shouldn’t rely on that. And Dev’s only subject is NULL, so his whole result is NULL.
To control the order of values inside each string, put ORDER BY inside the function call. To change the separator, add SEPARATOR after it. Both are part of GROUP_CONCAT’s own syntax, separate from the query’s outer ORDER BY.
SELECT s.name,
GROUP_CONCAT(sub.subject ORDER BY sub.subject SEPARATOR ', ') AS subjects
FROM students s
JOIN subjects sub ON sub.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;
+--------+-------------------------+
| name | subjects |
+--------+-------------------------+
| Ayesha | English, Maths, Physics |
| Ben | History, Maths |
| Chloe | Biology, Biology, Maths |
| Dev | NULL |
+--------+-------------------------+
The outer ORDER BY s.id sorts the rows of the result. The inner ORDER BY sub.subject sorts the values within each string. You’ll often want both.
The inner ORDER BY doesn’t have to use the column you’re concatenating. Here I list each student’s subjects best score first, with the score in brackets and a pipe as the separator:
SELECT s.name,
GROUP_CONCAT(CONCAT(sub.subject, ' (', sub.score, ')')
ORDER BY sub.score DESC SEPARATOR ' | ') AS best_first
FROM students s
JOIN subjects sub ON sub.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;
+--------+------------------------------------------+
| name | best_first |
+--------+------------------------------------------+
| Ayesha | Maths (91) | Physics (84) | English (77) |
| Ben | Maths (73) | History (68) |
| Chloe | Biology (90) | Biology (88) |
| Dev | NULL |
+--------+------------------------------------------+
Look closely at Chloe. Her Maths entry has disappeared. Its score is NULL, CONCAT() returns NULL if any argument is NULL, and GROUP_CONCAT skips NULL values. That leads us to the biggest gotcha.
The MySQL manual states that, unless otherwise stated, aggregate functions ignore NULL values, and GROUP_CONCAT follows that rule. If every value in a group is NULL, the result is NULL rather than an empty string.
SELECT s.name, GROUP_CONCAT(sub.subject) AS subjects, COUNT(*) AS rows_in_group
FROM students s
LEFT JOIN subjects sub ON sub.student_id = s.id
WHERE s.id = 4
GROUP BY s.id, s.name;
+------+----------+---------------+
| name | subjects | rows_in_group |
+------+----------+---------------+
| Dev | NULL | 1 |
+------+----------+---------------+
There’s one row in the group, but its subject is NULL, so the concatenated result is NULL. Two fixes:
COALESCE(GROUP_CONCAT(sub.subject), 'none'), which returned none for Dev in my test.COALESCE() or IFNULL() on the value inside, for example GROUP_CONCAT(CONCAT(sub.subject, ' (', COALESCE(sub.score, '-'), ')')), so Chloe’s Maths stays in the list instead of vanishing. In my test that returned Biology (90) | Biology (88) | Maths (-). Note that NULL scores sort last with DESC, which is usually what you want.Put DISTINCT first inside the brackets to keep only unique values. You can combine it with ORDER BY and SEPARATOR:
SELECT s.name,
GROUP_CONCAT(DISTINCT sub.subject ORDER BY sub.subject SEPARATOR ', ') AS subjects
FROM students s
JOIN subjects sub ON sub.student_id = s.id
WHERE s.id = 3
GROUP BY s.id, s.name;
+-------+----------------+
| name | subjects |
+-------+----------------+
| Chloe | Biology, Maths |
+-------+----------------+
Biology now appears once. If duplicate rows are a symptom of bad data rather than a legitimate repeat, fix the table instead; my post on deleting duplicate rows in MySQL covers safe ways to do that.
Any string literal works as a separator, including an empty one and escape sequences:
SELECT class,
GROUP_CONCAT(name ORDER BY name SEPARATOR '') AS squashed,
GROUP_CONCAT(name ORDER BY name SEPARATOR '\n') AS one_per_line
FROM students
GROUP BY class;
+-------+-----------+--------------+
| class | squashed | one_per_line |
+-------+-----------+--------------+
| 10A | AyeshaBen | Ayesha
Ben |
| 10B | ChloeDev | Chloe
Dev |
+-------+-----------+--------------+
The new-line version breaks the text table, which is exactly what you want when you’re building a plain-text email or a CSV cell. Other separators I find handy are ' | ' for readable reports and ';' for exporting to spreadsheets that treat commas as column breaks.
This one causes real bugs in production. GROUP_CONCAT results are capped by the group_concat_max_len system variable. On my MySQL 8.4.6 server, SELECT @@group_concat_max_len; returned 1024. When a result is longer, MySQL cuts it off and raises a warning, but the query still succeeds, so your application never notices.
To demonstrate, I set a tiny limit of 20 bytes:
SET SESSION group_concat_max_len = 20;
SELECT GROUP_CONCAT(name ORDER BY id SEPARATOR ', ') AS names FROM students;
SHOW WARNINGS;
+----------------------+
| names |
+----------------------+
| Ayesha, Ben, Chloe, |
+----------------------+
+---------+------+---------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------+
| Warning | 1260 | Row 4 was cut by GROUP_CONCAT() |
+---------+------+---------------------------------+
Dev was silently dropped, and the string even ends with a stray separator. Raising the limit for the session fixes it:
SET SESSION group_concat_max_len = 1000000;
SELECT GROUP_CONCAT(name ORDER BY id SEPARATOR ', ') AS names FROM students;
+-------------------------+
| names |
+-------------------------+
| Ayesha, Ben, Chloe, Dev |
+-------------------------+
Practical advice:
SET SESSION group_concat_max_len = ... right after connecting, in the same connection that runs the query. It only lasts for that session.MySQL’s GROUP_CONCAT has no LIMIT clause inside the brackets (MariaDB added one, MySQL hasn’t), so a common trick is to order the list and then keep the first N items with SUBSTRING_INDEX():
SELECT s.name,
SUBSTRING_INDEX(GROUP_CONCAT(sub.subject ORDER BY sub.score DESC SEPARATOR ','), ',', 2) AS top2
FROM students s
JOIN subjects sub ON sub.student_id = s.id
WHERE s.id = 1
GROUP BY s.id, s.name;
+--------+---------------+
| name | top2 |
+--------+---------------+
| Ayesha | Maths,Physics |
+--------+---------------+
SUBSTRING_INDEX(str, ',', 2) returns everything before the second comma. It breaks if the values themselves contain your separator, so pick one that can’t appear in the data. For anything more complex, a window function such as ROW_NUMBER() in a subquery is more robust.
If the result is going to code rather than a human, consider JSON_ARRAYAGG(), which the MySQL manual lists as returning the result set “as a single JSON array”. You get proper quoting for free, so commas inside values can’t break your parsing:
SELECT s.name, JSON_ARRAYAGG(sub.subject) AS subjects_json
FROM students s
JOIN subjects sub ON sub.student_id = s.id
WHERE s.id IN (1, 2)
GROUP BY s.id, s.name
ORDER BY s.id;
+--------+---------------------------------+
| name | subjects_json |
+--------+---------------------------------+
| Ayesha | ["Maths", "Physics", "English"] |
| Ben | ["History", "Maths"] |
+--------+---------------------------------+
In PHP you can json_decode() that column straight into an array. The trade-off: JSON_ARRAYAGG doesn’t accept an ORDER BY inside the brackets. When I tried JSON_ARRAYAGG(name ORDER BY name) on MySQL 8.4.6, it failed with syntax error 1064. Use GROUP_CONCAT when the order of items matters, or sort the array in your application code after decoding it.
Because MySQL 8.4 enables ONLY_FULL_GROUP_BY by default, every non-aggregated column in your SELECT must be in the GROUP BY or depend on it. Grouping by the primary key s.id while selecting s.name is fine, since the name depends on the id. But this query fails:
SELECT s.class, s.name, GROUP_CONCAT(sub.subject)
FROM students s
JOIN subjects sub ON sub.student_id = s.id
GROUP BY s.class;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'gc_demo.s.name' which is not functionally dependent on columns in
GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Each class has several names, so MySQL can’t pick one. Either add s.name to the GROUP BY or aggregate it too, for example GROUP_CONCAT(DISTINCT s.name). Don’t switch the SQL mode off to make the error go away; it’s protecting you from random results.
GROUP_CONCAT is brilliant for display: a tag list next to a blog post, a comma-separated list of products in an order summary, or a quick report you paste into a spreadsheet. It’s a poor fit when you need to filter, count or join on the individual values later. Searching inside a concatenated string with LIKE '%Maths%' is slow and matches partial words, and storing comma-separated lists in a column breaks the basic rule of one value per field. Keep the data in proper rows and only concatenate at the very end, in the query that feeds the page.
If any part of the query comes from user input, such as a class name from a dropdown, bind it with a prepared statement rather than gluing it into the SQL string. My guide to PHP PDO prepared statements shows the pattern. GROUP_CONCAT itself is just another column in the result; you read it with fetch() like any other value and explode(', ', $row['subjects']) if you need an array back.
Put it inside the brackets, after the column: GROUP_CONCAT(subject ORDER BY subject ASC). It sorts the values within each concatenated string. The query’s outer ORDER BY sorts the result rows separately.
Add SEPARATOR as the last part inside the brackets: GROUP_CONCAT(subject SEPARATOR ', '). The default is a comma with no space. Use SEPARATOR '' for no separator or SEPARATOR '\n' for one value per line.
It has hit group_concat_max_len, which was 1,024 bytes by default on my MySQL 8.4 server. MySQL truncates the string and raises warning 1260. Run SET SESSION group_concat_max_len = 1000000; before the query.
Every value in that group was NULL, and aggregate functions ignore NULLs. Wrap the call in COALESCE(GROUP_CONCAT(col), '') to get an empty string or a default label instead.
Add DISTINCT straight after the opening bracket: GROUP_CONCAT(DISTINCT subject ORDER BY subject SEPARATOR ', ').
Not with a LIMIT inside the function in MySQL, but you can order the values and keep the first N with SUBSTRING_INDEX(GROUP_CONCAT(col ORDER BY score DESC), ',', N). For complex cases, use ROW_NUMBER() in a subquery.
GROUP_CONCAT returns a plain string with your chosen separator and supports ORDER BY and DISTINCT inside the call. JSON_ARRAYAGG returns a JSON array with proper quoting, which is safer to parse in code.
// note
This is a learning note from studying the web. It is one small topic, written so I can remember it. It is not a course and not a claim that I have finished the subject.
If a sentence is wrong, say so from the contact page and name this title. Drafts never appear here. Related notes, when they exist, are other published posts, and the same sample rule applies to each of them.
Find duplicates with GROUP BY and HAVING, preview them with ROW_NUMBER(), then delete all but one with a self-join. Error 1093, NULLs, case and spaces, and a UNIQUE key.
$stmt->fetch(PDO::FETCH_ASSOC) returns one row as an array, fetchAll() returns them all and fetchColumn() returns one value. Tested fetch modes, JOIN key clashes and the rowCount() trap.
Learn PHP PDO prepared statements with MySQL: connect safely, insert, select, update and delete, plus LIKE, IN lists, LIMIT, transactions and SQL injection.