Joindre des tables

Introduction à SQL Server

John MacKintosh

Instructor

Bases de données relationnelles

Clés primaires

Clés primaires : Identifier de manière unique chaque ligne d'une table

+-----------+-------------------+
| artist_id | name              | 
|-----------+-------------------|
| 1         | AC/DC             |
| 2         | Accept            |
| 3         | Aerosmith         |
| 4         | Alanis Morissette |
| 5         | Alice In Chains   |
+-----------+-------------------+

Clé primaire : artist_id

+----------+-------------------------+-----------+
| album_id | title                   | artist_id |  
|----------+-------------------------+-----------|
| 1        | For Those About To Rock | 1         |
| 2        | Balls to the Wall       | 2         |
| 3        | Restless and Wild       | 2         |
| 4        | Let There Be Rock       | 1         |
| 5        | Big Ones                | 3         |
+----------+-------------------------+-----------+

Clé primaire : album_id
Qu'en est-il de artist_id ?

Clés externes

Table artist

+-----------+-------------------+
| artist_id | name              | 
|-----------+-------------------|
| 1         | AC/DC             |
| 2         | Accept            |
| 3         | Aerosmith         |
| 4         | Alanis Morissette |
| 5         | Alice In Chains   |
+-----------+-------------------+

Table album

+----------+-------------------------+-----------+
| album_id | title                   | artist_id |  
|----------+-------------------------+-----------|
| 1        | For Those About To Rock | 1         |
| 2        | Balls to the Wall       | 2         |
| 3        | Restless and Wild       | 2         |
| 4        | Let There Be Rock       | 1         |
| 5        | Big Ones                | 3         |
+----------+-------------------------+-----------+

artist_id : Clé externe vers artist

Rejoindre l'album et l'artiste

Table artist

+-----------+-------------------+
| artist_id | name              | 
|-----------+-------------------|
| 1         | AC/DC             |
| 2         | Accept            |
| 3         | Aerosmith         |
| 4         | Alanis Morissette |
| 5         | Alice In Chains   |
+-----------+-------------------+

AC/DC a artist_id = 1

Table album

+----------+-------------------------+-----------+
| album_id | title                   | artist_id |  
|----------+-------------------------+-----------|
| 1        | For Those About To Rock | 1         |
| 2        | Balls to the Wall       | 2         |
| 3        | Restless and Wild       | 2         |
| 4        | Let There Be Rock       | 1         |
| 5        | Big Ones                | 3         |
+----------+-------------------------+-----------+

Les lignes 1 et 4 ont pour valeur artist_id = 1

Rejoindre l'album et l'artiste

+----------+-------------------------+-----------+-------------+
| album_id | title                   | artist_id | artist_name | 
|----------+---------------------------------------+-----------|
| 1        | For Those About To Rock | 1         | AC/DC       |
| 4        | Let There Be Rock       | 1         | AC/DC       |
+----------+-------------------------+-----------+-------------|

Renvoyer les détails de l'album à partir de la table album
Renvoyer les informations correspondantes sur l'artiste à partir de la table artist
Jointure sur la colonne artist_id

INNER JOIN

SELECT 
  album_id, 
  title, 
  album.artist_id, 
  name AS artist_name 
FROM album 
INNER JOIN artist ON artist.artist_id = album.artist_id 
WHERE album.artist_id = 1;

+----------+-------------------------+-----------+-------------+
| album_id | title                   | artist_id | artist_name | 
|----------+---------------------------------------+-----------|
| 1        | For Those About To Rock | 1         | AC/DC       |
| 4        | Let There Be Rock       | 1         | AC/DC       |
+----------+-------------------------+-----------+-------------|

Syntaxe CROSS JOIN

SELECT 
  table_A.columnX, 
  table_A.columnY, 
  table_B.columnZ 
FROM table_A 
INNER JOIN table_B ON table_A.foreign_key = table_B.primary_key;

SELECT 
  album_id, 
  title, 
  album.artist_id, 
  name AS artist_name 
FROM album 
INNER JOIN artist on artist.artist_id = album.artist_id;

+----------+---------------------------------------+-----------+
| album_id | title                   | artist_id | artist_name | 
|----------+---------------------------------------+-----------|
| 1        | For Those About To Rock | 1         | AC/DC       |
| 4        | Let There Be Rock       | 1         | AC/DC       |
| 2        | Balls To The Wall       | 2         | Accept      |
| 3        | Restless and Wild       | 2         | Accept      |
+----------+---------------------------------------+-----------+

Renvoie toutes les combinaisons de toutes les correspondances entre album et artist

Jointures INNER multiples

SELECT 
  table_A.columnX, 
  table_A.columnY, 
  table_B.columnZ, table_C columnW 
FROM table_A 
INNER JOIN table_B ON table_B.foreign_key = table_A.primary_key 
INNER JOIN table_C ON table_C.foreign_key = table_B.primary_key;

Joignons quelques tables !

Introduction à SQL Server