Mwongozo wa Kazi ya IFNULL ya MySQL: Badilisha Thamani za NULL (Mbadala wa Oracle NVL)

1. Introduction

When working with databases, handling NULL values is a critical point.
In particular, it’s not uncommon for people who are used to Oracle’s NVL function to realize that NVL is not available when migrating to MySQL.

In MySQL, you can properly handle NULL values by using the IFNULL function instead of NVL.
This article explains how to work with NULL values in MySQL in detail, covering how to use IFNULL and how it differs from other NULL-handling functions.

1.1 What is a NULL value?

In databases, NULL means “no value has been set.”
Because this is different from “0” or an “empty string”, failing to handle it properly can lead to unexpected errors or incorrect query results.

For example, assume you have data like the following.

In the data above, the age for ID “2” (Sato) is NULL.
If you run calculations as-is, you may get errors or unintended results.

1.2 How MySQL handles NULL values

2. What is the IFNULL function?

MySQL provides the IFNULL function as a function to replace NULL values with another value.
It plays a role similar to Oracle’s NVL function.

By handling NULL values appropriately, you can prevent calculation issues caused by missing data and build more stable SQL queries.
Let’s take a closer look at the basic usage of IFNULL.

2.1 Basic syntax of IFNULL

The basic syntax of the IFNULL function is as follows.

IFNULL(expression, fallback_value)

• expression : The column or value to check for NULL
• fallback_value : The value to return when the expression is NULL (if not NULL, the expression is returned as-is)

For example, consider the following SQL statement.

SELECT IFNULL(NULL, 'Fallback value');

In this case, because NULL is specified, the result becomes 'Fallback value'.

On the other hand, if you specify a non-NULL value, that value is returned as-is.

SELECT IFNULL('Hello', 'Fallback value');

2.2 Key characteristics of IFNULL

The IFNULL function has the following characteristics.

1. It can convert NULL values into a specific value

• You can set an alternative default value for columns that may contain NULL.

1. It uses a simple syntax

• It’s simpler to write than using a CASE expression.

1. You should consider data types

• It’s recommended that the arguments to IFNULL are the same data type .

For example, the following SQL can cause an error.

SELECT IFNULL(100, 'Error');

Reason: A numeric type (100) and a string type (‘Error’) are mixed.
In this case, the fallback value should also be numeric.

SELECT IFNULL(100, 0);

2.3 When you should use IFNULL

Here are some practical situations where IFNULL is useful.

1. Set a default value for NULL

• For example, if an employee’s bonus is NULL, set it to 0.

  SELECT name, IFNULL(bonus, 0) AS bonus
  FROM employees;
  

2. Avoid calculations involving NULL

• If you calculate with NULL as-is, the result also becomes NULL.
• Using IFNULL to avoid NULL enables the intended calculation.

  SELECT name, salary, IFNULL(bonus, 0) AS bonus, salary + IFNULL(bonus, 0) AS total_income
  FROM employees;
  

3. Handle NULL properly in reports and aggregations

• If NULL is present during analysis, incorrect reports may be produced.
• By replacing NULL with a specific value using IFNULL , you can process data consistently.

3. Practical examples of the IFNULL function

In the previous section, we explained the basics of the IFNULL function.
In this section, we’ll introduce concrete examples of using it with real data.

3.1 Replace NULL values with a default value

If a table contains NULL values, it may cause unintended behavior.
To solve this, you can use IFNULL to replace NULL with a default value.

Mfano: Ikiwa bonasi ya mfanyakazi ni NULL, weka thamani chaguo-msingi kuwa 0

SELECT name, IFNULL(bonus, 0) AS bonus
FROM employees;

Data kabla ya utekelezaji

Baada ya kutumia IFNULL

Kubadilisha NULL na 0 hufanya muungano na usindikaji unaohusiana kuwa laini zaidi.

3.2 Epuka mahesabu yanayojumuisha NULL

Katika MySQL, matokeo ya hesabu inayojumuisha NULL yanakuwa NULL.
Kwa hiyo, unahitaji kutumia IFNULL ili kuepuka NULL.

Mfano: Hesabu jumla ya mshahara na bonasi

SELECT name, salary, IFNULL(bonus, 0) AS bonus, salary + IFNULL(bonus, 0) AS total_income
FROM employees;

Data kabla ya utekelezaji

