How to Concatenate Two Columns in SQL (MySQL, PostgreSQL, SQL Server)

👤 Written by Samragyi Vats | 🔄 Last updated: October 16, 2026 | 📁 Database

When working with databases, there are many situations where you need to "Concatenate Two Columns in SQL" to create a single readable value. For example, you might combine the first name and the last name to display a full name in a report or an application.

SQL provides several ways to do this, depending on the database system you are using. This is where students get stuck: the same query can work in one database, return NULL in another, and quietly return 0 in a third. Understanding these differences is essential when you need clean and meaningful output from raw database data.

In this article, we will explore the different methods used to concatenate columns in SQL, along with practical examples. Every result table below comes from a real run on MariaDB 10.11 (which behaves like MySQL here), PostgreSQL 16, and SQLite 3.45. By the end, you will clearly understand which method to use in your own database.

TL;DR: Concatenate Two Columns In SQL

What Is SQL Concatenation?

Concatenation in SQL simply means combining multiple strings, values, or columns into a single value. There are various ways to concatenate values in SQL, depending on which database system you are using.

SQL concatenation is an important concept because it helps you present data the way people actually want to read it. Instead of showing "Harry" and "Styles" in two separate columns, you can show "Harry Styles" in one.

Below are the main ways to concatenate in SQL:

• Using the CONCAT() function (and its partner, CONCAT_WS())
• Using the concatenation operator ||
• Using the + operator in SQL Server

We will discuss each of these in this article with the help of examples.

The best way to get comfortable with SQL functions like CONCAT() or the concatenation operator is to use them in real projects. Trying out some database project ideas for students can help you see how these query techniques are used in real database applications.

Why Students Often Struggle With SQL Concatenation?

From my experience teaching databases, I have noticed that students often assume SQL concatenation is simple, but then struggle with real, practical problems.

After watching how students approach it, I have put together the core reasons behind this. Here they are.

1) Different Databases Use Different Concatenation Syntax

Students expect SQL syntax to behave the same in every database. But concatenation operators vary between systems, which creates confusion.

SELECT first_name || ' ' || last_name AS full_name   -- Concatenation Using The Operator (||)
FROM students;

Some databases support the concatenation operator (||), while others need a function like CONCAT(). Students often think their query is wrong when the issue is actually database-specific syntax. Here is the same query on all three databases:

PostgreSQL and SQLite:

+-------------+
| full_name   |
+-------------+
| John Smith  |
| Maria Lopez |
+-------------+

MySQL / MariaDB:

+-----------+
| full_name |
+-----------+
| 0         |
| 0         |
+-----------+

The query does not fail in MySQL. It runs and returns 0 for every row. That is because in MySQL, || means logical OR by default, so it tries to treat the names as numbers. A silent wrong answer like this is much harder to notice than an error message. (MySQL 8 also adds a warning that using || as OR is deprecated, but it still runs it as OR.)

2) Mixing Text And Numbers Feels Unclear

I have seen students frequently concatenate strings with numeric values, but different databases handle data types differently. If not done properly, you can get wrong output or an error.

SELECT 'Total Score: ' || score AS result   -- Combine Text And A Number
FROM students;

PostgreSQL and SQLite:

+-----------------+
| result          |
+-----------------+
| Total Score: 88 |
| Total Score: 94 |
+-----------------+

Here, PostgreSQL and SQLite convert the number for you, because one side of || is already text. But that is not true everywhere. In PostgreSQL, if both sides are numbers, you get an error:

ERROR: operator does not exist: integer || integer

And in SQL Server, 'Total Score: ' + score fails with a "Conversion failed" error, because SQL Server tries to turn the text into a number, which fails for text like 'Total Score: '. The safe fix in every database is to use CONCAT(), or convert the number to text yourself with CAST(score AS VARCHAR(10)).

3) NULL Values Break The Concatenation

Another confusing situation happens when one of the fields being concatenated contains NULL. If you are concatenating a field that can be NULL, you have to be extra careful.

SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name   -- Concatenating NULL Values
FROM students;

PostgreSQL and SQLite:

+-------------------+
| full_name         |
+-------------------+
| NULL              |
| Maria Elena Lopez |
+-------------------+

John has no middle name (it is NULL), so his entire full name became NULL, even though his first and last names are there. Students expect the query to still print the available names, which makes the behavior seem wrong. We will fix this properly in the NULL section below.

Syntax Overview: SQL Concatenation Methods Across Databases

SQL concatenation simply means joining two or more text values into one string, for example, combining a first name and a last name into one full name field.

The tricky part is that SQL syntax is not the same across all database systems. Each system provides its own operator or function to combine strings. Here is a quick reference you can come back to:

I tested the MySQL/MariaDB, PostgreSQL, and SQLite rows myself. The SQL Server and Oracle rows come from Microsoft's and Oracle's official documentation, since I didn't have those two databases in my test setup.

How To Create A Database Table In SQL For Concatenation?

Before using CONCAT() and the concatenation operator, we need a table. For that purpose, we will create a table named Person and then insert a few sample values into it. The step-by-step process is shown below.

CREATE TABLE Person(id int, firstname varchar(10), lastname varchar(10), city varchar(14));

Now that we have the table, we need to insert some values to work with. We will insert the values in the same order as the fields we created in the table. Check the following code.

INSERT INTO Person VALUES
(1, 'Abby', 'Hart', 'Paris'),
(2, 'Harry', 'Styles', 'London'),
(3, 'Taylor', 'Swift', 'Kansas');

The resulting table is given below. We will use this table as our example throughout the article. Let us check the table.

Table Output:

+----+-----------+----------+--------+
| id | firstname | lastname | city   |
+----+-----------+----------+--------+
| 1  | Abby      | Hart     | Paris  |
| 2  | Harry     | Styles   | London |
| 3  | Taylor    | Swift    | Kansas |
+----+-----------+----------+--------+

How To Concatenate Two Columns Using The CONCAT Function?

The CONCAT() function can be used in two ways. We can show the joined value in a new column of the query result, or we can store it in the table. Here, we will see how to do both and write the SQL queries for them.

The CONCAT() function takes the columns (or text values) we want to join as parameters and returns one combined value. The syntax for using it is given below.

General Syntax: CONCAT(column1, column2) AS columnname

Let us now concatenate the first name and last name columns using CONCAT() without changing the existing columns. The query to do this is given below.

SELECT *, CONCAT(firstname, lastname) AS fullname FROM Person;

The result of the above query is given below. You will notice that a new column named fullname has been added at the end.

Output (same in all three databases):

+----+-----------+----------+--------+-------------+
| id | firstname | lastname | city   | fullname    |
+----+-----------+----------+--------+-------------+
| 1  | Abby      | Hart     | Paris  | AbbyHart    |
| 2  | Harry     | Styles   | London | HarryStyles |
| 3  | Taylor    | Swift    | Kansas | TaylorSwift |
+----+-----------+----------+--------+-------------+

The names are joined, but there is no space between them. CONCAT() only joins exactly what you give it, so to get a readable name, add a space as its own argument:

SELECT *, CONCAT(firstname, ' ', lastname) AS fullname FROM Person;

Output:

+----+-----------+----------+--------+--------------+
| id | firstname | lastname | city   | fullname     |
+----+-----------+----------+--------+--------------+
| 1  | Abby      | Hart     | Paris  | Abby Hart    |
| 2  | Harry     | Styles   | London | Harry Styles |
| 3  | Taylor    | Swift    | Kansas | Taylor Swift |
+----+-----------+----------+--------+--------------+

Storing The Concatenated Value In The Table

Now, let us see how to save the joined value instead of only showing it. The original version of this example replaced the firstname column with the full name, using the REPLACE() function and a space as the separator:

UPDATE Person
SET firstname = REPLACE(firstname, firstname, CONCAT(firstname, ' ', lastname));

When I tested this, it only worked in SQLite. In MySQL and PostgreSQL, it failed:

