¿Cuál es la diferencia entre WHERE y ON en los JOINs de SQL?

Cuando une tablas en SQL, puede tener condiciones en una cláusula ON y en una cláusula WHERE. Muchos se confunden con la diferencia entre ellas. En este artículo, discutiremos este tema recordando primero el propósito de las cláusulas ON y WHERE y luego demostrando con ejemplos qué tipos de condiciones deben estar en cada una de estas cláusulas.

Tanto la cláusula ON como la cláusula WHERE pueden especificar condiciones. ¿Pero hay alguna diferencia entre ellas? Si es así, ¿dónde deberías especificar qué condiciones en tu consulta SQL? ¡Averigüémoslo juntos!

Condiciones ON vs. WHERE

El propósito de la cláusula ON es especificar las condiciones de unión, es decir, definir cómo se deben unir las tablas. En concreto, se define cómo deben coincidir los registros.

En cambio, el propósito de la cláusula WHERE es especificar las condiciones de filtrado, es decir, definir qué filas deben mantenerse en el conjunto de resultados.

Veamos un ejemplo para entender la diferencia. Tenemos las siguientes dos tablas que (1) enumeran los usuarios (la tabla users) de nuestro sitio web de alquileres y (2) enumeran las casas (la tabla houses) disponibles para el alquiler.

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
JOIN houses h
ON u.id = h.owner_id
WHERE u.registration_date < '2020-01-01';

Observe que tenemos condiciones tanto en la cláusula ON como en la cláusula WHERE:

• Con la condición ON, especificamos que las tablas se unan mediante la coincidencia de la columna id en la tabla users y la columna owner_id en las casas
• Con la condición WHERE, filtramos el conjunto de resultados manteniendo sólo los usuarios que se registraron antes del 1 de enero de 2020.

De este modo, hemos utilizado las condiciones ON y WHERE de acuerdo con su propósito, dando como resultado una consulta SQL clara y legible.

Este es el conjunto de resultados:

¿No está seguro de cómo funciona el JOIN en nuestra consulta SQL? Practique la unión de tablas con este curso interactivo de SQL JOINs.

Condiciones ON y WHERE en INNER JOINs

En el ejemplo anterior, podemos ver cómo se utilizan las condiciones ON y WHERE de acuerdo a su respectivo propósito y práctica común.

Sin embargo, es útil saber que, para (INNER) JOINs, se puede especificar tanto la condición JOIN como la condición de filtrado con una cláusula ON. Por ejemplo, podemos obtener el mismo resultado que el anterior con la siguiente consulta SQL:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
JOIN houses h
ON u.id = h.owner_id AND u.registration_date < '2020-01-01';

Esta consulta se ejecuta de la misma manera. Dicho esto, no recomiendo mezclar la condición de unión y la condición de filtrado en la misma cláusula. Si comparas las dos consultas, verás que la primera es más legible:

• Es más fácil seguir la primera consulta: primero, se unen las tablas por una determinada condición, y luego se filtra el resultado por una condición diferente.
• La intención de toda la consulta es más clara para el lector externo cuando las condiciones están separadas siguiendo las reglas.

Condiciones ON y WHERE en OUTER JOINs

Cuando se trata de OUTER JOINs (es decir, LEFT JOIN, RIGHT JOIN, y FULL JOIN), es crucial utilizar las condiciones ON y WHERE de la forma en que están previstas. De lo contrario, obtendrá resultados erróneos. Veamos con un ejemplo.

De nuevo, queremos obtener la lista de usuarios que se registraron antes del 1 de enero de 2020, junto con sus respectivas casas. Esta vez, sin embargo, queremos mantener a todos los usuarios, incluidos los que no tienen casas registradas en nuestra web de alquiler. Por lo tanto, vamos a hacer un LEFT JOIN en lugar de un JOIN (es decir, un INNER JOIN).

Vamos a ver si hay alguna diferencia entre especificar la condición de filtrado en la cláusula ON y especificarla en la cláusula WHERE. Si seguimos las reglas y utilizamos las condiciones como se pretende, tenemos la siguiente consulta:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id
WHERE u.registration_date < '2020-01-01';

