In SQL, columns are fundamental block of any relational database. Without columns and rows, a relational database would be unable to store the structured data using records and attributes.

One of the most fundamental and common tasks in relational databases is combining two or more columns into a single entity. Column combination allows us to create new columns by merging the data from two or more existing columns within the database.

In this tutorial, we will explore how we can combine two columns in SQL. For demonstration purposes, we will use the Sakila sample database which provides an extensive dataset for a wide variety of use cases.

Prerequisites:

Before we start, make sure that you have MySQL installed and you have an access to the Sakila sample database.

If you do not have the Sakila sample database installed or you wish to use another dataset, feel free to do so.

Method 1: Column Concatenation

One of the best methods that we can use to combine two database columns is using the concat() function. This function allows us to concatenate two or more columns into a single unit.

Take the following query for example:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM actor;

In this example, we use the concat() function to concatenate the value of the “first_name” and “last_name” columns from the “actor” table. We then create a new column called “full_name” from the resulting set.

An example output is as follows:

Method 2: Using the CONCAT_WS Function

We also have access to the CONCAT_WS function which behaves closely similar to the CONCAT() function. However, unlike the CONCAT() function, the CONCAT_WS() function allows us to define a separator between the concatenated values.

This can be useful if you need a custom formatting without the need for external parsing. For example, we want to the full name to follow the “first_name.last_name” format as shown in the following example query:

SELECT CONCAT_WS('.', first_name , last_name) AS full_name
FROM actor;

Similarly, we combine the “first_name” and “last_name” columns into a single column. However, we separate the values using a dot character.

The resulting output is as follows:

Method 3: Concat with Conditionals

We can also combine two conditional columns by adding a basic CASE statement. Let us take an example demonstration:

SELECT
CASE
WHEN rental_rate > 2.0 THEN CONCAT(title, ' (Expensive)')
ELSE CONCAT(title, ' (Affordable)')
END AS movie_pricing
FROM film;

For our case, we combine the “title” column with a condition based on the “rental rate” column. This creates a column called “movie_pricing” which adds a metadata to the title on whether the rental rate is expensive or affordable.

An example output is as follows:

Method 4: Concat with Aliases and Join

A more readable approach is giving the columns with aliases and then joining the aliases values into a single unit.

An example is as follows:

SELECT
CONCAT(customer.first_name, ' ', customer.last_name) AS customer_name,
CONCAT(staff.first_name, ' ', staff.last_name) AS staff_name
FROM customer
JOIN staff ON customer.support_rep_id = staff.staff_id;

Example Output:

Conclusion

In this tutorial, we learned all about the methods and techniques of combining two columns in an SQL database to create a new database.

Combine Two Columns in SQL