MySQL / MariaDB:

ERROR 1406: Data too long for column 'firstname' at row 2

PostgreSQL:

ERROR: value too long for type character varying(10)

The firstname column is varchar(10), and "Harry Styles" is 12 characters long, so it does not fit. SQLite ignores the length you give a varchar column, which is why it seemed to work there. Also, the REPLACE() part does nothing useful here. SET firstname = CONCAT(firstname, ' ', lastname) would do the same thing.

Overwriting the first name is also risky, because the original first name is gone after the update. A safer way is to add a new column with enough room and fill it:

ALTER TABLE Person ADD fullname varchar(25);

UPDATE Person
SET fullname = CONCAT(firstname, ' ', lastname);

SELECT * FROM Person;

Output (same in all three databases):

+----+-----------+----------+--------+--------------+
| id | firstname | lastname | city   | fullname     |
+----+-----------+----------+--------+--------------+
| 1  | Abby      | Hart     | Paris  | Abby Hart    |
| 2  | Harry     | Styles   | London | Harry Styles |
| 3  | Taylor    | Swift    | Kansas | Taylor Swift |
+----+-----------+----------+--------+--------------+

In the output above, we can see that the new fullname column has the concatenated values, and the firstname and lastname columns are untouched.

There is one problem left: if you insert a new person later, their fullname will be empty until you run the UPDATE again. MySQL and PostgreSQL (version 12 and later) can fix this with a generated column, which fills itself in automatically. (SQLite 3.31 and later has generated columns too, but ALTER TABLE can only add the VIRTUAL kind there.) Starting again from the original three-row table:

-- MySQL / MariaDB
ALTER TABLE Person
ADD fullname varchar(25) GENERATED ALWAYS AS (CONCAT(firstname, ' ', lastname)) STORED;

-- PostgreSQL
ALTER TABLE Person
ADD fullname varchar(25) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED;

INSERT INTO Person (id, firstname, lastname, city) VALUES (5, 'Sam', 'Lee', 'Austin');
SELECT id, fullname FROM Person;

Output (MySQL and PostgreSQL):

+----+--------------+
| id | fullname     |
+----+--------------+
| 1  | Abby Hart    |
| 2  | Harry Styles |
| 3  | Taylor Swift |
| 5  | Sam Lee      |
+----+--------------+

We never typed "Sam Lee". The database built it from the two columns when the row was inserted.

How To Concatenate Two Columns Using The Concatenation Operator?

We saw how to use the CONCAT() function to concatenate the firstname and lastname values. Now, we will use the || operator to do the same.

General Syntax: column1 || column2

Again, we will concatenate the firstname column and the lastname column in the following example, with a space in between this time.

SELECT firstname || ' ' || lastname AS fullname
FROM Person;

Output (PostgreSQL and SQLite):

+--------------+
| fullname     |
+--------------+
| Abby Hart    |
| Harry Styles |
| Taylor Swift |
+--------------+

Output (MySQL / MariaDB):

+----------+
| fullname |
+----------+
| 0        |
| 0        |
| 0        |
+----------+

This is the same trap from earlier. || is the SQL standard concatenation operator, and PostgreSQL, Oracle, and SQLite follow it. But MySQL and MariaDB treat || as OR unless the PIPES_AS_CONCAT SQL mode is turned on. Turning it on for your session makes the same query work:

SET SESSION sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT');
SELECT firstname || ' ' || lastname AS fullname FROM Person;

Output (MySQL / MariaDB):

+--------------+
| fullname     |
+--------------+
| Abby Hart    |
| Harry Styles |
| Taylor Swift |
+--------------+

For homework, though, it is simpler to just use CONCAT() in MySQL.

There are many SQL databases, like MySQL, SQLite, Oracle, Microsoft SQL Server, and more, and how you concatenate columns depends on the one you are using. In SQL Server, you can use the + operator for concatenation.

How To Concatenate Columns Using The Plus (+) Operator In SQL Server?

