Więcej złączeń

Wprowadzenie do Oracle SQL

Sara Billen

Curriculum Manager

Inne typy złączeń

CROSS JOIN
Self JOIN

Iloczyn kartezjański (inaczej: iloczyn krzyżowy)

Diagram iloczynu kartezjańskiego

Iloczyn krzyżowy na tabelach

Przykład iloczynu krzyżowego

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              |

Przykład iloczynu krzyżowego

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

Złączenie tabeli z samą sobą

Przykład użycia:

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

Czas na ćwiczenia!

Wprowadzenie do Oracle SQL