NULL semantikasi - Ma'lumotlar bazasini loyihalash

NULL - "nol" ham, "bo'sh satr" ham emas. U "noma'lum" degani va shu sababli boshqa qoidalar bo'yicha ishlaydi.

`NULL` nima emas #

SQL

CREATE TABLE sinov (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    izoh    VARCHAR(50),
    son     INT
);

INSERT INTO sinov (izoh, son) VALUES
    ('matn', 10),
    ('',     0),
    (NULL,   NULL);

SQL

SELECT id,
       izoh,
       izoh IS NULL     AS null_mi,
       izoh = ''        AS bosh_satrmi,
       son,
       son IS NULL      AS son_null_mi,
       son = 0          AS son_nolmi
FROM sinov
ORDER BY id;

Natija

+----+------+---------+-------------+------+-------------+-----------+
| id | izoh | null_mi | bosh_satrmi | son  | son_null_mi | son_nolmi |
+----+------+---------+-------------+------+-------------+-----------+
|  1 | matn |       0 |           0 |   10 |           0 |         0 |
|  2 |      |       0 |           1 |    0 |           0 |         1 |
|  3 | NULL |       1 |        NULL | NULL |           1 |      NULL |
+----+------+---------+-------------+------+-------------+-----------+

E'tibor bering: uchinchi qatorda izoh = '' natijasi 0 yoki 1 emas, balki NULL.

Uchtasini aralashtirish - eng ko'p uchraydigan mantiqiy xato

Uch qiymatli mantiq #

SQL

SELECT
    1 = 1        AS rost,
    1 = 2        AS yolgon,
    1 = NULL     AS tenglik,
    NULL = NULL  AS null_tenglik,
    NULL <> NULL AS null_tengsizlik,
    NULL IS NULL AS is_null;

Natija

+------+--------+---------+--------------+-----------------+---------+
| rost | yolgon | tenglik | null_tenglik | null_tengsizlik | is_null |
+------+--------+---------+--------------+-----------------+---------+
|    1 |      0 |    NULL |         NULL |            NULL |       1 |
+------+--------+---------+--------------+-----------------+---------+

NULL = NULL rost emas

Bu SQL dagi eng ko'p chalkashtiriladigan qoida.

NULL "noma'lum" degani. Ikki noma'lum qiymat teng bo'lishi ham, bo'lmasligi ham mumkin - shuning uchun javob "noma'lum", ya'ni yana NULL.

Amaliy oqibat:

SQL

-- HECH QACHON qator qaytarmaydi
SELECT * FROM sinov WHERE izoh = NULL;

-- To'g'ri
SELECT * FROM sinov WHERE izoh IS NULL;

Shuning uchun SQL da IS NULL va IS NOT NULL operatorlari bor - oddiy = ishlamaydi.

MariaDB da qo'shimcha operator ham mavjud:

SQL

izoh <=> NULL      -- "NULL-xavfsiz tenglik", NULL uchun 1 qaytaradi

Bu standart SQL emas, lekin MySQL/MariaDB da qulay.

`WHERE` da `NULL` yo'qoladi #

SQL

SELECT id, son FROM sinov WHERE son <> 10 ORDER BY id;

Natija

+----+------+
| id | son  |
+----+------+
|  2 |    0 |
+----+------+

son = NULL bo'lgan qator chiqmadi, garchi u 10 ga teng bo'lmasa ham.

SQL

-- NULL ni ham qo'shish uchun aniq yozish kerak
SELECT id, son FROM sinov WHERE son <> 10 OR son IS NULL ORDER BY id;

Natija

+----+------+
| id | son  |
+----+------+
|  2 |    0 |
|  3 | NULL |
+----+------+

NOT IN va NULL - jimgina yo'qoladigan natija

Bu tuzoq juda ko'p xatoga sabab bo'lgan:

SQL

SELECT * FROM talabalar
WHERE id NOT IN (SELECT talaba_id FROM chetlashtirilganlar);

Agar chetlashtirilganlar.talaba_id da bitta NULL bo'lsa, bu so'rov hech qanday qator qaytarmaydi.

