sql - ¿Son asociativas externas izquierdas asociativas?
join outer-join (3)
Es fácil comprender por qué las uniones externas de la izquierda no son conmutativas, pero me cuesta entender si son asociativas. Varias fuentes en línea sugieren que no lo son, pero no he logrado convencerme de que este es el caso.
Supongamos que tenemos tres tablas: A, B y C.
Deje que A contenga dos columnas, ID y B_ID, donde ID es la clave principal de la tabla A y B_ID es una clave externa correspondiente a la clave principal de la tabla B.
Deje que B contenga dos columnas, ID y C_ID, donde ID es la clave principal de la tabla B y C_ID es una clave externa correspondiente a la clave principal de la tabla C.
Deje que C contenga dos columnas, ID y VALOR, donde ID es la clave principal de la tabla C y VALOR solo contiene algunos valores arbitrarios.
Entonces, ¿no debería (A left outer join B) left outer join C debería ser igual a A left outer join (B left outer join C) ?
Además de las respuestas anteriores: El tema se discute bien en Michael M. David, Advanced ANSI SQL Data Modeling and Structure Processing, Artech House, 1999, páginas 19--21. Páginas disponibles en online .
Me parece particularmente notable que él discute que la tabla (IZQUIERDA JUNTA ...) y las cláusulas de unión (ON ...) deben considerarse por separado, por lo que la asociatividad podría referirse a ambas (reorganización de cláusulas de tabla y reorganización de condiciones de unión, es decir, en cláusulas). Entonces, la noción de asociatividad no es la misma que, por ejemplo, la suma de números, tiene dos dimensiones.
En este hilo, se dice, que no son asociativos: ¿Es IZQUIERDA UNIÓN EXTERNA IZQUIERDA asociativa?
Sin embargo, he encontrado un libro en línea en el que se dice que las JUNTAS EXTERIORES son asociativas, cuando las tablas en el extremo izquierdo y en el extremo derecho no tienen atributos en común ( here ).
Aquí hay una presentación gráfica (MSPaint ftw):
Otra forma de verlo:
Como dijo que la tabla A se une con B, y B se une con C, entonces:
- Cuando te unes por primera vez a A y B, te quedan todos los registros de A. Algunos de ellos tienen valores de B. Ahora, para algunas de esas filas para las que obtuviste un valor de B, obtienes valores de C.
- Cuando te unes por primera vez a B y C, subes con toda la tabla B, donde algunos de los registros tienen valores de C. Ahora, tomas todos los registros de A y unes algunos de ellos con todas las filas de B unidas con C. Aquí , nuevamente, obtienes todas las filas de A, pero algunas de ellas tienen valores de B, algunas de las cuales tienen valores de C.
No veo ninguna posibilidad donde, en las condiciones descritas por usted, habría una pérdida de datos dependiendo de la secuencia de las combinaciones IZQUIERDA.
Basándome en los datos proporcionados por Tilak en su respuesta (que ahora está eliminada), he creado un caso de prueba simple:
CREATE TABLE atab (id NUMBER, val VARCHAR2(10));
CREATE TABLE btab (id NUMBER, val VARCHAR2(10));
CREATE TABLE ctab (id NUMBER, val VARCHAR2(10));
INSERT INTO atab VALUES (1, ''A1'');
INSERT INTO atab VALUES (2, ''A2'');
INSERT INTO atab VALUES (3, ''A3'');
INSERT INTO btab VALUES (1, ''B1'');
INSERT INTO btab VALUES (2, ''B2'');
INSERT INTO btab VALUES (4, ''B4'');
INSERT INTO ctab VALUES (1, ''C1'');
INSERT INTO ctab VALUES (3, ''C3'');
INSERT INTO ctab VALUES (5, ''C5'');
SELECT ab.aid, ab.aval, ab.bval, c.val AS cval
FROM (
SELECT a.id AS aid, a.val AS aval, b.id AS bid, b.val AS bval
FROM atab a LEFT OUTER JOIN btab b ON (a.id = b.id)
) ab
LEFT OUTER JOIN ctab c ON (ab.bid = c.id)
ORDER BY ab.aid
;
AID AVAL BVAL CVAL ---------- ---------- ---------- ---------- 1 A1 B1 C1 2 A2 B2 3 A3
SELECT a.id, a.val AS aval, bc.bval, bc.cval
FROM
atab a
LEFT OUTER JOIN (
SELECT b.id AS bid, b.val AS bval, c.id AS cid, c.val AS cval
FROM btab b LEFT OUTER JOIN ctab c ON (b.id = c.id)
) bc
ON (a.id = bc.bid)
ORDER BY a.id
;
ID AVAL BVAL CVAL ---------- ---------- ---------- ---------- 1 A1 B1 C1 2 A2 B2 3 A3
Parece en este ejemplo particular, que ambas soluciones dan el mismo resultado. No puedo pensar en ningún otro conjunto de datos que haga que esas consultas arrojen resultados diferentes.
Compruebe en SQLFiddle:
Si está asumiendo que se está UNIENDO con una clave externa, como su pregunta parece implicar, entonces sí, creo que OUTER JOIN está garantizado para ser asociativo, como lo explica la respuesta de Przemyslaw Kruglej .
Sin embargo, dado que en realidad no ha especificado la condición de UNIR, la respuesta pedesticamente correcta es que no, no se garantiza que sean asociativos. Hay dos formas fáciles de violar la asociatividad con cláusulas de ON perversas.
1. Una de las condiciones de UNIR involucra columnas de las 3 tablas
Esta es una forma bastante barata de violar la asociatividad, pero estrictamente hablando nada en su pregunta lo prohibió. Usando los nombres de columna sugeridos en su pregunta, considere las siguientes dos consultas:
-- This is legal
SELECT * FROM (A JOIN B ON A.b_id = B.id)
JOIN C ON (A.id = B.id) AND (B.id = C.id)
-- This is not legal
SELECT * FROM A
JOIN (B JOIN C ON (A.id = B.id) AND (B.id = C.id))
ON A.b_id = B.id
La consulta inferior no es siquiera una consulta válida, pero la superior es. Claramente esto viola la asociatividad.
2. Una de las condiciones de JOIN puede cumplirse a pesar de que todos los campos de una tabla tienen el valor NULL
De esta manera, incluso podemos tener diferentes números de filas en nuestro conjunto de resultados, dependiendo del orden de las UNIONES. Por ejemplo, deje que la condición para UNIRSE A en B sea A.b_id = B.id , pero la condición para UNIRSE en B en C sea B.id IS NULL .
Así obtenemos estas dos consultas, con resultados muy diferentes:
SELECT * FROM (A LEFT OUTER JOIN B ON A.b_id = B.id)
LEFT OUTER JOIN C ON B.id IS NULL;
SELECT * FROM A
LEFT OUTER JOIN (B LEFT OUTER JOIN C ON B.id IS NULL)
ON A.b_id = B.id;
Puede ver esto en acción aquí: http://sqlfiddle.com/#!2/0d462/3