Baada ya kutumia IFNULL

Ikiwa bonasi ni NULL, jumla (salary + bonus) pia inakuwa NULL,
lakini kwa kutumia IFNULL, MySQL inachukulia NULL kama 0 na huhesabu kwa usahihi.

3.3 Badilisha NULL na kamba nyingine

Unaweza kuweka kamba chaguo-msingi si tu kwa nambari, bali pia wakati thamani ni NULL.

Mfano: Onyesha “Haijaregistrwa” kwa watumiaji ambao hawana anwani ya barua pepe

SELECT id, name, IFNULL(email, 'Not registered') AS email
FROM users;

Data kabla ya utekelezaji

Baada ya kutumia IFNULL

Kama itaachwa kama NULL, uga huo utaonekana tupu, lakini IFNULL inafanya iwe wazi na “Haijaregistrwa”.

3.4 Tumia IFNULL katika kifungu cha WHERE

Unaweza kutumia IFNULL katika kifungu cha WHERE kuchuja kulingana na masharti yanayojumuisha thamani za NULL.

Mfano: Pata watumiaji tu wenye thamani za NULL

SELECT *
FROM users
WHERE IFNULL(email, '') = '';

SQL hii inachukulia email kama '' (kamba tupu) wakati ni NULL, na inapata watumiaji ambao barua pepe yao ni NULL.

3.5 Weka thamani za NULL mwisho katika ORDER BY

Kawaida, unapotumia ORDER BY, thamani za NULL zinaweza kuonekana kwanza, lakini unaweza kutumia IFNULL kuzihamisha mwisho.

Mfano: Weka safu zilizo na thamani za NULL mwisho

SELECT name, salary
FROM employees
ORDER BY IFNULL(salary, 0) ASC;

4. Tofauti kati ya IFNULL na COALESCE

MySQL hutoa kazi nyingi za kushughulikia thamani za NULL, na IFNULL na COALESCE mara nyingi hupimwa.
Zote mbili hubadilisha thamani za NULL na mbadala, lakini matumizi na tabia yao hutofautiana.

Katika sehemu hii, tunaelezea tofauti kati ya IFNULL na COALESCE na jinsi ya kuchagua ile sahihi.

4.1 Ni nini kazi ya COALESCE?

Kazi ya COALESCE hurejesha thamani ya kwanza isiyo-NULL kati ya hoja nyingi.
Kwa maneno mengine, wakati IFNULL huchagua thamani isiyo-NULL kati ya thamani mbili,
COALESCE hutofautiana kwa kuchagua thamani ya kwanza isiyo-NULL kutoka kwa wagombea wengi.

Sintaks

COALESCE(expr1, expr2, ... , exprN)

• Inapima kutoka kushoto kwenda kulia na hurudisha thamani ya kwanza isiyo-NULL
• Inarudisha NULL ikiwa hoja zote ni NULL

Mfano: Kubadilisha NULL kwa kutumia COALESCE

SELECT name, COALESCE(phone, email, 'Not registered') AS contact_info
FROM customers;

Katika kesi hii, thamani zinaamuliwa kama ifuatavyo.

1. Ikiwa phone si NULL, rudisha phone.
2. Ikiwa phone ni NULL na email si NULL, rudisha email.
3. Ikiwa phone na email zote ni NULL, rudisha 'Haijaregistrwa'.

4.2 Toftauti kati ya IFNULL na COALESCE

4.3 Mifano ya vitendo ya IFNULL na COALESCE

Mfano 1: Badilisha NULL kwa urahisi

Kwa kutumia IFNULL, unaweza kuchagua thamani isiyo-NULL kati ya thamani mbili.

SELECT name, IFNULL(phone, 'Not registered') AS contact_info
FROM customers;

Matokeo

Mfano 2: Pendelea thamani ya kwanza inayopatikana isiyo-NULL

Kwa kutumia COALESCE, unaweza kupata thamani ya kwanza isiyo-NULL.

SELECT name, COALESCE(phone, email, 'Not registered') AS contact_info
FROM customers;

Matokeo

• Ikiwa phone si NULL, rudisha phone
• Ikiwa phone ni NULL na email si NULL, rudisha email
• Ikiwa phone na email zote ni NULL, rudisha 'Not registered'

4.4 Jinsi ya kuchagua kati ya IFNULL na COALESCE

