Qual è la differenza tra WHERE e ON nelle JOIN SQL?

Quando si uniscono tabelle in SQL, si possono inserire condizioni in una clausola ON e in una clausola WHERE. Molti si confondono sulla differenza tra le due. In questo articolo tratteremo l'argomento ricordando prima lo scopo delle clausole ON e WHERE e poi dimostrando con degli esempi quali tipi di condizioni dovrebbero essere presenti in ciascuna di queste clausole.

Sia la clausola ON che la clausola WHERE possono specificare condizioni. Ma ci sono differenze tra le due? In caso affermativo, dove si devono specificare le condizioni nella query SQL? Scopriamolo insieme!

Condizioni ON vs. WHERE

Lo scopo della clausola ON è quello di specificare le condizioni di unione, cioè di definire il modo in cui le tabelle devono essere unite. In particolare, si definisce il modo in cui i record devono essere abbinati.

Al contrario, lo scopo della clausolaWHERE è quello di specificare le condizioni di filtraggio, cioè di definire quali righe devono essere mantenute nell'insieme dei risultati.

Vediamo un esempio per capire la differenza. Abbiamo le seguenti due tabelle che (1) elencano gli utenti (la tabella users) del nostro sito web di affitti e (2) elencano le case (la tabella houses) disponibili per l'affitto.

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
JOIN houses h
ON u.id = h.owner_id
WHERE u.registration_date < '2020-01-01';

Si noti che le condizioni sono presenti sia nella clausola ON che nella clausola WHERE:

• Con la condizione ON, si specifica che le tabelle devono essere unite in base alla corrispondenza tra la colonna id della tabella utenti e la colonna owner_id delle case.
• Con la condizione WHERE, filtriamo l'insieme dei risultati mantenendo solo gli utenti che si sono registrati prima del 1° gennaio 2020.

Abbiamo quindi utilizzato le condizioni ON e WHERE in base al loro scopo, ottenendo una query SQL chiara e leggibile.

Ecco l'insieme dei risultati:

Non siete sicuri di come funziona JOIN nella nostra query SQL? Esercitatevi a unire le tabelle con questo corso interattivo sulle JOIN SQL .

Condizioni ON e WHERE nelle JOIN INTERNE

Nell'esempio precedente, possiamo vedere come le condizioni ON e WHERE siano utilizzate in base al loro scopo e alla pratica comune.

Tuttavia, è utile sapere che, per (INNER) JOINs, è possibile specificare sia la condizione JOIN sia la condizione di filtraggio con una clausola ON. Ad esempio, è possibile ottenere lo stesso risultato di cui sopra con la seguente query SQL:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
JOIN houses h
ON u.id = h.owner_id AND u.registration_date < '2020-01-01';

Questa query viene eseguita allo stesso modo. Detto questo, non è consigliabile mischiare la condizione di join e la condizione di filtraggio nella stessa clausola. Se si confrontano le due query, si nota che la prima è più leggibile:

• È più facile seguire la prima query: prima si uniscono le tabelle in base a una certa condizione, poi si filtra il risultato in base a una condizione diversa.
• L'intento dell'intera query è più chiaro per il lettore esterno quando le condizioni sono separate seguendo le regole.

Condizioni ON e WHERE nelle JOIN OUTER

Quando si tratta di OUTER JOINs (cioè LEFT JOIN, RIGHT JOIN e FULL JOIN), è fondamentale utilizzare le condizioni ON e WHERE nel modo previsto. Altrimenti, si otterranno risultati sbagliati. Vediamo un esempio.

Anche in questo caso, vogliamo ottenere l'elenco degli utenti che si sono registrati prima del 1° gennaio 2020, insieme alle rispettive case. Questa volta, però, vogliamo mantenere tutti gli utenti, compresi quelli che non hanno registrato case sul nostro sito di affitti. Pertanto, faremo un LEFT JOIN invece di un JOIN (cioè, un INNER JOIN).

Vedremo se ci sono differenze tra specificare la condizione di filtraggio nella clausola ON e specificarla nella clausola WHERE. Se seguiamo le regole e utilizziamo le condizioni come previsto, otteniamo la seguente query:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id
WHERE u.registration_date < '2020-01-01';

