SQL Reference ยท Operators and Predicates
SQL || concatenation operator
Reference for joining strings in SQL: the standard || operator, NULL propagation, CONCAT, + in SQL Server and MySQL's default behavior, with examples.
The || operator (two vertical bars) joins two strings into one. It is the concatenation operator of standard SQL and works in SQLite, PostgreSQL and Oracle. MySQL and SQL Server use other spellings by default, so concatenation is one of the first places portable SQL breaks.
If either operand is NULL, the standard says the result is NULL. This is the most common concatenation bug: one missing middle name turns a whole full name into nothing.
SELECT first_name || ' ' || last_name AS full_name FROM customers;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
string1 || string2 [|| string3 ...]
| Part | Description |
|---|---|
string1, string2 |
Strings, or values the database converts to strings. |
|| |
Concatenation. Left to right. |
Returns: the joined string. The result is NULL if any operand is NULL (except in Oracle, where NULL behaves like an empty string).
Dialect differences
| Database | Operator | Function | NULL handling |
|---|---|---|---|
| Standard SQL | || |
none | NULL result |
| SQLite | || |
concat() (3.44+), concat_ws() |
|| gives NULL; concat() skips NULLs |
| PostgreSQL | || |
concat(), concat_ws() |
|| gives NULL; concat() skips NULLs |
| MySQL | CONCAT(); || is logical OR unless PIPES_AS_CONCAT is on |
CONCAT(), CONCAT_WS() |
CONCAT() gives NULL if any argument is NULL |
| SQL Server | + |
CONCAT(), CONCAT_WS() |
+ gives NULL (by default); CONCAT() treats NULL as empty |
| Oracle | || |
CONCAT(a, b) takes only two arguments |
NULL acts like '' |
-- MySQL SELECT CONCAT(first_name, ' ', last_name) FROM customers; -- SQL Server SELECT first_name + ' ' + last_name FROM customers;
Usage notes
- Guard nullable columns with
COALESCE():first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name. - Numbers are converted. In SQLite and PostgreSQL,
'Order #' || 42givesOrder #42. Oracle converts implicitly too; SQL Server's+would try to add instead, soCASTthe number first. - Dates and decimals convert using the database's default format, which may vary by settings. Format them explicitly with
STRFTIME()orFORMAT() and printf(). - Precedence: in SQLite,
||binds tighter than*and+, so'a' || 1 + 2is evaluated as('a' || 1) + 2. Use parentheses. - Use
CONCAT_WS()to join many pieces with a separator and skipNULLs: seeCONCAT() and CONCAT_WS(). - Indexes: filtering on a concatenated expression (
WHERE first_name || last_name = ...) cannot use indexes on the single columns. - Aggregate joining of values across rows is a different feature:
GROUP_CONCAT() and STRING_AGG().