✔ Unapaswa kutumia IFNULL

✅ Unapotaka ubadilishaji rahisi wa NULL kwa thamani chaguo-msingi
✅ Wakati hoja mbili zinatosha (kwa mfano, geuza NULL kuwa 0)

✔ Unapaswa kutumia COALESCE

✅ Unapotaka kupata thamani ya kwanza isiyo NULL (kwa mfano, phone → email → “Not registered”)
✅ Unapohitaji kutathmini thamani tatu au zaidi

4.5 Ulinganisho wa Utendaji kati ya IFNULL na COALESCE

Kwa ujumla, IFNULL ni haraka kuliko COALESCE.
Hii ni kwa sababu IFNULL inatathmini thamani mbili tu, wakati COALESCE inatathmini thamani nyingi kwa mpangilio.

Jaribio la Utendaji

Unapotumia IFNULL na COALESCE kwenye safu milioni 1, unaweza kuona matokeo kama ifuatayo.

➡ Kwa kiasi kikubwa cha data, IFNULL inaweza kuwa haraka kidogo.
➡ Hata hivyo, ikiwa unahitaji nakala moja tu, tumia IFNULL; ikiwa unataka wagombea wengi, tumia COALESCE.

5. Functions za kushughulikia NULL katika hifadhidata zisizo MySQL

MySQL inatoa kazi ya IFNULL kushughulikia thamani za NULL, lakini mifumo mingine ya usimamizi wa hifadhidata (DBMS) hutumia kazi tofauti.
Katika hifadhidata kuu kama Oracle, PostgreSQL, na SQL Server, ni kawaida kutumia kazi ambazo zinatofautiana na MySQL kwa kushughulikia NULL.

Sehemu hii inaelezea jinsi thamani za NULL zinavyoshughulikiwa katika hifadhidata zisizo MySQL.

5.1 Kushughulikia NULL katika Oracle: kazi ya NVL

Katika Oracle, kazi ya NVL inatolewa kama sawa na IFNULL ya MySQL.
Kazi ya NVL inarudisha thamani nyingine wakati thamani iliyobainishwa ni NULL.

Sintaks

NVL(expression, fallback_value)

• expression : Safu wima au thamani ya kuangalia ikiwa ni NULL
• fallback_value : Thamani ya kurudisha wakati ni NULL (ikiwa si NULL, expression inarudishwa kama ilivyo)

Mfano

SELECT name, NVL(salary, 0) AS salary
FROM employees;

Matokeo

Tabia ya NVL karibu sawa na IFNULL ya MySQL, hivyo katika Oracle unaweza tu kutumia NVL.

5.2 Kushughulikia NULL katika PostgreSQL na SQL Server: kazi ya COALESCE

Katika PostgreSQL na SQL Server, kazi ya COALESCE inatumiwa kubadilisha thamani za NULL na mbadala.
Kazi hii inaweza kurudisha thamni ya kwanza isiyo NULL kati ya wagombea wengi.

Sintaks

COALESCE(expr1, expr2, ..., exprN)

• Inatathmini kutoka kushoto kwenda kulia na kurudisha thamani ya kwanza isiyo NULL
• Inarudisha NULL ikiwa hoja zote ni NULL

Mfano

SELECT name, COALESCE(phone, email, 'Not registered') AS contact_info
FROM customers;

Matokeo

Kama ilivyoonyeshwa hapo juu, katika PostgreSQL na SQL Server, kutumia COALESCE kunaruhusu kushughulikia NULL kwa ubunifu zaidi kuliko IFNULL ya MySQL.

5.3 Ulinganisho wa kazi za kushughulikia NULL katika hifadhidata mbalimbali

• Ushughulikiaji rahisi wa NULL → IFNULL (MySQL) au NVL (Oracle)
• Chagua thamani bora kutoka kwa wagombea wengi → COALESCE (PostgreSQL, SQL Server)

5.4 Vidokezo wakati wa kuhamisha kati ya DBMS tofauti

Unapo hamisha mifumo kati ya hifadhidata tofauti, unapaswa kuzingatia tofauti katika kazi za kushughulikia NULL.
Haswa, unapohamisha kutoka Oracle kwenda MySQL, lazima uandike upya NVL kuwa IFNULL.

Mifano ya kuandika upya wakati wa uhamisho