Sabab: x NOT IN (1, 2, NULL) aslida x <> 1 AND x <> 2 AND x <> NULL - oxirgisi NULL beradi, AND esa NULL bilan hech qachon TRUE bo'lmaydi.

Uch yechim:

SQL

-- 1. NULL larni chiqarib tashlash
WHERE id NOT IN (SELECT talaba_id FROM chetlashtirilganlar
                 WHERE talaba_id IS NOT NULL)

-- 2. NOT EXISTS ishlatish (eng ishonchli)
WHERE NOT EXISTS (SELECT 1 FROM chetlashtirilganlar c
                  WHERE c.talaba_id = talabalar.id)

-- 3. Ustunni NOT NULL qilish

NOT EXISTS afzal - u NULL ga befarq va odatda tezroq ham ishlaydi.

Agregatsiyada `NULL` #

SQL

CREATE TABLE ballar (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    talaba VARCHAR(50) NOT NULL,
    ball   INT
);

INSERT INTO ballar (talaba, ball) VALUES
    ('Husanboy', 90),
    ('Malika',   80),
    ('Kamola',   NULL),
    ('Nodira',   NULL);

SQL

SELECT
    COUNT(*)        AS qatorlar,
    COUNT(ball)     AS ballar_soni,
    SUM(ball)       AS yigindi,
    AVG(ball)       AS ortacha,
    MIN(ball)       AS eng_kam,
    MAX(ball)       AS eng_kop
FROM ballar;

Natija

+----------+-------------+---------+---------+---------+---------+
| qatorlar | ballar_soni | yigindi | ortacha | eng_kam | eng_kop |
+----------+-------------+---------+---------+---------+---------+
|        4 |           2 |     170 | 85.0000 |      80 |      90 |
+----------+-------------+---------+---------+---------+---------+

Agregat funksiyalar NULL ni tashlab yuboradi

Funksiya	`NULL` bilan
`COUNT(*)`	Barcha qatorni sanaydi
`COUNT(ustun)`	`NULL` larni sanamaydi
`SUM`, `AVG`, `MIN`, `MAX`	`NULL` larni e'tiborsiz qoldiradi

Bu AVG da muhim oqibatga ega:

SQL

AVG(ball) = 170 / 2 = 85      -- NULL lar hisobga olinmadi

Agar NULL ni nol deb hisoblash kerak bo'lsa, aniq aytishingiz kerak:

SQL

SELECT AVG(COALESCE(ball, 0)) FROM ballar;    -- 170 / 4 = 42.5

Qaysi biri to'g'ri - biznes savoli:

Savol	To'g'ri hisob
"Baho olganlarning o'rtachasi"	`AVG(ball)`
"Sinfning o'rtacha bahosi"	`AVG(COALESCE(ball, 0))`

Bo'sh jadvalda esa SUM 0 emas, NULL qaytaradi:

SQL

SELECT COALESCE(SUM(ball), 0) FROM ballar WHERE 1 = 0;   -- 0

Shuning uchun SUM natijasini deyarli har doim COALESCE bilan o'rash kerak.

`NULL` bilan ishlash funksiyalari #

SQL

SELECT
    talaba,
    ball,
    COALESCE(ball, 0)           AS nol_bilan,
    IFNULL(ball, -1)            AS ifnull_bilan,
    NULLIF(ball, 90)            AS nullif_90,
    CASE WHEN ball IS NULL THEN 'topshirmagan'
         ELSE CAST(ball AS CHAR) END AS holat
FROM ballar
ORDER BY id;

Natija

+----------+------+-----------+--------------+-----------+--------------+
| talaba   | ball | nol_bilan | ifnull_bilan | nullif_90 | holat        |
+----------+------+-----------+--------------+-----------+--------------+
| Husanboy |   90 |        90 |           90 |      NULL | 90           |
| Malika   |   80 |        80 |           80 |        80 | 80           |
| Kamola   | NULL |         0 |           -1 |      NULL | topshirmagan |
| Nodira   | NULL |         0 |           -1 |      NULL | topshirmagan |
+----------+------+-----------+--------------+-----------+--------------+

