right - outer join sql server ejemplos




¿Cuál es la diferencia entre “INNER JOIN” y “OUTER JOIN”? (20)

¿Cuál es la diferencia entre “INNER JOIN” y “OUTER JOIN”?

Son los operadores existenciales más utilizados en SQL, donde INNER JOINse usa para 'existe' y LEFT OUTER JOINse usa para 'no existe'.

Considere estas consultas:

users who have posted and have votes
users who have posted but have no badges

Las personas que buscan soluciones basadas en conjuntos (un término de la industria) reconocerían las consultas respectivas como:

users who have posted INTERSECT users who have votes
users who have posted MINUS users who have badges

Traducir estos a SQL estándar:

SELECT UserId FROM Posts
INTERSECT 
SELECT UserId FROM Votes;

SELECT UserId FROM Posts
EXCEPT 
SELECT UserId FROM Badges;

Otros pensarán en líneas similares de inclusión de conjuntos:

users who have posted and IN the set of users who have votes
users who have posted and NOT IN the set of users who have badges

Traducir estos a SQL estándar:

SELECT UserId 
  FROM Posts
 WHERE UserId IN ( SELECT UserId FROM Votes );

SELECT UserId 
  FROM Posts
 WHERE UserId NOT IN ( SELECT UserId FROM Badges );

Algunos pensarán en términos de 'existencia' dentro de conjuntos, por ejemplo,

users who have posted and EXIST in the set of users who have votes
users who have posted and do NOT EXIST in the set of users who have badges

Traducirlos en SQL estándar (en cuenta que ahora tenemos que utilizar variables de rango es decir p, v, b):

SELECT p.UserId 
  FROM Posts p
 WHERE EXISTS ( SELECT *
                  FROM Votes v
                 WHERE v.UserId = p.UserId );

SELECT p.UserId 
  FROM Posts p
 WHERE NOT EXISTS ( SELECT *
                      FROM Badges b
                     WHERE b.UserId = p.UserId );

Sin embargo, he encontrado que el enfoque "estándar de la industria" es utilizar uniones exclusivamente. No sé cuál es el pensamiento aquí (¿ Ley del instrumento ? ¿ Optimización prematura ?), Así que pasaré directamente a la sintaxis:

SELECT p.UserId 
  FROM Posts p
       INNER JOIN Votes v ON v.UserId = p.UserId;

SELECT p.UserId 
  FROM Posts p
       LEFT JOIN Badges b ON b.UserId = p.UserId
 WHERE b.UserId IS NULL;

Cosas a tener en cuenta:

  • La única proyección es de Users, pero todavía necesitamos todas esas variables de rango ( p, v, b) para condiciones de búsqueda.
  • La UserId IS NULLcondición de búsqueda 'pertenece' a la OUTER JOINpero se desconecta en la consulta.
  • LEFTes el estándar de la industria: los profesionales reescribirán una consulta para evitar el uso RIGHT.
  • La OUTERpalabra clave from LEFT OUTER JOINse omite.

Observaciones finales:

A veces, las combinaciones se utilizan en consultas únicamente para determinar si existen valores o no en otro conjunto. Aprenda a observar detenidamente los atributos que se proyectan (las columnas en la SELECTcláusula): si no hay ninguno de la tabla unida, simplemente se están utilizando como operadores existenciales. Además de la combinación externa, busque instancias de <key_column> IS NULLla WHEREcláusula.

Además, ¿cómo encajan LEFT JOIN , RIGHT JOIN y FULL JOIN ?


Unir internamente

Recuperar solo las filas coincidentes, es decir, A intersect B

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Izquierda combinación externa

Seleccione todos los registros de la primera tabla, y cualquier registro en la segunda tabla que coincida con las claves unidas.

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Unión externa completa

Seleccione todos los registros de la segunda tabla y cualquier registro en la primera tabla que coincida con las claves unidas.

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Referencias


En palabras simples:

Combinación interna -> Tome SOLO registros comunes de las tablas primarias y secundarias DONDE la clave primaria de la tabla principal coincide con la clave externa en la tabla secundaria.

Unirse a la izquierda ->

pseudo codigo

1.Take All records from left Table
2.for(each record in right table,) {
    if(Records from left & right table matching on primary & foreign key){
       use their values as it is as result of join at the right side for 2nd table.
    } else {
       put value NULL values in that particular record as result of join at the right side for 2nd table.
    }
  }

Unión derecha : exactamente lo contrario de la unión izquierda. Coloque el nombre de la tabla en LEFT JOIN en el lado derecho en Right join, obtendrá el mismo resultado que LEFT JOIN.

Unión externa : muestre todos los registros en ambas tablas No matter what . Si los registros en la tabla izquierda no coinciden con la tabla derecha basada en la clave principal, Forieign, use el valor NULL como resultado de la unión.

Ejemplo:

Asumamos ahora para 2 tablas

1.employees , 2.phone_numbers_employees

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id   

Aquí, la tabla de empleados es la tabla maestra, phone_numbers_employees es la tabla secundaria (contiene emp_id como clave externa que conecta employee.id y su tabla secundaria).

Uniones internas

Tome los registros de 2 tablas SOLAMENTE SI La clave principal de la tabla de empleados (su id) coincide con la clave externa de la tabla secundaria phone_numbers_employees (emp_id) .

Entonces la consulta sería:

SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Aquí tome solo las filas coincidentes en la clave principal = clave externa como se explicó anteriormente. Aquí las filas no coincidentes en la clave principal = clave externa se omiten como resultado de la unión.

La izquierda se une :

La combinación izquierda conserva todas las filas de la tabla izquierda, independientemente de si hay una fila que coincida en la tabla derecha.

SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Uniones externas :

SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Diagramáticamente se ve como:


Las combinaciones internas requieren que exista un registro con un ID relacionado en la tabla combinada.

Las combinaciones externas devolverán registros para el lado izquierdo incluso si no existe nada para el lado derecho.

Por ejemplo, usted tiene una tabla de pedidos y una de detalles de pedidos. Están relacionados por un "OrderID".

Pedidos

  • Solicitar ID
  • Nombre del cliente

OrderDetails

  • OrderDetailID
  • Solicitar ID
  • Nombre del producto
  • Cantidad
  • Precio

La solicitud

SELECT Orders.OrderID, Orders.CustomerName
  FROM Orders 
 INNER JOIN OrderDetails
    ON Orders.OrderID = OrderDetails.OrderID

solo devolverá pedidos que también tengan algo en la tabla OrderDetails.

Si lo cambias a UNO EXTERIOR IZQUIERDO

SELECT Orders.OrderID, Orders.CustomerName
  FROM Orders 
  LEFT JOIN OrderDetails
    ON Orders.OrderID = OrderDetails.OrderID

luego devolverá registros de la tabla Pedidos incluso si no tienen registros OrderDetails.

Puede usar esto para encontrar Pedidos que no tengan ningún Detalle de Pedido que indique un posible pedido huérfano agregando una cláusula donde WHERE OrderDetails.OrderID IS NULL .


Los diagramas de Venn realmente no lo hacen por mí.

No muestran ninguna distinción entre una combinación cruzada y una combinación interna, por ejemplo, o más generalmente muestran una distinción entre los diferentes tipos de predicados de unión o proporcionan un marco para razonar sobre cómo operarán.

No hay sustituto para entender el procesamiento lógico y es relativamente sencillo de entender de todos modos.

  1. Imagina una unión cruzada.
  2. Evalúe la cláusula on contra todas las filas del paso 1 manteniendo aquellas donde el predicado se evalúa como true
  3. (Solo para combinaciones externas) agregue de nuevo en las filas externas que se perdieron en el paso 2.