• Oracle (NVL)

  SELECT NVL(salary, 0) AS salary FROM employees;
  

• MySQL (IFNULL)

  SELECT IFNULL(salary, 0) AS salary FROM employees;
  

• PostgreSQL / SQL Server (COALESCE)

  SELECT COALESCE(salary, 0) AS salary FROM employees;
  

Pia, kwa sababu COALESCE inaweza kukubali hoja nyingi, ni rahisi zaidi kuliko NVL ya Oracle au IFNULL ya MySQL.
Wakati wa uhamisho, ni muhimu kuchagua kazi sahihi kwa hifadhidata lengo.

6. Masuala Yanayoulizwa Mara Kwa Mara (FAQ)

Masuala kuhusu kazi ya IFNULL ya MySQL na utunzaji wa NULL ni muhimu kwa watengenezaji programu na wasimamizi wa hifadhidata.
Sehemu hii inahitimisha masuala ya kawaida kuhusu IFNULL.

Swali la 1. Je, kazi ya IFNULL na kazi ya NVL ni sawa?

➡ Zinatoa utendaji karibu sawa, lakini jina la kazi linatofautiana kulingana na hifadhidata.

Katika MySQL, tumia IFNULL; katika Oracle, tumia NVL kubadilisha maadili ya NULL kuwa maadili ya mbadala.

Swali la 2. Ni tofauti gani kati ya IFNULL na COALESCE?

➡ IFNULL inarudisha maadili yasiyo ya NULL kati ya hoja mbili, wakati
COALESCE inarudisha maadili ya kwanza yasiyo ya NULL miongoni mwa hoja nyingi.

Mifano

SELECT IFNULL(NULL, 'Fallback value'); -- Result: 'Fallback value'
SELECT COALESCE(NULL, NULL, 'First non-NULL value'); -- Result: 'First non-NULL value'

✔ Wakati unapaswa kutumia IFNULL
✅ Rudisha maadili ya msingi maalum wakati NULL (k.m., ikiwa NULL basi 0)
✅ Wakati unalinganisha maadili mawili tu

✔ Wakati unapaswa kutumia COALESCE
✅ Pata maadili ya kwanza yasiyo ya NULL (k.m., simu → barua pepe → maadili ya msingi)
✅ Wakati unahitaji kutathmini maadili matatu au zaidi

Swali la 3. Je, IFNULL inaweza kutumiwa na aina za data zaidi ya nambari?

➡ Ndiyo. IFNULL inaweza kutumiwa na mistari, tarehe, nambari, na zaidi.

Mfano 1: Kutumia mistari

SELECT name, IFNULL(email, 'Not registered') AS email
FROM users;

Mfano 2: Kutumia tarehe

SELECT name, IFNULL(last_login, '2000-01-01') AS last_login
FROM users;

Hata hivyo, kuchanganya aina tofauti za data (kama nambari na mistari) kunaweza kusababisha makosa, kwa hivyo kuwa mwangalifu.

SELECT IFNULL(100, 'Error'); -- May cause an error due to different data types

Swali la 4. Je, kutumia IFNULL kunapunguza utendaji?

➡ Kwa ujumla, athari ya utendaji ni ndogo, lakini inaweza kuwa muhimu wakati wa kusindika kiasi kikubwa cha data.

• IFNULL kwa kawaida ni haraka kwa sababu inachunguza maadili mawili tu
• Hata hivyo, matumizi mazito ya IFNULL kwenye data kubwa sana (mamilioni ya safu) yanaweza kuathiri uboreshaji wa fahirisi katika baadhi ya hali

🔹 Vidokezo vya uboreshaji wa utendaji

1. Weka fahirisi kwa usahihi

• Swali kama IFNULL(column, 0) = 100 linaweza kuzuia fahirisi kutumiwa katika baadhi ya hali
• Mbinu moja ni kuhifadhi maadili yenye msingi sahihi badala ya NULL tangu mwanzo

1. IFNULL ni nyepesi kuliko COALESCE

• Kwa sababu COALESCE inatathmini maadili mengi kwa mpangilio, IFNULL inaweza kuwa haraka katika hali nyingi

Swali la 5. Je, unaweza kutumia CASE badala ya IFNULL?

➡ Ndiyo. Unaweza kufikia utendaji sawa na CASE, lakini inakuwa ndefu zaidi.

