Sqlda join (qo’shilish)amali


Download 302.24 Kb.
Sana19.05.2022
Hajmi302.24 Kb.
#679573
Bog'liq
Mavzu 7. SQL joins
milliy iqtisodiyot, 6-12 boblar mehmonxona, sadasdas, 3000 та БУХГАЛТЕРИЯ ЎТКАЗМАСИ, 40-Mavzu BI, 72-Mavzu BI, 72-Mavzu BI, 78-Mavzu BI, 69-Mavzu BI, 69-Mavzu BI, 6-Mavzu BI, 6-Mavzu BI, 13-Mavzu BI, 11-Mavzu BI, 141948833783-1617298545-ticket

SQLda JOIN (birlashtirish) amali

JOIN bu ikki yoki undan ortiq jadvallardagi qator ma’lumotlarini bog’liq bo’lgan ustunlarga ko’ra birlashtirish.

  • JOIN bu ikki yoki undan ortiq jadvallardagi qator ma’lumotlarini bog’liq bo’lgan ustunlarga ko’ra birlashtirish.

Buyurtma_ID

Mijoz_ID

Buyurtma_Sana

10308

2

2020-09-18

10309

37

2020-09-19

10310

77

2020-09-20

Buyurtma

Mijoz_ID

Mijoz_Ismi

Mijoz_Telefon

Davlat

1

Akbar

+998976589451

O’zbekiston

2

Aziz

+998945786958

Germaniya

3

Hayot

+998997458685

Qozog’iston

Mijozlar

Buyurtma_ID

Mijoz_Ismi

Buyurtma_Sana

10308

Akbar

2020-09-18

10309

Aziz

2020-09-19

10310

Sherzod

2020-09-20

SELECT Buyurtma.Buyurtma_ID, Mijozlar.Mijozlar_Ismi, Buyurtma.Buyurtma_Sana FROM Buyurtma INNER JOIN Mijozlar ON Buyurtma.Mijoz_ID=Mijozlar.Mijoz_ID;

Buyurtma_ID

Mijoz_ID

Buyurtma_Sana

10308

2

2020-09-18

10309

37

2020-09-19

10310

77

2020-09-20

Buyurtma

Mijoz_ID

Mijoz_Ismi

Mijoz_Telefon

Davlat

1

Akbar

+998976589451

O’zbekiston

2

Aziz

+998945786958

Germaniya

3

Hayot

+998997458685

Qozog’iston

Mijozlar

Buyurtma_ID

Mijoz_Ismi

Buyurtma_Sana

10308

Akbar

2020-09-18

10309

Aziz

2020-09-19

10310

Sherzod

2020-09-20

Birlashmalarning turlari


Ichki birlashish
Chapga birlashtirish
To’liq tashqi birlashish
O’ngga birlashish

Inner Join – ichki birlashish


INNER JOIN kalit so'zi ikkala jadvaldan ustunlarni birlashtiradi
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Inner Join – ichki birlashish


Orders
Customers
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Inner Join – ichki birlashish

Left Join-Chapga birlashtirish

  • Chapga birlashtirishda birinchi jadvalning barcha ustun ma’lumotlari va ikkinchi jadvaldan tanlangan ustun ma’lumotlari birlashtirib chiqariladi.

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Left Join-Chapga birlashtirish


Customers
Orders
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;

Left Join-Chapga birlashtirish

Right Join-O’ngga birlashtirish

  • RIGHT JOIN kalit so'zi o'ng jadvaldagi barcha yozuvlarni qaytaradi (2 -jadval) va chap jadvaldagi mos yozuvlarni (1 -jadval). Natija, agar mos kelmasa, chap tomondan null beriladi.

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Right Join-O’ngga birlashtirish


Orders
Employees
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;

Right Join-O’ngga birlashtirish

Full join – to’liq birlashtirish

  • FULL OUTER JOIN kalit so'zi barcha yozuvlarni chap (jadval1) yoki o'ng (jadval2) jadval yozuvlarida mos kelganda qaytaradi.

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;

Full join – to’liq birlashtirish


SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;
Download 302.24 Kb.

Do'stlaringiz bilan baham:




Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©fayllar.org 2022
ma'muriyatiga murojaat qiling