outer - join on en sql

Condición dentro de JOIN o DONDE

Question

¿Hay alguna diferencia (rendimiento, mejores prácticas, etc.) entre poner una condición en la cláusula JOIN contra la cláusula WHERE?

Por ejemplo...

-- Condition in JOIN
SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
AND CUS.FirstName = ''John''

-- Condition in WHERE
SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
WHERE CUS.FirstName = ''John''

¿Cuál prefieres (y quizás por qué)?

Answer 1

DONDE se filtrará después de que se haya producido la UNIÓN.

Filtre en JOIN para evitar que se agreguen filas durante el proceso JOIN.

Answer 2

El álgebra relacional permite la intercambiabilidad de los predicados en la cláusula WHERE y en la INNER JOIN , por lo que incluso las consultas INNER JOIN con cláusulas WHERE pueden tener los predicados reordenados por el optimizador para que ya puedan ser excluidos durante el proceso JOIN .

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

A veces esto incluye hacer que la INNER JOIN relativamente "incompleta" y poner algunos de los criterios en el 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.

Answer 3

En el caso de las uniones internas, realmente no noté la diferencia (pero, como sucede con todos los ajustes de rendimiento, debe consultar su base de datos en sus condiciones).

Sin embargo, donde pones la condición hace una gran diferencia si estás utilizando uniones hacia la izquierda o hacia la derecha. Por ejemplo, considere estas dos consultas:

SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderDate >''20090515''

SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
AND ORD.OrderDate >''20090515''

La primera solo le dará aquellos registros que tengan una orden de fecha posterior al 15 de mayo de 2009, convirtiendo así la combinación de la izquierda en una combinación interna. El segundo dará esos registros más clientes sin pedidos. El conjunto de resultados es muy diferente dependiendo de dónde coloque la condición. (Seleccione * si, por ejemplo, solo, no debe usar el curso en el código de producción). La excepción a esto es cuando desea ver solo los registros en una tabla pero no en la otra. Luego usa la cláusula where para la condición, no la unión.

SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderID is null

Answer 4

La mayoría de los productos RDBMS optimizarán ambas consultas de forma idéntica. En "SQL Performance Tuning" de Peter Gulutzan y Trudy Pelzer, probaron varias marcas de RDBMS y no encontraron diferencias en el rendimiento.

Prefiero mantener las condiciones de combinación separadas de las condiciones de restricción de consultas.

Si usa OUTER JOIN algunas veces es necesario poner condiciones en la cláusula join.

Answer 5

Las uniones son más rápidas en mi opinión cuando tienes una mesa más grande. Realmente no es mucha diferencia, especialmente si se trata de una mesa bastante pequeña. Cuando me enteré de las uniones, me dijeron que las condiciones en las uniones son como las condiciones de la cláusula where y que podría usarlas indistintamente si la cláusula where era específica sobre en qué tabla hacer la condición.

Answer 6

Normalmente veo un aumento en el rendimiento al filtrar en la unión. Especialmente si puede unirse a columnas indexadas para ambas tablas. Debería poder reducir las lecturas lógicas con la mayoría de las consultas haciendo esto también, lo que es, en un entorno de alto volumen, un indicador de rendimiento mucho mejor que el tiempo de ejecución.

Siempre me divierto un poco cuando alguien muestra su evaluación comparativa de SQL y han ejecutado ambas versiones de un sproc 50,000 veces a la medianoche en el servidor de desarrollo y comparan los tiempos promedio.

Answer 7

Poner la condición en la unión parece "semánticamente incorrecto" para mí, ya que eso no es para lo que JOINs es "para". Pero eso es muy cualitativo.

Problema adicional: si decide cambiar de una unión interna a, por ejemplo, una unión a la derecha, tener la condición dentro de la UNIÓN podría dar lugar a resultados inesperados.

Answer 8

Prefiero el JOIN para unir mesas / Vistas completas y luego usar WHERE para introducir el predicado del conjunto resultante.

Se siente sintácticamente más limpio.