Dati · SQL

Perché un WHERE annulla una LEFT JOIN?

Pubblicato il

Quattro ordini da 40, 60, 20 e 50 €: quanti passano amount > 30?

Da un filtro sugli importi alle due versioni della stessa query, in cinque tappe:

  1. Filtrare le righe di una JOIN con WHERE
  2. Seguire la riga NULL fino a WHERE
  3. Tenere ogni cliente con una condizione in ON
  4. Contare le righe delle due versioni
  5. Esercitarsi

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
customers
idname
1Giulia
2Marco
3Sara
orders
idcustomer_idamount
101140 €
102170 €
103225 €
104260 €
105350 €
LEFT JOIN, prima di WHERE
nameamount
Giulia40 €
Giulia70 €
Marco25 €
Marco60 €
Sara50 €
risultato
nameamount
Giulia40 €
Giulia70 €
Marco60 €
Sara50 €
  1. Ogni cliente compare una volta per ogni ordine, come in qualsiasi JOIN.

    righe

  2. 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.

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Giulia
    2Marco
    3Sara
    orders
    idcustomer_idamount
    101135 €
    102165 €
    103220 €
    104245 €
    105370 €

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
customers
idname
1Giulia
2Marco
3Sara
orders
idcustomer_idamount
101140 €
102360 €
LEFT JOIN, prima di WHERE
nameamount
Giulia40 €
MarcoNULL
Sara60 €
risultato
nameamount
Giulia40 €
Sara60 €
  1. LEFT JOIN tiene ogni cliente, anche senza ordini.

    righe

  2. Quali righe passano amount > 30?

    Si confronta ogni importo con 30. Nella riga di Marco, si confronta NULL con 30.

  3. Senza WHERE, quale JOIN dà queste stesse righe?

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Giulia
    2Marco
    3Sara
    4Luca
    orders
    idcustomer_idamount
    101145 €
    102320 €
    103365 €
    104435 €

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
customers
idname
1Giulia
2Marco
3Sara
orders
idcustomer_idamount
101140 €
102160 €
103125 €
104320 €
risultato
nameamount
Giulia40 €
Giulia60 €
MarcoNULL
SaraNULL
  1. Che cosa contiene la colonna amount nella riga di Sara?

    Prima si guarda quali ordini di Sara passano amount > 30.

  2. Ogni cliente compare una volta per ogni ordine collegato, e una volta se non ne ha nessuno.

    righe

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
      AND orders.amount > 40
    customers
    idname
    1Giulia
    2Marco
    3Sara
    4Luca
    orders
    idcustomer_idamount
    101150 €
    102165 €
    103215 €
    104245 €
    105320 €
    106435 €

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
customers
idname
1Giulia
2Marco
3Sara
4Luca
orders
idcustomer_idamount
101170 €
102145 €
103120 €
104235 €
105415 €
risultato con WHERE
nameamount
Giulia70 €
Giulia45 €
Marco35 €
risultato con ON
nameamount
Giulia70 €
Giulia45 €
Marco35 €
SaraNULL
LucaNULL
  1. WHERE rilegge ogni riga della LEFT JOIN. Nella riga di Sara l'importo è NULL.

    righe

  2. Ogni cliente compare una volta per ogni ordine che passa amount > 30, e una volta se non ne ha nessuno.

    righe

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
customers
idname
1Giulia
2Marco
3Sara
4Luca
orders
idcustomer_idamount
101125 €
102155 €
103340 €
104365 €
105350 €
106420 €

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.

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Giulia
    2Marco
    3Sara
    orders
    idcustomer_idamount
    101135 €
    102360 €
    103315 €