inner join vs outer join
Unió interior contra unió exterior: prepareu-vos per explorar les diferències exactes entre la combinació interior i exterior
Abans d'explorar les diferències entre Inner Join Vs Outer Join, primer veiem què és un SQL JOIN?
Una clàusula de combinació s’utilitza per combinar registres o manipular els registres de dues o més taules mitjançant una condició de combinació. La condició de combinació indica com es combinen les columnes de cada taula.
La unió es basa en una columna relacionada entre aquestes taules. Un exemple més comú és la combinació entre dues taules a través de la columna de clau principal i la columna de clau estrangera.
Suposem que tenim una taula que conté el salari dels empleats i que hi ha una altra taula que conté les dades dels empleats.
En aquest cas, hi haurà una columna comuna com l'identificador d'empleat que s'uneix a aquestes dues taules. Aquesta columna d'identificació d'empleat seria la clau principal de les taules de detalls dels empleats i la clau estrangera de la taula de salaris dels empleats.
És molt important tenir una clau comuna entre les dues entitats. Podeu pensar en una taula com una entitat i la clau com un enllaç comú entre les dues taules que s'utilitza per a l'operació d'unió.
preguntes i respostes d’entrevistes de suport tècnic bàsic
Bàsicament, hi ha dos tipus de Join in SQL, és a dir, Unió interior i unió exterior . La unió exterior es subdivideix en tres tipus, és a dir, Unió exterior esquerra, unió exterior dreta i unió exterior completa.
En aquest article, veurem la diferència entre Unió interior i unió exterior en detall. Mantindrem les combinacions creuades i les combinacions desiguals fora de l’abast d’aquest article.
Què aprendreu:
- Què és Inner Join?
- Què és Outer Join?
- Diferència entre unió interior i exterior
- Rendiment
- Unió interna i exterior MS Access
- Unió esquerra vs Unió exterior esquerra
- Unió exterior esquerra vs Unió exterior dreta
- Diferència entre unió interior i unió exterior en format tabular
- Unió interior i exterior contra unió
- Conclusió
- Lectura recomanada
Què és Inner Join?
Una unió interna només retorna les files que tenen valors coincidents a les dues taules (aquí estem considerant que la unió es fa entre les dues taules).
Què és Outer Join?
La combinació exterior inclou les files coincidents, així com algunes de les files que no coincideixen entre les dues taules. Una unió externa bàsicament es diferencia de la combinació interior per tractar la condició de concordança falsa.
Hi ha 3 tipus d'unió externa:
- Unió exterior esquerra : Retorna totes les files de la taula ESQUERRA i els registres coincidents entre ambdues taules.
- Unió exterior dreta : Retorna totes les files de la taula RIGHT i els registres coincidents entre ambdues taules.
- Unió exterior completa : Combina el resultat de la combinació exterior esquerra i la combinació exterior dreta.
Diferència entre unió interior i exterior
(imatge font )
Com es mostra al diagrama anterior, hi ha dues entitats, és a dir, la taula 1 i la taula 2 i les dues taules comparteixen algunes dades comunes.
Una unió interna retornarà l’àrea comuna entre aquestes taules (la zona ombrejada de color verd del diagrama anterior), és a dir, tots els registres que són comuns entre la taula 1 i la taula 2.
Una unió exterior esquerra retornarà totes les files de la taula 1 i només les files de la taula 2 que també són comunes a la taula 1. Una unió exterior correcta farà tot el contrari. Proporcionarà tots els registres de la taula 2 i només els registres coincidents corresponents de la taula 1.
A més, un Full Outer Join ens proporcionarà tots els registres de la taula 1 i la taula 2.
Comencem amb un exemple per fer-ho més clar.
Suposem que en tenim dos taules: EmpDetails i EmpSalary .
Taula EmpDetails:
| ID empleat | Nom d'empleat |
| 7 | Lliri |
| 1 | Joan |
| 2 | Samantha |
| 3 | Cap |
| 4 | Sedós |
| 5 | Ram |
| 6 | Arpit |
| 8 | Sita |
| 9 | Farah |
| 10 | Jerry |
Taula EmpSalary:
| ID empleat | Nom d'empleat | EmpleatSalari |
|---|---|---|
| 11 | Rosa | 90000 |
| 1 | Joan | 50000 |
| 2 | Samantha | 120000 |
| 3 | Cap | 75000 |
| 4 | Sedós | 25000 |
| 5 | Ram | 150000 |
| 6 | Arpit | 80000 |
| 12 | Sakshi | 45000 |
| 13 | Jack | 250000 |
Fem una unió interna en aquestes dues taules i observem el resultat:
Consulta:
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails INNER JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID; Resultat:
| ID empleat | Nom d'empleat | EmpleatSalari |
|---|---|---|
| 7 | Lliri | NUL |
| 1 | Joan | 50000 |
| 2 | Samantha | 120000 |
| 3 | Cap | 75000 |
| 4 | Sedós | 25000 |
| 5 | Ram | 150000 |
| 6 | Arpit | 80000 |
Al conjunt de resultats anterior, podeu veure que Inner Join ha retornat els primers 6 registres que eren presents tant a EmpDetails com a EmpSalary amb una clau coincident, és a dir, EmployeeID. Per tant, si A i B són dues entitats, la combinació interna retornarà el conjunt de resultats que serà igual a 'Registres en A i B', en funció de la clau de coincidència.
Vegem ara què farà una unió exterior esquerra.
Consulta:
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails LEFT JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID; Resultat:
| ID empleat | Nom d'empleat | EmpleatSalari |
|---|---|---|
| 1 | Joan | 50000 |
| 2 | Samantha | 120000 |
| 3 | Cap | 75000 |
| 4 | Sedós | 25000 |
| 5 | Ram | 150000 |
| 6 | Arpit | 80000 |
| 8 | Sita | NUL |
| 9 | Farah | NUL |
| 10 | Jerry | NUL |
Al conjunt de resultats anterior, podeu veure que la unió exterior esquerra ha retornat tots els 10 registres de la taula LEFT, és a dir, la taula EmpDetails i, com que els primers 6 registres coincideixen, ha retornat el salari dels empleats per aquests registres coincidents.
Com que la resta de registres no tenen una clau coincident a la taula RIGHT, és a dir, a la taula EmpSalary, ha retornat NULL corresponent a aquests. Com que, Lily, Sita, Farah i Jerry no tenen un ID d'empleat coincident a la taula EmpSalary, el seu salari apareix com a NUL al conjunt de resultats.
Per tant, si A i B són dues entitats, la combinació externa esquerra retornarà el conjunt de resultats que serà igual a 'Registres a A NO B', basat en la clau de coincidència.
Ara observem el que fa la unió exterior correcta.
Consulta:
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails RIGHT join EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID; Resultat:
| ID empleat | Nom d'empleat | EmpleatSalari |
|---|---|---|
| NUL | NUL | 90000 |
| 1 | Joan | 50000 |
| 2 | Samantha | 120000 |
| 3 | Cap | 75000 |
| 4 | Sedós | 25000 |
| 5 | Ram | 150000 |
| 6 | Arpit | 80000 |
| NUL | NUL | 250000 |
| NUL | NUL | 250000 |
Al conjunt de resultats anterior, podeu veure que la combinació exterior dreta ha fet exactament el contrari de la combinació esquerra. Ha retornat tots els sous de la taula adequada, és a dir, la taula EmpSalary.
Però, com que Rose, Sakshi i Jack no tenen un identificador d'empleat coincident a la taula esquerra, és a dir, a la taula EmpDetails, tenim la identificació d'empleat i el nom d'empleat com a NUL de la taula esquerra.
Per tant, si A i B són dues entitats, la combinació externa dreta retornarà el conjunt de resultats que serà igual a 'Registres a B NO A', basat en la clau de coincidència.
Vegem també quin serà el conjunt de resultats si fem una operació de selecció a totes les columnes de les dues taules.
Consulta:
SELECT * FROM EmpDetails RIGHT JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;Resultat:
| ID empleat | Nom d'empleat | ID empleat | Nom d'empleat | EmpleatSalari |
|---|---|---|---|---|
| NUL | NUL | 11 | Rosa | 90000 |
| 1 | Joan | 1 | Joan | 50000 |
| 2 | Samantha | 2 | Samantha | 120000 |
| 3 | Cap | 3 | Cap | 75000 |
| 4 | Sedós | 4 | Sedós | 25000 |
| 5 | Ram | 5 | Ram | 150000 |
| 6 | Arpit | 6 | Arpit | 80000 |
| NUL | NUL | 12 | Sakshi | 250000 |
| NUL | NUL | 13 | Jack | 250000 |
Ara, passem a la Unió completa.
Es realitza una unió exterior completa quan volem totes les dades de les dues taules, independentment de si hi ha una coincidència o no. Per tant, si vull tots els empleats encara que no trobi una clau coincident, executaré una consulta com es mostra a continuació.
Consulta:
SELECT * FROM EmpDetails FULL JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;Resultat:
| ID empleat | Nom d'empleat | ID empleat | Nom d'empleat | EmpleatSalari |
|---|---|---|---|---|
| 7 | Lliri | NUL | NUL | NUL |
| 1 | Joan | 1 | Joan | 50000 |
| 2 | Samantha | 2 | Samantha | 120000 |
| 3 | Cap | 3 | Cap | 75000 |
| 4 | Sedós | 4 | Sedós | 25000 |
| 5 | Ram | 5 | Ram | 150000 |
| 6 | Arpit | 6 | Arpit | 80000 |
| 8 | Sita | NUL | NUL | NUL |
| 9 | Farah | NUL | NUL | NUL |
| 10 | Jerry | NUL | NUL | NUL |
| NUL | NUL | 11 | Rosa | 90000 |
| NUL | NUL | 12 | Sakshi | 250000 |
| NUL | NUL | 13 | Jack | 250000 |
Podeu veure al conjunt de resultats anterior que, com que els primers sis registres coincideixen a les dues taules, tenim totes les dades sense NULL. Els següents quatre registres existeixen a la taula esquerra però no a la taula dreta, per tant les dades corresponents a la taula dreta són NULS.
Els tres darrers registres existeixen a la taula dreta i no a la taula esquerra, per tant tenim NULL a les dades corresponents de la taula esquerra. Per tant, si A i B són dues entitats, la unió exterior completa retornarà el conjunt de resultats que serà igual a 'Registres en A I B', independentment de la clau que coincideixi.
Teòricament, és una combinació d'unió esquerra i unió dreta.
Rendiment
Comparem una unió interna amb una unió exterior esquerra al servidor SQL. Parlant de la velocitat de funcionament, una unió exterior esquerra òbviament no és més ràpida que una unió interior.
Segons la definició, una unió exterior, ja sigui esquerra o dreta, ha de realitzar tot el treball d'una unió interior juntament amb el treball addicional nul, ampliant els resultats. S'espera que una unió externa retorni un major nombre de registres, cosa que augmenta encara més el seu temps d'execució total només a causa del conjunt de resultats més gran.
preguntes d'entrevistes sobre angularjs per a persones experimentades
Per tant, una unió exterior és més lenta que una unió interior.
A més, pot haver-hi algunes situacions específiques en què la combinació esquerra sigui més ràpida que la combinació interior, però no podem continuar substituint-les entre elles, ja que una unió exterior esquerra no és funcionalment equivalent a una unió interior.
Parlem d'un cas en què la combinació esquerra pot ser més ràpida que la combinació interna. Si les taules implicades en l'operació d'unió són massa petites, digueu que tenen menys de 10 registres i que les taules no tenen índexs suficients per cobrir la consulta, en aquest cas, la combinació esquerra és generalment més ràpida que la combinació interna.
Creem les dues taules següents i fem un JOIN INTERIOR i un JOTER OUTER JOIN entre elles com a exemple:
CREATE TABLE #Table1 ( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL ) INSERT #Table1 (ID, Name) VALUES (1, 'A') INSERT #Table1 (ID, Name) VALUES (2, 'B') INSERT #Table1 (ID, Name) VALUES (3, 'C') INSERT #Table1 (ID, Name) VALUES (4, 'D') INSERT #Table1 (ID, Name) VALUES (5, 'E') CREATE TABLE #Table2 ( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL ) INSERT #Table2 (ID, Name) VALUES (1, 'A') INSERT #Table2 (ID, Name) VALUES (2, 'B') INSERT #Table2 (ID, Name) VALUES (3, 'C') INSERT #Table2 (ID, Name) VALUES (4, 'D') INSERT #Table2 (ID, Name) VALUES (5, 'E') SELECT * FROM #Table1 t1 INNER JOIN #Table2 t2 ON t2.Name = t1.Name | Identificació | Nom | Identificació | Nom | |
|---|---|---|---|---|
A continuació es mostra la visualització d’una unió interior:  | A continuació es mostra la visualització d’una unió exterior  | |||
| 1 | 1 | A | 1 | A |
| 2 | 2 | B | 2 | B |
| 3 | 3 | C | 3 | C |
| 4 | 4 | D | 4 | D |
| 5 | 5 | ÉS | 5 | ÉS |
SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS bar ON (55=55); | Identificació | Nom | Identificació | Nom | |
|---|---|---|---|---|
| 1 | 1 | A | 1 | A |
| 2 | 2 | B | 2 | B |
| 3 | 3 | C | 3 | C |
| 4 | 4 | D | 4 | D |
| 5 | 5 | ÉS | 5 | ÉS |
Com podeu veure més amunt, totes dues consultes han retornat el mateix conjunt de resultats. En aquest cas, si visualitzeu el pla d'execució de les dues consultes, trobareu que la unió interna ha costat més que la combinació exterior. Això es deu al fet que, per a una unió interna, el servidor SQL fa una coincidència de hash, mentre que fa bucles imbricats per a la combinació esquerra.
Una coincidència de hash normalment és més ràpida que els bucles imbricats. Però, en aquest cas, com que el nombre de files és tan reduït i no hi ha cap índex per utilitzar (ja que estem fent la unió a la columna Nom), l'operació hash ha resultat una consulta d'unió interna més cara.
Tanmateix, si canvieu la clau de coincidència de la consulta de combinació de Nom a ID i si hi ha un gran nombre de files a la taula, trobareu que la combinació interna serà més ràpida que la combinació externa esquerra.
Unió interna i exterior MS Access
Quan utilitzeu diverses fonts de dades a la consulta de MS Access, apliqueu JOIN per controlar els registres que vulgueu veure, en funció de com es vinculin les fonts de dades entre si.
En una unió interior, només els relacionats de les dues taules es combinen en un únic conjunt de resultats. Aquesta és una unió predeterminada a Access i la més utilitzada també. Si apliqueu una unió però no especifiqueu explícitament quin tipus d’adherència és, l’Access assumeix que es tracta d’una unió interna.
A les combinacions externes, totes les dades relacionades de les dues taules es combinen correctament, a més de totes les files restants d’una taula. En combinacions exteriors completes, totes les dades es combinen sempre que sigui possible.
Unió esquerra vs Unió exterior esquerra
Al servidor SQL, la paraula clau exterior és opcional quan apliqueu la combinació externa esquerra. Per tant, no té cap diferència si escriviu 'ESQUERRA EXTERIOR UNIDA' o 'ESQUERRA ESQUERRA', ja que tots dos us donaran el mateix resultat.
A LEFT JOIN B és una sintaxi equivalent a A LEFT OUTER JOIN B.
A continuació es mostra la llista de sintaxis equivalents al servidor SQL:
(imatge font )
Unió exterior esquerra vs Unió exterior dreta
Ja hem vist aquesta diferència en aquest article. Podeu fer referència a les consultes i al conjunt de resultats de les consultes de combinació exterior esquerra i combinació externa dreta per veure la diferència.
La principal diferència entre la combinació esquerra i la combinació dreta es troba en la inclusió de files no coincidents. La combinació exterior esquerra inclou les files sense correspondència de la taula que es troba a l’esquerra de la clàusula de combinació, mentre que la combinació externa dreta inclou les files sense correspondència de la taula que es troba a la dreta de la clàusula de combinació.
La gent pregunta què és millor utilitzar, és a dir, unió esquerra o unió dreta? Bàsicament, són el mateix tipus d’operacions excepte amb els seus arguments invertits. Per tant, quan pregunteu quina unió cal utilitzar, realment esteu preguntant si escriviu un a. És només una qüestió de preferència.
En general, les persones prefereixen fer servir Left join a la consulta SQL. Us suggeriria que us mantinguéssiu coherent en la forma en què escriviu la consulta per evitar confusions en la interpretació de la consulta.
Fins ara hem vist tot sobre les unions interiors i tot tipus de combinacions exteriors. Resumim ràpidament la diferència entre unió interna i unió exterior.
Diferència entre unió interior i unió exterior en format tabular
| Combinació interna | Unió exterior |
|---|---|
| Retorna només les files que tenen valors coincidents a les dues taules. | Inclou les files coincidents i algunes de les files que no coincideixen entre les dues taules. |
| En cas que hi hagi un gran nombre de files a les taules i hi hagi un índex per utilitzar, INNER JOIN és generalment més ràpid que OUTER JOIN. | En general, un OUTER JOIN és més lent que un INNER JOIN, ja que ha de retornar més nombre de registres en comparació amb INNER JOIN. No obstant això, pot haver-hi alguns escenaris específics en què OUTER JOIN sigui més ràpid. |
| Quan no es troba una coincidència, no retorna res. | Quan no es troba una coincidència, es col·loca un valor NULL al valor de la columna retornat. |
| Utilitzeu INNER JOIN quan vulgueu consultar informació detallada de qualsevol columna específica. | Utilitzeu OUTER JOIN quan vulgueu mostrar la llista de tota la informació a les dues taules. |
| INNER JOIN actua com un filtre. Hi ha d’haver una coincidència a les dues taules perquè una unió interna retorni dades. | Actuen com a complements de dades. |
| Existeix una notació implícita d'unió per a unió interior que registra les taules que s'uneixen de manera separada per comes a la clàusula FROM. Exemple: SELECCIONA * DEL producte, categoria WHERE product.CategoryID = category.CategoryID; | No hi ha cap notació implícita d'unió per a unió externa. |
Unió interior i exterior contra unió
De vegades, confonem Join i Union, i aquesta és també una de les preguntes més freqüents a Entrevistes SQL . Ja hem vist la diferència entre unió interior i unió exterior. Ara, vegem com un JOIN és diferent d’un UNION.
UNION col·loca una línia de consultes una darrere l’altra, mentre que join crea un producte cartesià i el subconjunta. Per tant, UNION i JOIN són operacions completament diferents.
Executem les dues consultes següents a MySQL i vegem el seu resultat.
Consulta UNION:
SELECT 28 AS bah UNION SELECT 35 AS bah; Resultat:
| Bah | |
|---|---|
| 1 | 28 |
| 2 | 35 |
UNEIX-TE a la consulta:
SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS bar ON (55=55); Resultat:
| foo | Bar | |
|---|---|---|
| 1 | 38 | 35 |
Una operació UNION posa el resultat de dues o més consultes en un conjunt de resultats únic. Aquest conjunt de resultats conté tots els registres que es retornen a través de totes les consultes relacionades amb UNION. Així, bàsicament, una UNIÓ combina els dos conjunts de resultats.
Una operació de combinació obté dades de dues o més taules en funció de les relacions lògiques entre aquestes taules, és a dir, basades en la condició de combinació. A la consulta d'unió, les dades d'una taula s'utilitzen per seleccionar registres d'una altra taula. Us permet enllaçar dades similars que hi ha a diferents taules.
Per entendre-ho de forma molt senzilla, podeu dir que una UNION combina files de dues taules mentre que una unió combina columnes de dues o més taules. Per tant, tots dos s’utilitzen per combinar les dades de n taules, però la diferència es basa en com es combinen les dades.
A continuació es mostren les representacions pictòriques d’UNION i JOIN.
L'anterior és una representació pictòrica d'una operació d'unió que mostra que cada registre del conjunt de resultats conté columnes de les taules, és a dir, de la taula A i de la taula B. Aquest resultat es torna en funció de la condició d'unió aplicada a la consulta.
Una unió és generalment el resultat de la desnormalització (oposada a la normalització) i utilitza la clau externa d’una taula per buscar els valors de les columnes emprant la clau primària en una altra taula.
L'anterior és una representació pictòrica d'una operació UNION que representa que cada registre del conjunt de resultats és una fila de qualsevol de les dues taules. Per tant, el resultat de la UNIÓ ha combinat les files de la taula A i la taula B.
Més lectura = >> MySQL UNION s'explica amb exemples
Conclusió
En aquest article, hem vist les principals diferències entre Unió interior i unió externa a SQL . També vam veure la classificació d’una unió exterior, és a dir, unió esquerra, unió dreta i unió completa. Hem vist com funcionen cadascun d’aquests tipus d’unions i com varien entre ells.
També hem fet una comparació del rendiment entre aquests tipus d’unions. També hem debatut sobre com és diferent la participació d'un sindicat.
Llegiu també = >> Tipus d'unió MySQL
Espero que aquest article us hagi ajudat a esborrar els vostres dubtes sobre les diferències entre els diversos tipus d’unions. Estem segurs que, efectivament, això us farà decidir quin tipus d'unió triar en funció del conjunt de resultats desitjat.
Lectura recomanada
- Diferència exacta entre verificació i validació amb exemples
- Mòdem Vs Router: coneixeu la diferència exacta
- Diferència entre SQL vs MySQL vs SQL Server (amb exemples)
- Tutorial de Python DateTime amb exemples
- LAN Vs WAN VS MAN: Diferència exacta entre els tipus de xarxa
- Talla l'ordre a Unix amb exemples
- Sintaxi d'ordres Unix Cat, opcions amb exemples
- Ús del cursor a MongoDB amb exemples