How to Use GROUP_CONCAT in MySQL

Key Insights

GROUP_CONCAT aggregates multiple rows into a single comma-separated string, eliminating the need for application-level processing when building lists from related records
The function supports DISTINCT, ORDER BY, and custom separators, but has a default 1024-byte length limit that you’ll need to increase for production use
While powerful for reporting and denormalization, GROUP_CONCAT can create performance bottlenecks on large datasets and should be avoided in frequently-executed queries with millions of rows

Introduction to GROUP_CONCAT

GROUP_CONCAT is MySQL’s most underutilized aggregate function. While developers reach for COUNT, SUM, and AVG regularly, they often write application code to handle what GROUP_CONCAT does natively: combining multiple row values into a single string.

The function shines when you need to collapse one-to-many relationships into a single result row. Instead of fetching 50 rows for a user’s tags and processing them in your application, GROUP_CONCAT returns a single string: “php,mysql,docker,kubernetes”. This reduces network overhead and simplifies your code.

Here’s the difference in output:

-- Without GROUP_CONCAT: Multiple rows
SELECT u.username, t.tag_name
FROM users u
JOIN user_tags ut ON u.id = ut.user_id
JOIN tags t ON ut.tag_id = t.id
WHERE u.id = 1;

-- Result:
-- username | tag_name
-- john     | php
-- john     | mysql
-- john     | docker

-- With GROUP_CONCAT: Single row
SELECT u.username, GROUP_CONCAT(t.tag_name) AS tags
FROM users u
JOIN user_tags ut ON u.id = ut.user_id
JOIN tags t ON ut.tag_id = t.id
WHERE u.id = 1
GROUP BY u.id;

-- Result:
-- username | tags
-- john     | php,mysql,docker

The second query returns exactly what you need for display or API responses without iteration loops.

Basic Syntax and Simple Examples

The fundamental syntax is straightforward: GROUP_CONCAT(column_name). Like other aggregate functions, it works with GROUP BY to combine values per group.

Let’s use a realistic schema for a project management system:

