NULL bilan ishlash — nega = NULL ishlamaydi

NULL — bu nol ham, bo'sh satr ham emas. Bu "qiymat noma'lum" degani. SQL dagi ko'p sirli xatolar aynan shu farqni unutishdan kelib chiqadi.

Jadval: employees

Nega "= NULL" hech narsa topmaydi

SELECT name FROM employees WHERE phone = NULL;

Natija bo'sh. Sababi: noma'lum qiymatni biror narsa bilan solishtirish natijasi ham noma'lum (NULL), WHERE esa faqat rost bo'lgan qatorlarni qoldiradi. Hatto NULL = NULL ham rost emas.

To'g'risi — maxsus operator:

SELECT name FROM employees WHERE phone IS NULL;

Teskarisi — IS NOT NULL.

Uch qiymatli mantiq

SQL da shart uch xil natija beradi: TRUE, FALSE va NULL (noma'lum).

SELECT name FROM employees WHERE bonus <> 500;

Malika chiqmadi! Uning bonusi noma'lum, "noma'lum 500 ga teng emasmi?" degan savolga javob ham noma'lum. Malikani ham qo'shish kerak bo'lsa, buni aniq aytish kerak:

SELECT name FROM employees WHERE bonus <> 500 OR bonus IS NULL;

PostgreSQL da qisqaroq yo'li ham bor: WHERE bonus IS DISTINCT FROM 500 — u NULL ni oddiy qiymat kabi solishtiradi.

Eng xavfli tuzoq: NOT IN va NULL

Vazifa: buyurtma bermagan mijozlar. orders.customer_id ichida bitta NULL bo'lsa:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Natija bo'sh chiqadi, garchi buyurtmasiz mijozlar bo'lsa ham. NOT IN (1, 2, NULL) aslida id <> 1 AND id <> 2 AND id <> NULL, oxirgisi esa doim noma'lum. Xavfsiz variant — NOT EXISTS:

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Agregatlar NULL ni tashlab ketadi

SELECT AVG(bonus) AS avg_bonus, COUNT(bonus) AS known, COUNT(*) AS total
FROM employees;

O'rtacha (500 + 0) / 2 = 250, uchtaga emas, ikkitaga bo'lindi. Agar noma'lum bonusni 0 deb hisoblash kerak bo'lsa — AVG(COALESCE(bonus, 0)), natija 166.67.

Xulosa

• NULL bilan solishtirish uchun faqat IS NULL / IS NOT NULL.
• <>, =, > kabi shartlar NULL li qatorlarni jimgina tashlaydi.
• NOT IN ichida NULL bo'lsa natija bo'sh — NOT EXISTS dan foydalaning.
• SUM, AVG, COUNT(ustun) NULL ni hisobga olmaydi; kerak bo'lsa COALESCE bilan almashtiring.