MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL

MySQL COALESCE function

This statement displays the first non-null value of the table data. The COALESCE function returns a null value when all values of the table are null or do not find any non-null value.

Syntax

The COALESCE function syntax shows below.

COALESCE (data1, data2, data3, dataN);

The COALESCE function syntax with table shows below.

SELECT COALESCE (column_name, expression) FROM table name;

The COALESCE function with WHERE clause syntax shows below.

SELECT * FROM table name WHERE COALESCE (column_name);

Prerequisite

  • Create table in the database with data type and key.
mysql> CREATE TABLE `marks` (
  `roll_no` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `physics` INT NULL,
  `chemistry` INT NULL,
  `maths` INT NULL,
  `english` INT NULL,
  PRIMARY KEY (`roll_no`));
  • Insert value in the table as per requirement.
  • The below command shows the table format and its information.
mysql> Select * from marks;

Use of the COALESCE function

The following are the uses of coalesce function:

  • The COALESCE function is used to compare the column data between null and not null values.
  • This function is used to find the not null values in the table columns.
  • This COALESCE function determines and maintains table data according to null and not null values.

Example of the COALESCE function

Example1: Execute the following query to know about the "COALESCE" statement.

mysql> SELECT COALESCE(NULL, 'Parameter', NULL);

Output

Executing the statement will show the below output:

MySQL COALESCE function

The image shows the output as a "parameter" value. If a single value is available, then the "COALESCE" function returns it.

Example2: The below query shows the working procedure of the "null" values and their output.

mysql> SELECT COALESCE(NULL, NULL);

Output

Executing the query will return the NULL value because it does not have any non-value.

MySQL COALESCE function

Example3: This example will explain the "COALESCE" function with the table. Here, we applied this function to the single column of the table.

mysql> select coalesce(physics, 'N/A') from marks;

Output

Executing the query will return the below output where we can see that the null value of the physics column replaces it with the "N/A" keyword.

MySQL COALESCE function

Example4: Execute the following query to know how the "COALESCE" function work with the WHERE condition. Here, this function applies to the single column of the table.

mysql> select coalesce(physics, 'N/A' ) from marks WHERE roll_no < 3;

Output

This query shows the below result:

MySQL COALESCE function

Example5: Execute the following query to know about the "COALESCE" statement used in the multiple columns of the table.

mysql> select coalesce(physics, 'N/A'), coalesce(chemistry, 10) , coalesce(maths, 'N/A')  from marks;

Output

It gives the below result where we can see that the null value of the given column replaces with a given value.

MySQL COALESCE function

Example6: This example explains the COALESCE function with the ORDER BY clause. Here we have applied this function to the multiple columns of the table. The "ORDER BY" clause applies to the descending order on the column.

mysql> select coalesce(physics, 'N/A'), coalesce(chemistry, 10) , coalesce(maths, 'N/A')  from marks WHERE roll_no < 4 ORDER BY physics DESC;

Output

Executing the query return the below result:

MySQL COALESCE function