Dati · SQL
Perché un WHERE annulla una LEFT JOIN?
Quattro ordini da 40, 60, 20 e 50 €: quanti passano amount > 30?
E se l'importo da 20 € diventa NULL?
Da un filtro sugli importi alle due versioni della stessa query, in cinque tappe:
Quando SQL applica il WHERE di una JOIN?
WHERE arriva dopo la JOIN, su ogni riga del suo risultato. Un ordine da 12 € esce dalla JOIN, poi WHERE amount > 30 scarta la sua riga.
Prendiamo tre clienti e i loro ordini.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 30
| id | name |
|---|---|
| 1 | Giulia |
| 2 | Marco |
| 3 | Sara |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 40 € |
| 102 | 1 | 70 € |
| 103 | 2 | 25 € |
| 104 | 2 | 60 € |
| 105 | 3 | 50 € |
| name | amount |
|---|---|
| Giulia | 40 € |
| Giulia | 70 € |
| Marco | 25 € |
| Marco | 60 € |
| Sara | 50 € |
| name | amount |
|---|---|
| Giulia | 40 € |
| Giulia | 70 € |
| Marco | 60 € |
| Sara | 50 € |
Quali righe passano
amount > 30? Si toccano nella tabella della LEFT JOIN.Si legge l'importo di ogni riga e lo si confronta con 30.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Giulia 2 Marco 3 Sara orders id customer_id amount 101 1 35 € 102 1 65 € 103 2 20 € 104 2 45 € 105 3 70 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 30
customers id name 1 Giulia 2 Marco 3 Sara 4 Luca orders id customer_id amount 106 4 15 € 101 1 25 € 103 2 35 € 105 4 40 € 102 2 50 € 104 3 75 €
Perché un WHERE fa sparire la riga NULL di una LEFT JOIN?
Il cliente con id 7 non ha nessun ordine: nella sua riga l'importo è NULL. NULL > 0 non è vero, quindi WHERE amount > 0 scarta la sua riga.
Prendiamo tre clienti, di cui uno senza ordini.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 30
| id | name |
|---|---|
| 1 | Giulia |
| 2 | Marco |
| 3 | Sara |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 40 € |
| 102 | 3 | 60 € |
| name | amount |
|---|---|
| Giulia | 40 € |
| Marco | NULL |
| Sara | 60 € |
| name | amount |
|---|---|
| Giulia | 40 € |
| Sara | 60 € |
Quali righe passano
amount > 30?Si confronta ogni importo con 30. Nella riga di Marco, si confronta NULL con 30.
Senza
WHERE, quale JOIN dà queste stesse righe?
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Giulia 2 Marco 3 Sara 4 Luca orders id customer_id amount 101 1 45 € 102 3 20 € 103 3 65 € 104 4 35 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Giulia 2 Marco 3 Sara 4 Luca 5 Anna orders id customer_id amount 104 4 15 € 106 5 25 € 102 2 30 € 101 1 50 € 105 5 60 € 103 2 70 €
Come filtrare una LEFT JOIN senza perdere clienti?
Scritta in ON, la condizione sceglie quali ordini collegare, poi LEFT JOIN tiene ogni cliente. Il cliente con id 7 ha un solo ordine, da 12 €: AND amount > 30 lo scarta, e il cliente compare lo stesso, con un importo NULL.
Prendiamo tre clienti, con la condizione in ON.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 30
| id | name |
|---|---|
| 1 | Giulia |
| 2 | Marco |
| 3 | Sara |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 40 € |
| 102 | 1 | 60 € |
| 103 | 1 | 25 € |
| 104 | 3 | 20 € |
| name | amount |
|---|---|
| Giulia | 40 € |
| Giulia | 60 € |
| Marco | NULL |
| Sara | NULL |
Che cosa contiene la colonna
amountnella riga di Sara?Prima si guarda quali ordini di Sara passano
amount > 30.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 40
customers id name 1 Giulia 2 Marco 3 Sara 4 Luca orders id customer_id amount 101 1 50 € 102 1 65 € 103 2 15 € 104 2 45 € 105 3 20 € 106 4 35 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 50
customers id name 1 Giulia 2 Marco 3 Sara 4 Luca 5 Anna orders id customer_id amount 104 3 15 € 101 1 25 € 106 5 30 € 105 4 45 € 102 3 55 € 103 3 70 €
Che differenza c'è tra una condizione in ON e in WHERE?
Con 7 clienti, la condizione scritta in ON dà almeno 7 righe, almeno una per cliente. Scritta in WHERE, tiene solo le righe in cui è vera, e può non lasciarne nessuna.
Prendiamo quattro clienti e le due versioni della query.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 30
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 30
| id | name |
|---|---|
| 1 | Giulia |
| 2 | Marco |
| 3 | Sara |
| 4 | Luca |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 70 € |
| 102 | 1 | 45 € |
| 103 | 1 | 20 € |
| 104 | 2 | 35 € |
| 105 | 4 | 15 € |
| name | amount |
|---|---|
| Giulia | 70 € |
| Giulia | 45 € |
| Marco | 35 € |
| name | amount |
|---|---|
| Giulia | 70 € |
| Giulia | 45 € |
| Marco | 35 € |
| Sara | NULL |
| Luca | NULL |
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 30
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 30
| id | name |
|---|---|
| 1 | Giulia |
| 2 | Marco |
| 3 | Sara |
| 4 | Luca |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 25 € |
| 102 | 1 | 55 € |
| 103 | 3 | 40 € |
| 104 | 3 | 65 € |
| 105 | 3 | 50 € |
| 106 | 4 | 20 € |
Come verificare velocemente una LEFT JOIN filtrata?
Prima si guarda dove è scritta la condizione. In WHERE, si contano gli ordini che la passano. In ON, ogni cliente compare una volta per ogni ordine che la passa, e una volta se non ne ha nessuno.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Giulia 2 Marco 3 Sara orders id customer_id amount 101 1 35 € 102 3 60 € 103 3 15 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 40
customers id name 1 Giulia 2 Marco 3 Sara 4 Luca orders id customer_id amount 101 1 45 € 102 1 20 € 103 3 30 € 104 4 55 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Giulia 2 Marco 3 Sara 4 Luca orders id customer_id amount 101 1 20 € 105 4 30 € 103 2 35 € 104 4 65 € 102 1 75 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 40
customers id name 1 Giulia 2 Marco 3 Sara 4 Luca 5 Anna orders id customer_id amount 104 3 15 € 103 3 25 € 107 5 35 € 105 4 45 € 102 1 50 € 101 1 65 € 106 5 80 €