La sottoquery restituisce una tabella
Infine, la terza opzione è quando la sottoquery restituisce l'intera tabella. Questa è l'opzione più comune.
Molto spesso ci sono situazioni in cui vogliamo modificare un po' un determinato tavolo. E solo allora unisci (usando l'operatore JOIN ON) la tabella corretta con un'altra.
Iniziamo con il caso più semplice, in cui abbiamo unito due tabelle con un JOIN:
SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id
E come probabilmente ricorderai, ci sono attività nella tabella delle attività che non sono assegnate a nessuno: employee_id is NULL .
Generiamo una tabella corretta , dove assegniamo tutti i compiti sospesi al direttore (il suo ID = 4).
Per fare questo, usiamo la funzione IFNULL() :
SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task
E il risultato di questa query:
id | ID Dipendente | nome | scadenza |
---|---|---|---|
1 | 1 | Risolto un bug sul frontend | 2022-06-01 |
2 | 2 | Risolto un bug sul backend | 2022-06-15 |
3 | 5 | Compra il caffè | 2022-07-01 |
4 | 5 | Compra il caffè | 2022-08-01 |
5 | 5 | Compra il caffè | 2022-09-01 |
6 | 4 | Pulisci l'ufficio | (NULLO) |
7 | 4 | Goditi la vita | (NULLO) |
8 | 6 | Goditi la vita | (NULLO) |
La cella corretta è contrassegnata in rosso.
Ora sostituiamo la nostra tabella corretta nella query:
SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id
Invece della tabella delle attività .
Tale richiesta sarebbe simile a questa:
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
Invece della parola task , abbiamo scritto delle parentesi e vi abbiamo inserito il corpo della richiesta.
A proposito, l'alias t (alias) per la query nidificata è stato molto utile. Una query nidificata, a differenza di una tabella, non ha un proprio nome, quindi l'alias è decisamente fuori luogo.
Ed ecco il risultato di tale query:
id | nome | occupazione | stipendio | età | data di iscrizione | id | ID Dipendente | nome |
---|---|---|---|---|---|---|---|---|
1 | Ivanov Ivan | Programmatore | 100000 | 25 | 2012-06-30 | 1 | 1 | Risolto un bug sul frontend |
2 | Petrov Petr | Programmatore | 80000 | 23 | 2013-08-12 | 2 | 2 | Risolto un bug sul backend |
4 | Rabinovich Moisha | Direttore | 200000 | 35 | 2015-05-12 | 6 | 4 | Pulisci l'ufficio |
4 | Rabinovich Moisha | Direttore | 200000 | 35 | 2015-05-12 | 7 | 4 | Goditi la vita |
5 | Kirienko Anastasia | Capo ufficio | 40000 | 25 | 2015-10-10 | 4 | 5 | Compra il caffè |
5 | Kirienko Anastasia | Capo ufficio | 40000 | 25 | 2015-10-10 | 5 | 5 | Compra il caffè |
5 | Kirienko Anastasia | Capo ufficio | 40000 | 25 | 2015-10-10 | 3 | 5 | Compra il caffè |
6 | Vasca | gatto | 1000 | 3 | 2018-11-11 | 8 | 6 | Goditi la vita |
Il nostro direttore ha il compito di “ripulire l'ufficio”, penso che troverà presto qualcuno a cui delegarlo :) Usando l'istruzione WITH
A proposito, a partire dalla versione 8 di MySQL, non è più necessario inserire tutte le sottoquery all'interno della query finale. Possono essere eseguiti separatamente. Per questo, viene utilizzata l'istruzione WITH .
Permette di creare una tabella virtuale (chiamata query) e il suo aspetto è dato da un template:
WITH Name AS (request)
Ci sono spesso momenti in cui la tua sottoquery ha colonne senza nome, come COUNT(*), a cui non hai assegnato un nome univoco. In questo caso, l'istruzione WITH ha la possibilità di specificare nuovi nomi di colonna per la sottoquery.
La sua seconda forma è data dal template:
WITH Name(column1, column2, …) AS (request)
È possibile utilizzare tutte le tabelle virtuali (query denominate) che si desidera e fare riferimento l'una all'altra in esse. La forma generale della tua richiesta avrà qualcosa del genere:
WITH name1 AS (request1),
name2 AS (request2),
name3 AS (request3)
SELECT * FROM name1 JOIN name2 ON …
Ora prendiamo la nostra domanda spaventosa:
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
E riscrivilo usando l'istruzione 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
Oppure puoi fare a meno dei nomi delle colonne, ma poi dovrai specificare un alias per la funzione 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