SQL join: where clausola contro clausola

Dopo averlo letto, questo non è un duplicato dei join SQL espliciti o impliciti . La risposta può essere correlata (o anche la stessa) ma la domanda è diversa.


Qual è la differenza e cosa dovrebbe andare in ciascuno?

Se comprendo correttamente la teoria, Query Optimizer dovrebbe essere in grado di utilizzare entrambi in modo intercambiabile.

Non sono la stessa cosa.

Considera queste domande:

SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID WHERE Orders.ID = 12345 

e

 SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID AND Orders.ID = 12345 

Il primo restituirà un ordine e le sue linee, se presenti, per il numero di ordine 12345 . Il secondo restituirà tutti gli ordini, ma solo l’ordine 12345 avrà delle linee associate.

Con un INNER JOIN , le clausole sono effettivamente equivalenti. Tuttavia, solo perché sono funzionalmente uguali, in quanto producono gli stessi risultati, non significa che i due tipi di clausole abbiano lo stesso significato semantico.

  • Non importa per i join interiori
  • Aspetti esteriori

    un. Clausola WHERE : dopo l’ adesione. I record verranno filtrati dopo l’adesione.

    b. Clausola ONPrima di unirsi. I record (dalla tabella di destra) verranno filtrati prima di unirsi. Questo può finire come null nel risultato (dal join OUTER).

Esempio : considera le seguenti tabelle:

  1. documents: | id | name | --------|-------------| | 1 | Document1 | | 2 | Document2 | | 3 | Document3 | | 4 | Document4 | | 5 | Document5 | 2. downloads: | id | document_id | username | |------|---------------|----------| | 1 | 1 | sandeep | | 2 | 1 | simi | | 3 | 2 | sandeep | | 4 | 2 | reya | | 5 | 3 | simi | 

a) Clausola WHERE interna:

  SELECT documents.name, downloads.id FROM documents LEFT OUTER JOIN downloads ON documents.id = downloads.document_id WHERE username = 'sandeep' For above query the intermediate join table will look like this. | id(from documents) | name | id (from downloads) | document_id | username | |--------------------|--------------|---------------------|-------------|----------| | 1 | Document1 | 1 | 1 | sandeep | | 1 | Document1 | 2 | 1 | simi | | 2 | Document2 | 3 | 2 | sandeep | | 2 | Document2 | 4 | 2 | reya | | 3 | Document3 | 5 | 3 | simi | | 4 | Document4 | NULL | NULL | NULL | | 5 | Document5 | NULL | NULL | NULL | After applying the `WHERE` clause and selecting the listed attributes, the result will be: | name | id | |--------------|----| | Document1 | 1 | | Document2 | 3 | 

b) Clausola JOIN interna

  SELECT documents.name, downloads.id FROM documents LEFT OUTER JOIN downloads ON documents.id = downloads.document_id AND username = 'sandeep' For above query the intermediate join table will look like this. | id(from documents) | name | id (from downloads) | document_id | username | |--------------------|--------------|---------------------|-------------|----------| | 1 | Document1 | 1 | 1 | sandeep | | 2 | Document2 | 3 | 2 | sandeep | | 3 | Document3 | NULL | NULL | NULL | | 4 | Document4 | NULL | NULL | NULL | | 5 | Document5 | NULL | NULL | NULL | Notice how the rows in `documents` that did not match both the conditions are populated with `NULL` values. After Selecting the listed attributes, the result will be: | name | id | |------------|------| | Document1 | 1 | | Document2 | 3 | | Document3 | NULL | | Document4 | NULL | | Document5 | NULL | 

Su INNER JOIN s sono intercambiabili e l’ottimizzatore li riorganizza a piacimento.

Su OUTER JOIN s, non sono necessariamente intercambiabili, a seconda del lato del join da cui dipendono.

Li ho posizionati in entrambi i posti in base alla leggibilità.

Il modo in cui lo faccio è:

Inserisci sempre le condizioni di join nella clausola on Se stai facendo un inner join, quindi non aggiungere nessuna clausola where alla clausola on, inserirle nella clausola where

Se si sta eseguendo un join di sinistra, aggiungere eventuali condizioni in corrispondenza della clausola on per la tabella nella parte destra del join. Questo è indispensabile poiché l’aggiunta di una clausola where che fa riferimento al lato destro del join convertirà il join in un join interno (con un’eccezione descritta di seguito).

L’eccezione è che quando si cercano i record che non si trovano in una determinata tabella, si aggiungerà la refernza a un identificativo univoco (che non è mai nullo) nella tabella di unione destra alla clausola where in questo modo “Where t2. l’idfield è nullo “. Quindi l’unica volta in cui dovresti fare riferimento a una tabella sul lato destro del join è trovare quei record che non sono nella tabella.

Su un join interno, significano la stessa cosa. Tuttavia otterrai risultati diversi in un join esterno a seconda che tu inserisca la condizione di join nella clausola WHERE vs ON. Dai un’occhiata a questa domanda correlata e questa risposta (da me).

