Combining Two Columns into One with SQL MySQL Query

If you desire the merged column to have distinct values, utilize UNION. Alternatively, for Solution 3, you can attempt this output that will resemble Barcelona Real Madrid and Milan Inter.


To achieve this, utilize the COALESCE() function. This function retrieves the first value that is not NULL from the column. To grasp the concept, we will begin by creating a demonstration table.

mysql> create table combineTwoColumnsDemo
   -> (
   -> UserId int,
   -> UserName varchar(20),
   -> UserAge int
   -> );
Query OK, 0 rows affected (1.12 sec)

Execute the insert command to add records into the table. The query is as shown below –

mysql> insert into combineTwoColumnsDemo values(101,'John',23);
Query OK, 1 row affected (0.16 sec)
mysql> insert into combineTwoColumnsDemo values(102,'Carol',20);
Query OK, 1 row affected (0.14 sec)
mysql> insert into combineTwoColumnsDemo values(103,'Bob',25);
Query OK, 1 row affected (0.13 sec)
mysql> insert into combineTwoColumnsDemo values(104,'Mike',26);
Query OK, 1 row affected (0.18 sec)
mysql> insert into combineTwoColumnsDemo values(105,NULL,23);
Query OK, 1 row affected (0.22 sec)
mysql> insert into combineTwoColumnsDemo values(105,'Maxwell',NULL);
Query OK, 1 row affected (0.15 sec)

Now, by using the select statement, you have the ability to display all records from the table. The query is as shown below −

mysql> select *from combineTwoColumnsDemo;

The following is the output

+--------+----------+---------+
| UserId | UserName | UserAge |
+--------+----------+---------+
|    101 | John     |      23 |
|    102 | Carol    |      20 |
|    103 | Bob      |      25 |
|    104 | Mike     |      26 |
|    105 | NULL     |      23 |
|    105 | Maxwell  |    NULL |
+--------+----------+---------+
6 rows in set (0.00 sec)

This is the query to merge two columns into a single column.

mysql> SELECT UserName,
   -> UserAge,
   -> COALESCE(UserName, UserAge) AS Combine_UserName_UserAge
   -> FROM combineTwoColumnsDemo;

The following is the output

+----------+---------+--------------------------+
| UserName | UserAge | Combine_UserName_UserAge |
+----------+---------+--------------------------+
| John     |      23 | John                     |
| Carol    |      20 | Carol                    |
| Bob      |      25 | Bob                      |
| Mike     |      26 | Mike                     |
| NULL     |      23 | 23                       |
| Maxwell  |    NULL | Maxwell                  |
+----------+---------+--------------------------+
6 rows in set (0.00 sec)

Frequently Asked Questions