Another method to concatenate strings is the plus sign. This is the + operator in SQL Server, and it can be used to concatenate two or more columns in a table.

Let us use the same Person table as the example. We will concatenate the city and lastname columns and show the result in a column named passkey. Look at the query below to see how it works.

SELECT id, city + ' ' + lastname AS passkey FROM Person;

When you run the above query in SQL Server, the + operator joins the city with the last name, with a space in between, because we used a space as the separator. Two things to know about + in SQL Server, according to Microsoft's documentation:

• If any value is NULL, the whole result is NULL (this is the default setting, CONCAT_NULL_YIELDS_NULL ON).
• If one value is a number, SQL Server tries to turn the text into a number, which fails for normal text, so convert numbers with CAST() first, or use CONCAT().

Since + works only in SQL Server, I tried the same query on our other databases to show what happens when you use it in the wrong place:

MySQL / MariaDB (no error, just a wrong answer):

+----+---------+
| id | passkey |
+----+---------+
| 1  | 0       |
| 2  | 0       |
| 3  | 0       |
+----+---------+

PostgreSQL:

ERROR: operator does not exist: character varying + unknown

MySQL treats + as math only, so it turns the text into the number 0. PostgreSQL at least stops with an error that tells you the operator is wrong.

How To Handle NULL Values At The Time Of Concatenation?

NULL values can make the concatenation result NULL, depending on the database system. To avoid this, you can use CONCAT_WS(), or the COALESCE() function (ISNULL() in SQL Server) to handle a missing or NULL value in your data.

Be mindful that a NULL value is not an empty string. They are different from each other. NULL means "no value at all", while '' is a value that happens to have no characters.

For the examples below, let us go back to the original three-row table and add another row where the firstname field is NULL instead of a string value:

INSERT INTO Person VALUES (4, NULL, 'Moore', 'Denver');

Here is how each method handles row 4:

Notice the middle row. CONCAT() is the same function name in all three databases, but MySQL returns NULL while PostgreSQL and SQLite skip the NULL and keep the space, which leaves an extra space at the start.

The Cleanest Fix: CONCAT_WS()

CONCAT_WS() stands for "concatenate with separator". You give it the separator once, as the first argument, and it puts it between the values. Most importantly, it skips NULL values completely, so you never get a double space or a stray separator.

SELECT id, CONCAT_WS(' ', firstname, lastname) AS fullname FROM Person;

Output (same in all three databases):

+----+--------------+
| id | fullname     |
+----+--------------+
| 1  | Abby Hart    |
| 2  | Harry Styles |
| 3  | Taylor Swift |
| 4  | Moore        |
+----+--------------+

It also fixes our middle name problem from the start of the article:

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

Output:

+-------------------+
| full_name         |
+-------------------+
| John Smith        |
| Maria Elena Lopez |
+-------------------+

John gets "John Smith" with a single space, and Maria gets all three names. CONCAT_WS() works in MySQL, PostgreSQL, SQLite 3.44 and later, and SQL Server 2017 and later. Oracle does not have it.

Using COALESCE() To Show A Fallback Value

Sometimes you don't want to skip the missing value. You want to show something else in its place. That is the job of COALESCE(), which returns the first value in its list that is not NULL.

For example, when the full name can't be built, let us show the person's ID instead. The original version of this example looked like this:

SELECT id, COALESCE(firstname || lastname, id) AS fullname, city FROM Person;

I ran it on all three databases, and it only worked correctly in SQLite. PostgreSQL refused to run it:

ERROR: COALESCE types text and integer cannot be matched

The problem is that COALESCE() needs all its values to be the same type, and here the first one is text while id is a number. (In MySQL, it ran but showed 0 for every name, because of the || issue again.) The fix is to turn the ID into text and use the right concatenation method for each database:

-- PostgreSQL and SQLite
SELECT id, COALESCE(firstname || ' ' || lastname, CAST(id AS TEXT)) AS fullname, city
FROM Person;