Funksiya	Vazifasi
`COALESCE(a, b, c)`	Birinchi `NULL` bo'lmagan qiymat
`IFNULL(a, b)`	`a` `NULL` bo'lsa `b` (MySQL/MariaDB)
`NULLIF(a, b)`	`a = b` bo'lsa `NULL`, aks holda `a`

NULLIF - nolga bo'lishdan himoya

SQL

SELECT jami / NULLIF(soni, 0) AS ortacha FROM hisobot;

soni nol bo'lsa, NULLIF uni NULL ga aylantiradi va natija NULL bo'ladi - xato o'rniga.

MariaDB da nolga bo'lish odatda NULL beradi, lekin qat'iy rejimda xato bo'lishi mumkin. NULLIF har ikkala holatda ham ishonchli.

COALESCE esa standart SQL, IFNULL faqat MySQL/MariaDB da bor - portativ kod uchun COALESCE ishlating.

`UNIQUE` va `NULL` #

SQL

CREATE TABLE foydalanuvchilar (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    email    VARCHAR(100) UNIQUE,
    telefon  VARCHAR(20)  UNIQUE
);

INSERT INTO foydalanuvchilar (email, telefon) VALUES
    ('[email protected]', '901234567'),
    ('[email protected]', NULL),
    ('[email protected]', NULL),
    (NULL,     '911112233');

SQL

SELECT id, COALESCE(email, '(null)') AS email,
       COALESCE(telefon, '(null)') AS telefon
FROM foydalanuvchilar
ORDER BY id;

Natija

+----+--------+-----------+
| id | email  | telefon   |
+----+--------+-----------+
|  1 | [email protected] | 901234567 |
|  2 | [email protected] | (null)    |
|  3 | [email protected] | (null)    |
|  4 | (null) | 911112233 |
+----+--------+-----------+

UNIQUE bir nechta NULL ga ruxsat beradi

Ikkita qatorda telefon NULL - va UNIQUE cheklov e'tiroz bildirmadi.

Sabab yana o'sha: NULL = NULL rost emas, shuning uchun ma'lumotlar bazasi ularni takror deb hisoblamaydi.

Bu standart SQL xatti-harakati va odatda foydali:

SQL

pasport VARCHAR(9) UNIQUE      -- hammada ham bo'lmasligi mumkin

Lekin ba'zan bu kutilmagan bo'ladi. "Faqat bitta NULL" kerak bo'lsa, uni cheklov bilan ifodalab bo'lmaydi - NOT NULL qilib, "yo'q" ma'nosini boshqa qiymat bilan bering:

SQL

holat ENUM('faol', 'nofaol', 'nomalum') NOT NULL DEFAULT 'nomalum'

Diqqat: birlamchi kalit NULL ni umuman qabul qilmaydi - u avtomatik NOT NULL.

`NULL` va tartiblash #

SQL

SELECT talaba, ball FROM ballar ORDER BY ball ASC;

Natija

+----------+------+
| talaba   | ball |
+----------+------+
| Kamola   | NULL |
| Nodira   | NULL |
| Malika   |   80 |
| Husanboy |   90 |
+----------+------+

SQL

-- NULL larni oxiriga surish
SELECT talaba, ball
FROM ballar
ORDER BY ball IS NULL, ball ASC;

Natija

+----------+------+
| talaba   | ball |
+----------+------+
| Malika   |   80 |
| Husanboy |   90 |
| Kamola   | NULL |
| Nodira   | NULL |
+----------+------+

NULL tartibi standartlashtirilmagan

MySQL/MariaDB da NULL eng kichik hisoblanadi - ASC da boshida, DESC da oxirida turadi.

PostgreSQL da esa aksincha - NULL eng katta.

Standart SQL da NULLS FIRST / NULLS LAST bor, lekin MySQL/MariaDB uni qo'llab-quvvatlamaydi. Yuqoridagi hiyla ishlatiladi:

SQL

ORDER BY ball IS NULL, ball        -- NULL lar oxirida
ORDER BY ball IS NOT NULL, ball    -- NULL lar boshida

ball IS NULL 0 yoki 1 qaytaradi, shuning uchun u birinchi saralash mezoni bo'lib xizmat qiladi.

