MySQL NULL को 0 में बदलें (IFNULL/COALESCE)

IFNULL() या COALESCE() से NULL को 0 बनाएं

MySQL में NULL मान को 0 में बदलने के लिए IFNULL() (MySQL-विशिष्ट) या COALESCE() (ANSI मानक) फ़ंक्शन का उपयोग करें।

सिंटैक्स

-- IFNULL (2 तर्क)
SELECT IFNULL(yourColumnName, 0) AS alias FROM yourTableName;

-- COALESCE (2+ तर्क, ANSI मानक)
SELECT COALESCE(yourColumnName, 0) AS alias FROM yourTableName;

उदाहरण: टेबल और डेटा

mysql> CREATE TABLE convertNullToZeroDemo (
    -> Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Name VARCHAR(20),
    -> Salary INT
    -> );

mysql> INSERT INTO convertNullToZeroDemo(Name, Salary) VALUES
('John', NULL), ('Carol', 5610), ('Bob', NULL), ('David', NULL);

डेटा

+----+-------+--------+
| Id | Name  | Salary |
+----+-------+--------+
| 1  | John  | NULL   |
| 2  | Carol | 5610   |
| 3  | Bob   | NULL   |
| 4  | David | NULL   |
+----+-------+--------+

IFNULL परिणाम

mysql> SELECT IFNULL(Salary, 0) AS CONVERT_NULL_TO_0 FROM convertNullToZeroDemo;

+--------------------+
| CONVERT_NULL_TO_0  |
+--------------------+
| 0                  |
| 5610               |
| 0                  |
| 0                  |
+--------------------+

COALESCE परिणाम

mysql> SELECT COALESCE(Salary, 0) AS CONVERT_NULL_TO_0 FROM convertNullToZeroDemo;

+--------------------+
| CONVERT_NULL_TO_0  |
+--------------------+
| 0                  |
| 5610               |
| 0                  |
| 0                  |
+--------------------+

तुलना

बहु-कॉलम COALESCE उदाहरण

SELECT COALESCE(bonus, commission, salary, 0) AS total_pay FROM employees;