Unir valoraciones con datos de clientes

Toma de decisiones basada en datos en SQL

Irene Ortner

Data Scientist at Applied Statistics

JOIN

Curso previo sobre joins en SQL

LEFT JOIN

LEFT JOIN es un outer join.
Mantiene todas las filas de la tabla izquierda y empareja con la derecha.
Usa un identificador para definir qué filas coinciden.

Dar un alias a una tabla

SELECT *
FROM customers AS c
WHERE c.customer_id = 1;

Tablas para LEFT JOIN

Tabla izquierda: renting_selected

| renting_id | customer_id | rating |
|------------|-------------|--------|
| 518        | 1           | `null` |
| 203        | 2           | 6      |
| 478        | 4           | 7      |
| 292        | 4           | 8      |
| 477        | 5           | `null` |
| 400        | 6           | `null` |

Tabla derecha: customers_selected

| customer_id | name               | gender |
|-------------|--------------------|--------|
| 1           | Robert Bohm        | male   |
| 2           | Wolfgang Ackermann | male   |
| 3           | Daniela Herzog     | female |
| 4           | Julia Jung         | female |

Ejemplo de LEFT JOIN

SELECT *
FROM renting_selected AS r
LEFT JOIN customers_selected AS c
ON r.customer_id = c.customer_id;

| renting_id | customer_id | rating | customer_id | name               | gender |
|------------|-------------|--------|-------------|--------------------|--------|
| 518        | 1           | `null` | 1           | Robert Bohm        | male   |
| 203        | 2           | 6      | 2           | Wolfgang Ackermann | male   |
| 478        | 4           | `null` | 4           | Julia Jung         | female |
| 292        | 4           | 8      | 4           | Julia Jung         | female |
| 477        | 5           | 7      | `null`      | `null`             | `null` |

Más de un JOIN

SELECT m.title, 
       c.name
FROM renting AS r
LEFT JOIN movies AS m
ON r.movie_id = m.movie_id
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id;

¡Vamos a practicar!

Toma de decisiones basada en datos en SQL