¿Cómo hacer una UNIÓN EXTERIOR COMPLETA en MySQL?

Te sugerimos que pruebes esta solución en un entorno controlado antes de enviarlo a producción, un saludo.

Solución:

No tiene FULL JOINS en MySQL, pero puede emularlos.

Para una MUESTRA de código transcrita de esta pregunta SO, tiene:

con dos tablas t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

---

La consulta anterior funciona para casos especiales en los que una operación FULL OUTER JOIN no produciría filas duplicadas. La consulta anterior depende de la UNION establecer operador para eliminar filas duplicadas introducidas por el patrón de consulta. Podemos evitar la introducción de filas duplicadas utilizando un anti-unión patrón para la segunda consulta, y luego use un operador de conjunto UNION ALL para combinar los dos conjuntos. En el caso más general, donde una FULL OUTER JOIN devolvería filas duplicadas, podemos hacer esto:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL

La respuesta que dio Pablo Santa Cruz es correcta; sin embargo, en caso de que alguien haya tropezado con esta página y quiera más aclaraciones, aquí hay un desglose detallado.

Tablas de ejemplo

Supongamos que tenemos las siguientes tablas:

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

Uniones internas

Una unión interna, como esta:

SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

Nos obtendría solo los registros que aparecen en ambas tablas, así:

1 Tim  1 Tim

Las combinaciones internas no tienen una dirección (como izquierda o derecha) porque son explícitamente bidireccionales; necesitamos una coincidencia en ambos lados.

Uniones externas

Las combinaciones externas, por otro lado, son para encontrar registros que pueden no tener una coincidencia en la otra tabla. Como tal, debe especificar qué lado de la combinación puede tener un registro faltante.

LEFT JOIN y RIGHT JOIN son la abreviatura de LEFT OUTER JOIN y RIGHT OUTER JOIN; Usaré sus nombres completos a continuación para reforzar el concepto de uniones externas frente a uniones internas.

Izquierda combinación externa

Una combinación externa izquierda, como esta:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

… obtendríamos todos los registros de la tabla de la izquierda independientemente de si tienen o no una coincidencia en la tabla de la derecha, así:

1 Tim   1    Tim
2 Marta NULL NULL

Unión exterior derecha

Una combinación externa derecha, como esta:

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

… obtendríamos todos los registros de la tabla de la derecha independientemente de si tienen o no una coincidencia en la tabla de la izquierda, así:

1    Tim   1  Tim
NULL NULL  3  Katarina

Unión externa completa

Una combinación externa completa nos daría todos los registros de ambas tablas, tengan o no coincidencia en la otra tabla, con NULL en ambos lados donde no hay coincidencia. El resultado se vería así:

1    Tim   1    Tim
2    Marta NULL NULL
NULL NULL  3    Katarina

Sin embargo, como señaló Pablo Santa Cruz, MySQL no es compatible con esto. Podemos emularlo haciendo una UNION de una combinación izquierda y una combinación derecha, como esta:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

Puedes pensar en un UNION en el sentido de “ejecutar ambas consultas, luego apilar los resultados uno encima del otro”; algunas de las filas provendrán de la primera consulta y algunas de la segunda.

Cabe señalar que un UNION en MySQL eliminará los duplicados exactos: Tim aparecería en ambas consultas aquí, pero el resultado de la UNION solo lo enumera una vez. Mi colega gurú de la base de datos cree que no se debe confiar en este comportamiento. Entonces, para ser más explícitos al respecto, podríamos agregar un WHERE cláusula a la segunda consulta:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`id` IS NULL;

Por otro lado, si querido para ver duplicados por alguna razón, puede usar UNION ALL.

Usando un union La consulta eliminará los duplicados, y esto es diferente al comportamiento de full outer join que nunca elimina ningún duplicado:

[Table: t1]                            [Table: t2]
value                                  value
-------                                -------
1                                      1
2                                      2
4                                      2
4                                      5

Este es el resultado esperado de full outer join:

value | value
------+-------
1     | 1
2     | 2
2     | 2
Null  | 5
4     | Null
4     | Null

Este es el resultado de usar left y right Join con union:

value | value
------+-------
Null  | 5 
1     | 1
2     | 2
4     | Null

[SQL Fiddle]

Mi consulta sugerida es:

select 
    t1.value, t2.value
from t1 
left outer join t2  
  on t1.value = t2.value
union all      -- Using `union all` instead of `union`
select 
    t1.value, t2.value
from t2 
left outer join t1 
  on t1.value = t2.value
where 
    t1.value IS NULL 

Resultado de la consulta anterior que es el mismo que el resultado esperado:

value | value
------+-------
1     | 1
2     | 2
2     | 2
4     | NULL
4     | NULL
NULL  | 5

[SQL Fiddle]

---

@ Steve Chambers: ^{[From comments, with many thanks!]}
Nota: Ésta puede ser la mejor solución, tanto por eficiencia como por generar los mismos resultados que un FULL OUTER JOIN. Esta publicación de blog también lo explica bien, para citar el Método 2: “Esto maneja filas duplicadas correctamente y no incluye nada que no debería. Es necesario usar UNION ALL en lugar de simple UNION, lo que eliminaría los duplicados que quiero conservar. Esto puede ser significativamente más eficiente en conjuntos de resultados grandes, ya que no es necesario ordenar y eliminar duplicados “.

---

Decidí agregar otra solución que proviene de full outer join visualización y matemáticas, no es mejor que el anterior pero más legible:

Medios de unión externa completa (t1 ∪ t2): todo dentro t1 o en t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only: todo en ambos t1 y t2 más todo en t1 que no estan en t2 y más todo en t2 que no estan en t1:

-- (t1 ∩ t2): all in both t1 and t2
select t1.value, t2.value
from t1 join t2 on t1.value = t2.value    
union all  -- And plus 
-- all in t1 that not exists in t2
select t1.value, null
from t1
where not exists( select 1 from t2 where t2.value = t1.value)    
union all  -- and plus
-- all in t2 that not exists in t1
select null, t2.value
from t2
where not exists( select 1 from t1 where t2.value = t1.value)

[SQL Fiddle]

Si para ti ha resultado de ayuda nuestro post, sería de mucha ayuda si lo compartes con otros seniors así contrubuyes a extender este contenido.

---

---

Utiliza Nuestro Buscador