Pregunta INNER JOIN ON vs WHERE cláusula


Para simplificar, suponga que todos los campos relevantes 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 trabajan de la misma manera en MySQL?


761
2018-06-19 16:16


origen


Respuestas:


INNER JOIN es la sintaxis ANSI que debes usar.

Generalmente se considera más legible, especialmente cuando se une a muchas tablas.

También se puede reemplazar fácilmente con un OUTER JOIN cada vez que surge una necesidad.

los WHERE la sintaxis está más orientada al modelo relacional.

Un resultado de dos tablas JOINed es un producto cartesiano de las tablas a las que se aplica un filtro que selecciona solo aquellas filas con columnas de unión coincidentes.

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

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 STRAIGHT_JOIN cláusula.

Usando esta cláusula, puedes controlar el JOIN orden: qué tabla se escanea en el bucle externo y cuál se encuentra en el bucle interno.

No puedes controlar esto en MySQL usando WHERE sintaxis.


611
2018-06-19 16:17



Otros han señalado que INNER JOIN ayuda a la legibilidad humana, y esa es una prioridad principal; Estoy de acuerdo. Déjame intentar explicar por qué la sintaxis de unión es más legible.

Una consulta básica SELECT es esta:

SELECT stuff
FROM tables
WHERE conditions

La cláusula SELECT nos dice qué estamos volviendo; la cláusula FROM nos dice dónde lo estamos obteniendo, y la cláusula WHERE nos dice cual los que estamos recibiendo

JOIN es una declaración sobre las tablas, cómo están unidas (conceptualmente, en realidad, en una sola tabla). Todos los elementos de consulta que controlan las tablas (de dónde obtenemos cosas) pertenecen semánticamente 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 combina el cual y el de donde; es por eso que se prefiere la sintaxis JOIN.


145
2018-06-19 16:30



 Aplicando declaraciones condicionales en ON / WHERE

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


Referencia: consulta interna de T SQL de SQL Server 2005
Editorial: Microsoft Press
Pub Fecha: 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 de 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 usa como entrada para el siguiente paso. Estas tablas virtuales no están disponibles para la persona que llama (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 el momento. Estos se proporcionan como referencia. Las secciones que vienen después del ejemplo del 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 en la cláusula FROM, y como resultado, se genera la tabla virtual VT1.

  2. ON: el filtro ON se aplica a VT1. Solo filas para las cuales <join_condition> es VERDADERO se insertan en VT2.

  3. OUTER (join): si se especifica OUTER JOIN (en oposición a CROSS JOIN o INNER JOIN), las filas de la tabla conservada o las tablas 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 unión y la siguiente tabla en la cláusula FROM hasta que se procesen todas las tablas.

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

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

  6. CUBE | ROLLUP: Supergroups (grupos de grupos) se agregan a las filas de VT5, generando VT6.

  7. TENIENDO: El filtro HAVING se aplica a VT6. Solo grupos para los cuales el <having_condition> es VERDADERO se insertan en VT7.

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

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

  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. ARRIBA: El número especificado o porcentaje de filas se selecciona desde el comienzo 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 conteo de datos de VT se reducirá aquí) antes de aplicar la cláusula WHERE. Las condiciones de unión posteriores 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 cuántas filas haya disponibles en cada tabla de combinación)


112
2017-12-22 06:24



La sintaxis ANSI de unión implícita es anterior, menos obvia y no recomendada.

Además, el álgebra relacional permite la intercambiabilidad de los predicados en WHERE cláusula y el INNER JOIN, aun asi INNER JOIN consultas con WHERE las cláusulas pueden tener los predicados reordenados por el optimizador.

Te recomiendo que escribas las consultas de la manera más leída posible.

Algunas veces esto incluye hacer el INNER JOIN relativamente "incompleto" y poniendo algunos de los criterios en WHERE simplemente para hacer 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.


55
2018-06-19 16:23



Las combinaciones implícitas (que es lo que se conoce como 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 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.


25
2018-06-19 16:19



También señalaré que el uso de la sintaxis anterior está más sujeto a error. Si usa combinaciones 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 corrigen esto agregando la palabra clave distinta (en lugar de corregir la unión porque aún no se dan cuenta de que la unión está rota) lo que puede parecer que soluciona el problema, pero ralentizará considerablemente la consulta.

Además, para el mantenimiento si tiene una unión cruzada en la sintaxis anterior, ¿cómo sabrá el mantenedor si tenía intenciones de hacerlo? (Hay situaciones en las que se necesitan uniones cruzadas) o si fue un accidente que debería corregirse.

Déjame señalarte esta pregunta para ver por qué la sintaxis implícita es mala si usas combinaciones a la izquierda. Sybase * = a Ansi Standard con 2 tablas externas diferentes para la misma mesa interna

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


21
2018-06-19 16:46



Tienen un significado diferente legible para los humanos.

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

Siempre debe codificar para ser legible.

Es decir, si esta es una relación incorporada, use la unión explícita. si hace coincidir datos débilmente relacionados, use la cláusula where.


12
2018-06-19 16:20



El estándar SQL: 2003 cambió algunas reglas de precedencia por lo que una declaración JOIN tiene prioridad sobre una combinación "coma". Esto realmente puede cambiar los resultados de su consulta dependiendo de cómo esté configurada. Esto causa algunos problemas para algunas personas cuando MySQL 5.0.12 cambió a adherirse al estándar.

Entonces en tu ejemplo, tus consultas funcionarían de la misma manera. Pero si agregaste una tercera mesa: SELECCIONAR ... DE table1, table2 JOIN table3 ON ... WHERE ...

Antes de MySQL 5.0.12, table1 y table2 se unirían primero, luego table3. Ahora (5.0.12 y on), table2 y table3 se unen primero, luego table1. No siempre cambia los resultados, pero puede y ni siquiera te das cuenta.

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


10
2018-06-19 17:28



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


4
2018-06-19 17:03