Menggunakan join

Pengantar Oracle SQL

Sara Billen

Curriculum Manager

Dataset Chinook

Diagram ER lengkap dataset Chinook

Menggabungkan data dari lebih dari satu tabel

Album Pearl Jam

Join di SQL

Jenis join:

Inner Join
Outer Join
Cross Join
Self Join

Inner join

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

Rincian inner join

Pilih kolom output dan sertakan nama tabel
Di FROM, daftar tabel yang relevan dipisahkan dengan INNER JOIN
Gunakan ON untuk mendefinisikan kolom penggabungan

SELECT Album.Title, Artist.Name

FROM Album INNER JOIN Artist

ON Album.ArtistId = Artist.ArtistId

Tambahkan klausa lain seperti WHERE, ORDER BY!

Inner join

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 alih-alih ON

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

sama dengan

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

Nama kolom harus identik di kedua tabel
Cantumkan nama kolom dalam tanda kurung

Alias tabel

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

Dengan alias:

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

Menggabungkan lebih dari dua tabel

Tabel track, album, dan 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)

Menggabungkan lebih dari dua tabel

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

Ayo berlatih!

Pengantar Oracle SQL