Utiliser les jointures

Introduction à Oracle SQL

Sara Billen

Curriculum Manager

Ensemble de données Chinook

Schéma relationnel complet de l'ensemble de données Chinook

Combiner des données de plusieurs tables

Albums de Pearl Jam

Jointures SQL

Types de jointures :

Jointure interne
Jointures externes
Jointures croisées
Auto-jointures

Jointure interne

SELECT Album.Title, Artist.Name
FROM Album INNER JOIN Artist
ON  Album.ArtistId = Artist.ArtistId

| Album.Title                     | Artist.Name   |
|---------------------------------|---------------|
| Black Sabbath                   | Black Sabbath |
| Black Sabbath Vol. 4 (Remaster) | Black Sabbath |
| The Cream Of Clapton            | Eric Clapton  |
| Unplugged                       | Eric Clapton  |
| ...                             | ...           |

Décomposition d'une jointure interne

Sélectionner les colonnes à afficher et inclure les noms de table
Dans FROM, lister les tables séparées par INNER JOIN
Utiliser ON pour définir la colonne de jointure

SELECT Album.Title, Artist.Name

FROM Album INNER JOIN Artist

ON Album.ArtistId = Artist.ArtistId

Ajouter d'autres clauses au besoin, comme WHERE, ORDER BY !

Jointure interne

SELECT Album.Title, Artist.Name
FROM Album INNER JOIN Artist
ON  Album.ArtistId = Artist.ArtistId
WHERE Artist.Name = 'Pearl Jam'

| Album.Title | Artist.Name |
|-------------|-------------|
| Pearl Jam   | Pearl Jam   |
| Riot Act    | Pearl Jam   |
| Ten         | Pearl Jam   |
| ...         | ...         |

USING au lieu de ON

SELECT Album.Title, Artist.Name
FROM Album INNER JOIN Artist
ON  Album.ArtistId = Artist.ArtistId

équivaut à

SELECT Album.Title, Artist.Name
FROM Album INNER JOIN Artist
USING (ArtistId)

Les colonnes doivent avoir le même nom dans les deux tables
Mettre le nom de colonne entre parenthèses

Alias de table

SELECT DISTINCT Customer.FirstName, Customer.LastName, 
                Employee.FirstName, Employee.LastName
FROM Customer INNER JOIN Employee
ON Customer.SupportRepID = Employee.EmployeeID

Avec alias :

SELECT DISTINCT c.FirstName, c.LastName, e.FirstName, e.LastName
FROM Customer c INNER JOIN Employee e
ON c.SupportRepID = e.EmployeeID

Joindre plus de deux tables

Tables Track, Album et Artist

SELECT t.Name AS Track, al.Title AS Album, ar.Name AS Artist
FROM 
    Track t INNER JOIN Album al USING (AlbumId)
    INNER JOIN Artist ar USING (ArtistId)

Joindre plus de deux tables

SELECT t.Name as Track, al.Title as Album, ar.Name as Artist
FROM 
    Track t INNER JOIN Album al USING (AlbumId)
    INNER JOIN Artist ar USING (ArtistId)

| Track              | Album                          | Artist      |
|--------------------|--------------------------------|-------------|
| The Legacy         | A Matter of Life and Death     | Iron Maiden |
| Lord of Light      | A Matter of Life and Death     | Iron Maiden |
| Out of the Shadows | A Matter of Life and Death     | Iron Maiden |
| ...                | ...                            | ...         |

Passons à la pratique !

Introduction à Oracle SQL