Display first non-null values with coalesce() in MySQL?

The coalesce() can be used to print first NOT NULL column value. Let us first create a table −

mysql> create table DemoTable1927
   (
   StudentName varchar(20),
   StudentSubject varchar(20)
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1927 values('Chris','MySQL');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1927 values('David',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1927 values(NULL,'MongoDB');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1927;

This will produce the following output −

+-------------+----------------+
| StudentName | StudentSubject |
+-------------+----------------+
| Chris       |         MySQL  |
| David       |         NULL   |
| NULL        |      MongoDB   |
+-------------+----------------+
3 rows in set (0.00 sec)

Here is the query to implement coalesce() in MySQL −

mysql> select coalesce(StudentName,StudentSubject) as Result from DemoTable1927;

This will produce the following output −

+---------+
| Result  |
+---------+
| Chris   |
| David   |
| MongoDB |
+---------+
3 rows in set (0.00 sec)

AmitDiwan

Published on 30-Dec-2019 07:29:19

• Related Questions & Answers
• How does COALESCE order results with NULL and NON-NULL values?
• Query non-empty values of a row first in ascending order and then display NULL values
• Perform mathematical calculations in a MySQL table with NULL and NON-NULL values
• Update all the fields in a table with null or non-null values with MySQL
• Display the result with not null value first and then with null value in MySQL
• Display 1 for NULL values in MySQL
• Display only NOT NULL values from a column with NULL and NOT NULL records in MySQL
• Return only the non-empty and non-null values from a table and fill the empty and NULL values with the corresponding column values in MySQL?
• Fetch maximum value from multiple columns with null and non-null values?
• Ignore null values in MySQL and display rest of the values
• Display and concatenate records ignoring NULL values in MySQL
• Which MySQL function is used to find first non-NULL value from a list of values?
• MySQL query to compare and display only the rows with NULL values
• How to convert MySQL null to 0 using COALESCE() function?
• Looping in JavaScript to count non-null and non-empty values