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

La respuesta que Pablo Santa Cruz dio es correcta; sin embargo, en caso de que alguien se topara con esta página y quisiera 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

Interior Une

Una unión interior, así:

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

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

1 Tim  1 Tim

Las uniones internas no tienen una dirección (como izquierda o derecha) porque son explícitamente bidireccionales-requerimos un partido en ambos lados.

Unión exterior

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

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

Exterior izquierdo Unirse

Una unión exterior izquierda, así:

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

...nos conseguiría 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 unión exterior derecha, así:

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

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

1    Tim   1  Tim
NULL NULL  3  Katarina

Unión exterior completa

Una unión exterior completa nos daría todos los registros de ambas tablas, tengan o no una coincidencia en la otra tabla, con NULOs 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 Pablo Santa Cruz señaló, MySQL no soporta esto. Podemos emularlo haciendo una UNIÓN de un join de izquierda y un join de derecha, así:

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`;

Puede pensar que un UNION significa "ejecute ambas consultas, luego apile los resultados una encima de la otra"; algunas de las filas vendrá de la primera consulta y algunos de la segunda.

Debe tenerse en cuenta que un UNION en MySQL eliminará duplicados exactos: Tim aparecería en ambas consultas aquí, pero el resultado de la UNION solo lo lista una vez. Mi colega gurú de la base de datos siente que no se debe confiar en este comportamiento. Así que para ser más explícito al respecto, podríamos agregar una cláusula WHERE 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 quería ver duplicados para algunos razón, podrías usar UNION ALL.

El uso de una consulta union eliminará 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 pregunta 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 se espera resultado:

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

[SQL Fiddle]

@Steve Chambers: ^{[De los comentarios, con muchas gracias!]}
Nota: Esta puede ser la mejor solución, tanto para la eficiencia como para generar los mismos resultados que un FULL OUTER JOIN. Esta entrada de blog también lo explica bien-citando el Método 2: " Esto maneja correctamente las filas duplicadas y no incluye nada que no debería. Es necesario usar UNION ALL en lugar de 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 hay necesidad de ordenar y eliminar duplicados."

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

Full outer join significa (t1 ∪ t2): todos en t1 o en t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only: todos en tanto t1 y t2, además de todos en t1 que no están en t2 y más todos en t2 que no están 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]

En SQLite debes hacer esto:

SELECT * 
FROM leftTable lt 
LEFT JOIN rightTable rt ON lt.id = rt.lrid 
UNION
SELECT lt.*, rl.*  -- To match column set
FROM rightTable rt 
LEFT JOIN  leftTable lt ON lt.id = rt.lrid

Ninguna de las respuestas anteriores son realmente correctas, porque no siguen la semántica cuando hay valores duplicados.

Para una consulta como (de este duplicado):

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.Name = t2.Name;

El equivalente correcto es:

SELECT t1.*, t2.*
FROM (SELECT name FROM t1 UNION  -- This is intentionally UNION to remove duplicates
      SELECT name FROM t2
     ) n LEFT JOIN
     t1
     ON t1.name = n.name LEFT JOIN
     t2
     ON t2.name = n.name;

Si necesita que esto funcione con valores NULL (que también pueden ser necesarios), use el operador de comparación NULL-safe, <=> en lugar de =.

SHA modificado.consulta de t para mayor claridad:

-- t1 left join t2
SELECT t1.value, t2.value
FROM t1 LEFT JOIN t2 ON t1.value = t2.value   

    UNION ALL -- include duplicates

-- t1 right exclude join t2 (records found only in t2)
SELECT t1.value, t2.value
FROM t1 RIGHT JOIN t2 ON t1.value = t2.value
WHERE t2.value IS NULL 

MySQL no tiene sintaxis FULL-OUTER-JOIN. Tienes que emular haciendo tanto la UNIÓN IZQUIERDA como la UNIÓN DERECHA de la siguiente manera -

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

Pero MySQL tampoco tiene una sintaxis de UNIÓN CORRECTA. De acuerdo con la simplificación de la unión externa de MySQL, la unión derecha se convierte en la unión izquierda equivalente cambiando la t1 y t2 en la cláusula FROM y ON en la consulta. Por lo tanto, el Optimizador de consultas MySQL traduce la consulta original en la siguiente -

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

Ahora, no hay daño en la escritura de la consulta original como es, pero decir si usted tiene predicados como la cláusula WHERE, que es un antes de unirse predicado o an Y predicado en la cláusula ON, que es un durante-unirse predicado, entonces es posible que desee echar un vistazo al diablo; que está en detalles.

MySQL query optimizer comprueba rutinariamente los predicados si son null-rejected. Ahora, si has hecho lo CORRECTO ÚNETE, pero con DÓNDE predicado en la columna de t1, entonces usted podría estar en un riesgo de encontrarse con un null-rejected escenario.

Por ejemplo, La siguiente consulta -

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'

Se traduce a lo siguiente por el Optimizador de consultas -

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id
WHERE t1.col1 = 'someValue'

Así que el orden de las tablas ha cambiado, pero el predicado todavía se aplica a t1, pero t1 ahora está en la cláusula 'ON'. Si t1.col1 se define como NOT NULL columna, entonces esta consulta será null-rejected.

Cualquier unión externa (izquierda, derecha, full) que es null-rejected se convierte en una unión interna por MySQL.

Por lo tanto, los resultados que podría estar esperando podrían ser completamente diferentes de lo que devuelve MySQL. Usted podría pensar que es un error con MySQL DERECHO UNIRSE, pero eso no es correcto. Es exactamente cómo funciona el optimizador de consultas MySQL. Así que el desarrollador a cargo tiene que prestar atención a estos matices cuando está construyendo la consulta.

Puedes hacer lo siguiente:

(SELECT 
    *
FROM
    table1 t1
        LEFT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t2.id IS NULL)
UNION ALL
 (SELECT 
    *
FROM
    table1 t1
        RIGHT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t1.id IS NULL);

SELECT
    a.name,
    b.title
FROM
    author AS a
LEFT JOIN
    book AS b
    ON a.id = b.author_id
UNION
SELECT
    a.name,
    b.title
FROM
    author AS a
RIGHT JOIN
    book AS b
    ON a.id = b.author_id

¿Qué dijiste sobre la solución Cross join?

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 
ON 1=1;

También es posible, pero debe mencionar los mismos nombres de campo en select.

SELECT t1.name, t2.name FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT t1.name, t2.name FROM t2
LEFT JOIN t1 ON t1.id = t2.id

Mysql como tal no soporta ningún comando llamado FULL OUTER JOIN. Las tres uniones soportadas son UNIÓN INTERIOR,UNIÓN IZQUIERDA Y UNIÓN DERECHA.

Sin embargo, puede implementar una unión externa completa utilizando la UNIÓN de comandos como
(left join query) UNION (right join query)

Por ejemplo, considere el siguiente ejemplo donde tengo dos tablas estudiantes y notas. Para realizar la unión externa completa, ejecutaría el siguiente código:

SELECT * FROM students  
LEFT JOIN marks   
ON students.id = marks.id  
UNION ALL  
SELECT * FROM students 
RIGHT JOIN marks  
ON students.id = marks.id;

Arreglo la respuesta, y los trabajos incluyen todas las filas (basado en la respuesta de Pavle Lekic)

    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    WHERE b.`key` is null
    )
    UNION ALL
    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    where  a.`key` = b.`key`
    )
    UNION ALL
    (
    SELECT b.* FROM tablea a
    right JOIN tableb b ON b.`key` = a.key
    WHERE a.`key` is null
    );

Respuesta:

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

Se puede recrear de la siguiente manera:

 SELECT t1.*, t2.* 
 FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
 LEFT JOIN t1 ON t1.id = tmp.id
 LEFT JOIN t2 ON t2.id = tmp.id;

El uso de una respuesta UNION o UNION ALL no cubre el caso edge donde las tablas base tienen entradas duplicadas.

Explicación:

Hay un caso límite que una UNIÓN o UNIÓN NO pueden cubrir. No podemos probar esto en mysql ya que no soporta uniones EXTERNAS COMPLETAS, pero podemos ilustrar esto en una base de datos que sí lo soporta:

 WITH cte_t1 AS
 (
       SELECT 1 AS id1
       UNION ALL SELECT 2
       UNION ALL SELECT 5
       UNION ALL SELECT 6
       UNION ALL SELECT 6
 ),
cte_t2 AS
(
      SELECT 3 AS id2
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 6
)
SELECT  *  FROM  cte_t1 t1 FULL OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2;

This gives us this answer:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

La UNIÓN solución:

SELECT  * FROM  cte_t1 t1 LEFT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
UNION    
SELECT  * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

Da una respuesta incorrecta:

 id1  id2
NULL  3
NULL  4
1  NULL
2  NULL
5  5
6  6

La UNIÓN TODA la solución:

SELECT  * FROM cte_t1 t1 LEFT OUTER join cte_t2 t2 ON t1.id1 = t2.id2
UNION ALL
SELECT  * FROM  cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

También Es incorrecto.

id1  id2
1  NULL
2  NULL
5  5
6  6
6  6
6  6
6  6
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

Considerando que esta consulta:

SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp 
LEFT JOIN t1 ON t1.id = tmp.id 
LEFT JOIN t2 ON t2.id = tmp.id;

Da lo siguiente:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

El orden es diferente, pero por lo demás coincide con la respuesta correcta.

El estándar SQL dice full join on es inner join on filas union all filas de tabla izquierda sin igual extendidas por nulos union all filas de tabla derecha extendidas por nulos. Es decir inner join on filas union all filas en left join on pero no inner join on union all filas de right join on pero no inner join on.

Ie left join on filas union all right join on filas que no están en inner join on. O si sabe que su resultado inner join on no puede tener null en una columna de tabla derecha en particular, entonces "right join on filas que no están en inner join on" son filas en right join on con la condición on extendida por and esa columna is null.

Ie del mismo modo right join on union all filas left join on apropiadas.

De ¿Cuál es la diferencia entre "UNIÓN INTERIOR" y "UNIÓN EXTERIOR"?:

(SQL Standard 2006 SQL / Foundation 7.7 Syntax Rules 1, General Rules 1 b, 3 c & d, 5 b.)