MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

COALESCE() Function
Sometimes a record in a table might have missing data that the user cannot fill with zeroes. In such cases, MySQL allows the user to fill that record with a NULL value.
A NULL value is nothing but a placeholder in database tables to represent missing values or when the data is not available to insert.
The MySQL COALESCE() Function
The MySQL COALESCE() function returns the first non-NULL value in a list of expressions. It takes multiple expressions as arguments and returns the value of the first expression that is not NULL. If all expressions are NULL, it returns NULL.
When all its arguments are NOT NULL, the COALESCE() function evaluates the values based on the priority of their datatypes. For example, an integer is always prioritized over a character expression in the COALESCE() function, resulting in an integer as the output.
When the COALESCE() function is used on MySQL tables with arguments representing field names that require comparison, the function compares the corresponding values in these columns, and retrieves the first occurrence that is NOT NULL.
Syntax
Following is the basic syntax for the COALESCE() function 
SELECT COALESCE (expression_1, expression_2, ..., expression_n) 
FROM table_name;
Example
In the following query, we are retrieving the first occurrence of nnon-NULL value from the list of arguments passed to the COALESCE() function 
SELECT COALESCE(NULL, NULL, 'Hello', 'Tutorialspoint') 
AS RESULT;
Output
Following is the output obtained 
RESULT
Hello
Example
Now, let us create a table named "CUSTOMERS" to store personal details of customers, including their name, age, address, and salary using the following query 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20),
   AGE INT,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);
Now, insert values into this table using the INSERT statement as follows 
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, NULL, NULL ),
(2, 'Khilan', 25, 'Delhi', NULL ),
(3, 'kaushik', 23, 'Kota', NULL ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'MP', NULL ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
Following is the CUSTOMERS table obtained 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
NULL
NULL
2
Khilan
25
Delhi
NULL
3
Kaushik
23
Kota
NULL
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
MP
NULL
7
Muffy
24
Indore
10000.00
Now, let us use the SELECT statement to retrieve a result-set that contains values from the "NAME," "AGE," and "SALARY" columns. We will also pass "AGE" and "SALARY" as arguments to the COALESCE() function, and the return values will be displayed in another column named "RESULT." 
SELECT NAME, AGE, SALARY, 
COALESCE(SALARY, AGE) RESULT 
FROM CUSTOMERS;
Output
The result obtained is as shown below 
NAME
AGE
SALARY
RESULT
Ramesh
32
NULL
32.00
Khilan
25
NULL
25.00
Kaushik
23
NULL
23.00
Chaitali
25
6500.00
6500.00
Hardik
27
8500.00
8500.00
Komal
22
NULL
22.00
Muffy
24
10000.00
10000.00
In the result-set, you will notice that the "NAME," "AGE," and "SALARY" values are displayed normally. However, the "RESULT" column contains the first non-NULL value from the "AGE" and "SALARY" columns. For example, in the first record, the "SALARY" column has a NULL value, but "AGE" holds a non-NULL value, so the "RESULT" column displays the age value.
In cases where both columns contain non-NULL values, the COALESCE() function returns the highest value.
Coalesce() Function Using Client Program
We can also perform coalesce() function using client program.
Syntax
PHPNodeJSJavaPython
To perform COALESCE() function through a PHP program, we need to execute the "SELECT" statement using the mysqli function query() as follows 
$sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
// Create table Customer   
$sql = 'CREATE TABLE IF NOT EXISTS CUSTOMER ( ID INT NOT NULL, NAME VARCHAR (20), AGE INT, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY(ID) )';
$result = $mysqli->query($sql);
if ($result) {
    echo "Table created successfully...!\n";
}
﻿
// Insert data into the created table    
$q = "INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY) VALUES
    (1, 'Ramesh', 32, NULL, NULL ),
    (2, 'Khilan', 25, 'Delhi', NULL ),
    (3, 'kaushik', 23, 'Kota', NULL ),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
    (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
    (6, 'Komal', 22, 'MP', NULL ),
    (7, 'Muffy', 24, 'Indore', 10000.00 )";
if ($res = $mysqli->query($q)) {
    echo "Data inserted successfully...! \n";
}
﻿
$sql = "SELECT * FROM CUSTOMER";
if ($res = $mysqli->query($sql)) {
    echo "\nTable's records before coalesce()...!\n\n";
    while ($row = $res->fetch_array()) {
        printf(
            "iD: %d, Name: %s, Age: %d, Address: %s, Salary: %2f",
            $row['ID'], $row['NAME'], $row['AGE'], $row['ADDRESS'], $row['SALARY']
        );
        printf("\n");
    }
}
//using coalesce function:
$sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
if ($res = $mysqli->query($sql)) {
    echo "\nTable's records after coalesce()...!\n\n";
    while ($row = $res->fetch_array()) {
        printf(
            "Name: %s, Age: %d, Salary: %2f, Result: %2f",
            $row['NAME'], $row['AGE'], $row['SALARY'], $row['RESULT']
        );
        printf("\n");
    }
}
$mysqli->close();
Output
The output obtained is as shown below 
Table's records before coalesce()...!
iD: 1, Name: Ramesh, Age: 32, Address: , Salary: 0.000000
iD: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 0.000000
iD: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 0.000000
iD: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.000000
iD: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.000000
iD: 6, Name: Komal, Age: 22, Address: MP, Salary: 0.000000
iD: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.000000
﻿
Table's records after coalesce()...!
Name: Ramesh, Age: 32, Salary: 0.000000, Result: 32.000000
Name: Khilan, Age: 25, Salary: 0.000000, Result: 25.000000
Name: kaushik, Age: 23, Salary: 0.000000, Result: 23.000000
Name: Chaitali, Age: 25, Salary: 6500.000000, Result: 6500.000000
Name: Hardik, Age: 27, Salary: 8500.000000, Result: 8500.000000
Name: Komal, Age: 22, Salary: 0.000000, Result: 22.000000
Name: Muffy, Age: 24, Salary: 10000.000000, Result: 10000.000000     
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	NULL	NULL
2	Khilan	25	Delhi	NULL
3	Kaushik	23	Kota	NULL
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	MP	NULL
7	Muffy	24	Indore	10000.00

NAME	AGE	SALARY	RESULT
Ramesh	32	NULL	32.00
Khilan	25	NULL	25.00
Kaushik	23	NULL	23.00
Chaitali	25	6500.00	6500.00
Hardik	27	8500.00	8500.00
Komal	22	NULL	22.00
Muffy	24	10000.00	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

COALESCE() Function

The MySQL COALESCE() Function

Syntax

Example

Output

Example

Output

Coalesce() Function Using Client Program

Syntax

Example

Output