Esterno interno e sinistro Unisci con Clausola Where e Clausola ON

Filtrare i risultati con la clausola [ON] e la clausola [Where] usando LEFT OUTER JOIN e INNER JOIN è una tecnica molto potente.

Un INNER JOIN dà file che corrispondono ai valori in colonne comuni di due o più tabelle usando un operatore come (=) uguale.

UN LEFT JOIN o LEFT OUTER JOIN fornisce tutte le righe dalla tabella sinistra con le righe corrispondenti di entrambe le tabelle. Quando una riga nella tabella di sinistra non ha righe corrispondenti nella tabella di destra, la riga di set di risultati associata contiene valori nulli per tutte le colonne di elenco selezionate provenienti dalla tabella di destra.

Ora dimostreremo come funzionano.

Creeremo due tabelle: FRUTTA e colore_del_frutto.

IF OBJECT_ID ('FRUIT') NON E 'FRUTTA DELLA TABELLA DEL DROP NULL; GO CREATE TABLE FRUIT (nome VARCHAR (25), colore INT); GO IF OBJECT_ID ('FRUIT_COLOR') NON È NULL DROP TABLE FRUIT_COLOR; GO CREATE TABLE FRUIT_COLOR (id INT, nome VARCHAR (25)); PARTIRE

Per quanto riguarda la struttura delle tabelle create, la tabella FRUIT_COLOR ha una colonna univoca (ID) e un colore (nome) s.

La tabella FRUIT è composta da frutta (nome) e rispettivi colori (colore).

Non ho creato alcuna chiave primaria e relazioni di chiave esterna per mantenerla semplice, ma puoi facilmente capire la chiave primaria FRUIT_COLOR (ID) è una chiave esterna di FRUIT (colore).

Compila queste tabelle con alcuni dati.

INSERISCI in FRUIT_COLOR VALUES (1, 'orange'); INSERISCI in FRUIT_COLOR VALUES (2, 'yellow'); INSERISCI in FRUIT_COLOR VALUES (3, 'red'); INSERISCI in FRUIT_COLOR VALUES (4, 'blue'); GO INSERT in FRUIT VALUES ('banana', 2); INSERISCI in FRUIT VALUES ('mango', 2); INSERISCI in FRUIT VALUES ('orange', 1); INSERISCI in FRUIT VALUES ('apple', 3); INSERIMENTO in VALORI DI FRUTTA ('uva', null); INSERISCI in FRUIT VALUES ('avocado', null); VAI SELEZIONA * da FRUTTA; SELEZIONA * da FRUIT_COLOR; PARTIRE

Dimostreremo diversi scenari con un join esterno sinistro.

Query 1.1 - Un semplice, semplice join esterno sinistro

SELEZIONA * FROM FRUIT F SINISTRA outer join FRUIT_COLOR FC ON F.color = FC.id; PARTIRE

Poiché conosciamo il concetto di un LEFT OUTER JOIN, sappiamo che mostrerà tutte le righe dalla tabella di sinistra. In questo caso, FRUIT è la tabella di sinistra e, se viene trovata una corrispondenza con la tabella di destra denominata FRUIT_COLOR, mostrerà le righe corrispondenti. Altrimenti, restituirà NULL.

Nell'esempio sopra, uva e avocado hanno il loro colore come NULL, quindi il confronto NULL fallisce e restituisce NULL.

Query 1.2 - A Left Outer Join con la clausola Where

SELEZIONA * FROM FRUIT F SINISTRA outer join FRUIT_COLOR FC ON F.color = FC.id WHERE F.name = 'apple'; PARTIRE

Quando aggiungiamo una clausola where con un join esterno sinistro, si comporta come un join interno, in cui il filtro viene applicato dopo la clausola ON, mostrando solo le righe che hanno frutti con il nome "mela".

Query 1.3 - A Left Outer Join con la clausola ON

SELECT * da FRUIT F LEFT outer join FRUIT_COLOR FC ON F.color = FC.id AND F.name = 'apple'; PARTIRE

Nella query sopra, la maggior parte delle persone si confonde su come viene valutata questa query. Puoi vedere i risultati sotto tutte le righe dalla tabella di sinistra sono inclusi nel set di risultati come una query di join esterno sinistro semplice che abbiamo visto in Query 1.1, ma dalla seconda tabella (cioè, fruit_color), vengono mostrate solo quelle righe che corrisponde alla condizione f.name = 'apple.' Altrimenti, viene mostrato come NULL.

Questo è utile quando si filtrano i dati con un join esterno sinistro. La clausola [ON] può essere utile quando è necessario restituire tutte le righe dalla tabella sinistra e solo quelle dalla tabella di destra che corrispondono alla condizione della clausola On.

- Query 1.4 - Un inner join con la clausola Where

SELECT * FROM FRUIT F INNER join FRUIT_COLOR FC ON F.color = FC.id WHERE F.name = 'orange'; PARTIRE

- Query 1.5 - Un inner join con la clausola ON

SELECT * FROM FRUIT F INNER join FRUIT_COLOR FC ON F.color = FC.id AND F.name = 'orange'; PARTIRE

Puoi vedere sia la query 1.4 che la query 1.5, utilizzando un inner join, restituiscono lo stesso risultato perché non importa dove stai filtrando le righe con un inner join. Con la clausola on o la clausola where, entrambi i casi forniscono gli stessi risultati.