More joins

Introduction to Oracle SQL

Sara Billen

Curriculum Manager

Other join types

CROSS JOIN
Self JOIN

Cartesian product (AKA cross product)

Cartesian product diagram

Cross product on tables

Cross product example

SELECT * 
FROM MediaType

| MediaTypeId | Name                        |
|-------------|-----------------------------|
| 1           | MPEG audio file             |
| 2           | Protected AAC audio file    |
| 3           | Protected MPEG-4 video file |
| 4           | Purchased AAC audio file    |
| 5           | AAC audio file              |

Cross product example

SELECT m.MediaTypeId, m.Name, t.TrackId, t.Name, t.MediaTypeId
FROM MediaType m CROSS JOIN Track t

| MediaTypeId | Name                        | TrackId | Name       | MediaTypeId       |
|-------------|-----------------------------|---------|------------|-------------------|
| 1           | MPEG audio file             | 3021    | Forty      | 1                 |
| 2           | Protected AAC audio file    | 3021    | Forty      | 1                 |
| 3           | Protected MPEG-4 video file | 3021    | Forty      | 1                 |
| 4           | Purchased AAC audio file    | 3021    | Forty      | 1                 |
| 5           | AAC audio file              | 3021    | Forty      | 1                 |

Self join

Join a table to itself

Use case:

SELECT * FROM Employee

| EmployeeId | LastName | Title               | ... | ReportsTo |
|------------|----------|---------------------|-----|-----------|
| 1          | Adames   | General Manager     | ... | null      |
| 2          | Edwards  | Sales Manager       | ... | 1         |
| 3          | Peacock  | Sales Support Agent | ... | 2         |
| 4          | Park     | Sales Support Agent | ... | 2         |

Self join

SELECT e.LastName Employee, m.LastName ReportsTo
FROM Employee e JOIN Employee m
ON (e.ReportsTo = m.EmployeeId)

| Employee | ReportsTo |
|----------|-----------|
| Edwards  | Adams     |
| Peacock  | Edwards   |
| Mitchell | Adams     |
| Park       | Edwards   |
| ...      | ...       |

Let's practice!

Introduction to Oracle SQL