Birlashmalarni hosil qilish
Birlashmani yaratish juda oddiy protsedura. Birlashmaga qo'shilishi kerak bo'lgan barcha jadvallarni ko'rsatishingiz kerak, shuningdek, ular bir-biri bilan qanday bog'liq bo'lishi kerakligini MBBTga aytib berishingiz kerak. Quyidagi misolni ko'rib chiqing.
SELECT vend_name, prod_name, prod_price FROM Vendors, Products
WHERE Vendors.vend id = Products.vend id;
Natija:
vend name
|
prod_name
|
prod_price
|
Doll House Inc.
|
Fish bean bag toy
|
3.4900
|
Doll House Inc.
|
Bird bean bag toy
|
3.4900
|
Doll House Inc.
|
Rabbit bean bag toy
|
3.4900
|
Bears R Us
|
8 inch teddy bear
|
5.9900
|
Bears R Us
|
12 inch teddy bear
|
8.9900
|
Dekart ko’paytma. Natijalar birlashma shartini ko'rsatmasdan jadvallarni birlashtirganda qaytarildi. Qabul qilingan qatorlar soni birinchi jadvaldagi qatorlar sonining ikkinchi jadvaldagi qatorlar soniga ko'paytirilganiga teng bo'ladi.
Buni tushunish uchun quyidagi SELECT iborasini va uning natijasini ko'rib chiqing.
SELECT vend_name, prod_name, prod_price FROM Vendors, Products;
Natija
vend_name
|
prod_name
|
prod price
|
Bears R Us
|
8 inch teddy bear
|
5.99
|
Bears R Us
|
12 inch teddy bear
|
8.99
|
Bears R Us
|
.18 inch teddy bear
|
11.99
|
Bears R Us
|
Fish bean bag toy
|
3.49
|
Bears R Us
|
Bird bean bag toy
|
3.49
|
Bears R Us
|
Rabbit bean bag toy
|
3.49
|
Bears R Us
|
Raggedy Ann
|
4.99
|
Bears R Us
|
King doll
|
9.49
|
Bears R Us
|
Queen doll
|
9.49
|
Bear Emporium
|
8 inch teddy bear
|
5.99
|
Bear Emporium
|
12 inch teddy bear
|
8.99
|
Bear Emporium
|
18 inch teddy bear
|
11.99
|
Bear Emporium
|
Fish bean bag toy
|
3.49
|
Bear Emporium
|
Bird bean bag toy
|
3.49
|
Bear Emporium
|
Rabbit bean bag toy
|
3.49
|
Bear Emporium
|
Raggedy Ann
|
4.99
|
Bear Emporium
|
King doll
|
9.49
|
Bear Emporium
|
Queen doll
|
9.49
|
Doll House Inc.
|
8 inch teddy bear
|
5.99
|
Doll House Inc.
|
12 inch teddy bear
|
8.99
|
Doll House Inc.
|
18 inch teddy bear
|
11.99
|
Doll House Inc.
|
Fish bean bag toy
|
3.49
|
Doll House Inc.
|
, Bird bean bag toy
|
3.49
|
Doll House Inc.
|
Rabbit bean bag toy
|
3.49
|
Doll House Inc.
|
Raggedy Ann
|
4.99
|
Doll House Inc.
|
King doll
|
9.49
|
Doll House Inc.
|
Queen doll
|
9.49
|
Furball Inc.
|
8 inch teddy bear
|
5.99
|
Furball Inc.
|
12 inch teddy bear
|
8.99
|
Furball Inc.
|
18 inch teddy bear
|
11.99
|
Furball Inc.
|
Fish bean bag toy
|
3.49
|
Furball Inc.
|
Bird bean bag toy
|
3.49
|
Furball Inc.
|
Rabbit bean bag toy
|
3.49
|
Furball Inc.
|
Raggedy Ann
|
4.99
|
Furball Inc.
|
King doll ^
|
9.49
|
Furball Inc.
|
Queen doll
|
9.49
|
Fun and Games
|
8 inch teddy bear
|
5.99
|
Fun and Games
|
12 inch teddy bear
|
8.99
|
Fun and Games
|
18 inch teddy bear
|
11.99
|
Fun and Games
|
Fish bean bag toy
|
3.49
|
Fun and Games
|
Bird bean bag toy
|
3.49
|
Fun and Games
|
Rabbit bean bag toy
|
3.49
|
Fun and Games
|
Raggedy Ann
|
4.99
|
Fun and Games
|
King doll
|
9.49
|
Fun and Games
|
Queen doll
|
9.49
|
Jouets et ours
|
8 inch teddy bear
|
5.99
|
Jouets et ours
|
12 inch teddy bear
|
8.99
|
Jouets et ours
|
18 inch teddy bear
|
11.99
|
Jouets et ours
|
Fish bean bag toy
|
3.49
|
Jouets et ours
|
Bird bean bag toy
|
3.49
|
Jouets et ours
|
Rabbit bean bag toy
|
3.49
|
Jouets et ours
|
Raggedy Ann
|
4.99
|
Jouets et ours
|
King doll
|
9.49
|
Jouets et ours
|
Queen doll
|
9.49
|
Taqdim etilgan natijalardan ko'rinib turibdiki, siz dekart ko’paytmadan juda kamdan-kam foydalanasiz. Shu tarzda olingan ma'lumotlar har bir ma’lumot bilan bog'liq.
O'zaro bog'lanish. Ba'zan Dekart ko’paytmani qaytaradigan birikma o'zaro bog'lanish deb ataladi.
Ichki birlashma. Biz hozirgacha ishlatib kelayotgan birlashma ikki jadval yozuvlarining teng yoki tengligini tekshirishga asoslangan equi-join deb ataladi. Ushbu turdagi birlashma ichki birlashma deb ham ataladi. Bunday birlashmalar uchun siz qo'shilish turini aniq ko'rsatish uchun biroz boshqacha sintaksisdan foydalanishingiz mumkin. Quyidagi SELECT iborasi oldingi misol bilan bir xil ma'lumotlarni qaytaradi.
SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;
Bu yerda SELECT bandi oldingi holatda bo'lgani kabi bir xil, ammo FROM bandi boshqacha. Ushbu so'rovda ikkita jadval o'rtasidagi munosabat INNER JOIN spetsifikatsiyasini o'z ichiga olgan FROM bandida aniqlanadi. Ushbu sintaksis bilan birlashish sharti WHERE bandi emas, balki maxsus ON bandi bilan belgilanadi. ON bandida ko'rsatilgan haqiqiy holat WHERE bandida ko'rsatilgan holat bilan bir xil.
Bir nechta jadvallarni birlashtirish
SQL SELECT iborasi bilan birlashtirilishi mumkin bo'lgan jadvallar sonini cheklamaydi. Uyushmani yaratishning asosiy qoidalari bir xil bo'lib qolmoqda. Birinchidan, barcha jadvallar ro'yxatga olinadi, so'ngra ular orasidagi munosabatlar aniqlanadi. Bir misolni ko'rib chiqing.
SELECT prod_name, vend_name, prod_price, quantity FROM Orderltems, Products, Vendors WHERE Products.vend_id = Vendors.vend_id AND Orderltems.prod_id = Products.prod_id AND order num = 20007;
Birlashmadagi jadvallarning maksimal soni. SQL qo'shilishdagi jadvallar soniga hech qanday cheklovlar qo'ymasa ham, ko'pgina ma'lumotlar bazasi tizimlarida bunday cheklovlar mavjud. U qanday cheklovlar qo'yishini (agar mavjud bo'lsa) bilish uchun ma'lumotlar bazasi tizimi hujjatlariga murojaat qiling.
SELECT cust_name, cust_contact FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM Orderltems WHERE prod_id =
’RGANOIV) );
Yuqorida aytib o'tilganidek, pastki so'rovlar har doim ham murakkab SELECT iboralarini bajarishning eng samarali usuli emas, shuning uchun bir xil so'rovni birlashma sintaksisi yordamida qayta yozish mumkin.
SELECT cust_name, cust_contact FROM Customers, Orders, Orderlterns WHERE Customers.cust_id = Orders.cust_id AND Orderlterns.order_num = Orders.order_num AND prod_id = ’RGAN01';
Xulosa. Ko'rib turganingizdek, ko'pincha bir xil SQL so'rovni bajarishning bir necha usullari mavjud va kamdan-kam hollarda qaysi biri to'g'ri ekanligini aniq aytish mumkin. Samaradorlik MBBT tomonidan qo'llaniladigan operatsiya turiga, jadvallardagi ma'lumotlar miqdoriga, indekslar va kalitlarning mavjudligi yoki yo'qligiga va boshqa turli mezonlarga bog'liq bo'lishi mumkin. Shuning uchun, qaysi biri tezroq ishlashini aniqlash uchun har xil so'rov turlari bilan tajriba o'tkazishga arziydi.
Kengaytirilgan birlashmalar yaratish
Boshqa turdagi birlashmalar. Biz hozirgacha faqat oddiy birikmalardan foydalandik, ular ichki birikmalar deb ataladi. Endi birlashmaning yana uchta turini ko'rib chiqing: o'z-o'zidan qo'shilish, tabiiy birlashma va tashqi birlashma.
|
