inner - sql avanzado ejercicios

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

Question

Quiero hacer una unión externa completa en MySQL. es posible? ¿Es compatible una unión externa completa con MySQL?

Answer 1

¿Qué dijiste acerca de la solución de unión cruzada ?

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

Answer 2

Arreglo la respuesta y los trabajos incluyen todas las filas (según 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
);

Answer 3

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

Es decir, left join on union all filas, union all right join on filas que no están en inner join on . O si sabe que su inner join on resultado no puede tener un valor nulo en una columna de la 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 activada extendida and esa columna is null .

De manera similar, right join on union all unidas a la left join on filas.

De ¿Cuál es la diferencia entre “UNIÓN INTERNA” y “UNIÓN EXTERNA”? :

(SQL Standard 2006 SQL / Foundation 7.7 Reglas de sintaxis 1, Reglas generales 1 b, 3 c y d, 5 b.)

Answer 4

El uso de una consulta de union eliminará los duplicados, y esto es diferente del 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 del uso de la union 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]

: ^{[De los comentarios, ¡muchas gracias!]}
Nota: Esta puede ser la mejor solución, tanto para la eficiencia como para generar los mismos resultados que una FULL OUTER JOIN . Esta publicación del blog también lo explica bien, para citar del Método 2: "Maneja las filas duplicadas correctamente y no incluye nada que no deba. Es necesario usar UNION ALL lugar de UNION simple, lo que eliminaría los duplicados que quiero. Esto puede ser significativamente más eficiente en grandes conjuntos de resultados, ya que no hay necesidad de ordenar y eliminar duplicados ".

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

Medios de unión externa completa (t1 ∪ t2) : todos en t1 o en t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only : todos en t1 y t2 más todos en t1 que no están en t2 y más 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]

Answer 5

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

Answer 6

La respuesta que dio Pablo Santa Cruz es correcta; sin embargo, en caso de que alguien haya tropezado en esta página y desee 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 uniones internas no tienen una dirección (como izquierda o derecha) porque son explícitamente bidireccionales; requerimos una coincidencia en ambos lados.

Uniones externas

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

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

Izquierda combinación externa

Una unión externa izquierda, como esta:

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

... nos conseguiría todos los registros de la tabla izquierda, independientemente de si tienen o no una coincidencia en la tabla derecha, como esto:

1 Tim 1 Tim
2 Marta NULL NULL

Unión externa derecha

Una unión externa derecha, como esta:

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

... nos obtendría todos los registros de la tabla derecha, independientemente de si tienen o no una coincidencia en la tabla izquierda, como esto:

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 una coincidencia en la otra tabla, con NULL en ambos lados donde no haya 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 admite esto. Podemos emularlo haciendo una UNION de una unión izquierda y una combinación derecha, como esto:

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 una UNION significa "ejecutar ambas consultas, luego apilar los resultados uno encima del otro"; algunas de las filas provendrán de la primera consulta y otras de la segunda.

Se debe tener en cuenta que una UNION en MySQL eliminará los duplicados exactos: Tim aparecería en ambas consultas aquí, pero el resultado de la UNION solo lo menciona una vez. Mi colega de gurú de la base de datos siente que este comportamiento no debe ser invocado. Para ser más explícitos 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 quisiera ver duplicados por alguna razón, podría usar UNION ALL .

Answer 7

MySql no tiene sintaxis FULL-OUTER-JOIN. Tienes que emular haciendo tanto LEFT JOIN como RIGHT JOIN 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 RIGHT JOIN. De acuerdo con la simplificación de la combinación externa de MySql , la combinación derecha se convierte en la combinación izquierda equivalente al cambiar t1 y t2 en las cláusulas FROM y ON de la consulta. Por lo tanto, MySql Query Optimizer traduce la consulta original a lo 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 ningún daño en escribir la consulta original tal como está, pero diga si tiene predicados como la cláusula WHERE, que es un predicado before-join o un predicado AND en la cláusula ON , que es un predicado during-join , entonces es posible que desee echar un vistazo al diablo; que está en los detalles.

El optimizador de consultas MySql verifica rutinariamente los predicados si son rechazados nulos . Ahora, si ha realizado la JUNTA DERECHA, pero con el predicado WHERE en la columna de t1, puede correr el riesgo de encontrarse en un escenario de rechazo nulo .

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 al 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''

Entonces, el orden de las tablas ha cambiado, pero el predicado aún se aplica a t1, pero t1 ahora está en la cláusula ''ON''. Si t1.col1 se define como una columna NOT NULL NULA, entonces esta consulta será rechazada nulo .

Cualquier unión externa (izquierda, derecha, completa) que se rechace nulo se convierte en una unión interna mediante MySql.

Por lo tanto, los resultados que podría estar esperando podrían ser completamente diferentes de lo que está devolviendo MySql. Podrías pensar que es un error con el RIGHT JOIN de MySql, pero eso no está bien. Es justo 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.

Answer 8

Mysql como tal no admite ningún comando llamado FULL OUTER JOIN. Las tres combinaciones admitidas son INNER JOIN, LEFT JOIN y RIGHT JOIN.

Sin embargo, puede implementar la unión externa completa utilizando el comando UNION como
(consulta de combinación izquierda) UNION (consulta de combinación derecha)

Por ejemplo, considere el siguiente ejemplo donde tengo dos tablas, estudiantes y marcas. Para realizar una 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;

Answer 9

Ninguna de las respuestas anteriores es realmente correcta, ya que no siguen la semántica cuando hay valores duplicados.

Para una consulta como (a partir de este duplicate ):

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 seguro NULL , <=> lugar de = .

Answer 10

No tienes FULL JOINS en MySQL, pero seguro que puedes emularlos .

Para un código SAMPLE transcrito de esta pregunta SO, usted 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á ninguna fila duplicada. La consulta anterior depende del operador de configuración UNION para eliminar filas duplicadas introducidas por el patrón de consulta. Podemos evitar la introducción de filas duplicadas utilizando un patrón anti-join para la segunda consulta, y luego usar un operador de conjunto UNION ALL para combinar los dos conjuntos. En el caso más general, donde una COMPLETO EXTERIOR COMPLETO 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

Answer 11

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

Answer 12

Se modificó la consulta de shA.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

Answer 13

También es posible, pero tiene que mencionar los mismos nombres de campo en seleccionar.

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

Answer 14

Responder:

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 UNIÓN TODO no cubre el caso de borde en el que las tablas base tienen entradas duplicadas.

Explicación:

Hay un caso de borde que un UNION o UNION ALL no puede cubrir. No podemos probar esto en mysql ya que no admite FULL OUTER JOINs, pero podemos ilustrarlo en una base de datos que sí lo admite:

 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 solución de UNION:

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 solución UNION ALL:

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.

Answer 15

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