tablas - unir dos consultas sql misma tabla
¿Se dejó unir SQL contra varias tablas en la línea FROM? (11)
Básicamente, cuando su cláusula FROM enumera tablas como tales:
SELECT * FROM
tableA, tableB, tableC
el resultado es un producto cruzado de todas las filas en las tablas A, B, C. Luego, aplica la restricción WHERE tableA.id = tableB.a_id que eliminará una gran cantidad de filas, luego más ... AND tableB.id = tableC.b_id y luego deberías obtener solo las filas que realmente te interesan.
Los DBMS saben cómo optimizar este SQL, de modo que la diferencia de rendimiento al escribir esto utilizando JOINs es insignificante (si existe). El uso de la notación JOIN hace que la instrucción SQL sea más legible (IMHO, no usar combinaciones convierte la declaración en un desastre). Al usar el producto cruzado, debe proporcionar criterios de unión en la cláusula WHERE, y ese es el problema con la notación. Estás llenando tu cláusula WHERE con cosas como
tableA.id = tableB.a_id
AND tableB.id = tableC.b_id
que sólo se utiliza para restringir el producto cruzado. La cláusula WHERE solo debe contener RESTRICCIONES al conjunto de resultados. Si combina criterios de combinación de tabla con restricciones de conjunto de resultados, usted (y otros) encontrarán su consulta más difícil de leer. Definitivamente, debe usar JOIN y mantener la cláusula FROM como una cláusula FROM, y la cláusula WHERE una cláusula WHERE.
La mayoría de los dialectos de SQL aceptan las siguientes consultas:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x
SELECT a.foo, b.foo
FROM a
LEFT JOIN b ON a.x = b.x
Ahora, obviamente, cuando necesita una combinación externa, se requiere la segunda sintaxis. Pero cuando hago una unión interna, ¿por qué debería preferir la segunda sintaxis a la primera (o viceversa)?
Bueno, la primera y la segunda consulta pueden producir resultados diferentes porque una IZQUIERDA IZQUIERDA incluye todos los registros de la primera tabla, incluso si no hay registros correspondientes en la tabla de la derecha.
Creo que hay algunas buenas razones en esta página para adoptar el segundo método: usar JOIN explícitos. Sin embargo, el factor decisivo es que cuando los criterios de UNIR se eliminan de la cláusula WHERE, resulta mucho más fácil ver los criterios de selección restantes en la cláusula WHERE.
En declaraciones SELECT realmente complejas, es mucho más fácil para un lector comprender lo que está sucediendo.
Cuando necesita una combinación externa, la segunda sintaxis no siempre es necesaria:
Oráculo:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x(+)
MSSQLServer (aunque está en deprecated en la versión 2000) / Sybase:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x *= b.x
Pero volviendo a tu pregunta. No sé la respuesta, pero probablemente esté relacionado con el hecho de que una combinación es más natural (sintácticamente, al menos) que agregar una expresión a una cláusula where cuando está haciendo exactamente eso: unirse .
Escucho a mucha gente quejarse de que la primera es demasiado difícil de entender y no está claro. No veo ningún problema con eso, pero después de tener esa discusión, uso el segundo incluso en INNER JOINS para mayor claridad.
La primera forma es la norma más antigua. El segundo método se introdujo en SQL-92, http://en.wikipedia.org/wiki/SQL . El estándar completo se puede ver en http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt .
Pasaron muchos años antes de que las compañías de bases de datos adoptaran el estándar SQL-92.
Entonces, la razón por la cual se prefiere el segundo método, es el estándar SQL según el comité de estándares ANSI e ISO.
La sintaxis anterior, con solo listar las tablas y usar la cláusula WHERE para especificar los criterios de unión, está en desuso en la mayoría de las bases de datos modernas.
No es solo para mostrar, la sintaxis antigua tiene la posibilidad de ser ambigua cuando se usan las combinaciones INNER y OUTER en la misma consulta.
Dejame darte un ejemplo.
Supongamos que tiene 3 tablas en su sistema:
Company
Department
Employee
Cada tabla contiene numerosas filas, unidas entre sí. Usted tiene varias compañías y cada compañía puede tener varios departamentos, y cada departamento puede tener varios empleados.
Ok, ahora quieres hacer lo siguiente:
Enumere todas las compañías e incluya todos sus departamentos y todos sus empleados. Tenga en cuenta que algunas empresas aún no tienen departamentos, pero asegúrese de incluirlos también. Asegúrese de recuperar solo los departamentos que tienen empleados, pero siempre enumere todas las compañías.
Así que haces esto:
SELECT * -- for simplicity
FROM Company, Department, Employee
WHERE Company.ID *= Department.CompanyID
AND Department.ID = Employee.DepartmentID
Tenga en cuenta que la última es una combinación interna, para cumplir con los criterios de que solo desea departamentos con personas.
Ok, entonces que pasa ahora. Bueno, el problema es que depende del motor de la base de datos, el optimizador de consultas, los índices y las estadísticas de la tabla. Dejame explicar.
Si el optimizador de consultas determina que la forma de hacerlo es tomar primero una empresa, luego buscar los departamentos y luego hacer una unión interna con los empleados, no obtendrá ninguna empresa que no tenga departamentos.
La razón de esto es que la cláusula WHERE determina qué filas terminan en el resultado final, no partes individuales de las filas.
Y en este caso, debido a la unión a la izquierda, la columna Department.ID será NULA, y por lo tanto, cuando se trata de la UNIÓN INTERNA al Empleado, no hay forma de cumplir esa restricción para la fila de Empleado, por lo que no Aparecer.
Por otro lado, si el optimizador de consultas decide abordar primero la unión del departamento-empleado, y luego hacer una combinación izquierda con las empresas, las verá.
Así que la sintaxis antigua es ambigua. No hay forma de especificar lo que desea, sin tratar con sugerencias de consulta, y algunas bases de datos no tienen forma alguna.
Ingrese la nueva sintaxis, con esto usted puede elegir.
Por ejemplo, si desea que todas las empresas, como se indica en la descripción del problema, esto es lo que escribiría:
SELECT *
FROM Company
LEFT JOIN (
Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
) ON Company.ID = Department.CompanyID
Aquí usted especifica que desea que la unión de departamento-empleado se realice como una unión, y luego deje de unir los resultados de eso con las compañías.
Además, digamos que solo desea departamentos que contengan la letra X en su nombre. Nuevamente, con las combinaciones de estilo antiguo, corre el riesgo de perder la empresa también, si no tiene ningún departamento con una X en su nombre, pero con la nueva sintaxis, puede hacer esto:
SELECT *
FROM Company
LEFT JOIN (
Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
) ON Company.ID = Department.CompanyID AND Department.Name LIKE ''%X%''
Esta cláusula adicional se utiliza para la unión, pero no es un filtro para toda la fila. Por lo tanto, la fila puede aparecer con información de la compañía, pero puede tener NULL en todas las columnas de departamento y empleado para esa fila, porque no hay departamento con una X en su nombre para esa compañía. Esto es difícil con la vieja sintaxis.
Por esta razón, entre otros proveedores, Microsoft ha desaprobado la antigua sintaxis de combinación externa, pero no la antigua sintaxis de combinación interna, desde SQL Server 2005 y versiones posteriores. La única forma de comunicarse con una base de datos que se ejecuta en Microsoft SQL Server 2005 o 2008, usando la sintaxis de combinación externa de estilo antiguo, es configurar esa base de datos en modo de compatibilidad 8.0 (también conocido como SQL Server 2000).
Además, la forma anterior, al lanzar un montón de tablas en el optimizador de consultas, con un montón de cláusulas WHERE, era como decir "aquí estás, haz lo mejor que puedas". Con la nueva sintaxis, el optimizador de consultas tiene menos trabajo que hacer para averiguar qué partes van juntas.
Así que ahí lo tienen.
IZQUIERDA e INNER JOIN es la ola del futuro.
La sintaxis de SELECT * FROM table1, table2, ... está bien para un par de tablas, pero se vuelve cada vez más exponencial ( no necesariamente una declaración matemáticamente precisa ) a medida que aumenta el número de tablas.
La sintaxis de JOIN es más difícil de escribir (al principio), pero hace que sea explícito qué criterios afectan a qué tablas. Esto hace que sea mucho más difícil cometer un error.
Además, si todas las uniones son INTERIORES, entonces ambas versiones son equivalentes. Sin embargo, en el momento en que tiene una inscripción EXTERNA en cualquier parte de la declaración, las cosas se complican mucho más y prácticamente garantiza que lo que escriba no cuestionará lo que cree que escribió.
La sintaxis de JOIN mantiene las condiciones cerca de la tabla a la que se aplican. Esto es especialmente útil cuando se une una gran cantidad de tablas.
Por cierto, también puede hacer una combinación externa con la primera sintaxis:
WHERE a.x = b.x(+)
O
WHERE a.x *= b.x
O
WHERE a.x = b.x or a.x not in (select x from b)
Para la base de datos, terminan siendo los mismos. Para ti, sin embargo, tendrás que usar esa segunda sintaxis en algunas situaciones. Por el bien de las consultas de edición que terminan por tener que usarlo (porque descubrió que necesitaba una combinación a la izquierda donde tenía una combinación directa), y para mantener la coherencia, usé el patrón solo en el segundo método. Hará las consultas de lectura más fáciles.
Se prefiere el segundo porque es mucho menos probable que resulte en una unión cruzada accidental al olvidarse de poner la cláusula where. Una unión sin cláusula de activación generará un error en la comprobación de sintaxis, una combinación de estilo antiguo sin una cláusula where no fallará, realizará una combinación cruzada.
Además, cuando más tarde tenga que unirse a la izquierda, es útil para el mantenimiento que todos estén en la misma estructura. Y la sintaxis antigua ha estado desactualizada desde 1992, ya es hora de dejar de usarla.
Además, he descubierto que muchas personas que utilizan exclusivamente la primera sintaxis que realmente no entienden las uniones y entenderlas son fundamentales para obtener resultados correctos al realizar consultas.