tipos teoria right outer left inner ejemplos conjuntos sql mysqli visualization set-theory

teoria - sql se une como diagrama de venn



tipos de join mysql (5)

He tenido problemas para entender las uniones en sql y encontré esta imagen que creo que podría ayudarme. El problema es que no lo entiendo completamente. Por ejemplo, la unión en la esquina superior derecha de la imagen, que colorea el círculo rojo completo B pero solo la superposición de A. La imagen hace que parezca que el círculo B es el foco primario de la declaración sql, pero el enunciado sql en sí, al comenzar con A (seleccionar de A, unir B), me transmite la impresión opuesta, es decir, que A sería el foco de la declaración sql.

De manera similar, la imagen a continuación que solo incluye datos del círculo B, entonces, ¿por qué A está incluido en absoluto en la declaración conjunta?

Pregunta: Trabajando en el sentido de las agujas del reloj desde la esquina superior derecha y terminando en el centro, ¿alguien puede proporcionar más información sobre la representación de cada imagen sql, explicando

a) por qué sería necesaria una unión en cada caso (por ejemplo, especialmente en situaciones donde no se toman datos de A o B, es decir, donde solo A o B, pero no ambos, están coloreados)

b) y cualquier otro detalle que aclare por qué la imagen es una buena representación de sql

Cuando te unes, es probable que tus dos tablas no coincidan perfectamente. Específicamente, podría haber algunas filas en A que no coinciden con nada en B, o filas duplicadas en A que coincidan con una sola fila en B, y viceversa.

Cuando esto sucede, tienes una opción:

  1. para cada A, toma una única B que funcione, si hay una. (arriba a la izquierda)
  2. tomar cada par que coincida por completo (descartar cualquiera que le falte A o B - centro)
  3. para cada B, toma una sola A que funcione, si hay una (arriba a la derecha)
  4. tomar TODO (abajo a la izquierda)

Centro izquierda y derecha son técnicamente unidas, pero inútiles; probablemente podrían escribirse más eficientemente SELECT <select_list> FROM TableA A WHERE A.Key NOT IN (SELECT B.Key FROM TableB B) (o lo contrario).

En respuesta directa a su confusión, RIGHT JOIN dice "la siguiente expresión es el foco de esta consulta". La esquina inferior derecha es bastante extraña, y no veo ninguna razón por la que desees eso. Devuelve los resultados de las dos consultas externas centrales, mezcladas con NULL en todas las columnas de la tabla opuesta.

Estoy de acuerdo con Cade sobre las limitaciones de los diagramas de Venn aquí. Una representación visual más adecuada podría ser esta.

Mesas

SELECCIONE A.Colour, B.Colour FROM A CROSS JOIN B SQL Fiddle

La unión cruzada (o producto cartesiano) produce un resultado con cada combinación de las filas de las dos tablas. Cada tabla tiene 4 filas, por lo que esto produce 16 filas en el resultado.

SELECCIONE A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour SQL Fiddle

La combinación interna devuelve lógicamente todas las filas de la unión cruzada que coinciden con la condición de unión. En este caso, cinco do.

SELECCIONE A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN (''Verde'', ''Azul'') SQL Fiddle

La condición de unión interna no tiene que ser necesariamente una condición de igualdad y no necesita columnas de referencia de ambas (o incluso de ninguna) de las tablas. A.Colour NOT IN (''Green'',''Blue'') en cada fila de la unión cruzada.

Una condición de unión interna de 1=1 se evaluaría como verdadera para cada fila en la unión cruzada, por lo que las dos son equivalentes ( SQL Fiddle ).

SELECCIONE A.Colour, B.Colour DESDE UN EXTERIOR IZQUIERDO ÚNASE A B ON A.Color = B.Colour SQL Fiddle

Las combinaciones externas se evalúan lógicamente del mismo modo 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 derecha, se conserva en el resultado con valores NULL para las columnas de la derecha.

SELECCIONA A.Color, B.Color DESDE UN EXTERIOR IZQUIERDO ÚNASE A B ON A.Color = B.Color DONDE B.Color ES NULL SQL Fiddle

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

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

SELECCIONE A.Color, B.Color FROM A DIGHT OUTER JOIN B ON A.Color = B.Colour SQL Fiddle

Las uniones externas derechas actúan de forma similar a las uniones externas izquierdas, excepto que conservan filas que no coinciden de la tabla derecha y nulas extienden las columnas de la izquierda.

SELECCIONE A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour SQL Fiddle

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

Los diagramas de Venn son adecuados para representar operaciones de conjuntos como UNION, INTERSECTS, EXCEPT etc.

En la medida en que solo aquellas operaciones establecidas como EXCEPT se simulen con cosas como LEFT JOIN WHERE rhs.KEY es NULL, este diagrama es preciso.

