IN operator

SQL IN Operator

IN operator sizga WHERE qatorida bir nechta qiymatlarni belgilash imkonini beradi.

IN operatori bir nechta OR shartlari uchun qisqacha ifodadir.

'Germany', 'France' yoki 'UK' dan barcha mijozlarni qaytarish:

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Quyida misollarda ishlatilgan Customers jadvidan tanlov keltirilgan:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germaniya
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución	Meksika D.F.	05021	Meksika
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	Meksika D.F.	05023	Meksika
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	Birlashgan Qirollik
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Shvetsiya

IN operatoridan oldin NOT kalit so'zini ishlatish orqali, siz ro'yxatdagi qiymatlardan hech biri bo'lmagan barcha yozuvlarni qaytarasiz.

Misol: 'Germany', 'France' yoki 'UK' dan bo'lmagan barcha mijozlarni qaytarish:

SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

Siz IN operatorini WHERE qatorida subquery bilan ham ishlatishingiz mumkin.

Subquery bilan siz asosiy so'rov natijalarida mavjud bo'lgan barcha yozuvlarni qaytarishingiz mumkin.

Misol: Orders jadvalida buyurtma bergan barcha mijozlarni qaytarish:

SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

Yuqoridagi misolda natija 74 yozuvni qaytardi, bu esa 17 ta mijozning buyurtma bermaganligini anglatadi.

Bu to'g'ri ekanligini tekshirish uchun NOT IN operatoridan foydalanamiz.

Misol: Orders jadvalida hech qanday buyurtma bermagan barcha mijozlarni qaytarish:

SELECT * FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);