Subqueries karo tabel

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:

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:

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