NULL nima o’zi?
NULL — bu 0 emas, bo'sh matn emas. NULL ma'lumot mavjud emasligini bildiradi.
CREATE TABLE Participants
(
ParticipantID INT IDENTITY(1,1) PRIMARY KEY,
FullName NVARCHAR(100) NOT NULL,
Email NVARCHAR(100) NOT NULL,
PhoneNumber NVARCHAR(20),
Age INT NOT NULL,
Address NVARCHAR(200),
Comments NVARCHAR(MAX),
CreatedDate DATETIME DEFAULT GETDATE()
);INSERT INTO Participants
(
FullName,
Email,
PhoneNumber,
Age
)
VALUES
(
'Valijon Qodirov',
'valijon@gmail.com',
'+998901234567',
30
);
NULL bilan ishlash funksiyalari
Funksiya | Vazifasi | Namuna | Natija |
|---|---|---|---|
ISNULL | NULL o'rniga boshqa qiymat qo'yadi | SELECT FullName, ISNULL (Address, 'Manzil kiritilmagan') FROM Participants | Manzil kiritilmagan |
COALESCE | Birinchi NULL bo'lmagan qiymatni qaytaradi | COALESCE(null, null, 55) | 55 |
NULLIF | Ikkita qiymatni solishtiradi. | NULLIF(50, 50) | NULL |
IS NULL | NULL qiymatlarni topadi | SELECT * FROM Participants WHERE Address IS NULL | Address kiritilmagan foydalanuvchilar chiqadi. |
IS NOT NULL | NULL bo'lmaganlarni topadi | SELECT * FROM Participants WHERE PhoneNumber IS NOT NULL | Telefon raqami mavjud foydalanuvchilar chiqadi. |
ISNULL vs COALESCE
ISNULL | COALESCE |
|---|---|
Faqat 2 ta qiymat bilan ishlaydi | Cheksiz |
Tezroq | Sekinroq |
SQL Server'da ISNULL | Barcha databazalarda bir xil |
Oracle'da NVL | Barcha databazalarda bir xil |
MySQL'da IFNULL | Barcha databazalarda bir xil |
Real loyiha misoli
Tasavvur qiling:
CREATETABLE Customers
(
customer_nameVARCHAR(50),
mobile_phoneVARCHAR(20),
home_phoneVARCHAR(20)
);Ma'lumotlar:
customer_name | mobile_phone | home_phone |
|---|---|---|
Ali | 998901111111 | NULL |
Vali | NULL | 998902222222 |
Hasan | NULL | NULL |
ISNULL bilan
SELECT
customer_name,
ISNULL(mobile_phone,'Telefon yo''q')
FROM Customers;Natija:
customer_name | phone |
|---|---|
Ali | 998901111111 |
Vali | Telefon yo'q |
Hasan | Telefon yo'q |
COALESCE bilan
SELECT
customer_name,
COALESCE(mobile_phone, home_phone,'Telefon yo''q')
FROM Customers;Natija:
customer_name | phone |
|---|---|
Ali | 998901111111 |
Vali | 998902222222 |
Hasan | Telefon yo'q |
Bu yerda COALESCE ancha qulay.
NULL bilan ishlash kerak bo'lgan jihatlar
Agregatlar bilan qo'llash
Matematik amallar bilan qo'llash
JOIN bilan qo'llash
Saralashda qo'llash
JOIN bilan qo’llashga misol
Darsga tegishli SQL fayllar
⬇️ Darsda ishlatilgan .sql fayli: 📎 dars_10.sql (3.6 KB)
⬇️ Uyga vazifa .sql fayli: 📎 dars_10_uy_ishi.sql (1.3 KB)