Foydalanuvchiga ko'rsatiladigan ro'yxatda NULL lar odatda oxirida bo'lishi kutiladi - buni aniq yozing.

`NULL` ni qachon ishlatish kerak #

NULL yoki standart qiymat?

Holat	Qaror
Qiymat haqiqatan noma'lum	`NULL`
Qiymat hali kiritilmagan	`NULL`
Qiymat qo'llanmaydi	`NULL`
"Nol" mazmunli qiymat	`0`, `NULL` emas
"Bo'sh" mazmunli qiymat	`''`, `NULL` emas
Har doim qiymat bo'ladi	`NOT NULL`

Misollar:

SQL

tugilgan_sana   DATE NULL         -- bilmasligimiz mumkin
oxirgi_kirish   DATETIME NULL     -- hali kirmagan bo'lishi mumkin
qoldiq          DECIMAL NOT NULL DEFAULT 0   -- nol - haqiqiy qiymat
izoh            TEXT NULL         -- yozilmagan bo'lishi mumkin
faolmi          BOOLEAN NOT NULL DEFAULT TRUE

NULL ga ruxsat berish narxi bor:

Har so'rovda IS NULL haqida o'ylash kerak;
NOT IN, <>, JOIN da tuzoqlar paydo bo'ladi;
Ilova kodida null tekshiruvi kerak;
Indeks samaradorligi biroz pasayadi.

Shuning uchun imkon bo'lsa NOT NULL qo'ying. 2-bo'limda aytilganidek, keyinchalik bo'shatish oson, qattiqlashtirish qiyin.

Amaliy tekshiruv #

SQL

SELECT
    COUNT(*)                              AS jami,
    SUM(ball IS NULL)                     AS ballsiz,
    ROUND(100.0 * SUM(ball IS NULL) / COUNT(*), 1) AS foiz
FROM ballar;

Natija

+------+---------+------+
| jami | ballsiz | foiz |
+------+---------+------+
|    4 |       2 | 50.0 |
+------+---------+------+

Amaliy topshiriq

NULL, 0 va '' ni bir jadvalda taqqoslang.
izoh = NULL nima uchun ishlamasligini tushuntiring.
WHERE son <> 10 NULL qatorni tashlab ketishini ko'ring.
NOT IN va NULL tuzog'ini takrorlang.
NOT EXISTS bilan uni tuzating.
COUNT(*) va COUNT(ustun) farqini ko'rsating.
AVG NULL larni tashlashini isbotlang.
COALESCE, IFNULL, NULLIF ni sinang.
UNIQUE bir nechta NULL ga ruxsat berishini tekshiring.
NULL larni tartibda oxiriga suring.

Xulosa #

NULL - "noma'lum", 0 yoki '' emas.
NULL = NULL rost emas - IS NULL ishlating.
WHERE da NULL qatorlar jimgina tushib qoladi.
NOT IN + NULL = hech qanday natija; NOT EXISTS ishlating.
COUNT(*) hammasini, COUNT(ustun) faqat NULL bo'lmaganini sanaydi.
SUM, AVG, MIN, MAX NULL larni tashlab yuboradi.
Bo'sh natijada SUM NULL qaytaradi - COALESCE bilan o'rang.
UNIQUE bir nechta NULL ga ruxsat beradi.
NULL tartibi tizimga bog'liq - aniq yozing.
Imkon bo'lsa NOT NULL - NULL ning narxi bor.

Keyingi bo'limda cheklovlar bilan ma'lumot sifatini ta'minlashni ko'ramiz.

NULL nima emas #

Uch qiymatli mantiq #

WHERE da NULL yo'qoladi #

Agregatsiyada NULL #

NULL bilan ishlash funksiyalari #

UNIQUE va NULL #

NULL va tartiblash #

NULL ni qachon ishlatish kerak #

Amaliy tekshiruv #

Xulosa #

Xatolik topdingizmi?

`NULL` nima emas #

`WHERE` da `NULL` yo'qoladi #

Agregatsiyada `NULL` #

`NULL` bilan ishlash funksiyalari #

`UNIQUE` va `NULL` #

`NULL` va tartiblash #

`NULL` ni qachon ishlatish kerak #