और जोइन

Oracle SQL परिचय

Sara Billen

Curriculum Manager

अन्य जोइन प्रकार

CROSS JOIN
Self JOIN

Cartesian product (उर्फ़ cross product)

कार्तीय गुणन का आरेख

टेबल्स पर क्रॉस प्रोडक्ट

क्रॉस प्रोडक्ट उदाहरण

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              |

क्रॉस प्रोडक्ट उदाहरण

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

एक टेबल को उसी से जोड़ेँ

उपयोग:

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

अभ्यास करते हैं!

Oracle SQL परिचय