Chuqur filtering (IN, LIKE, NULL)

3-bob 12 kod bloki 2499 so'z

2-bobda kundalik operatorlar ko'rildi. Bu bobda query ni o'qishli qiladigan operatorlar — va query ni indamay noto'g'ri qiladigan NULL xatti-harakati ko'riladi. Agar talaba SQL dan faqat bitta bobni eslab qolsa, shu bo'lsin: quyidagi NOT IN tuzog'i real kompaniyalarda production hisobotlarini buzgan.

IN va NOT IN

IN — bir zanjir OR ning qisqartmasi. Bu ikkitasi bir xil:

-- uzun ko'rinishi
SELECT first_name, city FROM students
WHERE city = 'Tashkent' OR city = 'Samarkand' OR city = 'Bukhara';

-- xuddi shu narsa, o'qish oson
SELECT first_name, city FROM students
WHERE city IN ('Tashkent', 'Samarkand', 'Bukhara');

1-natija

… 160 qatordan birinchi 15 tasi

160 qator

2-natija

… 160 qatordan birinchi 15 tasi

160 qator

1 ms

first_namecity

AzizaTashkent BekzodSamarkand DilnozaBukhara JasurTashkent OtabekTashkent ZarinaSamarkand

Ro'yxat subquery ham bo'lishi mumkin — aynan shu yerda IN haqiqatan kuchli bo'ladi, va 6-bob shuning ustiga quriladi:

-- "Machine Learning (5-course) ga qaysi student lar yozilgan?"
SELECT first_name, last_name FROM students
WHERE student_id IN (SELECT student_id FROM enrollments WHERE course_id = 5);
-- Aziza (1), Dilnoza (3), Otabek (6), Zarina (9)

… 130 qatordan birinchi 15 tasi

130 qator

0 ms

BETWEEN BETWEEN a AND b — bu >= a AND <= b degani. Ikkala chekka ham kiradi.

SELECT enrollment_id, grade FROM enrollments
WHERE grade BETWEEN 80 AND 100
ORDER BY grade DESC;
-- 8 ta row: 100, 95, 92, 91, 89, 88, 83, 81

… 494 qatordan birinchi 15 tasi

494 qator

1 ms

:::gotcha ⚠️ BETWEEN va sana/vaqt Sof DATE column i uchun BETWEEN '2026-02-01' AND '2026-02-28' yaxshi. Lekin column TIMESTAMP bo'lsa, '2026-02-28' 2026-02-28 00:00:00 degani — ya'ni 28-kuni sodir bo'lgan hamma narsa tashqarida qoladi. Bu oylik hisobotlarda indamay bir kunlik data ni yo'qotadi. Xavfsiz usul — yarim ochiq oraliq:

WHERE enrolled_at >= '2026-02-01' AND enrolled_at < '2026-03-01'; -- ← doim to'g'ri, date ham, timestamp ham Yarim ochiq oraliqni standart odat sifatida o'rgating; uning chekka holati umuman yo'q. :::

LIKE va wildcard lar

LIKE matnni pattern bilan solishtiradi. Aynan ikkita wildcard bor:

-- A bilan boshlanadi
SELECT first_name FROM students WHERE first_name LIKE 'A%';        -- Aziza