-- MySQL / MariaDB
SELECT id, COALESCE(CONCAT(firstname, ' ', lastname), CAST(id AS CHAR)) AS fullname, city
FROM Person;

Output (same in all three databases):

+----+--------------+--------+
| id | fullname     | city   |
+----+--------------+--------+
| 1  | Abby Hart    | Paris  |
| 2  | Harry Styles | London |
| 3  | Taylor Swift | Kansas |
| 4  | 4            | Denver |
+----+--------------+--------+

Concatenating the name is not possible for row 4, so the query returns the ID as a substitute. In the output, we can see that the last row shows 4 instead of a name or an empty value. This is done using COALESCE().

Concatenating More Than Two Columns, And Using It In WHERE And ORDER BY

You are not limited to two columns. CONCAT() takes as many values as you need, and you can mix columns with your own text:

SELECT CONCAT(firstname, ' ', lastname, ' (', city, ')') AS label FROM Person;

Output:

+-----------------------+
| label                 |
+-----------------------+
| Abby Hart (Paris)     |
| Harry Styles (London) |
| Taylor Swift (Kansas) |
+-----------------------+

A concatenated value can also be used to filter and sort, not just to display:

SELECT id, city FROM Person
WHERE CONCAT(firstname, ' ', lastname) = 'Harry Styles';

SELECT CONCAT(lastname, ', ', firstname) AS name
FROM Person
ORDER BY name;

Output:

+----+--------+
| id | city   |
+----+--------+
| 2  | London |
+----+--------+

+---------------+
| name          |
+---------------+
| Hart, Abby    |
| Styles, Harry |
| Swift, Taylor |
+---------------+

One tip: filtering on a concatenated value works, but the database has to build the string for every row before it can compare. On a big table, WHERE firstname = 'Harry' AND lastname = 'Styles' is faster, because it can use an index.

Concatenating Values From Multiple Rows (GROUP_CONCAT And STRING_AGG)

Students often search for "concatenate columns" when what they really need is to join the values from many rows into one string, like a comma-separated list of names. CONCAT() can't do that, because it works across columns in one row. You need an aggregate function instead:

-- MySQL / MariaDB
SELECT GROUP_CONCAT(firstname ORDER BY firstname SEPARATOR ', ') AS everyone FROM Person;

-- PostgreSQL (and SQL Server 2017+, which uses WITHIN GROUP for ordering)
SELECT STRING_AGG(firstname, ', ' ORDER BY firstname) AS everyone FROM Person;

-- SQLite 3.44 and later
SELECT GROUP_CONCAT(firstname, ', ' ORDER BY firstname) AS everyone FROM Person;

Output (each database, with its own query):

+---------------------+
| everyone            |
+---------------------+
| Abby, Harry, Taylor |
+---------------------+

All three rows became a single value. Add a GROUP BY and you get one list per group, for example, one list of students per class.

What Are Some Real-World Use Cases Of SQL Concatenation?

Over the years, working with databases, I have seen that many real queries rely on concatenation to make data easier to read and use. Once students understand its practical value, it becomes much easier to remember.

Here are some common real-world examples that you should remember:

• Creating a full name column by combining the first name and last name, so applications can show a clean name instead of separate fields.
• Building readable addresses by joining the street, city, state, and ZIP code into a single formatted address.
• Building descriptive labels in reports, such as combining a product name with its category.
• Formatting customer messages, where a greeting is created from the stored name fields.
• Creating display IDs, such as joining an "ORD-" prefix with an order number.

Common Mistakes Students Make With Concatenation In SQL

When students start practicing SQL queries, concatenation errors are very common. From my experience helping students, most of these issues come down to small syntax details rather than a lack of understanding.

Once learners practice writing queries regularly, concatenation becomes one of the easiest SQL skills to master.

A very common mistake I see is that many students forget to add spaces or proper separators while combining columns. A query with this mistake looks like the following.

SELECT first_name || last_name AS full_name   -- No Separator Has Been Used
FROM students;

Output (PostgreSQL and SQLite):

