Cláusula WHERE vs ON al usar JOIN

Solo ten cuidado con la diferencia con las uniones externas. Una consulta donde se agrega un filtro de b.IsApproved (en la tabla derecha, Barra) a la condición ON del JOIN:

SELECT * 
FROM Foo f 
LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId); 

Es NO lo mismo que colocar el filtro en la cláusula WHERE:

SELECT * 
FROM Foo f 
LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId)
WHERE (b.IsApproved = 1); 

Dado que para las uniones externas 'fallidas' a Bar (es decir, donde no hay b.BarId para un f.BarId), esto dejará b.IsApproved como NULL para todas las filas de unión fallidas, y estas filas se filtrarán.

Otra forma de mirando esto es que para la primera consulta, LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId) siempre devolverá las filas de la tabla IZQUIERDA, ya que LEFT OUTER JOIN garantiza que las filas de la tabla IZQUIERDA serán devueltas incluso si la combinación falla. Sin embargo, el efecto de agregar (b.IsApproved = 1) a la condición LEFT OUTER JOIN on es ANULAR cualquier columna de tabla correcta cuando (b.IsApproved = 1) es falsa, es decir, según las mismas reglas que se aplican normalmente a una condición LEFT JOIN en (b.BarId = f.BarId).

Actualización : Para completar la pregunta de Conrad, el LOJ equivalente para un filtro OPCIONAL sería:

SELECT * 
FROM Foo f 
LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId)
WHERE (b.IsApproved IS NULL OR b.IsApproved = 1);

Es decir, la cláusula WHERE necesita considerar tanto la condición de si la combinación falla (NULL) y el filtro debe ser ignorado, y donde la combinación tiene éxito y el filtro debe ser aplicado. (b.IsApproved o b.BarId podría probarse para NULL)

He puesto un SQLFiddle juntos aquí que demuestra las diferencias entre las diversas ubicaciones del filtro b.IsApproved en relación con el JOIN.

SELECT * FROM Foo f
INNER JOIN Bar b ON b.BarId = f.BarId
WHERE b.IsApproved = 1;

Esta es la mejor forma de ir. Es fácil de leer y fácil de modificar. En el mundo de los negocios esto es lo que usted querría ir con. En cuanto al rendimiento, son los mismos.

Me parece que algunos casos en los que el optimizador no era lo suficientemente inteligente, incluso en las versiones recientes de MSSQL - y la diferencia de rendimiento era monstruo.

Pero esta es una excepción, la mayoría de las veces SQL Server optimizer resolverá el problema y obtendrá el plan correcto.

Así que mantenga la política de usar filtros en la cláusula WHERE y optimice cuando sea necesario.

Acabo de ejecutar una prueba de una consulta contra cuatro tablas - una tabla primaria con tres uniones INTERNAS y un total de cuatro parámetros, y comparé los planes de ejecución de ambos enfoques (utilizando los criterios de filtro en el ON de la UNIÓN, y luego también en la cláusula WHERE).

Los planes de ejecución son exactamente los mismos. Ejecuté esto en SQL Server 2008 R2.