Using joins

Introduction to Oracle SQL

Sara Billen

Curriculum Manager

Chinook dataset

Full Chinook dataset ER diagram

Combining data from more than one table

Pearl Jam Albums

SQL joins

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

Breakdown of an inner join

Select columns to output and include table names
In the FROM, list the relevant tables separated by INNER JOIN
Use ON to define the column to join on

SELECT Album.Title, Artist.Name

FROM Album INNER JOIN Artist

ON Album.ArtistId = Artist.ArtistId

Add in other necessary clauses like 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 instead of ON

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

same as

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

Columns need to be identically named in the two tables
Enclose column name in parentheses

Table aliases

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

With aliases:

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

Joining more than two tables

Track, album, and artist tables

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)

Joining more than two tables

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

Let's practice!

Introduction to Oracle SQL