Menggabungkan tabel

Pengantar SQL Server

John MacKintosh

Instructor

Basis Data Relasional

Primary Key

Primary key: Mengidentifikasi unik setiap baris dalam tabel

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

Primary key: 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         |
+----------+-------------------------+-----------+

Primary key: album_id
Bagaimana dengan artist_id?

Foreign key

Tabel artist

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

Tabel 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: Foreign key ke artist

Menggabungkan album dan artist

Tabel artist

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

AC/DC memiliki artist_id = 1

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

Baris 1 dan 4 memiliki artist_id = 1

Menggabungkan album dan artist

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

Kembalikan detail album dari tabel album
Kembalikan detail artis terkait dari tabel artist
Digabung menggunakan kolom 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       |
+----------+-------------------------+-----------+-------------|

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

Mengembalikan semua kombinasi dari semua kecocokan antara album dan artist

Beberapa INNER JOIN

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;

Ayo gabungkan beberapa tabel!

Pengantar SQL Server