Beoordelingen koppelen aan klantgegevens

Data-driven beslissingen nemen met SQL

Irene Ortner

Data Scientist at Applied Statistics

JOIN

Vereiste cursus over data joinen in SQL

LEFT JOIN

LEFT JOIN is een outer join.
Behoud alle rijen van de linkertabel, match met rijen in de rechtertabel.
Gebruik een sleutel om rijen tussen tabellen te matchen.

Een tabel een naam geven

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

Tabellen voor LEFT JOIN

Linkertabel: 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` |

Rechtertabel: customers_selected

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

LEFT JOIN-voorbeeld

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` |

Meer dan één 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;

Laten we oefenen!

Data-driven beslissingen nemen met SQL