Más joins

Introducción a Oracle SQL

Sara Billen

Curriculum Manager

Otros tipos de join

CROSS JOIN
Self JOIN

Producto cartesiano (también llamado cross product)

Diagrama de producto cartesiano

Cross product en tablas

Ejemplo de producto cartesiano

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              |

Ejemplo de producto cartesiano

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

Une una tabla consigo misma

Caso de uso:

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

¡Vamos a practicar!

Introducción a Oracle SQL