Datos · SQL
¿Por qué un WHERE convierte un LEFT JOIN en INNER JOIN?
Cuatro pedidos de 40, 60, 20 y 50 €: ¿cuántos cumplen amount > 30?
¿Y si el importe de 20 € pasa a ser NULL?
De un filtro sobre importes a las dos versiones de una misma consulta, en cinco pasos:
¿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
| id | name |
|---|---|
| 1 | Lucía |
| 2 | Pablo |
| 3 | Marta |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 40 € |
| 102 | 1 | 70 € |
| 103 | 2 | 25 € |
| 104 | 2 | 60 € |
| 105 | 3 | 50 € |
| name | amount |
|---|---|
| Lucía | 40 € |
| Lucía | 70 € |
| Pablo | 25 € |
| Pablo | 60 € |
| Marta | 50 € |
| name | amount |
|---|---|
| Lucía | 40 € |
| Lucía | 70 € |
| Pablo | 60 € |
| Marta | 50 € |
¿Qué filas cumplen
amount > 30? Las tocamos en la tabla delLEFT JOIN.Leemos el importe de cada fila y lo comparamos con 30.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Lucía 2 Pablo 3 Marta orders id customer_id amount 101 1 35 € 102 1 65 € 103 2 20 € 104 2 45 € 105 3 70 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 30
customers id name 1 Lucía 2 Pablo 3 Marta 4 Hugo orders id customer_id amount 106 4 15 € 101 1 25 € 103 2 35 € 105 4 40 € 102 2 50 € 104 3 75 €
¿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
| id | name |
|---|---|
| 1 | Lucía |
| 2 | Pablo |
| 3 | Marta |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 40 € |
| 102 | 3 | 60 € |
| name | amount |
|---|---|
| Lucía | 40 € |
| Pablo | NULL |
| Marta | 60 € |
| name | amount |
|---|---|
| Lucía | 40 € |
| Marta | 60 € |
¿Qué filas cumplen
amount > 30?Comparamos cada importe con 30. En la fila de Pablo, comparamos NULL con 30.
Sin
WHERE, ¿qué JOIN da estas mismas filas?
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Lucía 2 Pablo 3 Marta 4 Hugo orders id customer_id amount 101 1 45 € 102 3 20 € 103 3 65 € 104 4 35 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Lucía 2 Pablo 3 Marta 4 Hugo 5 Sara orders id customer_id amount 104 4 15 € 106 5 25 € 102 2 30 € 101 1 50 € 105 5 60 € 103 2 70 €
¿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
| id | name |
|---|---|
| 1 | Lucía |
| 2 | Pablo |
| 3 | Marta |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 40 € |
| 102 | 1 | 60 € |
| 103 | 1 | 25 € |
| 104 | 3 | 20 € |
| name | amount |
|---|---|
| Lucía | 40 € |
| Lucía | 60 € |
| Pablo | NULL |
| Marta | NULL |
¿Qué contiene la columna
amounten la fila de Marta?Miramos primero qué pedidos de Marta cumplen
amount > 30.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 40
customers id name 1 Lucía 2 Pablo 3 Marta 4 Hugo orders id customer_id amount 101 1 50 € 102 1 65 € 103 2 15 € 104 2 45 € 105 3 20 € 106 4 35 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 50
customers id name 1 Lucía 2 Pablo 3 Marta 4 Hugo 5 Sara orders id customer_id amount 104 3 15 € 101 1 25 € 106 5 30 € 105 4 45 € 102 3 55 € 103 3 70 €
¿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
| id | name |
|---|---|
| 1 | Lucía |
| 2 | Pablo |
| 3 | Marta |
| 4 | Hugo |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 70 € |
| 102 | 1 | 45 € |
| 103 | 1 | 20 € |
| 104 | 2 | 35 € |
| 105 | 4 | 15 € |
| name | amount |
|---|---|
| Lucía | 70 € |
| Lucía | 45 € |
| Pablo | 35 € |
| name | amount |
|---|---|
| Lucía | 70 € |
| Lucía | 45 € |
| Pablo | 35 € |
| Marta | NULL |
| Hugo | NULL |
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
| id | name |
|---|---|
| 1 | Lucía |
| 2 | Pablo |
| 3 | Marta |
| 4 | Hugo |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 25 € |
| 102 | 1 | 55 € |
| 103 | 3 | 40 € |
| 104 | 3 | 65 € |
| 105 | 3 | 50 € |
| 106 | 4 | 20 € |
¿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.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Lucía 2 Pablo 3 Marta orders id customer_id amount 101 1 35 € 102 3 60 € 103 3 15 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 40
customers id name 1 Lucía 2 Pablo 3 Marta 4 Hugo orders id customer_id amount 101 1 45 € 102 1 20 € 103 3 30 € 104 4 55 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Lucía 2 Pablo 3 Marta 4 Hugo orders id customer_id amount 101 1 20 € 105 4 30 € 103 2 35 € 104 4 65 € 102 1 75 € SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 40
customers id name 1 Lucía 2 Pablo 3 Marta 4 Hugo 5 Sara orders id customer_id amount 104 3 15 € 103 3 25 € 107 5 35 € 105 4 45 € 102 1 50 € 101 1 65 € 106 5 80 €