(NB: en la práctica, el optimizador de consultas puede encontrar formas más eficientes de ejecutar la consulta que la descripción puramente lógica anterior, pero el resultado final debe ser el mismo)

Comenzaré con una versión animada de una combinación externa completa . Sigue explicaciones adicionales.

Explicación

Tablas de fuentes

Primero comience con un CROSS JOIN (también conocido como producto cartesiano). Esto no tiene una cláusula ON y simplemente devuelve cada combinación de filas de las dos tablas.

SELECCIONE A.Color, B.Color de una unión cruzada B

Las uniones internas y externas tienen un predicado de cláusula "ON".

  • Unir internamente. Evalúe la condición en la cláusula "ON" para todas las filas en el resultado de la unión cruzada. Si es verdadero, devuelve la fila unida. De lo contrario descartarlo.
  • Izquierda combinación externa. Igual que la unión interna, entonces para cualquier fila en la tabla de la izquierda que no haya coincidido con nada, esto genera valores NULL para las columnas de la tabla de la derecha.
  • Unirse a la derecha Igual que la unión interna, entonces para cualquier fila en la tabla derecha que no coincidió con nada, esto arroja valores NULL para las columnas de la tabla izquierda.
  • Unión externa completa. Igual que la combinación interna, a continuación, mantenga las filas no coincidentes izquierdas como en la combinación externa izquierda y las filas no coincidentes derechas según la combinación externa derecha.

Algunos ejemplos

SELECCIONE A.Colour, B.Colour DESDE UNA JUNTA INTERNA B EN A.Colour = B.Colour

Lo anterior es el clásico equi join.

Versión animada

SELECCIONE A.Colour, B.Colour DESDE UNA JUNTA INTERNA B EN A.Colour NOT IN ('Green', 'Blue')

La condición de unión interna no tiene por qué ser necesariamente una condición de igualdad y no necesita hacer referencia a columnas de ambas tablas (o incluso de cualquiera de ellas). Evaluar A.Colour NOT IN ('Green','Blue') en cada fila de las declaraciones de combinaciones cruzadas.

SELECCIONE A.Color, B.Colour DESDE UNA JUNTA INTERNA B EN 1 = 1

La condición de unión se evalúa como verdadera para todas las filas en el resultado de la combinación cruzada, por lo que esto es lo mismo que una combinación cruzada. No repetiré de nuevo la imagen de las 16 filas.

SELECCIONE A.Colour, B.Colour DESDE UNA IZQUIERDA EXTERIOR IZQUIERDA B EN A.Colour = B.Colour

Las combinaciones externas se evalúan lógicamente de la misma manera que las combinaciones internas, excepto que si una fila de la tabla izquierda (para una combinación izquierda) no se une con ninguna fila de la tabla de la derecha, se conserva en el resultado con valores NULL para Las columnas de la derecha.

SELECCIONE A.Colour, B.Colour DESDE UNA IZQUIERDA EXTERIOR IZQUIERDA B EN A.Colour = B.Cour DONDE B.Colour is NULL

Esto simplemente restringe el resultado anterior para devolver solo las filas donde B.Colour IS NULL . En este caso particular, estas serán las filas que se conservaron, ya que no tenían coincidencia en la tabla de la derecha y la consulta devuelve la fila roja única que no coincide en la tabla B Esto se conoce como un anti semi unirse.

Es importante seleccionar una columna para la prueba IS NULL que no sea anulable o para la cual la condición de unión garantice que se excluyan todos los valores NULL para que este patrón funcione correctamente y evite simplemente volver a colocar las filas que tengan una Valor NULL para esa columna además de las filas no coincidentes.

SELECCIONE A.Colour, B.Colour DESDE UNA DERECHA JUNTA EXTERIOR B EN A.Colour = B.Colour

Las combinaciones externas derechas actúan de manera similar a las combinaciones externas izquierdas, excepto que conservan las filas no coincidentes de la tabla derecha y extienden las columnas de la izquierda.