Na IFNULL

SELECT name, IFNULL(salary, 0) AS salary
FROM employees;

Na CASE

SELECT name,
  CASE WHEN salary IS NULL THEN 0 ELSE salary END AS salary
FROM employees;

✔ IFNULL ni fupi na rahisi kutumia
✔ CASE inasaidia hali zinazobadilika zaidi (k.m., unaweza kujumuisha hali zisizo za NULL pia)

Swali la 6. Je, IFNULL inaweza kutumiwa katika kifungu cha WHERE?

➡ Ndiyo. IFNULL inaweza kutumiwa ndani ya kifungu cha WHERE.

Mfano: Badilisha NULL na maadili maalum wakati wa kutafuta

SELECT * FROM users WHERE IFNULL(status, 'Not set') = 'Not set';

Hii inachukua rekodi ambapo status ni NULL.

Muhtasari

• IFNULL na NVL zina utendaji karibu sawa, lakini zinatofautiana kwa DBMS
• IFNULL inatathmini maadili 2; COALESCE inatathmini maadili mengi
• Inafanya kazi na mistari, tarehe, nambari, na zaidi
• Kwa data kubwa, zingatia uboreshaji wa fahirisi
• Unaweza kutumia CASE badala ya IFNULL
• IFNULL inaweza pia kutumiwa katika vifungu vya WHERE

7. Hitimisho

Katika makala hii, tumejifunza kwa kina kuhusu kazi ya IFNULL ya MySQL, ikijumuisha jinsi ya kushughulikia thamani za NULL, jinsi inavyotofautiana na kazi nyingine, na mifano ya vitendo.
Hatimaye, hebu tupitie kwa kifupi kile tulichokifunza.

7.1 IFNULL ni nini?

• IFNULL hurejesha thamani ya dharura wakati thamani iliyotajwa ni NULL
• Sintaksia :

  IFNULL(expression, fallback_value)
  

• Kwa kuepuka NULL, unaweza kuzuia makosa ya mahesabu na matatizo ya data isiyokamilika

7.2 Mifano ya vitendo ya IFNULL

• Badilisha NULL na thamani ya chaguo-msingi (0 au kamba maalum)

  SELECT name, IFNULL(bonus, 0) AS bonus FROM employees;
  

• Shughulikia mahesabu yanayojumuisha NULL kwa usahihi

  SELECT name, salary + IFNULL(bonus, 0) AS total_income FROM employees;
  

• Badilisha NULL kuwa kamba inayofaa kama “Haijarejistri”

  SELECT id, IFNULL(email, 'Not registered') AS email FROM users;
  

• Tumia katika masharti ya WHERE kuchuja thamani za NULL

  SELECT * FROM users WHERE IFNULL(status, 'Not set') = 'Not set';
  

7.3 Tofauti kati ya IFNULL na COALESCE

• IFNULL hurejesha thamani isiyo-NULL kati ya hoja mbili
• COALESCE hurejesha thamani ya kwanza isiyo-NULL kati ya hoja nyingi
• Jinsi ya kuchagua
• Ushughulikiaji rahisi wa NULL → IFNULL
• Chagua thamani ya kwanza isiyo-NULL → COALESCE

7.4 Ushughulikiaji wa NULL katika DBMS nyingine

• NVL ya Oracle inakaribia kuwa sawa na IFNULL ya MySQL
• PostgreSQL na SQL Server kwa kawaida hutumia COALESCE
• Wakati wa uhamisho, lazima ubadilishe kazi hizo ipasavyo

7.5 Utendaji na tahadhari kwa IFNULL

• IFNULL ni nyepesi zaidi kuliko COALESCE
• Kwa seti kubwa za data, utendaji unaweza kupungua ikiwa uboreshaji wa faharasa unaathiriwa
• Kuwa mwangalifu kudumisha aina za data kuwa thabiti (usichanganye aina za nambari na kamba)

7.6 Hitimisho ya mwisho

• Katika MySQL, tumia IFNULL kushughulikia NULL ipasavyo
• Unaweza kuchakata data bila kuathiriwa na NULL
• Elewa tofauti kati ya COALESCE na NVL na tumia kazi sahihi
• Wakati wa uhamisho kati ya hifadhidata, zingatia tofauti katika kazi za kushughulikia NULL