
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
MySQL query to combine two columns in a single column?
You can use COALESCE() function for this. In the COALESCE() function, it returns the first NON NULL value from the column. To understand the concept, let us first create a demo table
mysql> create table combineTwoColumnsDemo -> ( -> UserId int, -> UserName varchar(20), -> UserAge int -> ); Query OK, 0 rows affected (1.12 sec)
Insert some records in the table using insert command. The query is as follows −
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 you can display all records from the table using select statement. The query is as follows −
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)
Here is the query to combine two columns in 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)
- Related Articles
- Combine columns before matching it with LIKE in a single query in MySQL?
- Update two columns with a single MySQL query
- How to combine different columns of a table to yield a single column in query output in PostgreSQL?
- Count two different columns in a single query in MySQL?
- How to alter column type of multiple columns in a single MySQL query?
- Comparing two columns in a single MySQL query to get one row?
- Order by a function of two columns in a single MySQL query
- MySQL query to sort multiple columns together in a single query
- Change multiple columns in a single MySQL query?
- How can we combine values of two or more columns of MySQL table and get that value in a single column?
- MySQL query to display columns name first name, last name as full name in a single column?
- A single MySQL query to combine strings from many rows into a single row and display the corresponding User Id sum in another column?
- MySQL query to get the length of all columns and display the result in a single new column?
- How to set all values in a single column MySQL Query?
- How to add column and index in a single MySQL query?

Advertisements