Subquery ngasilake tabel
Lan pungkasane, pilihan katelu yaiku nalika subquery ngasilake kabeh tabel. Iki minangka pilihan sing paling umum.
Kerep banget ana kahanan nalika kita arep kanggo ngapiki Tabel tartamtu sethitik. Lan mung banjur gabung (nggunakake operator JOIN ON) tabel didandani karo liyane.
Ayo diwiwiti kanthi kasus sing paling gampang, ing ngendi kita gabung karo rong tabel kanthi JOIN:
SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id
Lan sing mbokmenawa sampeyan elinga, ana tugas ing meja tugas sing ora ditugasake kanggo sapa wae: employee_id is NULL .
Ayo generate Tabel didandani , ngendi kita nemtokake kabeh tugas hanging kanggo direktur (ID = 4).
Kanggo nindakake iki, kita nggunakake fungsi IFNULL() :
SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task
Lan asil saka pitakonan iki:
id | id_pegawe | jeneng | deadline |
---|---|---|---|
1 | 1 | Ndandani bug ing frontend | 2022-06-01 |
2 | 2 | Ndandani bug ing backend | 2022-06-15 |
3 | 5 | Tuku kopi | 2022-07-01 |
4 | 5 | Tuku kopi | 2022-08-01 |
5 | 5 | Tuku kopi | 2022-09-01 |
6 | 4 | Ngresiki kantor | (NULL) |
7 | 4 | Seneng urip | (NULL) |
8 | 6 | Seneng urip | (NULL) |
Sèl sing wis didandani ditandhani nganggo werna abang.
Saiki ayo ngganti tabel sing wis didandani menyang pitakon:
SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id
Tinimbang meja tugas .
Panjaluk kasebut bakal katon kaya iki:
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
Tinimbang tembung tugas , kita nulis kurung lan nyelehake badan panyuwunan kasebut.
Miturut cara, alias t (alias) kanggo query nested banget migunani. Pitakonan bersarang, ora kaya tabel, ora duwe jeneng dhewe, mula alias ora ana ing papan.
Lan iki minangka asil saka pitakon kasebut:
id | jeneng | pendhudhukan | gaji | umur | join_date | id | id_pegawe | jeneng |
---|---|---|---|---|---|---|---|---|
1 | Iwan Iwan | Programmer | 100000 | 25 | 30-06-2012 | 1 | 1 | Ndandani bug ing frontend |
2 | Petrov Petruk | Programmer | 80000 | 23 | 2013-08-12 | 2 | 2 | Ndandani bug ing backend |
4 | Rabinovich Moisha | direktur | 200000 | 35 | 2015-05-12 | 6 | 4 | Ngresiki kantor |
4 | Rabinovich Moisha | direktur | 200000 | 35 | 2015-05-12 | 7 | 4 | Seneng urip |
5 | Kirienko Anastasia | Kantor manager | 40000 | 25 | 10-10-2015 | 4 | 5 | Tuku kopi |
5 | Kirienko Anastasia | Kantor manager | 40000 | 25 | 10-10-2015 | 5 | 5 | Tuku kopi |
5 | Kirienko Anastasia | Kantor manager | 40000 | 25 | 10-10-2015 | 3 | 5 | Tuku kopi |
6 | Vaska | kucing | 1000 | 3 | 2018-11-11 | 8 | 6 | Seneng urip |
Direktur kita duwe tugas "ngresiki kantor", aku rumangsa bakal cepet golek wong sing bakal didelegasikan :) Nggunakake operator WITH
Miturut cara, miwiti karo versi 8 saka MySQL, sampeyan ora maneh kudu sijine kabeh subqueries tengen ing pitakonan final. Padha bisa dileksanakake kanthi kapisah. Kanggo iki, statement WITH digunakake .
Ngidini sampeyan nggawe tabel virtual (jenenge query) lan tampilane diwenehi cithakan:
WITH Name AS (request)
Kadhangkala subquery sampeyan duwe kolom sing ora dijenengi, kayata COUNT(*), sing sampeyan durung menehi jeneng unik. Ing kasus iki, statement WITH nduweni pilihan kanggo nemtokake jeneng kolom anyar kanggo subquery.
Wangun kapindho diwenehi dening cithakan:
WITH Name(column1, column2, …) AS (request)
Sampeyan bisa nggunakake minangka akeh tabel virtual (jeneng pitakon) sing pengin lan deleng saben liyane ing. Bentuk umum panyuwunan sampeyan bakal kaya mangkene:
WITH name1 AS (request1),
name2 AS (request2),
name3 AS (request3)
SELECT * FROM name1 JOIN name2 ON …
Saiki ayo goleki pitakon sing medeni:
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
Lan tulis maneh nggunakake statement WITH:
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
Utawa sampeyan bisa nindakake tanpa jeneng kolom, nanging sampeyan kudu nemtokake alias kanggo fungsi IFNULL ():
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