Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT first_name || ' ' || last_name AS full_name FROM customers;

Try it

SQL Building a full name and a label ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL NULL turns the whole result into NULL

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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 ''
SQL
-- 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 #' || 42 gives Order #42. Oracle converts implicitly too; SQL Server's + would try to add instead, so CAST the number first.
  • Dates and decimals convert using the database's default format, which may vary by settings. Format them explicitly with STRFTIME() or FORMAT() and printf().
  • Precedence: in SQLite, || binds tighter than * and +, so 'a' || 1 + 2 is evaluated as ('a' || 1) + 2. Use parentheses.
  • Use CONCAT_WS() to join many pieces with a separator and skip NULLs: see CONCAT() 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().