CONCAT()

Syntax

How it works

CONCAT() glues values together into one string. You pass it two or more arguments and it returns them stuck end to end, in order, with nothing added between them. It is the standard way to build a full name from separate columns, prefix a value with a label, or assemble a formatted line of output. If you want a separator between every piece, reach for CONCAT_WS() instead.

CONCAT() is supported as a function in MySQL, PostgreSQL and SQL Server. Every engine also offers an operator form. The SQL standard concatenation operator is ||, used by PostgreSQL, Oracle and SQLite, while SQL Server uses +. So CONCAT(a, b), a || b and a + b can all produce the same result depending on the engine you are on.

The important difference is how each form handles NULL. The CONCAT() function treats a NULL argument as an empty string, so a missing middle name does not wipe out the whole result. The standard || operator (and SQL Server +) instead returns NULL if any operand is NULL, because in SQL almost anything combined with NULL is NULL. That single rule is the source of most concatenation bugs.

One more trap is specific to MySQL: there || is a logical OR by default, not a string join. So 'a' || 'b' in MySQL evaluates the operands as booleans and returns a number, unless the server runs with the PIPES_AS_CONCAT SQL mode enabled. On MySQL, prefer the CONCAT() function and avoid || entirely.

Examples

Join first and last name

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

full_name
-------------
Aisha Khan
Omar Farouk
Lina Haddad

Join columns with a literal label

SELECT CONCAT('Order #', id, ' - ', status) AS label
FROM orders;

label
--------------------
Order #1 - paid
Order #2 - pending
Order #3 - paid

Numbers are auto cast to text

-- price is numeric; CONCAT converts it to a string
SELECT CONCAT(name, ': $', price) AS tag
FROM products;

tag
----------------
Mug: $9
Notebook: $12
Pen: $3

The || operator (PostgreSQL, SQLite, Oracle)

-- Standard SQL concatenation operator
SELECT first_name || ' ' || last_name AS full_name
FROM customers;

full_name
-------------
Aisha Khan
Omar Farouk
Lina Haddad

The + operator (SQL Server)

-- SQL Server uses + to concatenate strings
SELECT first_name + ' ' + last_name AS full_name
FROM customers;

full_name
-------------
Aisha Khan
Omar Farouk
Lina Haddad

CONCAT keeps rows when a value is NULL

-- middle_name is NULL for most rows
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name
FROM customers;

full_name
---------------
Aisha  Khan
Omar Al Farouk
Lina  Haddad

Common mistakes

-- In PostgreSQL/SQLite, one NULL wipes out the whole string
SELECT first_name || ' ' || middle_name || ' ' || last_name
FROM customers;  -- returns NULL when middle_name is NULL

-- CONCAT treats NULL as an empty string, so the row survives
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name)
FROM customers;

-- On MySQL, || is logical OR, not concatenation
SELECT first_name || last_name AS name
FROM customers;  -- returns 0 or 1, not a joined string

-- Use the CONCAT() function on MySQL
SELECT CONCAT(first_name, last_name) AS name
FROM customers;

-- SQL Server: + treats a NULL operand as NULL
SELECT first_name + ' ' + middle_name
FROM customers;  -- NULL when middle_name is NULL

-- CONCAT() is available in SQL Server 2012+ and ignores NULLs
SELECT CONCAT(first_name, ' ', middle_name)
FROM customers;

Performance

Concatenation itself is cheap: it is a per row string operation that runs in the projection step after rows are already selected. The cost is dominated by how many rows you touch, not by the join of a few short strings, so CONCAT() rarely shows up as a bottleneck on its own.

The real performance trap is concatenating inside a WHERE or JOIN condition, for example WHERE CONCAT(first_name, last_name) = 'AishaKhan'. Wrapping columns in a function makes the predicate non sargable, so the optimiser cannot use an index and falls back to a full scan. Filter on the raw columns instead, or build a computed and indexed column if you truly need to search the joined value.

When you concatenate many rows into report output, remember the work scales linearly with the result set. Push filtering and index use as early as possible so CONCAT() only runs on the rows you actually return.

Interview questions

-- PostgreSQL
SELECT CONCAT('a', NULL, 'b') AS with_concat,  -- 'ab'
       'a' || NULL || 'b'    AS with_pipes;   -- NULL

SELECT CONCAT('Total: ', 19.99) AS line;  -- 'Total: 19.99'

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM customers;