De lo contrario, es engañoso. Por ejemplo, cualquier unión puede hacer que las filas se multipliquen si los criterios de unión no son 1: 1. Pero los conjuntos solo pueden contener miembros distintos, por lo que no se pueden representar como operaciones establecidas.

Luego está el CROSS JOIN o INNER JOIN ON 1 = 1 - esto no es análogo al INNER JOIN como se muestra en este diagrama, ni el conjunto que se produce puede ser descrito realmente por un diagrama de Venn. Por no mencionar todas las otras posibles combinaciones triangulares, self y anti-join como:

lhs INNER JOIN rhs ON rhs.VALUE < lhs.VALUE (triangular)

o

SELF self1 INNER JOIN SELF self2 ON self2.key <> self1.key AND self1.type = self2.type

(self cross y anti-join para encontrar todos los miembros de la familia similares, excepto usted mismo - self1 y self2 son el mismo conjunto y el resultado es un subconjunto adecuado)

Cumplir con las uniones en las teclas puede estar bien durante los primeros minutos de un tutorial, pero esto puede conducir a un mal camino para aprender de qué se trata la unión. Creo que esto es lo que has encontrado.

Esta idea de que Venn Diagrams puede representar JOINs generalmente de esta manera debe desaparecer.

Para la combinación correcta, sí, la sintaxis puede ser confusa, pero sí es lo que parece ser. Cuando dices "TableA RIGHT JOIN tableB", de hecho está diciendo que TableB es la tabla principal a la que te refieres y la TableA simplemente está colgada donde tiene registros que coinciden. Esto se lee raro en las consultas, porque TableA se enumera primero, por lo que su cerebro le asigna automáticamente más prioridad, a pesar de que TableB es realmente la tabla más importante en la consulta. Por este motivo, rara vez se ven las uniones correctas en el código real.

Entonces, en lugar de A y B, tomemos dos cosas que son fáciles de seguir. Supuesto que tenemos dos tablas para la información de la gente, ShoeSize y IQ. Tienes información de ShoeSize para algunas personas, alguna información de IQ para algunas personas. Y tenga un PersonID en ambas tablas al que pueda unirse.

En sentido horario desde la parte superior derecha (aunque esto comienza con algunos de los casos más complicados y artificiales):

  • ShoeSize RIGHT JOIN IQ -> dame toda la información de IQ. Incluya cualquier información de ShoeSize para esas personas si la tenemos.
  • ShoeSize DERECHA ÚNETE A IQ DONDE ShowSize.PersonID = NULL -> Dame toda la información de IQ, pero solo para personas que no tienen ninguna información de tamaño de calzado
  • ShoeSize FULL OUTER ÚNETE A IQ WHERE ShoeSize.PersonID = NULL AND IQ.PersonID = NULL -> Dame la información del tamaño del zapato solo para personas que no tienen información de IQ, además de la información de IQ para personas que no tienen información sobre el tamaño del zapato
  • ShoeSize FULL OUTER JOIN IQ -> Dame todo, todos los tamaños de calzado y todos los datos de IQ. Si alguno ShoeSizes y los registros de IQ tienen el mismo PersonID, inclúyalos en una fila.
  • ShoeSize IZQUIERDA ÚNASE A IQ DONDE IQ.PersonID = NULL -> Dame toda la información del tamaño del zapato, pero solo para personas que no tienen información de IQ
  • ShoeSize IZQUIERDA ÚNETE A IQ -> Dame toda la información del tamaño del zapato. Incluya cualquier información de cociente intelectual para esas personas si lo tenemos.

Creo que la principal confusión subyacente es que cuando (por ejemplo) solo A se resalta en rojo, significa que "la consulta solo devuelve datos de A ", pero de hecho significa que "la consulta solo devuelve datos para aquellos casos donde A tiene un registro ". La consulta aún puede contener datos de B. (Para casos donde B no tiene un registro, la consulta sustituirá a NULL ).

De manera similar, la imagen a continuación que solo incluye datos del círculo B, entonces, ¿por qué A está incluido en absoluto en la declaración conjunta?

Si te refieres a la imagen donde A está completamente en blanco, y hay una media luna roja para la parte de B que no se superpone con A , entonces: el motivo por el que A aparece en la consulta es, A es cómo lo encuentra los registros en B que necesitan ser excluidos. (Si A no aparecía en la consulta, entonces el diagrama de Venn no tendría A , solo mostraría B , y no habría manera de distinguir los registros deseados de los no deseados).

La imagen hace que parezca que el círculo B es el foco primario de la declaración sql, pero la declaración sql misma, al comenzar con A (seleccionar de A, unir B), me transmite la impresión opuesta, es decir, que A sería el foco de la declaración sql.

Muy bien. Por esta razón, RIGHT JOIN s son relativamente poco comunes; aunque una consulta que utiliza un LEFT JOIN casi siempre puede reordenarse para usar un RIGHT JOIN lugar (y viceversa), generalmente las personas escriben sus consultas con LEFT JOIN y no con RIGHT JOIN .