Tabellen verknüpfen

Einführung in SQL Server

John MacKintosh

Instructor

Relationale Datenbanken

Primärschlüssel

Primärschlüssel: Identifiziert jede Zeile in einer Tabelle eindeutig

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

Primärschlüssel: 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         |
+----------+-------------------------+-----------+

Primärschlüssel: album_id
Was ist mit artist_id?

Fremdschlüssel

Tabelle artist

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

Tabelle 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: Fremdschlüssel zu artist

Album und Artist verknüpfen

Tabelle artist

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

AC/DC: artist_id = 1

Tabelle 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         |
+----------+-------------------------+-----------+

Zeilen 1 und 4: artist_id = 1

Album und Artist verknüpfen

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

Gibt Albumdetails aus Tabelle album zurück
Gibt passende Artist-Infos aus Tabelle artist zurück
Über Spalte artist_id verknüpft

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

Syntax für INNER JOINs

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

Gibt alle Kombinationen aller Übereinstimmungen zwischen album und artist zurück

Mehrere INNER JOINs

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;

Lass uns üben!

Einführung in SQL Server