SELECCIONE A.Colour, B.Colour DESDE UNA COMPLETACIÓN EXTERIOR COMPLETA B EN A.Colour = B.Colour

Las combinaciones externas completas combinan el comportamiento de las combinaciones izquierda y derecha y conservan las filas no coincidentes de las tablas izquierda y derecha.

SELECCIONE A.Colour, B.Colour DESDE UNA COMPLETACIÓN EXTERIOR COMPLETA B EN 1 = 0

Ninguna fila en la unión cruzada coincide con el predicado 1=0 . Todas las filas de ambos lados se conservan utilizando las reglas de unión externa normales con NULL en las columnas de la tabla en el otro lado.

SELECCIONE COALESCE (A.Colour, B.Colour) COMO COLOR DESDE UNA COMPLETACIÓN EXTERNA COMPLETA B ON 1 = 0

Con una pequeña modificación de la consulta anterior, se podría simular un UNION ALL de las dos tablas.

SELECCIONE A.Color, B.Color DESDE UNA IZQUIERDA EXTERIOR IZQUIERDA B EN A.Color = B.Color DONDE B.Color = 'Verde'

Tenga en cuenta que la cláusula WHERE (si está presente) se ejecuta lógicamente después de la unión. Un error común es realizar una combinación externa izquierda y luego incluir una cláusula WHERE con una condición en la tabla derecha que termina excluyendo las filas no coincidentes. Lo anterior termina realizando la unión externa ...

... Y luego se ejecuta la cláusula "dónde". NULL= 'Green' no se evalúa como verdadero, por lo que la fila conservada por la unión externa termina descartada (junto con la azul) convirtiendo la unión de nuevo en una interna.

Si la intención fuera incluir solo las filas de B, donde Color es verde y todas las filas de A, independientemente de la sintaxis correcta, sería

SELECCIONE A.Colour, B.Colour DESDE UNA IZQUIERDA EXTERIOR IZQUIERDA B EN A.Colour = B.Colour Y B.Color = 'Green'

SQL Fiddle

Vea estos ejemplos ejecutados en vivo en SQLFiddle.com .


Recomiendo el artículo del blog de Jeff . La mejor descripción que he visto, y además hay una visualización, por ejemplo:

Unir internamente:

Unión externa completa:



Una combinación interna solo muestra filas si hay un registro coincidente en el otro lado (derecho) de la combinación.

Una combinación externa (izquierda) muestra filas para cada registro en el lado izquierdo, incluso si no hay filas coincidentes en el otro lado (derecho) de la combinación. Si no hay una fila coincidente, las columnas del otro lado (derecha) mostrarán NULL.



INNER JOINrequiere que haya al menos una coincidencia en la comparación de las dos tablas. Por ejemplo, la tabla A y la tabla B que implica A ٨ B (A intersección B).

LEFT OUTER JOINy LEFT JOINson lo mismo. Da todos los registros que coinciden en ambas tablas y todas las posibilidades de la tabla izquierda.

Del mismo modo, RIGHT OUTER JOINy RIGHT JOINson los mismos. Da todos los registros que coinciden en ambas tablas y todas las posibilidades de la tabla correcta.

FULL JOINEs la combinación de LEFT OUTER JOINy RIGHT OUTER JOINsin duplicación.


Las combinaciones se utilizan para combinar los datos de dos tablas, con el resultado de ser una nueva tabla temporal. Las combinaciones se realizan en función de algo que se llama predicado, que especifica la condición que se debe utilizar para realizar una combinación. La diferencia entre una combinación interna y una externa es que una combinación interna solo devolverá las filas que realmente coincidan en función del predicado de la combinación. Consideremos la tabla de empleados y ubicación:

Unión interna: - La unión interna crea una nueva tabla de resultados al combinar valores de columna de dos tablas ( Empleado y Ubicación ) en función del predicado de unión. La consulta compara cada fila de Empleado con cada fila de Ubicación para encontrar todos los pares de filas que satisfacen el predicado de unión. Cuando se satisface el predicado de unión haciendo coincidir los valores no NULOS, los valores de columna para cada par de filas coincidentes de Empleado y Ubicación se combinan en una fila de resultados. Así es como se verá el SQL para una unión interna:

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

Ahora, aquí está cómo se vería el resultado de la ejecución de SQL:

Unión externa: - Una unión externa no requiere que cada registro en las dos tablas unidas tenga un registro coincidente. La tabla unida conserva cada registro, incluso si no existe otro registro coincidente. Las combinaciones externas se subdividen en combinaciones externas a la izquierda y combinaciones externas a la derecha, según las filas de la tabla que se mantengan (izquierda o derecha).

Unión externa izquierda: el resultado de una combinación externa izquierda (o simplemente combinación izquierda) para las tablas Empleado y Ubicación siempre contiene todos los registros de la tabla "izquierda" ( Empleado ), incluso si la condición de unión no encuentra ningún registro coincidente en la tabla "derecha" ( Ubicación ). Aquí es cómo se vería el SQL para una combinación externa izquierda, utilizando las tablas anteriores:

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Ahora, aquí está cómo se vería el resultado de ejecutar este SQL:

Unión externa derecha: - Una unión externa derecha (o unión derecha) se asemeja mucho a una unión externa izquierda, excepto con el tratamiento de las tablas invertido. Cada fila de la tabla "derecha" ( Ubicación ) aparecerá en la tabla unida al menos una vez. Si no existe una fila coincidente de la tabla "izquierda" ( Empleado ), aparecerá NULL en las columnas de Empleado para aquellos registros que no coincidan en la Ubicación . Así es como se ve el SQL:

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

Usando las tablas anteriores, podemos mostrar cómo se vería el conjunto de resultados de una unión externa derecha:

Uniones externas completas: - La unión externa completa o la unión completa es retener la información no coincidente mediante la inclusión de filas no coincidentes en los resultados de una unión, utilice una unión externa completa. Incluye todas las filas de ambas tablas, independientemente de si la otra tabla tiene o no un valor coincidente.

Fuente de imagen

MySQL 8.0 Reference Manual - Unirse a la sintaxis

Operaciones de unión de Oracle


UNIR INTERNAMENTE

Una unión interna produce un conjunto de resultados que está limitado a las filas donde hay una coincidencia en ambas tablas para lo que estamos buscando. Si no sabe qué tipo de unión necesita, esta será su mejor opción.

IZQUIERDA COMBINACIÓN EXTERNA

Una combinación externa izquierda, o combinación izquierda, da como resultado un conjunto donde se conservan todas las filas de la primera tabla, o la izquierda. Las filas de la segunda tabla, o del lado derecho, solo se muestran si coinciden con las filas de la primera tabla. Donde hay valores de la tabla de la izquierda pero no de la derecha, la tabla se leerá nula, lo que significa que el valor no se ha establecido.

JUSTE EXTERIOR DERECHO

Una combinación externa derecha, o combinación derecha, es lo mismo que una combinación izquierda, excepto que los roles se invierten. Todas las filas de la tabla del lado derecho se muestran en el resultado, pero las filas de la tabla de la izquierda solo aparecen si coinciden con la tabla de la derecha. Los espacios vacíos son nulos, al igual que con la combinación izquierda.

ÚNICAMENTE EN EL EXTERIOR

Una combinación externa completa, o simplemente una combinación externa, produce un conjunto de resultados con todas las filas de ambas tablas, independientemente de si hay coincidencias. De forma similar a las combinaciones izquierda y derecha, llamamos nulos a los espacios vacíos.

Para más Reference


Unir internamente.

