JOIN buyruqlari
JOIN — bir nechta table’dagi ma’lumotlarni birlashtirish uchun ishlatiladi. Chunki real projectlarda ma’lumotlar odatda bitta table’da saqlanmaydi.
Masalan:
users
orders
products alohida table’larda bo‘ladi.
JOIN yordamida ularni bog‘laymiz.
Misol uchun jadvallar
Customers
customer_id | name |
|---|---|
1 | Ali |
2 | Vali |
3 | Sardor |
Orders
order_id | customer_id | product |
|---|---|---|
101 | 1 | Laptop |
102 | 1 | Phone |
103 | 2 | Mouse |
104 | 5 | Keyboard |
INNER JOIN
INNER JOIN — ikkala jadvalda ham mavjud bo‘lgan ma’lumotlarni chiqaradi. JOIN joyida JOIN’ni o’zi bo’lsa avtomatik ravishda INNER JOIN ishlaydi.
SELECT * FROM customers as c
INNER JOIN orders as o
ON c.customer_id = o.customer_idname | product |
|---|---|
Ali | Laptop |
Ali | Phone |
Vali | Mouse |
⚠️ Sardor chiqmaydi.
Chunki unda order yo‘q.
⚠️ Keyboard ham chiqmaydi.
Chunki customer_id = 5 customers table’da yo‘q.
LEFT JOIN
LEFT JOIN — chap table’dagi barcha ma’lumotlarni chiqaradi. Mos kelmagan joylar NULL bo‘ladi.
SELECT * FROM customers as c
LEFT JOIN orders as o
ON c.customer_id = o.customer_idname | product |
|---|---|
Ali | Laptop |
Ali | Phone |
Vali | Mouse |
Sardor | NULL |
Muhim:
customers → chap table
chap table’dagi hamma data chiqadi
RIGHT JOIN
RIGHT JOIN — o‘ng jadvaldagi barcha ma’lumotlarni chiqaradi.
SELECT * FROM customers as c
RIGHT JOIN orders as o
ON c.customer_id = o.customer_idname | product |
|---|---|
Ali | Laptop |
Ali | Phone |
Vali | Mouse |
NULL | Keyboard |
Muhim:
orders → o‘ng table
orders’dagi barcha data chiqadi
FULL JOIN
FULL JOIN — ikkala jadvaldagi barcha ma’lumotlarni chiqaradi. Mos kelmagan joylar NULL bo‘ladi.
SELECT * FROM customers as c
FULL JOIN orders as o
ON c.customer_id= o.customer_idname | product |
|---|---|
Ali | Laptop |
Ali | Phone |
Vali | Mouse |
Sardor | NULL |
NULL | Keyboard |
JOIN’larni oddiy eslab qolish
JOIN turi | Nima chiqaradi |
|---|---|
INNER JOIN | faqat mos kelganlar |
LEFT JOIN | chapdagilar hammasi |
RIGHT JOIN | o‘ngdagilar hammasi |
FULL JOIN | ikkala tomondagi hammasi |
ANTI JOINS
ANTION JOINS | Vazifasi | Sintaksisi |
|---|---|---|
LEFT ANTI JOIN | Chap jadvalda bor, o‘ng jadvalda yo‘q ma’lumotlarni chiqaradi. | SELECT * FROM customers as c LEFT JOIN orders as o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL |
RIGHT ANTI JOIN | O’ng jadvalda bor, chap jadvalda yo‘q ma’lumotlarni chiqaradi. | SELECT * FROM customers as c RIGHT JOIN orders as o ON c.customer_id = o.customer_id WHERE c.customer_id IS NULL |
FULL ANTI JOIN | Ikkala jadvalda mos kelmaydigan ma’lumotlar chiqaradi | SELECT * FROM customers as c FULLJOIN orders as o ON c.customer_id = o.customer_id WHERE c.customer_id IS NULL OR o.customer_id IS NULL |
CROSS JOIN
CROSS JOIN — barcha kombinatsiyalarni chiqaradi.
SELECT * FROM colors
CROSS JOIN sizesColors | Sizes |
|---|---|
Red | S |
Blue | M |
color | size |
|---|---|
Red | S |
Red | M |
Blue | S |
Blue | M |
CROSS JOIN qachon ishlatiladi?
Masalan:
mahsulot variantlari
kombinatsiyalar
test data yaratish
Qaysi JOIN’ni qachon tanlash kerak?
Holat | Qaysi JOIN |
|---|---|
Faqat mos kelgan data kerak | INNER JOIN |
Chap table muhim bo‘lsa | LEFT JOIN |
O‘ng table muhim bo‘lsa | RIGHT JOIN |
Hammasi kerak bo‘lsa | FULL JOIN |
Mos kelmaganlarni topish | ANTI JOIN |
Kombinatsiya kerak bo‘lsa | CROSS JOIN |
Bir nechta jadvallarni birlashtirish
Bir nechta table ham JOIN qilsa bo‘ladi.
SELECT
c.name,
o.order_id,
p.product_name
FROM customers as c
JOIN orders as o
ON c.customer_id = o.customer_id
JOIN products as p
ON o.product_id = p.product_idDarsga tegishli SQL fayllar
⬇️ Darsda ishlatilgan .sql fayli: 📎 dars_6.sql (3.7 KB)
⬇️ Uyga vazifa .sql fayli: 📎 dars_6_uy_ishi.sql (3.5 KB)