El resultado parece bueno. Tenemos todos los usuarios que obtuvimos en nuestro ejemplo inicial. Además, tenemos dos usuarios más que no tienen casas correspondientes en nuestro sitio web, pero que se incluyeron en el conjunto de resultados debido a la cláusula LEFT JOIN. Obsérvese que ambos se registraron antes del 1 de enero de 2020, como se especifica en nuestra condición de filtrado.

¿Obtenemos el mismo resultado si mezclamos la condición de unión y la de filtrado en la cláusula ON? Averigüémoslo:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id AND u.registration_date < '2020-01-01';

Como puede ver, los resultados son diferentes. Tenemos todos los usuarios incluidos, incluso los que se registraron en 2020 o 2021. Esto se debe a que el LEFT JOIN mantiene todos los registros de la tabla de la izquierda incluso cuando la lógica de ON falla. Así que, en este ejemplo, especificar la condición de filtrado en la cláusula ON no nos sirve. Para obtener el resultado correcto, tenemos que especificar las condiciones como se pretende.

Curiosamente, hay situaciones en las que la condición WHERE puede "cancelar" la intención de una cláusula OUTER JOIN. Como ejemplo, digamos que queremos listar todos los usuarios con sus correspondientes casas pero sólo si las casas tienen 3 o más dormitorios.

Como queremos mantener a todos los usuarios, utilizaremos un OUTER JOIN, concretamente un LEFT JOIN. Nuestro requisito del número de dormitorios es claramente una condición de filtrado. Así que la incluiremos en la cláusula WHERE. Aquí está nuestra consulta SQL con las condiciones especificadas como se pretende:

SELECT u.id, u.name, h.address, h.city, h.bedrooms
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id
WHERE h.bedrooms > 2;

No parece correcto, ¿verdad? El resultado parece como si hubiéramos utilizado un INNER JOIN en lugar de un LEFT JOIN. Los usuarios sin casa no se incluyen en la tabla resultante, porque tienen NULL en la columna dormitorios cuando se unen las tablas. Dado que los valores de NULL se consideran menores que 0, las filas correspondientes se eliminan cuando aplicamos la condición de filtrado - el número de dormitorios mayor que 2.

Hay dos posibles soluciones a este problema:

• Añadir otra condición de filtrado a la cláusula WHERE, como dormitorios es NULL:
  

  SELECT u.id, u.name, h.address, h.city, h.bedrooms
  FROM users u
  LEFT JOIN houses h
  ON u.id = h.owner_id
  WHERE h.bedrooms > 2 OR h.bedrooms is NULL;
  

• Mover la condición de filtrado a la cláusula ON:
  

  SELECT u.id, u.name, h.address, h.city, h.bedrooms
  FROM users u
  LEFT JOIN houses h
  ON u.id = h.owner_id AND h.bedrooms > 2;
  

Cualquiera de estas consultas nos da el siguiente resultado:

¡Ahora ya lo sabes! En OUTER JOINs, sí es diferente cómo especificamos las condiciones.

¡Practiquemos los JOINs en SQL!

SQL JOINs no es muy difícil de entender. Sin embargo, como pudiste ver en los ejemplos de este artículo, hay matices que deben ser considerados cuando se unen tablas y se escriben condiciones de unión en SQL.

Si realmente quieres dominar Tipos de JOIN en SQL, practicar con conjuntos de datos del mundo real es un factor clave para el éxito. Recomiendo comenzar con el curso interactivo de JOINs de SQL - incluye 93 desafíos de codificación que cubren los tipos más comunes de uniones como JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, e incluso auto-uniones y uniones no-equi. Después de tomar este curso, usted sabrá cómo unir múltiples tablas, cómo unir tablas sin una columna común, y cómo filtrar correctamente los datos con diferentes tipos de JOINs.

Para aquellos que quieran experimentar con casos de uso aún más SQL JOIN, recomiendo tomar el Ejercicio de SQL pista. Incluye cinco cursos interactivos con más de 600 desafíos de codificación, que cubren no sólo los fundamentos de Tipos de JOIN en SQL sino también cómo filtrar el conjunto de resultados con una cláusula WHERE, cómo agregar datos con GROUP BY y HAVING, y cómo utilizar subconsultas, incluyendo subconsultas correlacionadas. ¡Te vas a divertir mucho!

BONO. Aquí están las 10 mejores preguntas de entrevista de SQL JOIN con respuestas.