Una unión es combinar las filas de dos tablas. Una combinación interna intenta hacer coincidir las dos tablas según los criterios que especifique en la consulta y solo devuelve las filas que coincidan. Si una fila de la primera tabla en la combinación coincide con dos filas en la segunda tabla, entonces se devolverán dos filas en los resultados. Si hay una fila en la primera tabla que no coincide con una fila en la segunda, no se devuelve; Del mismo modo, si hay una fila en la segunda tabla que no coincide con una fila en la primera, no se devuelve.

Unión externa.

Una combinación izquierda intenta encontrar coincidir las filas de la primera tabla con las filas de la segunda tabla. Si no puede encontrar una coincidencia, devolverá las columnas de la primera tabla y dejará las columnas de la segunda tabla en blanco (nulo).


En lenguaje sencillo,

1. INNER JOIN O EQUI JOIN: devuelve el conjunto de resultados que coincide solo con la condición en ambas tablas.

2. OUTER JOIN: devuelve el conjunto de resultados de todos los valores de ambas tablas, incluso si hay una coincidencia de condición o no.

3. LEFT JOIN: devuelve el conjunto de resultados de todos los valores de la tabla izquierda y solo las filas que coinciden con la condición en la tabla derecha.

4. JUNTA DERECHA: devuelve el conjunto de resultados de todos los valores de la tabla derecha y solo las filas que coinciden con la condición en la tabla izquierda.

5. UNIDAD COMPLETA: la combinación completa y la combinación externa completa son iguales.


La diferencia entre unión interna y unión externa es la siguiente:

  1. La combinación interna es una combinación que combina tablas basadas en tuplas coincidentes, mientras que la combinación externa es una combinación que combina tablas basadas tanto en tuplas combinadas como no coincidentes.
  2. La combinación interna combina la fila coincidente de dos tablas en donde se omiten las filas no coincidentes, mientras que la combinación externa combina las filas de dos tablas y las filas no coincidentes se llenan con valor nulo.
  3. La unión interna es como una operación de intersección, mientras que la unión externa es como una operación de unión.
  4. La unión interna es de dos tipos, mientras que la combinación externa es de tres tipos.
  5. La unión interna es más lenta, mientras que la combinación externa es más rápida que la unión interna.

La diferencia está en la forma en que se unen las tablas si no hay registros comunes.

JOINes igual que INNER JOINy significa mostrar solo los registros comunes a ambas tablas. Si los registros son comunes está determinado por los campos en la cláusula de unión.

Por ejemplo:

SELECT * 
FROM t1
JOIN t2 on t1.ID = t2.ID

Significa mostrar solo los registros donde IDexiste el mismo valor en ambas tablas.

LEFT JOINes igual que LEFT OUTER JOINy significa mostrar todos los registros de la tabla izquierda (es decir, el que precede en la declaración SQL) independientemente de la existencia de registros coincidentes en la tabla derecha.

RIGHT JOINes igual que RIGHT OUTER JOINy opuesto a LEFT JOIN, es decir, muestra todos los registros de la segunda tabla (derecha) y solo los registros coincidentes de la primera tabla (izquierda).


La respuesta está en el significado de cada uno, por lo que en los resultados.

Nota:
En SQLiteno hay RIGHT OUTER JOINni FULL OUTER JOIN.
Y también en que MySQLno hay FULL OUTER JOIN.

Mi respuesta se basa en la nota anterior .

Cuando tengas dos tablas como estas:

--[table1]               --[table2]
id | name                id | name
---+-------              ---+-------
1  | a1                  1  | a2
2  | b1                  3  | b2

CROSS JOIN / OUTER JOIN:
Puede tener todos esos datos de tablas con CROSS JOINo simplemente con ,esto:

SELECT * FROM table1, table2
--[OR]
SELECT * FROM table1 CROSS JOIN table2

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2
1  | a1   | 3  | b2
2  | b1   | 1  | a2
2  | b1   | 3  | b2

