INNER JOIN ON vs WHERE clause

Per semplicità, si supponga che tutti i campi rilevanti NOT NULL siano NOT NULL .

Tu puoi fare:

 SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions) 

O altro:

 SELECT table1.this, table2.that, table2.somethingelse FROM table1 INNER JOIN table2 ON table1.foreignkey = table2.primarykey WHERE (some other conditions) 

Questi due funzionano allo stesso modo in MySQL ?

INNER JOIN è la syntax ANSI che dovresti usare.

Generalmente è considerato più leggibile, specialmente quando si uniscono molti tavoli.

Può anche essere facilmente sostituito con un OUTER JOIN necessità.

La syntax WHERE è più orientata al modello relazionale.

Un risultato di due tabelle JOIN ed è un prodotto cartesiano delle tabelle a cui viene applicato un filtro che seleziona solo le righe corrispondenti alle colonne di unione.

È più facile vederlo con la syntax WHERE .

Per quanto riguarda il tuo esempio, in MySQL (e in SQL in generale) queste due query sono sinonimi.

Si noti inoltre che MySQL ha anche una clausola STRAIGHT_JOIN .

Usando questa clausola, puoi controllare l’ordine JOIN : quale tabella è scansionata nel ciclo esterno e quale è nel ciclo interno.

Non puoi controllare questo in MySQL usando la syntax WHERE .

Altri hanno sottolineato che INNER JOIN aiuta la leggibilità umana, e questa è una priorità assoluta; Sono d’accordo. Lasciatemi provare a spiegare perché la syntax del join è più leggibile.

Una query SELECT di base è questa:

 SELECT stuff FROM tables WHERE conditions 

La clausola SELECT ci dice cosa stiamo tornando; la clausola FROM ci dice da dove veniamo, e la clausola WHERE ci dice quali sono quelli che stiamo ricevendo.

JOIN è una dichiarazione sui tavoli, su come sono legati insieme (concettualmente, in realtà, in una singola tabella). Qualsiasi elemento di query che controlla le tabelle, da cui ricaviamo elementi, appartiene semanticamente alla clausola FROM (e, naturalmente, è qui che vanno gli elementi JOIN). Mettendo gli elementi di congiunzione nella clausola WHERE si confonde il che e il dove-da ; questo è il motivo per cui la syntax JOIN è preferita.

Applicazione di istruzioni condizionali in ON / WHERE

Qui ho spiegato le fasi di elaborazione della query logica.


Riferimento: all’interno di Microsoft SQL Server ™ 2005 query T-SQL
Editore: Microsoft Press
Pub Data: 7 marzo 2006
Stampa ISBN-10: 0-7356-2313-9
Stampa ISBN-13: 978-0-7356-2313-2
Pagine: 640

All’interno di Querying T-SQL di Microsoft SQL Server ™ 2005

 (8) SELECT (9) DISTINCT (11) TOP   (1) FROM  (3)  JOIN  (2) ON  (4) WHERE  (5) GROUP BY  (6) WITH {CUBE | ROLLUP} (7) HAVING  (10) ORDER BY  

Il primo aspetto evidente di SQL diverso dagli altri linguaggi di programmazione è l’ordine in cui il codice viene elaborato. Nella maggior parte dei linguaggi di programmazione, il codice viene elaborato nell’ordine in cui è scritto. In SQL, la prima clausola che viene elaborata è la clausola FROM, mentre la clausola SELECT, che appare per prima, viene elaborata quasi per ultima.

Ogni passaggio genera una tabella virtuale che viene utilizzata come input per il passaggio successivo. Queste tabelle virtuali non sono disponibili per il chiamante (applicazione client o query esterna). Solo la tabella generata dal passaggio finale viene restituita al chiamante. Se una determinata clausola non è specificata in una query, il passaggio corrispondente viene semplicemente saltato.

Breve descrizione delle fasi di elaborazione delle query logiche

Non preoccuparti troppo se la descrizione dei passaggi non sembra avere molto senso per ora. Questi sono forniti come riferimento. Le sezioni successive all’esempio di scenario copriranno i passaggi in modo molto più dettagliato.

  1. FROM: Un prodotto cartesiano (cross join) viene eseguito tra le prime due tabelle nella clausola FROM e, di conseguenza, viene generata la tabella virtuale VT1.

  2. ON: il filtro ON viene applicato a VT1. Solo le righe per le quali è TRUE vengono inserite in VT2.

  3. OUTER (join): Se viene specificato un JOIN OUTER (al contrario di un CROSS JOIN o UNO JOIN INTERNO), le righe della tabella o delle tabelle protette per le quali non è stata trovata una corrispondenza vengono aggiunte alle righe da VT2 come righe esterne, generando VT3. Se nella clausola FROM vengono visualizzate più di due tabelle, i passaggi da 1 a 3 vengono applicati ripetutamente tra il risultato dell’ultimo join e la successiva tabella nella clausola FROM fino a quando tutte le tabelle vengono elaborate.

  4. DOVE: Il filtro WHERE è applicato a VT3. Solo le righe per cui è TRUE vengono inserite in VT4.

  5. GROUP BY: le righe da VT4 sono organizzate in gruppi in base all’elenco di colonne specificato nella clausola GROUP BY. VT5 è generato.

  6. CUBO | ROLLUP: i supergroup (gruppi di gruppi) vengono aggiunti alle righe da VT5, generando VT6.

  7. HAVING: il filtro HAVING è applicato a VT6. Solo i gruppi per i quali è TRUE sono inseriti in VT7.

  8. SELEZIONA: l’elenco SELECT viene elaborato, generando VT8.

  9. DISTINCT: le file duplicate vengono rimosse da VT8. VT9 è generato.

  10. ORDER BY: le righe da VT9 vengono ordinate in base all’elenco di colonne specificato nella clausola ORDER BY. Viene generato un cursore (VC10).

  11. TOP: il numero specificato o la percentuale di righe viene selezionata dall’inizio di VC10. La tabella VT11 viene generata e restituita al chiamante.