+------------+
| full_name  |
+------------+
| JohnSmith  |
| MariaLopez |
+------------+

The query runs, but it produces output like 'JohnSmith' instead of 'John Smith', which is not user-friendly.

To fix it, we add a space (' ') between the two columns so the final result becomes readable.

SELECT first_name || ' ' || last_name AS full_name   -- A Space Is Added
FROM students;

Output (PostgreSQL and SQLite):

+-------------+
| full_name   |
+-------------+
| John Smith  |
| Maria Lopez |
+-------------+

Along with this one, here are other mistakes that show up often in student queries:

• Using the wrong operator for the database system, such as writing || in SQL Server, where the correct operator is +, or in MySQL, where it means OR.
• Mixing numbers and text without conversion, which causes errors when concatenating numeric columns with strings in SQL Server.
• Ignoring NULL values, which makes the whole result NULL in many databases.
• Missing the quotes around text values, such as writing first_name || space || last_name instead of first_name || ' ' || last_name.
• Using double quotes for text, like " ". In PostgreSQL and standard SQL, double quotes mean a column name, so " " looks for a column called " ". Always use single quotes for text.
• Not using column aliases (AS fullname), which leaves the result column with a messy name like ?column? or the whole expression.
• Storing a concatenated value in a column that is too short, like the varchar(10) example above.

If you are still getting comfortable with how a SQL query is put together, from SELECT and FROM to WHERE, our beginner's guide to SQL is a good place to start.

If your query keeps printing NULL or 0 and the deadline is close, a database tutor can check it and point out what is going wrong.

Conclusion

Understanding how to "Concatenate Two Columns in SQL" helps you build clearer reports, formatted output, and user-friendly query results.

It is also important to remember that different database management systems use different methods for concatenation. Before you write the query, check which database your class or project uses. With regular practice, you will be able to combine not just two but many columns in your SQL queries with confidence.

Key Takeaways:

• You can join one string with another in SQL using concatenation.
• It helps present data in a better way, and the joined value can also be used in WHERE, ORDER BY, and generated columns.
• In MySQL, use the CONCAT() function, because || means OR there. In PostgreSQL, Oracle, and SQLite, || works. In SQL Server, you can use the + operator or the CONCAT() function.
• Handle NULL values on purpose. CONCAT_WS() skips them, and COALESCE() replaces them with a fallback value.
• To join values from many rows into one, use GROUP_CONCAT() or STRING_AGG(), not CONCAT().

Frequently Asked Questions

Is SQL concatenation limited only to text columns?

No, concatenation is not limited to text columns. Numbers and dates can also be combined with strings. CONCAT() converts them to text automatically, while the + operator in SQL Server needs you to convert them first with CAST() or CONVERT().

Does SQL concatenation affect the original data stored in a table?

No, not in a SELECT query. Concatenation in a SELECT only changes how the data appears in the result, and the stored values stay the same. The data only changes if you use concatenation inside an UPDATE or INSERT statement, or in a generated column.

Can SQL concatenation be used inside reporting or filtering queries?

Yes, concatenation can be used in SELECT queries that generate reports, and also in WHERE, ORDER BY, and GROUP BY clauses. For large tables, filtering on the separate columns is faster than filtering on a concatenated value.

Why does || return 0 in MySQL?

In MySQL and MariaDB, || is the logical OR operator by default, not concatenation. MySQL tries to read the text as numbers, gets 0, and returns 0 or 1. Use CONCAT() instead, or turn on the PIPES_AS_CONCAT SQL mode.

What is the difference between CONCAT and CONCAT_WS?

CONCAT joins values exactly as given, so you have to add separators yourself. CONCAT_WS takes a separator as its first argument, puts it between every value, and skips NULL values, so you don't end up with extra spaces or commas.

How do I concatenate two columns with a space or comma in SQL?

Add the space or comma as its own text value between the columns, like CONCAT(firstname, ' ', lastname) or lastname || ', ' || firstname. With CONCAT_WS, write it once: CONCAT_WS(', ', lastname, firstname).