Recipe

Concatenate strings

Short answer: || is the ANSI standard concatenation operator (Postgres / DuckDB / SQLite / Oracle / Snowflake / Redshift / BigQuery). CONCAT(a, b, c) is a function-form alternative supported almost everywhere, and the only option on MySQL (where || is logical OR by default) and SQL Server (older versions). For inserting a separator between values, use CONCAT_WS(separator, a, b, c). Watch nulls: a || NULL returns NULL on most engines (Oracle is the exception, treating NULL as an empty string); CONCAT skips nulls on Postgres and SQL Server but returns NULL on MySQL and BigQuery.

Canonical SQL #

sql

-- ANSI || operator (Postgres / DuckDB / SQLite / Oracle / Snowflake / Redshift).
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- CONCAT function (works on every major engine).
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- CONCAT_WS — concat with separator, skips NULL inputs.
SELECT CONCAT_WS(', ', street, city, state, zip) AS address FROM addresses;

-- + operator on SQL Server.
SELECT first_name + ' ' + last_name AS full_name FROM users;

Example #

Loading SQL editor...

Common patterns #

sql

-- Build a contact label; fall back gracefully on NULL pieces.
SELECT CONCAT_WS(' ', name, email) AS contact FROM users;

-- Or with COALESCE for explicit fallback.
SELECT name || ' <' || COALESCE(email, 'no email') || '>' AS contact FROM users;

-- Concatenate a number into a string (engines auto-cast some types, not others).
SELECT 'order-' || id::TEXT FROM orders;        -- Postgres / DuckDB
SELECT CONCAT('order-', CAST(id AS VARCHAR)) FROM orders;  -- portable

-- Build a comma-separated list across rows — this is a different operation.
-- See: concatenate rows into a string (STRING_AGG / LISTAGG / GROUP_CONCAT).

Dialect comparison #

Engine	Syntax	Gotcha
PostgreSQL / DuckDB	`a \|\| b`	any NULL operand makes the result NULL; `CONCAT()` skips NULLs
MySQL	`CONCAT(a, b)`	`\|\|` is logical OR unless `PIPES_AS_CONCAT`; `CONCAT` returns NULL on any NULL arg
SQL Server	`CONCAT(a, b)`	`+` propagates NULL and throws on numeric operands; `CONCAT` treats NULL as `''`
BigQuery	`CONCAT(a, b)`	returns NULL on any NULL argument
Snowflake	`a \|\| b`	both `\|\|` and `CONCAT` propagate NULL
Oracle	`a \|\| b`	NULL is treated as empty string; does not propagate
SQLite	`a \|\| b`	any NULL operand makes the result NULL

Dialect notes #

Postgres / DuckDB / SQLite: || is concatenation. Any NULL operand makes the whole expression NULL. CONCAT (where present) is a "null-tolerant" alternative — Postgres' CONCAT skips nulls.
Oracle: || is concatenation, but NULL is treated as an empty string. It does not propagate, unlike every other || engine.
MySQL: || is logical OR by default, so 'a' || 'b' returns 1, not 'ab'. Set PIPES_AS_CONCAT SQL mode to make it concatenation. Use CONCAT(a, b, c) for portability; MySQL's CONCAT returns NULL if any argument is NULL. CONCAT_WS skips NULL arguments.
SQL Server: + is the legacy concat operator (and the cause of countless type-conversion errors when one side is numeric). CONCAT (2012+) is null-tolerant — treats NULL as empty string. CONCAT_WS (2017+).
Snowflake: || and CONCAT both work; both propagate NULL (like MySQL's CONCAT, unlike SQL Server's). CONCAT_WS skips nulls.
BigQuery: supports both || and CONCAT; CONCAT returns NULL if any argument is NULL. To join values with a separator, use ARRAY_TO_STRING(['a', 'b'], separator) — note BigQuery array literals use [...], not ARRAY[...].
Redshift: || and CONCAT(a, b) (two-argument only) work; both propagate NULL.

Warning

The single most common bug: a NULL in the middle of an || expression returns NULL for the whole row. first_name || ' ' || last_name returns NULL when either name is missing — not "Ana ", not "Costa", not "(unknown) Costa," but NULL. Either reach for CONCAT_WS (skips nulls cleanly), wrap each side in COALESCE(col, ''), or accept that the row should be NULL and document the choice. This bug shows up most often in dashboards where a "name" column suddenly displays as blank for the few users with a missing surname.