Pertanto, (INNER JOIN) ON filtrerà i dati (il conteggio dei dati di VT verrà ridotto qui stesso) prima di applicare la clausola WHERE. Le condizioni di join successive verranno eseguite con dati filtrati che migliorano le prestazioni. Successivamente, solo la condizione WHERE applicherà le condizioni del filtro.

(L’applicazione di istruzioni condizionali in ON / WHERE non farà molta differenza in pochi casi, dipende dal numero di tabelle che hai aggiunto e dal numero di righe disponibili in ciascuna tabella di join)

La syntax ANSI implicita è più vecchia, meno ovvia e non consigliata.

Inoltre, l’algebra relazionale consente l’intercambiabilità dei predicati nella clausola WHERE e INNER JOIN , quindi anche le query INTERNE INNER JOIN con le clausole WHERE possono avere i predicati riorganizzati dall’ottimizzatore.

Vi consiglio di scrivere le domande nel modo più leggibile ansible.

A volte ciò include rendere INNER JOIN relativamente “incompleto” e inserire alcuni dei criteri nel WHERE semplicemente per rendere gli elenchi dei criteri di filtro più facilmente mantenibili.

Ad esempio, invece di:

 SELECT * FROM Customers c INNER JOIN CustomerAccounts ca ON ca.CustomerID = c.CustomerID AND c.State = 'NY' INNER JOIN Accounts a ON ca.AccountID = a.AccountID AND a.Status = 1 

Scrivi:

 SELECT * FROM Customers c INNER JOIN CustomerAccounts ca ON ca.CustomerID = c.CustomerID INNER JOIN Accounts a ON ca.AccountID = a.AccountID WHERE c.State = 'NY' AND a.Status = 1 

Ma dipende, ovviamente.

I join impliciti (che sono noti come la tua prima query) diventano molto più confusi, difficili da leggere e difficili da mantenere una volta che hai bisogno di iniziare ad aggiungere più tabelle alla tua query. Immagina di fare la stessa query e il tipo di join su quattro o cinque tavoli diversi … è un incubo.

L’utilizzo di un join esplicito (il secondo esempio) è molto più leggibile e facile da mantenere.

Sottolineerò anche che l’uso della syntax più vecchia è più sobject a errori. Se si utilizzano join interni senza una clausola ON, si verificherà un errore di syntax. Se si utilizza la syntax più vecchia e si dimentica una delle condizioni di join nella clausola where, si otterrà un cross join. Gli sviluppatori spesso risolvono questo problema aggiungendo la parola chiave distinta (piuttosto che correggendo il join perché ancora non si rendono conto che il join stesso è interrotto), che potrebbe sembrare risolvere il problema, ma rallenterà considerevolmente la query.

Inoltre per la manutenzione se si ha un cross join nella vecchia syntax, come farà il manutentore a sapere se si intende averne uno (ci sono situazioni in cui sono necessari cross join) o se è stato un incidente che dovrebbe essere risolto?

Permettimi di indicarti questa domanda per capire perché la syntax implicita è ctriggers se usi i join a sinistra. Sybase * = per Ansi Standard con 2 differenti tabelle esterne per lo stesso tavolo interno

In più (sfogo personale qui), lo standard che utilizza i join espliciti ha più di 20 anni, il che significa che la syntax implicita della join è stata superata per quei 20 anni. Scriveresti il ​​codice dell’applicazione usando la syntax che è stata superata da 20 anni? Perché vuoi scrivere il codice del database che è?

Hanno un diverso significato leggibile dall’uomo.

Tuttavia, a seconda di Query Optimizer, possono avere lo stesso significato per la macchina.

Dovresti sempre codice per essere leggibile.

Vale a dire, se si tratta di una relazione incorporata, utilizzare l’unione esplicita. se si stanno confrontando su dati debolmente correlati, utilizzare la clausola where.

Lo standard SQL: 2003 ha modificato alcune regole di precedenza in modo che una dichiarazione JOIN abbia la precedenza su un join “virgola”. Questo può effettivamente cambiare i risultati della tua query a seconda di come è stata configurata. Ciò causa alcuni problemi per alcune persone quando MySQL 5.0.12 è passato all’aderenza allo standard.

Quindi nel tuo esempio, le tue query funzionerebbero allo stesso modo. Ma se hai aggiunto una terza tabella: SELECT … FROM table1, table2 JOIN table3 ON … WHERE …

Prima di MySQL 5.0.12, tabella1 e tabella2 venivano uniti per primi, quindi tabella3. Ora (5.0.12 e on), table2 e table3 vengono uniti per primi, quindi table1. Non cambia sempre i risultati, ma può e non lo puoi nemmeno realizzare.

Non uso più la syntax “virgola”, optando per il tuo secondo esempio. È comunque molto più leggibile, le condizioni JOIN sono con le JOIN, non separate in una sezione di query separata.

So che stai parlando di MySQL, ma in ogni caso: in Oracle 9 i join espliciti e i join impliciti genererebbero diversi piani di esecuzione. AFAIK che è stato risolto in Oracle 10+: non c’è più alcuna differenza.

La syntax di join ANSI è decisamente più portabile.

Sto passando un aggiornamento di Microsoft SQL Server e vorrei anche ricordare che la syntax = * e * = per i join esterni in SQL Server non è supportata (senza modalità di compatibilità) per il server SQL 2005 e versioni successive.