Penso che abbia più senso avere l’abitudine di mettere sempre la condizione di join nella clausola ON (a meno che non si tratti di un join esterno e in realtà lo si vuole nella clausola where) in quanto rende più chiaro a chiunque legga la query a quali condizioni vengono aggiunte le tabelle, e inoltre aiuta a impedire che la clausola WHERE sia dozzine di righe.

Questo articolo spiega chiaramente la differenza. Spiega anche “ON united_condition vs WHERE united_condition o united_alias è null”.

La clausola WHERE filtra sia il lato sinistro che il lato destro del JOIN, mentre la clausola ON filtrerà sempre solo il lato destro.

  1. Se vuoi sempre recuperare le righe di sinistra e solo JOIN se alcune condizioni corrispondono, dovresti inserire la clausola ON.
  2. Se si desidera filtrare il prodotto di unire entrambi i lati, è necessario utilizzare la clausola WHERE.

In termini di ottimizzatore, non dovrebbe fare la differenza se si definiscono le clausole di join con ON o WHERE.

Tuttavia, IMHO, penso che sia molto più chiaro usare la clausola ON durante l’esecuzione dei join. In questo modo hai una sezione specifica della tua query che stabilisce come viene gestito il join rispetto al resto delle clausole WHERE.

C’è una grande differenza tra la clausola where e la clausola on , quando si tratta di left join.

Ecco un esempio:

 mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | fid | int(11) | NO | | NULL | | | v | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 

Ci fid è id della tabella t2.

 mysql> desc t2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | v | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 

Query su “on clause”:

 mysql> SELECT * FROM `t1` left join t2 on fid = t2.id AND t1.v = 'K' -> ; +----+-----+---+------+------+ | id | fid | v | id | v | +----+-----+---+------+------+ | 1 | 1 | H | NULL | NULL | | 2 | 1 | B | NULL | NULL | | 3 | 2 | H | NULL | NULL | | 4 | 7 | K | NULL | NULL | | 5 | 5 | L | NULL | NULL | +----+-----+---+------+------+ 5 rows in set (0.00 sec) 

Query su “dove clausola”:

 mysql> SELECT * FROM `t1` left join t2 on fid = t2.id where t1.v = 'K'; +----+-----+---+------+------+ | id | fid | v | id | v | +----+-----+---+------+------+ | 4 | 7 | K | NULL | NULL | +----+-----+---+------+------+ 1 row in set (0.00 sec) 

È chiaro che la prima query restituisce un record da t1 e la sua riga dipendente da t2, se presente, per la riga t1.v = ‘K’.

La seconda query restituisce le righe da t1, ma solo per t1.v = ‘K’ avrà qualsiasi riga associata con essa.

per migliori prestazioni le tabelle dovrebbero avere una colonna speciale indicizzata da usare per JOINS.

quindi se la colonna su cui si esegue la condizionamento non è una di quelle colonne indicizzate, allora sospetto che sia meglio tenerlo in WHERE.

quindi ti unisci usando le colonne indicizzate, quindi dopo JOIN esegui la condizione sulla colonna senza indice.

Normalmente, il filtraggio viene elaborato nella clausola WHERE una volta che le due tabelle sono già state unite. È ansible, anche se potresti voler filtrare una o entrambe le tabelle prima di unirle. cioè, la clausola where si applica all’intero set di risultati mentre la clausola on si applica solo al join in questione.

Penso che sia l’effetto della sequenza di join. Nel caso di join in alto a sinistra, SQL do Left si unisce prima e poi fa il filtro. Nel caso di downer, trova Orders.ID = 12345 prima e poi unisciti a.

In SQL, la clausola ‘WHERE’ e ‘ON’, sono una specie di Statemants condizionali, ma la principale differenza tra loro è che la clausola ‘Where’ è usata in Select / Update Statements per specificare le Condizioni, mentre la clausola ‘ON’ è usato in Joins, dove verifica o verifica se i record sono abbinati nelle tabelle target e source, prima che le tabelle vengano unite

Per esempio: – ‘WHERE’

SELECT * FROM employee WHERE employee_id = 101

Per esempio: – ‘ON’

* Esistono due tabelle dipendenti e dipendenti_dettagli, le colonne corrispondenti sono employee_id. *

SELECT * FROM employee INNER JOIN employee_details ON employee.employee_id = employee_details.employee_id

Spero di aver risposto alla tua domanda. Torna indietro per chiarimenti.

Per un’unione interna, WHERE e ON possono essere utilizzati in modo intercambiabile. In effetti, è ansible utilizzare ON in una sottoquery correlata. Per esempio:

 update mytable set myscore=100 where exists ( select 1 from table1 inner join table2 on (table2.key = mytable.key) inner join table3 on (table3.key = table2.key and table3.key = table1.key) ... ) 

Questo è (IMHO) completamente confusionario per un essere umano, ed è molto facile dimenticare di colbind table1 a qualsiasi cosa (perché la tabella “driver” non ha una clausola “on”), ma è legale.

questa è la mia soluzione.

 SELECT song_ID,songs.fullname, singers.fullname FROM music JOIN songs ON songs.ID = music.song_ID JOIN singers ON singers.ID = music.singer_ID GROUP BY songs.fullname 

È necessario disporre di GROUP BY per farlo funzionare.

Spero che questo aiuto.