Concatenation is one of those everyday SQL skills that quietly powers a huge amount of reporting work. Think about it: learning how to use CONCAT in SQL lets you stitch together first and last names, build readable order references, format addresses for mailing labels, and assemble file paths or email addresses directly inside your queries instead of in application code. This guide walks through the syntax, the differences between database engines, the notorious NULL behavior, and the practical patterns you will actually reach for at work.
What Is CONCAT in SQL?
CONCAT is a string function that joins two or more values into a single continuous string. Instead of returning separate columns, the result is one combined value. If a table stores first_name as "Amara" and last_name as "Okafor", CONCAT(first_name, last_name) gives you "AmaraOkafor", and adding a space between them gives you "Amara Okafor" And that's really what it comes down to..
The name itself is short for concatenation, which simply means linking things in a chain. The function is variadic in most modern databases, meaning it accepts two arguments, three arguments, or many more.
The Basic Syntax of CONCAT
The general form looks like this:
CONCAT(expression1, expression2, expression3, ...)
A simple query against a customers table:
SELECT
customer_id,
CONCAT(first_name, ' ', last_name) AS full_name,
city,
country
FROM customers;
Notice that literal text such as ' ' (a single space) is passed as one of the arguments. This is the most common technique for inserting separators: you place the separator between the values you want to join.
You can also mix columns and constants freely:
SELECT
CONCAT('Customer #', customer_id, ' - ', city) AS label
FROM customers;
How CONCAT Behaves in Different SQL Databases
CONCAT is part of the SQL standard, but each database vendor implements it with its own quirks. Knowing these differences saves hours of debugging Worth keeping that in mind..
SQL Server
Available since SQL Server 2012. Two behaviors matter most:
- NULL is treated as an empty string, so
CONCAT('a', NULL, 'b')returns'ab'. - Non-string values are converted automatically, so you can pass an
INTorDATETIMEwithout casting.
The catch is return type. In practice, cONCAT returns varchar(8000) or nvarchar(4000) unless one of the inputs is already a large-value type, which means long results can be silently truncated. Cast to NVARCHAR(MAX) when you expect very long output Turns out it matters..
MySQL and MariaDB
CONCAT() returns NULL if any argument is NULL. In practice, CONCAT('a', NULL, 'b') yields NULL, not 'ab'. So this surprises people migrating from SQL Server. Use CONCAT_WS() or wrap values in IFNULL() / COALESCE() to avoid it Not complicated — just consistent..
Also note that in MySQL, || means logical OR by default, not concatenation, unless you enable the PIPES_AS_CONCAT SQL mode.
PostgreSQL
PostgreSQL offers both CONCAT() and the || operator, and they behave differently:
CONCAT()ignores NULLs and converts arguments to text.||propagates NULL, so'a' || NULLreturns NULL.
-- Returns 'ab'
SELECT CONCAT('a', NULL, 'b');
-- Returns NULL
SELECT 'a' || NULL || 'b';
Oracle Database
Oracle's CONCAT() accepts exactly two arguments. For anything longer, use the || operator, which is the idiomatic choice:
SELECT first_name || ' ' || last_name AS full_name
FROM employees;
Oracle also treats NULL as an empty string in string concatenation, so || behaves predictably.
SQLite
The traditional approach is the || operator. Starting with SQLite 3.44.0, concat() and concat_ws() are also available, bringing it in line with other engines Nothing fancy..
Handling NULL Values Safely
NULL handling is the single biggest source of confusion. The safest habit is to normalize values with COALESCE before concatenating:
SELECT
CONCAT(
COALESCE(first_name, ''),
' ',
COALESCE(last_name, '')
) AS full_name
FROM customers;
This works