IN, BETWEEN, LIKE va NULL

= va > bilan ko‘p narsa qilsa bo‘ladi, lekin SQL’da shartlarni qisqa va aniq yozish uchun yana to‘rtta foydali vosita bor.

IN — ro‘yxatdan biri

Bir nechta OR o‘rniga:

SELECT first_name, last_name, city
FROM students
WHERE city IN ('Buxoro', 'Navoiy', 'Urganch');

Bu city = 'Buxoro' OR city = 'Navoiy' OR city = 'Urganch' bilan bir xil, faqat qisqaroq. Teskarisi — NOT IN.

BETWEEN — oraliqda

SELECT first_name, last_name, score
FROM grades JOIN students ON students.id = grades.student_id
WHERE score BETWEEN 90 AND 100;

BETWEEN a AND b — chegaralar ham kiradi, ya’ni >= a AND <= b. Sanalar uchun ham ishlaydi:

SELECT first_name, last_name, birth_date FROM students
WHERE birth_date BETWEEN '2006-01-01' AND '2006-03-31';

LIKE — andaza bo‘yicha qidirish

• % — istalgan sondagi belgilar (hech narsa ham bo‘lishi mumkin);
• _ — aynan bitta belgi.

SELECT first_name, last_name FROM students WHERE last_name LIKE 'Ra%';

PostgreSQL’da LIKE katta-kichik harfni farqlaydi: 'a%' «Aziz» ni topmaydi. Farqlamasdan qidirish uchun ILIKE bor: WHERE first_name ILIKE 'a%'.

NULL — qiymat yo‘q

NULL — «noma’lum» yoki «kiritilmagan» degani. Masalan, ba’zi talabalarning telefoni yozilmagan. NULL hech narsaga teng emas — hatto o‘ziga ham! Shuning uchun = NULL ishlamaydi:

SELECT first_name, last_name FROM students WHERE phone IS NULL;

To‘g‘risi — IS NULL va IS NOT NULL.

SELECT name FROM study_groups WHERE curator_id IS NOT NULL LIMIT 5;

Esda qoldiring

• IN (…) — ro‘yxatdan biri; BETWEEN a AND b — chegaralar bilan oraliq.
• LIKE: % — istalgan belgilar, _ — bitta belgi.
• NULL bilan faqat IS NULL / IS NOT NULL ishlaydi.