outer - sql server join types

INNER JOIN ON vs WHERE cláusula

Question

Para simplificar, suponga que todos los campos relevantes NOT NULL son NOT NULL .

Tu puedes hacer:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1, table2
WHERE
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

Si no:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
WHERE
    (some other conditions)

¿Estos dos funcionan de la misma manera en MySQL ?

Answer 1

El estándar SQL: 2003 modificó algunas reglas de precedencia, de modo que una instrucción JOIN tiene prioridad sobre una combinación "coma". Esto realmente puede cambiar los resultados de su consulta dependiendo de cómo se configura. Esto causa algunos problemas para algunas personas cuando MySQL 5.0.12 cambió a cumplir con el estándar.

Así que en tu ejemplo, tus consultas funcionarán igual. Pero si agregó una tercera tabla: SELECCIONE ... DESDE tabla1, tabla2 ÚNETE tabla3 ENCENDIDO ... DÓNDE ...

Antes de MySQL 5.0.12, table1 y table2 se unirían primero, luego table3. Ahora (5.0.12 y en adelante), table2 y table3 se unen primero, luego table1. No siempre cambia los resultados, pero puede y es posible que usted ni siquiera se dé cuenta.

Ya nunca uso la sintaxis de "coma", optando por su segundo ejemplo. De todos modos, es mucho más legible, las condiciones de JOIN son con las JOIN, no separadas en una sección de consulta separada.

Answer 2

La sintaxis ANSI de unión implícita es más antigua, menos obvia y no se recomienda.

Además, el álgebra relacional permite la intercambiabilidad de los predicados en la cláusula WHERE y en INNER JOIN , por lo que incluso las consultas INNER JOIN con las cláusulas WHERE pueden hacer que el optimizador reordene los predicados.

Te recomiendo que escribas las consultas de la manera más legible posible.

A veces, esto incluye hacer que INNER JOIN relativamente "incompleto" y poner algunos de los criterios en el WHERE simplemente para que las listas de criterios de filtrado sean más fáciles de mantener.

Por ejemplo, en lugar de:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State = ''NY''
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

Escribir:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State = ''NY''
    AND a.Status = 1

Pero depende, por supuesto.

Answer 3

La sintaxis de unión ANSI es definitivamente más portátil.

Estoy realizando una actualización de Microsoft SQL Server, y también mencionaría que la sintaxis = * y * = para las combinaciones externas en SQL Server no es compatible (sin el modo de compatibilidad) para el servidor SQL 2005 y posteriores.

Answer 4

Las combinaciones implícitas (que es como se conoce a su primera consulta) se vuelven mucho más confusas, difíciles de leer y difíciles de mantener una vez que necesita comenzar a agregar más tablas a su consulta. Imagina hacer la misma consulta y el mismo tipo de combinación en cuatro o cinco tablas diferentes ... es una pesadilla.

Usar una unión explícita (su segundo ejemplo) es mucho más legible y fácil de mantener.

Answer 5

Otros han señalado que INNER JOIN ayuda a la legibilidad humana, y esa es una de las principales prioridades; Estoy de acuerdo. Permítanme tratar de explicar por qué la sintaxis de unión es más legible.

Una consulta básica de SELECT es esta:

SELECT stuff
FROM tables
WHERE conditions

La cláusula SELECT nos dice lo que estamos recuperando; La cláusula FROM nos dice de dónde lo estamos obteniendo, y la cláusula WHERE nos dice de cuáles estamos obteniendo.

JOIN es una declaración sobre las tablas, cómo se unen (conceptualmente, en realidad, en una sola tabla). Todos los elementos de consulta que controlan las tablas, de los que obtenemos elementos de forma semántica, pertenecen a la cláusula FROM (y, por supuesto, ahí es donde van los elementos JOIN). Poner elementos de unión en la cláusula WHERE confunde qué y de dónde ; por eso se prefiere la sintaxis de JOIN.

Answer 6

Sé que estás hablando de MySQL, pero de todos modos: en Oracle 9, las combinaciones explícitas y las implícitas generarían diferentes planes de ejecución. AFAIK que se ha resuelto en Oracle 10+: ya no existe tal diferencia.

Answer 7

También señalaré que el uso de la sintaxis más antigua está más sujeto a errores. Si usa uniones internas sin una cláusula ON, obtendrá un error de sintaxis. Si usa la sintaxis anterior y olvida una de las condiciones de unión en la cláusula where, obtendrá una combinación cruzada. Los desarrolladores a menudo solucionan esto agregando la palabra clave distinta (en lugar de arreglar la unión porque aún no se dan cuenta de que la unión en sí está rota), lo que parece solucionar el problema, pero ralentizará considerablemente la consulta.

Además, para el mantenimiento, si tiene una combinación cruzada en la sintaxis anterior, ¿cómo sabrá el mantenedor si pretendía tener una (hay situaciones en las que se necesitan combinaciones cruzadas) o si fue un accidente que debería solucionarse?

Permítame señalarle esta pregunta para ver por qué la sintaxis implícita es mala si usa uniones izquierdas. Sybase * = a Ansi Standard con 2 tablas externas diferentes para la misma tabla interna

Además (rant personal aquí), el estándar que usa las uniones explícitas tiene más de 20 años, lo que significa que la sintaxis de la unión implícita ha estado desactualizada durante esos 20 años. ¿Escribiría el código de la aplicación usando una sintaxis que ha estado desactualizada por 20 años? ¿Por qué quieres escribir código de base de datos que es?

