Joins का उपयोग

Oracle SQL परिचय

Sara Billen

Curriculum Manager

Chinook डेटासेट

पूरे Chinook डेटासेट का ER डायग्राम

एक से अधिक टेबल से डेटा जोड़ना

Pearl Jam एल्बम

SQL joins

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

Inner join का ब्रेकडाउन

आउटपुट के कॉलम चुनें और टेबल नाम शामिल करें
FROM में, संबंधित टेबल्स को INNER JOIN से अलग करें
किस कॉलम पर join होगा, यह बताने के लिए ON उपयोग करें

SELECT Album.Title, Artist.Name

FROM Album INNER JOIN Artist

ON Album.ArtistId = Artist.ArtistId

आवश्यक अन्य क्लॉज़ भी जोड़ें, जैसे 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   |
| ...         | ...         |

ON की जगह USING

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)

दोनों टेबल में कॉलम के नाम समान होने चाहिए
कॉलम नाम को कोष्ठकों में रखें

टेबल aliases

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

Aliases के साथ:

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

दो से अधिक टेबल्स को join करना

Track, Album, और Artist टेबल्स

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)

दो से अधिक टेबल्स को join करना

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

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

Oracle SQL परिचय