Ichki so'rov jadval qaytaradi
Va nihoyat uchinchi variant – ichki so'rov butun jadval qaytaradi. Bu eng keng tarqalgan variant.
Ko'pincha biz ma'lum bir jadvalni ozgina tahrirlashni xohlaymiz. Va faqat keyin (JOIN ON operatori orqali) to'g'irlangan jadvalni boshqasiga birlashtiramiz.
Keling, boshida biz ikkita jadvalni JOIN orqali birlashtirgan eng oddiy holatni olamiz:
SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id
Va esingda bo'lsa, task jadvalida hech kimga tayinlanmagan vazifalar bor: employee_id NULL ga teng.
Keling, to'g'irlangan jadvalni yarataylik, bunda barcha osilgan vazifalarni direktor (uning ID = 4) ga tayinlaymiz.
Buning uchun IFNULL() funksiyasidan foydalanamiz:
SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task
Va bunday so'rovning natijasi:
| id | employee_id | name | deadline |
|---|---|---|---|
| 1 | 1 | Front-enddagi xatoni to'g'irlash | 2022-06-01 |
| 2 | 2 | Back-enddagi xatoni to'g'irlash | 2022-06-15 |
| 3 | 5 | Kofe sotib olish | 2022-07-01 |
| 4 | 5 | Kofe sotib olish | 2022-08-01 |
| 5 | 5 | Kofe sotib olish | 2022-09-01 |
| 6 | 4 | Ofisni tozalash | (NULL) |
| 7 | 4 | Hayotdan zavqlanish | (NULL) |
| 8 | 6 | Hayotdan zavqlanish | (NULL) |
Qirmizi rang bilan to'g'irlangan katak belgilangan.
Endi to'g'irlangan jadvalimizni so'rovga qo'shamiz:
SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id
task jadvali o'rniga.
Bunday so'rov quyidagi ko'rinishda bo'ladi:
SELECT * FROM employee e JOIN ( SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task ) t ON e.id = t.emploee_id
Biz task so'zi o'rniga, qavslar ichiga so'rovning tanasini joylashtirdik.
Aytmoqchi, ichki so'rov uchun t alias juda foydali keldi. Ichki so'rov jadvaldan farqli o'laroq, o'z nomiga ega emas, shu sababli alias ayni muddao edi.
Mana shunday so'rovning natijasi:
| id | name | occupation | salary | age | join_date | id | employee_id | name |
|---|---|---|---|---|---|---|---|---|
| 1 | Ivanov Ivan | Dasturchi | 100000 | 25 | 2012-06-30 | 1 | 1 | Front-enddagi xatoni to'g'irlash |
| 2 | Petrov Pyotr | Dasturchi | 80000 | 23 | 2013-08-12 | 2 | 2 | Back-enddagi xatoni to'g'irlash |
| 4 | Rabinovich Moysha | Direktor | 200000 | 35 | 2015-05-12 | 6 | 4 | Ofisni tozalash |
| 4 | Rabinovich Moysha | Direktor | 200000 | 35 | 2015-05-12 | 7 | 4 | Hayotdan zavqlanish |
| 5 | Kirienko Anastasiya | Ofis-menajer | 40000 | 25 | 2015-10-10 | 4 | 5 | Kofe sotib olish |
| 5 | Kirienko Anastasiya | Ofis-menajer | 40000 | 25 | 2015-10-10 | 5 | 5 | Kofe sotib olish |
| 5 | Kirienko Anastasiya | Ofis-menajer | 40000 | 25 | 2015-10-10 | 3 | 5 | Kofe sotib olish |
| 6 | Vasya | mushuk | 1000 | 3 | 2018-11-11 | 8 | 6 | Hayotdan zavqlanish |
Bizning direktorda "ofisni tozalash" vazifasi mavjud, o'ylaymanki, u tezda kimga uni topshirishni topadi :)WITH operatoridan foydalanish
Aytmoqchi, 8-versiyadan boshlab MySQL-da barcha ichki so'rovlaringizni final so'rov ichiga to'g'ridan-to'g'ri qo'yishingiz shart emas. Ularni alohida bajarish mumkin. Buning uchun WITH operatori ishlatiladi.
U virtual jadval (nomlangan so'rov) yaratishga ruxsat beradi va uning ko'rinishi quyidagi shablon bilan belgilanadi:
WITH nom AS (so'rov)
Ko'pincha sizning ichki so'rovingizda nomlanmagan ustunlar mavjud bo'lganda, masalan COUNT(*) singari, ularga unikal ism bermagansiz. Bunday holatda WITH operatori ichki so'rov ustunlari uchun yangi nomlarni belgilash imkoniyatiga ega.
Ikkinchi ko'rinishi quyidagi shablon bilan belgilanadi:
WITH nom(ustun1, ustun2, …) AS (so'rov)
Sen xohlaganingcha ko'p virtual jadvallar (nomlangan so'rovlar) yaratib, ularga ishora qilishing mumkin. Umuman olganda, sening so'rovingning umumiy ko'rinishi taxminan quyidagi formatga ega bo'ladi:
WITH nom1 AS (so'rov1), nom2 AS (so'rov2), nom3 AS (so'rov3) SELECT * FROM nom1 JOIN nom2 ON …
Endi bizning qo'rqinchli so'rovimizni olaylik:
SELECT * FROM employee e JOIN ( SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task ) t ON e.id = t.emploee_id
Va WITH operatoridan foydalanib, uni qayta yozamiz:
WITH task2(id, employee_id, name, deadline) AS (SELECT id, IFNULL(employee_id, 4), name, deadline FROM task) SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id
Yoki ustunlar nomlarisiz mumkin, lekin bu holda IFNULL() funksiyasi uchun alias belgilash shart:
WITH task2 AS ( SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task ) SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id
GO TO FULL VERSION