Il risultato è buono. Abbiamo tutti gli utenti che abbiamo ottenuto nell'esempio iniziale. Inoltre, abbiamo altri due utenti che non hanno case corrispondenti sul nostro sito web, ma che sono stati inclusi nel set di risultati grazie alla clausola LEFT JOIN. Si noti che entrambi si sono registrati prima del 1° gennaio 2020, come specificato nella condizione di filtraggio.

Otteniamo lo stesso risultato se mescoliamo la condizione di join e la condizione di filtraggio nella clausola ON? Scopriamolo:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id AND u.registration_date < '2020-01-01';

Come si può vedere, i risultati sono diversi. Sono inclusi tutti gli utenti, anche quelli che si sono registrati nel 2020 o nel 2021. Questo perché LEFT JOIN mantiene tutti i record della tabella di sinistra anche quando la logica di ON fallisce. Quindi, in questo esempio, specificare la condizione di filtraggio nella clausola ON non funziona. Per ottenere il risultato corretto, dobbiamo specificare le condizioni come previsto.

È interessante notare che ci sono situazioni in cui la condizione WHERE può "annullare" l'intento di una clausola OUTER JOIN. Per esempio, supponiamo di voler elencare tutti gli utenti con le loro case corrispondenti, ma solo se le case hanno 3 o più camere da letto.

Poiché vogliamo mantenere tutti gli utenti, utilizzeremo una OUTER JOIN, in particolare una LEFT JOIN. Il requisito del numero di camere da letto è chiaramente una condizione di filtraggio. Pertanto, la includeremo nella clausola WHERE. Ecco la nostra query SQL con le condizioni specificate come previsto:

SELECT u.id, u.name, h.address, h.city, h.bedrooms
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id
WHERE h.bedrooms > 2;

Non sembra corretto, vero? Il risultato è come se avessimo usato una INNER JOIN anziché una LEFT JOIN. Gli utenti senza casa non sono inclusi nella tabella risultante, perché hanno NULL nella colonna camere da letto quando le tabelle vengono unite. Poiché i valori di NULL sono considerati inferiori a 0, le righe corrispondenti vengono rimosse quando si applica la condizione di filtraggio - il numero di camere da letto superiore a 2.

Esistono due possibili soluzioni a questo problema:

• Aggiungere un'altra condizione di filtraggio alla clausola WHERE, ad esempio bedrooms è NULL:
  

  SELECT u.id, u.name, h.address, h.city, h.bedrooms
  FROM users u
  LEFT JOIN houses h
  ON u.id = h.owner_id
  WHERE h.bedrooms > 2 OR h.bedrooms is NULL;
  

• Spostare la condizione di filtraggio nella clausola ON:
  

  SELECT u.id, u.name, h.address, h.city, h.bedrooms
  FROM users u
  LEFT JOIN houses h
  ON u.id = h.owner_id AND h.bedrooms > 2;
  

Una di queste due interrogazioni dà il seguente risultato:

Ora lo sapete! In OUTER JOINs fa differenza il modo in cui si specificano le condizioni.

Facciamo pratica con le JOIN in SQL!

SQL JOINs non è troppo difficile da capire. Tuttavia, come si può vedere dagli esempi di questo articolo, ci sono delle sfumature che vanno considerate quando si uniscono tabelle e si scrivono condizioni di join in SQL.

Se si vuole davvero padroneggiare SQL JOINs, fare pratica con set di dati reali è un fattore di successo fondamentale. Vi consiglio di iniziare con il corso interattivo SQL JOINs, che comprende 93 sfide di codifica che coprono i tipi più comuni di join come JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, e anche self-join e join non equi. Dopo aver seguito questo corso, saprete come unire più tabelle, come unire tabelle senza una colonna comune e come filtrare correttamente i dati con diversi tipi di JOINs.

Per chi vuole fare esperienza con casi d'uso ancora più numerosi di SQL JOIN, consiglio di seguire il percorso SQL Practice . Include cinque corsi interattivi con oltre 600 sfide di codifica, che coprono non solo le basi di SQL JOINs ma anche come filtrare l'insieme dei risultati con una clausola WHERE, come aggregare i dati con GROUP BY e HAVING e come utilizzare le subquery, comprese le subquery correlate. Vi divertirete un mondo!

BONUS. Ecco le 10 migliori domande di intervista su SQL JOIN con le relative risposte.