-- 'ova' bilan tugaydi (ko'p uchraydigan ayol familiya oxiri)
SELECT last_name FROM students WHERE last_name LIKE '%ova';
-- Karimova, Yusupova, Sobirova, Ismoilova, Qodirova — 5 ta row

-- ichida istalgan joyda 'Learning' bor
SELECT title FROM courses WHERE title LIKE '%Learning%';
-- Machine Learning, Deep Learning

-- aynan 5 ta belgidan iborat
SELECT first_name FROM students WHERE first_name LIKE '_____';
-- Aziza, Jasur, Timur

-- ichida 'Data' YO'Q
SELECT title FROM courses WHERE title NOT LIKE '%Data%';

1-natija

… 42 qatordan birinchi 15 tasi

42 qator

2-natija

… 126 qatordan birinchi 15 tasi

126 qator

3-natija

2 qator

4-natija

… 43 qatordan birinchi 15 tasi

43 qator

5-natija

14 qator

1 ms

:::gotcha ⚠️ Katta-kichik harf masalasi database ga bog'liq SQLite da LIKE ASCII harflar uchun katta-kichikni farqlamaydi, shuning uchun 'a%' Aziza ni topadi. PostgreSQL da LIKE katta-kichikni farqlaydi va farqlamaslik uchun ILIKE kerak. Universal yechim — ikkala tomonni bir xil holatga keltirish:

WHERE LOWER(first_name) LIKE LOWER('a%');

:::

NULL bilan filtrlash

1-bobdagi hamma narsa bir joyda, chunki talabalar aynan shu yerda unga duch keladi.

-- shahri yozilmagan student lar
SELECT first_name FROM students WHERE city IS NULL;          -- Sevara

-- hali baholanmagan enrollment lar
SELECT enrollment_id, status FROM enrollments WHERE grade IS NULL;
-- 6 ta row: 3, 8, 12, 16, 19, 20

-- baholangan enrollment lar
SELECT COUNT(*) FROM enrollments WHERE grade IS NOT NULL;     -- 14

1-natija

8 qator

2-natija

… 674 qatordan birinchi 15 tasi

674 qator

3-natija

1 qator

1 ms

:::formula Uch qiymatli rostlik jadvali TRUE FALSE UNKNOWN AND T | TRUE FALSE UNKNOWN F | FALSE FALSE FALSE ← AND uchun FALSE yutadi U | UNKNOWN FALSE UNKNOWN

OR T | TRUE TRUE TRUE ← OR uchun TRUE yutadi F | TRUE FALSE UNKNOWN U | TRUE UNKNOWN UNKNOWN

WHERE row ni faqat yakuniy qiymat TRUE bo'lgandagina qoldiradi. :::

Ko'rsatishga arziydigan ikkita foydali natija:

• FALSE AND UNKNOWN = FALSE. Ya'ni AND zanjirining biror qismi aniq noto'g'ri bo'lsa, boshqa joydagi NULL lar ahamiyatsiz.
• TRUE OR UNKNOWN = TRUE. Ya'ni WHERE city = 'Tashkent' OR city IS NULL siz kutgandek ishlaydi.

NOT IN + NULL tuzog'i

Bu kundalik SQL dagi eng xavfli xatti-harakat, va u hech qanday xato xabari bermaydi — shunchaki indamay bo'sh natija qaytaradi.

-- "Qaysi teacher lar hech qachon Machine Learning (5-course) o'qitmagan?"
SELECT full_name FROM teachers
WHERE teacher_id NOT IN (
    SELECT teacher_id FROM enrollments WHERE course_id = 5
);

-- Kutilgan: Rustam Halilov, Kamola Ergasheva, Sardor Yoqubov
-- Aslida:   0 ta row. Hech narsa. Xato ham yo'q.

0 qator

1 ms

Nega. Subquery (1, 1, 1, NULL) qaytaradi — 19-enrollment ga hali teacher biriktirilmagan. Endi Rustam (teacher_id 2) uchun NOT IN nimani anglatishini yozib chiqamiz:

Ro'yxatning istalgan joyidagi bitta NULL NOT IN ni har bir mos kelmaydigan row uchun UNKNOWN qiladi. Butun natija yo'qoladi.

Uchta tuzatish usuli

-- 1-usul: subquery dan NULL larni chiqarib tashlash (eng sodda)
SELECT full_name FROM teachers
WHERE teacher_id NOT IN (
    SELECT teacher_id FROM enrollments
    WHERE course_id = 5 AND teacher_id IS NOT NULL
);

-- 2-usul: NOT EXISTS — tabiatan NULL ga bog'liq emas (professional tanlov)
SELECT full_name FROM teachers t
WHERE NOT EXISTS (
    SELECT 1 FROM enrollments e
    WHERE e.course_id = 5 AND e.teacher_id = t.teacher_id
);

-- 3-usul: LEFT JOIN ... WHERE o'ng tomon IS NULL  (5-bob)

1-natija

10 qator

2-natija

10 qator

1 ms

:::tip 💡 Talabalarga beriladigan qoida Column NULL bo'lmasligiga to'liq ishonchingiz komil bo'lmasa, subquery ga qarshi NOT IN yozmang. NOT EXISTS ni afzal ko'ring. Oddiy IN xavfsiz — ro'yxatdagi NULL shunchaki hech narsaga mos kelmaydi, bu esa kutilgan xatti-harakat. Faqat inkor buziladi. :::

COALESCE, IFNULL, NULLIF

COALESCE(a, b, c, ...) NULL bo'lmagan birinchi argumentni qaytaradi. U hamma database da ishlaydi, shuning uchun uni IFNULL (SQLite/MySQL) yoki ISNULL (SQL Server) dan afzal ko'ring.

SELECT
    first_name,
    city                              AS raw_city,
    COALESCE(city, 'Not specified')   AS clean_city
FROM students
WHERE student_id IN (6, 7, 8);

3 qator

0 ms

first_nameraw_cityclean_city

OtabekTashkentTashkent SevaraNULLNot specified TimurAndijanAndijan

U xohlagancha argument oladi va ularni tartib bilan tekshirib chiqadi:

SELECT COALESCE(mobile_phone, home_phone, work_phone, 'no contact') AS phone
FROM some_contacts_table;

NULLIF — teskari yo'nalish NULLIF(a, b) a = b bo'lganda NULL, aks holda a ni qaytaradi. Uning eng foydali vazifasi — nolga bo'lishning oldini olish:

-- maxraj 0 bo'lsa, NULLIF uni NULL ga aylantiradi,
-- x / NULL esa xato emas, NULL beradi
SELECT total_score * 1.0 / NULLIF(attempts, 0) AS avg_score
FROM some_results_table;

:::gotcha ⚠️ Raqamlarni o'ylamay 0 ga COALESCE qilmang COALESCE(grade, 0) "hali baholanmagan" ni "nol ball oldi" ga aylantiradi. Student ning o'rtachasi 90 dan 60 ga tushib ketadi va hisobot hech kim sezmaydigan tarzda noto'g'ri bo'lib qoladi. NULL ni almashtirish — formatlash qadami emas, biznes qarori: yo'q qiymat e'tiborsiz qoldirilishi kerakmi (NULL qoldiring) yoki haqiqatan nol deb hisoblanishi kerakmi — shuni so'rang. :::

Sana bo'yicha filtrlash

SQLite da 'YYYY-MM-DD' matn ko'rinishida saqlangan sanalar <, > va BETWEEN bilan to'g'ri solishtiriladi, chunki bu format alifbo bo'yicha ham xronologik tartibda saralanadi.

-- 2026-yil fevralida yozilganlar — yarim ochiq oraliq, doim xavfsiz
SELECT enrollment_id, enrolled_at FROM enrollments
WHERE enrolled_at >= '2026-02-01' AND enrolled_at < '2026-03-01';
-- 12 ta row

-- sananing qismlarini ajratib olish (SQLite)
SELECT
    enrolled_at,
    strftime('%Y', enrolled_at) AS year,
    strftime('%m', enrolled_at) AS month
FROM enrollments LIMIT 3;

-- 2025-yilda yozilganlarning soni
SELECT COUNT(*) FROM enrollments
WHERE strftime('%Y', enrolled_at) = '2025';    -- 6

1-natija

0 qator

2-natija

3 qator

3-natija

1 qator

1 ms

:::gotcha ⚠️ Column ustidagi function index ni o'chiradi WHERE strftime('%Y', enrolled_at) = '2025' database ni solishtirishdan oldin har bir row uchun strftime ni hisoblashga majbur qiladi — demak enrolled_at ustidagi har qanday index foydasiz. Oraliqli variant xuddi shu ishni qiladi, lekin index bilan ishlayveradi:

WHERE enrolled_at >= '2025-01-01' AND enrolled_at < '2026-01-01'; Umumiy qoida, va u faqat sanaga taalluqli emas: solishtirishning chap tomonida column ni toza qoldiring. WHERE price * 1.1 > 1000 sekin; WHERE price > 1000 / 1.1 tez. :::

Dialect izohi — xuddi shu ish boshqa database larda:

Ko'p uchraydigan xatolar

Intervyu savollari

S: Nega NOT IN umuman hech qanday row qaytarmasligi mumkin?

Chunki qiymatlar ro'yxatida NULL bo'lsa, har bir solishtirish UNKNOWN bo'ladi va WHERE faqat TRUE ni qoldiradi. Natija bo'sh chiqadi, xato ham bo'lmaydi. Bunga bog'liq bo'lmagan NOT EXISTS ni ishlating.

S: IN va EXISTS farqi nima?

IN qiymatni tayyor qiymatlar ro'yxati bilan solishtiradi. EXISTS "kamida bitta mos row bormi?" deb so'raydi va birinchi topilganda to'xtashi mumkin. EXISTS NULL ga nisbatan xavfsiz va katta table lardagi correlated subquery lar uchun odatda yaxshiroq; IN esa qisqa literal ro'yxat uchun tushunarliroq. Zamonaviy optimizer lar ko'pincha birini ikkinchisiga o'zi aylantiradi.

S: BETWEEN chekkalarni o'z ichiga oladimi?

Ha, ikkala chekkani ham — u aynan >= a AND <= b. Aynan shuning uchun u timestamp da xavfli: '2026-02-28' 28-kunning yarim tunini bildiradi.

S: LIKE dagi % va _ farqi?

% istalgan sondagi (jumladan nol ta) belgiga, _ aynan bitta belgiga mos keladi.

S: % belgisining o'zini qanday qidirasiz?

Escape qilib: WHERE code LIKE '50\%%' ESCAPE '\' "50%" bilan boshlanadigan kodlarni topadi.

S: COALESCE nima qiladi va u NULLIF dan nimasi bilan farq qiladi?

COALESCE NULL bo'lmagan birinchi argumentni qaytaradi — u NULL ni yo'qotadi. NULLIF(a,b) a b ga teng bo'lganda NULL qaytaradi — u NULL ni yaratadi, ko'pincha nolga bo'lishdan saqlanish uchun.

S: Nega WHERE YEAR(order_date) = 2025 yomon amaliyot hisoblanadi?

Column ni function ga o'rash predicate ni non-sargable qiladi: order_date ustidagi index ishlatilmaydi va engine har bir row ni skanerlaydi. Uni oraliq ko'rinishida qayta yozing.

Xulosa

• IN = OR lar zanjiri, va uning ro'yxati subquery bo'lishi mumkin.
• NOT IN + ro'yxatdagi NULL = indamay nol ta row. NOT EXISTS ishlating.
• BETWEEN ikkala chekkani ham qamrab oladi — aynan shuning uchun sana va timestamp lar uchun xavfsiz odat yarim ochiq oraliq (>= start AND < next_start).
• LIKE: % = ko'p belgi, _ = aynan bitta. Boshidagi % index dan foydalanishga to'sqinlik qiladi; katta-kichik harf masalasi database ga bog'liq.
• = bilan emas, IS NULL / IS NOT NULL bilan solishtiring. FALSE AND UNKNOWN = FALSE, TRUE OR UNKNOWN = TRUE ekanini eslang.
• COALESCE NULL ni almashtiradi; NULLIF uni yaratadi (nolga bo'lishdan himoya). NULL ni 0 ga almashtirish — formatlash emas, biznes qarori.
• Filtrlanayotgan column ni hech qachon function ga o'ramang — bu index ni o'chiradi. Oraliq ko'rinishida qayta yozing.

Keyingi: 4-bob ko'p row ni bitta songa aylantiradi — COUNT, SUM, AVG, GROUP BY va HAVING.