Use CONCAT() to join values in one MySQL row, CONCAT_WS() to add a separator, and GROUP_CONCAT() to combine values across rows.
The functions are straightforward until NULL enters the query. Then one missing middle name can turn an entire display label into NULL, which is a lot of drama for a space character.
Table of contents
- Join columns with CONCAT
- Use CONCAT_WS for separators and optional values
- Combine values across rows with GROUP_CONCAT
- Watch types, collations, and output shape
- Make concatenation testable
- How this fits the rest of the stack
- FAQ
Join columns with CONCAT
CONCAT accepts one or more arguments and returns their joined string. Numbers are converted to strings. MySQL does not use the standard || operator for string concatenation under its usual SQL mode, so explicit functions are the portable choice within MySQL projects.
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;If any argument is NULL, CONCAT returns NULL. Use COALESCE when a missing value should become an empty string or a label, but decide whether hiding the missing value is actually correct.
Use CONCAT_WS for separators and optional values
CONCAT_WS means concatenate with separator. The first argument is placed between later values. It skips NULL arguments after the separator, which makes it useful for names and addresses with optional pieces. It does not skip empty strings.
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM users;
SELECT CONCAT_WS(', ', city, region, country) AS location
FROM offices;A NULL separator makes the result NULL. Normalize empty strings if your data uses them as missing values, or better, repair the model so one absence convention does not need to be guessed in every query.
Combine values across rows with GROUP_CONCAT
GROUP_CONCAT aggregates non-NULL values from several rows into one string. It supports DISTINCT, ordering, and a custom separator, which is useful for compact reports and administrative views.
SELECT team_id,
GROUP_CONCAT(DISTINCT role ORDER BY role SEPARATOR ', ') AS roles
FROM team_members
GROUP BY team_id;The result is constrained by group_concat_max_len and can become expensive or misleading for large groups. Do not use a giant comma-separated field as a substitute for returning normalized rows or a JSON aggregate to an application.
Watch types, collations, and output shape
Binary arguments can produce a binary result, while character arguments follow MySQL character-set and collation rules. Cast deliberately when concatenating identifiers, dates, or binary values for display. Formatting in SQL can be convenient, but it also couples output presentation to the database query.
Keep raw fields when the application needs sorting, localization, search, or structured API output. A full name rendered by SQL is fine for a report; it is less useful as the only representation available to every client.
Make concatenation testable
Test NULL, empty strings, whitespace-only values, multibyte text, long groups, and separator characters. Verify ordering explicitly when output order matters. SQL without an ORDER BY does not owe the application a stable sequence.
Use aliases that describe the derived value and inspect query plans when grouping large tables. String formatting should not quietly become the most expensive part of an endpoint.
How this fits the rest of the stack
If the query belongs to a database-backed service, model the MySQL instance, API, storage, and traffic together in the RunxBuild hosting calculator. Then connect the database to a service from the RunxBuild dashboard when the shape makes sense.
Useful related references:
- MySQL to MySQL: Migrating a Database Between Servers
- MySQL Pivot: There Is No PIVOT, So Here Is What to Do Instead
- mysql -u root -p: What the Flags Mean and Why You Should Stop Using Root
- Databases on RunxBuild
FAQ
How do I concatenate two strings in MySQL?
Use CONCAT(value1, value2). Add literal separators as additional arguments when needed.
Why does CONCAT return NULL?
CONCAT returns NULL if any argument is NULL. Use COALESCE or CONCAT_WS when the intended missing-value behavior differs.
What is the difference between CONCAT and CONCAT_WS?
CONCAT_WS takes a separator first and skips NULL values after it. CONCAT simply joins arguments and returns NULL if any argument is NULL.
How do I concatenate values from multiple rows?
Use GROUP_CONCAT, optionally with DISTINCT, ORDER BY, and SEPARATOR. Watch its configured maximum result length.
Can I use the pipe operator to concatenate in MySQL?
Under normal MySQL SQL mode, || is logical OR rather than concatenation. Use CONCAT for clear MySQL queries.