CREATE TABLE projects (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE team_members (
    id INT PRIMARY KEY,
    project_id INT,
    member_name VARCHAR(100),
    FOREIGN KEY (project_id) REFERENCES projects(id)
);

INSERT INTO projects VALUES (1, 'Website Redesign'), (2, 'Mobile App');
INSERT INTO team_members VALUES 
    (1, 1, 'Alice'),
    (2, 1, 'Bob'),
    (3, 1, 'Charlie'),
    (4, 2, 'Diana'),
    (5, 2, 'Eve');

Now aggregate team members per project:

SELECT 
    p.name AS project,
    GROUP_CONCAT(tm.member_name) AS team
FROM projects p
LEFT JOIN team_members tm ON p.id = tm.project_id
GROUP BY p.id;

-- Result:
-- project           | team
-- Website Redesign  | Alice,Bob,Charlie
-- Mobile App        | Diana,Eve

The GROUP BY clause is critical. Without it, GROUP_CONCAT combines all rows across all projects into a single string. With it, you get one concatenated string per group.

Customizing Output with Parameters

GROUP_CONCAT accepts several modifiers that control the output format.

DISTINCT removes duplicate values:

-- Sample data with duplicates
CREATE TABLE page_views (
    page_id INT,
    visitor_country VARCHAR(50)
);

INSERT INTO page_views VALUES 
    (1, 'USA'), (1, 'Canada'), (1, 'USA'), (1, 'Mexico'), (1, 'USA');

-- Without DISTINCT
SELECT GROUP_CONCAT(visitor_country) FROM page_views WHERE page_id = 1;
-- Result: USA,Canada,USA,Mexico,USA

-- With DISTINCT
SELECT GROUP_CONCAT(DISTINCT visitor_country) FROM page_views WHERE page_id = 1;
-- Result: USA,Canada,Mexico

ORDER BY sorts values before concatenation:

SELECT 
    p.name,
    GROUP_CONCAT(tm.member_name ORDER BY tm.member_name ASC) AS team
FROM projects p
JOIN team_members tm ON p.id = tm.project_id
GROUP BY p.id;

-- Result:
-- Website Redesign | Alice,Bob,Charlie  (alphabetically sorted)

SEPARATOR changes the delimiter from the default comma:

-- Pipe separator
SELECT GROUP_CONCAT(member_name SEPARATOR ' | ') FROM team_members WHERE project_id = 1;
-- Result: Alice | Bob | Charlie

-- Semicolon for CSV export
SELECT GROUP_CONCAT(member_name SEPARATOR '; ') FROM team_members WHERE project_id = 1;
-- Result: Alice; Bob; Charlie

-- Newline for text reports
SELECT GROUP_CONCAT(member_name SEPARATOR '\n') FROM team_members WHERE project_id = 1;
-- Result: Alice
--         Bob
--         Charlie

Combine all three for maximum control:

SELECT GROUP_CONCAT(
    DISTINCT visitor_country 
    ORDER BY visitor_country DESC 
    SEPARATOR ' → '
) AS visitor_flow
FROM page_views 
WHERE page_id = 1;
-- Result: USA → Mexico → Canada

Working with Multiple Columns

You can concatenate formatted strings by combining columns within GROUP_CONCAT using CONCAT:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    department VARCHAR(50),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO employees VALUES
    (1, 'Engineering', 'John', 'Doe', 'john@example.com'),
    (2, 'Engineering', 'Jane', 'Smith', 'jane@example.com'),
    (3, 'Engineering', 'Bob', 'Johnson', 'bob@example.com');

-- Format: "FirstName LastName (email)"
SELECT 
    department,
    GROUP_CONCAT(
        CONCAT(first_name, ' ', last_name, ' (', email, ')')
        ORDER BY last_name
        SEPARATOR '; '
    ) AS team_roster
FROM employees
GROUP BY department;

-- Result:
-- Engineering | John Doe (john@example.com); Bob Johnson (bob@example.com); Jane Smith (jane@example.com)

You can also use multiple GROUP_CONCAT calls in a single query:

SELECT 
    department,
    GROUP_CONCAT(DISTINCT last_name ORDER BY last_name) AS surnames,
    GROUP_CONCAT(email ORDER BY last_name SEPARATOR '; ') AS email_list,
    COUNT(*) AS headcount
FROM employees
GROUP BY department;

This pattern is useful for reports where you need different aggregations of the same data.

Handling the Length Limit

GROUP_CONCAT has a maximum length defined by group_concat_max_len, which defaults to 1024 bytes. This will truncate your results silently if exceeded—a nasty production bug.

Check your current limit:

SELECT @@group_concat_max_len;
-- Result: 1024 (default)

For session-level changes (affects only your current connection):

SET SESSION group_concat_max_len = 1000000;  -- 1MB

For global changes (affects all new connections):

SET GLOBAL group_concat_max_len = 1000000;

Demonstrate the truncation issue:

-- Create test data that exceeds 1024 bytes
SET SESSION group_concat_max_len = 50;  -- Artificially low for demo

SELECT GROUP_CONCAT(member_name SEPARATOR ', ') AS truncated
FROM team_members;
-- Result will be cut off at 50 bytes with no warning

-- Fix it
SET SESSION group_concat_max_len = 10000;

SELECT GROUP_CONCAT(member_name SEPARATOR ', ') AS complete
FROM team_members;
-- Now you get the full result

In production, set this in your MySQL configuration file:

[mysqld]
group_concat_max_len = 10000000  # 10MB

Choose a value based on your actual data. If you’re concatenating product descriptions or log entries, you might need 10MB+. For simple tag lists, 100KB is usually sufficient.

Common Pitfalls and Best Practices

NULL Handling: GROUP_CONCAT ignores NULL values entirely:

INSERT INTO team_members VALUES (6, 1, NULL);

SELECT GROUP_CONCAT(member_name) FROM team_members WHERE project_id = 1;
-- Result: Alice,Bob,Charlie (NULL is omitted, not shown as empty)

If you need to preserve NULLs, use COALESCE:

SELECT GROUP_CONCAT(COALESCE(member_name, 'TBD')) FROM team_members WHERE project_id = 1;
-- Result: Alice,Bob,Charlie,TBD

Performance Considerations: GROUP_CONCAT performs well on small to medium datasets (thousands of rows per group). But on millions of rows, it becomes expensive:

-- Bad: Concatenating millions of log entries
SELECT user_id, GROUP_CONCAT(log_message) 
FROM activity_log 
GROUP BY user_id;  -- This will crush your database

-- Better: Limit the scope
SELECT user_id, GROUP_CONCAT(log_message) 
FROM activity_log 
WHERE created_at > NOW() - INTERVAL 7 DAY
GROUP BY user_id;

-- Or use application-level aggregation for very large datasets

When NOT to Use GROUP_CONCAT:

When you need to parse the result back into individual values (use proper joins instead)
For frequently-executed queries on large tables (consider materialized views)
When the concatenated string will be used for further SQL operations (maintain normalized structure)

Real-World Use Cases

E-commerce Product Categories:

SELECT 
    p.id,
    p.product_name,
    p.price,
    GROUP_CONCAT(c.category_name ORDER BY c.category_name SEPARATOR ', ') AS categories
FROM products p
LEFT JOIN product_categories pc ON p.id = pc.product_id
LEFT JOIN categories c ON pc.category_id = c.id
GROUP BY p.id
LIMIT 10;

User Permissions Summary:

SELECT 
    u.username,
    GROUP_CONCAT(
        DISTINCT r.role_name 
        ORDER BY r.priority DESC 
        SEPARATOR ', '
    ) AS roles,
    GROUP_CONCAT(
        DISTINCT p.permission_name 
        ORDER BY p.permission_name 
        SEPARATOR ', '
    ) AS permissions
FROM users u
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
LEFT JOIN role_permissions rp ON r.id = rp.role_id
LEFT JOIN permissions p ON rp.permission_id = p.id
WHERE u.is_active = 1
GROUP BY u.id;

Building JSON-like Structures:

SELECT 
    order_id,
    CONCAT(
        '[',
        GROUP_CONCAT(
            CONCAT('{"product":"', product_name, '","qty":', quantity, '}')
            SEPARATOR ','
        ),
        ']'
    ) AS order_items_json
FROM order_items
GROUP BY order_id;
-- Result: [{"product":"Widget","qty":2},{"product":"Gadget","qty":1}]

GROUP_CONCAT is a powerful tool for data aggregation and reporting. Use it to simplify your queries, reduce application complexity, and improve performance on appropriately-sized datasets. Just remember to configure the length limit and monitor performance on large tables.