Answer 8

Tienen un significado diferente, legible por humanos.

Sin embargo, dependiendo del optimizador de consultas, pueden tener el mismo significado para la máquina.

Siempre debes codificar para que sea legible.

Es decir, si se trata de una relación integrada, use la unión explícita. si está haciendo coincidencias con datos débilmente relacionados, use la cláusula where.

Answer 9

Aplicando sentencias condicionales en ON / WHERE

Aquí he explicado sobre los pasos de procesamiento de consultas lógicas.

Referencia: Dentro de Microsoft® SQL Server ™ 2005 T-SQL Querying
Editor: Microsoft Press
Fecha de publicación: 07 de marzo de 2006
Imprimir ISBN-10: 0-7356-2313-9
Imprimir ISBN-13: 978-0-7356-2313-2
Páginas: 640

Dentro de la consulta T-SQL de Microsoft® SQL Server ™ 2005

(8)  SELECT (9) DISTINCT (11) TOP <top_specification> <select_list>
(1)  FROM <left_table>
(3)       <join_type> JOIN <right_table>
(2)       ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE | ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>

El primer aspecto notable de SQL que es diferente de otros lenguajes de programación es el orden en que se procesa el código. En la mayoría de los lenguajes de programación, el código se procesa en el orden en que se escribe. En SQL, la primera cláusula que se procesa es la cláusula FROM, mientras que la cláusula SELECT, que aparece primero, se procesa casi al final.

Cada paso genera una tabla virtual que se utiliza como entrada para el siguiente paso. Estas tablas virtuales no están disponibles para el llamante (aplicación cliente o consulta externa). Solo la tabla generada por el paso final se devuelve a la persona que llama. Si una determinada cláusula no se especifica en una consulta, el paso correspondiente simplemente se omite.

Breve descripción de las fases de procesamiento de consultas lógicas

No se preocupe demasiado si la descripción de los pasos no parece tener mucho sentido por ahora. Estos se proporcionan como una referencia. Las secciones que vienen después del ejemplo de escenario cubrirán los pasos con mucho más detalle.

1. FROM: se realiza un producto cartesiano (combinación cruzada) entre las dos primeras tablas de la cláusula FROM y, como resultado, se genera la tabla virtual VT1.

2. ON: El filtro ON se aplica a VT1. Solo las filas para las que <join_condition> es TRUE se insertan en VT2.

3. OUTER (join): si se especifica OUTER JOIN (a diferencia de CROSS JOIN o INNER JOIN), las filas de la tabla o tablas conservadas para las que no se encontró una coincidencia se agregan a las filas de VT2 como filas externas, generando VT3. Si aparecen más de dos tablas en la cláusula FROM, los pasos 1 a 3 se aplican repetidamente entre el resultado de la última combinación y la siguiente tabla en la cláusula FROM hasta que se procesen todas las tablas.

4. DONDE: El filtro DONDE se aplica a VT3. Solo se insertan en VT4 las filas para las que <where_condition> es TRUE.

5. GROUP BY: Las filas de VT4 se organizan en grupos según la lista de columnas especificada en la cláusula GROUP BY. Se genera VT5.

6. CUBO | ROLLUP: los supergrupos (grupos de grupos) se agregan a las filas de VT5, generando VT6.

7. HAVING: El filtro HAVING se aplica a VT6. Solo los grupos para los que <having_condition> es TRUE se insertan en VT7.

8. SELECCIONAR: se procesa la lista SELECCIONAR, generando VT8.

9. DISTINTO: las filas duplicadas se eliminan de VT8. Se genera VT9.

10. ORDER BY: Las filas de VT9 se ordenan según la lista de columnas especificada en la cláusula ORDER BY. Se genera un cursor (VC10).

11. TOP: El número especificado o el porcentaje de filas se selecciona desde el principio de VC10. La tabla VT11 se genera y se devuelve a la persona que llama.

Por lo tanto, (INNER JOIN) ON filtrará los datos (el recuento de datos de VT se reducirá aquí mismo) antes de aplicar la cláusula WHERE. Las siguientes condiciones de unión se ejecutarán con datos filtrados que mejoran el rendimiento. Después de eso, solo la condición WHERE aplicará las condiciones del filtro.

(La aplicación de sentencias condicionales en ON / WHERE no hará mucha diferencia en algunos casos. Esto depende de cuántas tablas haya unido y la cantidad de filas disponibles en cada una de las tablas).

Answer 10

INNER JOIN es una sintaxis ANSI que debe utilizar.

En general, se considera más legible, especialmente cuando se unen a muchas tablas.

También se puede reemplazar fácilmente con una OUTER JOIN siempre que surja una necesidad.

La sintaxis WHERE está más orientada al modelo relacional.

Un resultado de dos tablas que JOIN ed es es un producto cartesiano de las tablas a las que se aplica un filtro que selecciona solo aquellas filas con columnas que se unen.

Es más fácil ver esto con la sintaxis WHERE .

En cuanto a su ejemplo, en MySQL (y en SQL en general) estas dos consultas son sinónimos.

También tenga en cuenta que MySQL también tiene una cláusula STRAIGHT_JOIN .

Utilizando esta cláusula, puede controlar el orden JOIN : qué tabla se escanea en el bucle externo y cuál está en el bucle interno.

No puedes controlar esto en MySQL usando la sintaxis WHERE .