INNER JOIN:
cuando desee agregar un filtro a los resultados anteriores en función de una relación como la table1.id = table2.idque puede usar INNER JOIN:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
--[OR]
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2

IZQUIERDA [EXTERIOR] ÚNASE:
cuando desee tener todas las filas de una de las tablas en el resultado anterior, con la misma relación, puede usar LEFT JOIN:
(para la combinación correcta solo cambie la ubicación de las tablas)

SELECT * FROM table1, table2 WHERE table1.id = table2.id 
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
--[OR]
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id   | name 
---+------+------+------
1  | a1   | 1    | a2
2  | b1   | Null | Null

FULL OUTER JOIN:
cuando también desee tener todas las filas de la otra tabla en sus resultados, puede usar FULL OUTER JOIN:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
UNION ALL
SELECT Null, Null, * FROM table2 WHERE Not table2.id In (SELECT id FROM table1)
--[OR] (recommended for SQLite)
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION ALL
SELECT * FROM table2 LEFT JOIN table1 ON table2.id = table1.id
WHERE table1.id IS NULL
--[OR]
SELECT * FROM table1 FULL OUTER JOIN table2 On table1.id = table2.id

--[Results:]
id   | name | id   | name 
-----+------+------+------
1    | a1   | 1    | a2
2    | b1   | Null | Null
Null | Null | 3    | b2

Bueno, como su necesidad elija cada una que cubra su necesidad;).


No veo muchos detalles sobre el rendimiento y el optimizador en las otras respuestas.

A veces es bueno saber que solo INNER JOINes asociativo, lo que significa que el optimizador tiene la mejor opción para jugar con él. Puede reordenar la orden de unión para que sea más rápido manteniendo el mismo resultado. El optimizador puede utilizar la mayoría de los modos de combinación.

En general, es una buena práctica tratar de usar en INNER JOINlugar de los diferentes tipos de combinaciones. (Por supuesto, si es posible considerando el conjunto de resultados esperado).

Aquí hay un par de buenos ejemplos y explicaciones sobre este extraño comportamiento asociativo:


El algoritmo preciso para INNER JOIN, LEFT/RIGHT OUTER JOINson los siguientes:

  1. Tome cada fila de la primera tabla: a
  2. Considere todas las filas de la segunda tabla al lado: (a, b[i])
  3. Evalúe la ON ...cláusula contra cada par:ON( a, b[i] ) = true/false?
    • Cuando la condición se evalúe como true, devuelve esa fila combinada (a, b[i]).
    • Cuando extremo alcance de segunda mesa sin ningún partido, y esto es una Outer Joincontinuación, devolver un (virtual) par utilizando Nullpara todas las columnas de otra tabla: (a, Null)para externa izquierda o (Null, b)para externa derecha. Esto es para asegurar que todas las filas de la primera tabla existan en los resultados finales.

Nota: la condición especificada en la ONcláusula podría ser cualquier cosa, no es necesario usar claves primarias (y no es necesario que siempre haga referencia a las columnas de ambas tablas). Por ejemplo:

  • ... ON T1.title = T2.title AND T1.version < T2.version(=> vea esta publicación como un uso de muestra: seleccione solo filas con valor máximo en una columna )
  • ... ON T1.y IS NULL
  • ... ON 1 = 0 (solo como muestra)

Nota: Unión izquierda = Unión externa izquierda, Unión derecha = Unión externa derecha.


Se usa INNER JOINpara devolver todas las filas de ambas tablas donde hay una coincidencia. Es decir, en la tabla resultante todas las filas y columnas tendrán valores.

En OUTER JOINla tabla resultante puede haber columnas vacías. La unión externa puede ser LEFTo RIGHT.

LEFT OUTER JOIN devuelve todas las filas de la primera tabla, incluso si no hay coincidencias en la segunda tabla.

RIGHT OUTER JOIN devuelve todas las filas de la segunda tabla, incluso si no hay coincidencias en la primera tabla.







outer-join