Joins gebruiken

Introductie tot Oracle SQL

Sara Billen

Curriculum Manager

Chinook-dataset

Volledig ER-diagram van de Chinook-dataset

Gegevens uit meer dan één tabel combineren

Pearl Jam-albums

SQL-joins

Soorten joins:

Inner join
Outer joins
Cross joins
Self joins

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

Uitleg van een inner join

Kies de kolommen en zet de tabelnamen erbij
Noem in FROM de tabellen, gescheiden door INNER JOIN
Gebruik ON om de join-kolom te bepalen

SELECT Album.Title, Artist.Name

FROM Album INNER JOIN Artist

ON Album.ArtistId = Artist.ArtistId

Voeg andere clausules toe zoals 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 in plaats van ON

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

hetzelfde als

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

Kolommen moeten in beide tabellen exact dezelfde naam hebben
Plaats de kolomnaam tussen haakjes

Tabelaliassen

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

Met aliassen:

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

Meer dan twee tabellen joinen

Tabellen Track, Album en 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)

Meer dan twee tabellen joinen

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

Laten we oefenen!

Introductie tot Oracle SQL