SELECT * FROM Orders WHERE SNum = ( SELECT SNum FROM Salepeople WHERE SName = 'Motika'); Avval ichki so‘rov bajariladi, so‘ngra uning natijasi tashqi so‘rovni xosil qiligsh uchun ishlatiladi (SNum ostki so‘rov natijasi bilan solishtiriladi).
Ostki so‘rov bitta ustun tanlashi lozim, bu ustun qiymatlari tipi predikatda solishtiriladigan qiymat tipi bilan bir xil bo‘lishi kerak. Siz ba’zi xollarda ostki so‘rov bitta qiymat xosil qilishi uchun DISTINCT operatoridan foydalanish mumkin.
Misol: Hoffman (CNum=21) ga xizmat ko‘rsatuvchi sotuvchilar xamma buyurtmalarini topish lozim bo‘lsin.
SELECT * FROM Orders WHERE SNum = ( SELECT DISTINCT SNum FROM Orders WHERE CNum = 21 ); Bu xolda ostki so‘rov faqat bitta 11 qiymat chiqaradi, lekin umumiy xolda bir necha qiymatlar bo‘lishi mumkin va ular ichidan DISTINCT faqat bittasini tanlaydi.
Ixtiyoriy sondagi satrlar uchun avtomatik ravishda bitta qiymat xosil qiluvchi funksiya turi - agregat funksiya bo‘lib, undan ostki so‘rovda foydalanish mumkin.
34.Union kalit so’zi UNION
SELECT a.snum, sname, onum, ’Lowest on’, odate FROM Salespeople a,
Orders b WHERE a.snum b.snum
AND b.amt = (SEi FCT MIN (amt)FROM Orders с
WHERE c.odate ; b.odate);
10
Peel
30
High o
10/05/
10
Peel
30
Low o
10/05/
10
Peel
30
High o
10/06/
10
Serre
30
High o
10/03/
10
Serre
30
Low o
10/04/
10
Serre
30
Low o
10/06/
10
Axel
30
High o
10/04/
10
Rif
30
Low o
10/03/
Shu paytgacha UNION kalit so’zi yordamida birlashtirilgan so'rov
natijalari qanday tartibda matnga chiqarilishi to'g’risida gapirmadik.
Birlashtirilgan natijalarni ORDER BY kalit so'zi yordamida tartiblashtirish
mumkin. Yoqoridagi misolni tartub raqamlariga nisbatan tartiblashni ko'rib
o'tamiz.
SELECT a.snum, sname, onum, ’Highest on’, odate FROM Salespeople a.
Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt)
FROM Orders с WHERE c.odate = b.odate)
UNION
SELECT a.snum, sname, onum. ’Lowest on’, odate F ROM Salespeople a.
Orders b WHERE a.snum = b.snu AND b.amt = (SELECT MIN (amt)
FROM Orders с WHERE c.odate = b.odate)
