Datos · SQL

¿Por qué un WHERE convierte un LEFT JOIN en INNER JOIN?

Publicado el

Cuatro pedidos de 40, 60, 20 y 50 €: ¿cuántos cumplen amount > 30?

De un filtro sobre importes a las dos versiones de una misma consulta, en cinco pasos:

  1. Filtrar las filas de un JOIN con WHERE
  2. Seguir la fila NULL hasta el WHERE
  3. Conservar a cada cliente con una condición en ON
  4. Contar las filas de las dos versiones
  5. Practicar

¿Cuándo se aplica el WHERE en un JOIN?

El WHERE actúa después del JOIN, sobre cada fila de su resultado. Un pedido de 12 € sale del JOIN, y después WHERE amount > 30 descarta su fila.

Tomemos tres clientes y sus pedidos.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
customers
idname
1Lucía
2Pablo
3Marta
orders
idcustomer_idamount
101140 €
102170 €
103225 €
104260 €
105350 €
LEFT JOIN, antes del WHERE
nameamount
Lucía40 €
Lucía70 €
Pablo25 €
Pablo60 €
Marta50 €
resultado
nameamount
Lucía40 €
Lucía70 €
Pablo60 €
Marta50 €
  1. Cada cliente sale una vez por pedido, como en cualquier JOIN.

    filas

  2. ¿Qué filas cumplen amount > 30? Las tocamos en la tabla del LEFT JOIN.

    Leemos el importe de cada fila y lo comparamos con 30.

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Lucía
    2Pablo
    3Marta
    orders
    idcustomer_idamount
    101135 €
    102165 €
    103220 €
    104245 €
    105370 €

¿Por qué un WHERE hace desaparecer la fila NULL de un LEFT JOIN?

El cliente cuyo id vale 7 no tiene ningún pedido: su fila lleva un importe NULL. NULL > 0 no es verdadero, así que WHERE amount > 0 descarta su fila.

Tomemos tres clientes, uno de ellos sin pedidos.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
customers
idname
1Lucía
2Pablo
3Marta
orders
idcustomer_idamount
101140 €
102360 €
LEFT JOIN, antes del WHERE
nameamount
Lucía40 €
PabloNULL
Marta60 €
resultado
nameamount
Lucía40 €
Marta60 €
  1. LEFT JOIN conserva a cada cliente, aunque no tenga pedidos.

    filas

  2. ¿Qué filas cumplen amount > 30?

    Comparamos cada importe con 30. En la fila de Pablo, comparamos NULL con 30.

  3. Sin WHERE, ¿qué JOIN da estas mismas filas?

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Lucía
    2Pablo
    3Marta
    4Hugo
    orders
    idcustomer_idamount
    101145 €
    102320 €
    103365 €
    104435 €

¿Cómo filtrar un LEFT JOIN sin perder clientes?

Escrita en ON, la condición elige qué pedidos se unen, y después LEFT JOIN conserva a cada cliente. El cliente cuyo id vale 7 solo tiene un pedido de 12 €: AND amount > 30 lo rechaza, y el cliente sale igualmente, con un importe NULL.

Tomemos tres clientes, con la condición en ON.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
  AND orders.amount > 30
customers
idname
1Lucía
2Pablo
3Marta
orders
idcustomer_idamount
101140 €
102160 €
103125 €
104320 €
resultado
nameamount
Lucía40 €
Lucía60 €
PabloNULL
MartaNULL
  1. ¿Qué contiene la columna amount en la fila de Marta?

    Miramos primero qué pedidos de Marta cumplen amount > 30.

  2. Cada cliente sale una vez por pedido unido, y una vez si no tiene ninguno.

    filas

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
      AND orders.amount > 40
    customers
    idname
    1Lucía
    2Pablo
    3Marta
    4Hugo
    orders
    idcustomer_idamount
    101150 €
    102165 €
    103215 €
    104245 €
    105320 €
    106435 €

¿Qué diferencia hay entre una condición en ON y en WHERE?

Con 7 clientes, la condición escrita en ON da al menos 7 filas, al menos una por cliente. Escrita en WHERE, solo conserva las filas en las que se cumple, y puede no dejar ninguna.

Tomemos cuatro clientes y las dos versiones de la consulta.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
  AND orders.amount > 30
customers
idname
1Lucía
2Pablo
3Marta
4Hugo
orders
idcustomer_idamount
101170 €
102145 €
103120 €
104235 €
105415 €
resultado con WHERE
nameamount
Lucía70 €
Lucía45 €
Pablo35 €
resultado con ON
nameamount
Lucía70 €
Lucía45 €
Pablo35 €
MartaNULL
HugoNULL
  1. El WHERE vuelve a leer cada fila del LEFT JOIN. La fila de Marta lleva un importe NULL.

    filas

  2. Cada cliente sale una vez por pedido que cumple amount > 30, y una vez si no tiene ninguno.

    filas

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
  AND orders.amount > 30
customers
idname
1Lucía
2Pablo
3Marta
4Hugo
orders
idcustomer_idamount
101125 €
102155 €
103340 €
104365 €
105350 €
106420 €

¿Cómo comprobar rápido un LEFT JOIN con filtro?

Primero miramos dónde está escrita la condición. En WHERE, contamos los pedidos que la cumplen. En ON, cada cliente sale una vez por pedido que la cumple, y una vez si no tiene ninguno.

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Lucía
    2Pablo
    3Marta
    orders
    idcustomer_idamount
    101135 €
    102360 €
    103315 €