COALESCE va CASE — shartli qiymatlar

Hisobotda NULL o'rniga tushunarli qiymat, raqam o'rniga "Yuqori / O'rta / Past" kabi toifa kerak bo'ladi. Buning uchun ikki vosita bor: COALESCE va CASE.

Jadval: clients

COALESCE — birinchi mavjud qiymat

COALESCE(a, b, c) chapdan o'ngga qarab birinchi NULL bo'lmagan qiymatni qaytaradi.

SELECT name,
       COALESCE(phone, email, 'aloqa yo''q') AS contact
FROM clients;

Matn ichidagi apostrof ikkita yoziladi: 'aloqa yo''q'.

Eng ko'p ishlatilishi — hisobda NULL ni 0 ga almashtirish: COALESCE(bonus, 0).

CASE — shart bo'yicha qiymat

SELECT name, balance,
       CASE
         WHEN balance < 0         THEN 'Qarzdor'
         WHEN balance >= 1000000  THEN 'VIP'
         ELSE 'Oddiy'
       END AS segment
FROM clients;

CASE shartlarni yuqoridan pastga tekshiradi va birinchi rost chiqqanida to'xtaydi. Hech biri rost bo'lmasa — ELSE qiymati, ELSE yozilmagan bo'lsa — NULL.

Shartli sanash: CASE agregat ichida

Bitta so'rovda har bir toifadagi mijozlar soni:

SELECT
  COUNT(CASE WHEN balance < 0 THEN 1 END)        AS debtors,
  COUNT(CASE WHEN balance >= 1000000 THEN 1 END) AS vip,
  SUM(CASE WHEN balance > 0 THEN balance ELSE 0 END) AS positive_total
FROM clients;

PostgreSQL da xuddi shu narsani COUNT(*) FILTER (WHERE balance < 0) ko'rinishida ham yozish mumkin.

Ko'p uchraydigan xato: shartlar tartibi

SELECT name,
       CASE
         WHEN balance >= 0       THEN 'Oddiy'
         WHEN balance >= 1000000 THEN 'VIP'
         ELSE 'Qarzdor'
       END AS segment
FROM clients;

Aziz ham "Oddiy" bo'lib qoldi: birinchi shart (>= 0) uning uchun ham rost, CASE ikkinchisigacha yetib bormaydi. Torroq shartni yuqoriga yozing.

Ikkinchi xato: CASE da NULL ni "=" bilan tekshirish

SELECT name, CASE email WHEN NULL THEN 'email yo''q' ELSE email END
FROM clients;

Qisqa CASE email WHEN ... shakli = bilan solishtiradi, email = NULL esa hech qachon rost emas. To'g'risi — CASE WHEN email IS NULL THEN ... yoki shunchaki COALESCE(email, 'email yo''q').

Xulosa

• COALESCE(a, b, ...) — birinchi NULL bo'lmagan qiymat; bo'sh joylarni to'ldirish uchun.
• CASE WHEN ... THEN ... ELSE ... END — toifalarga ajratish; shartlar tartib bilan tekshiriladi.
• COUNT(CASE WHEN ... THEN 1 END) yoki FILTER — bitta so'rovda shartli sanash.
• NULL ni CASE da faqat IS NULL bilan tekshiring.