조인 사용

Oracle SQL 입문

Sara Billen

Curriculum Manager

Chinook 데이터셋

Chinook 데이터셋 ER 다이어그램

둘 이상 테이블의 데이터 결합

펄 잼 앨범

SQL 조인

조인 유형:

Inner Join
Outer Joins
Cross Joins
Self Joins

내부 조인

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

내부 조인 구성 요소

출력할 열 선택, 테이블명 포함
FROM에 관련 테이블을 INNER JOIN으로 나열
ON으로 조인할 열 지정

SELECT Album.Title, Artist.Name

FROM Album INNER JOIN Artist

ON Album.ArtistId = Artist.ArtistId

WHERE, ORDER BY 같은 필요한 절을 추가하십시오!

내부 조인

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)

두 테이블에서 열 이름이 같아야 함
열 이름은 괄호로 감싸기

테이블 별칭

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

별칭 사용:

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

두 개 초과 테이블 조인

트랙, 앨범, 아티스트 테이블

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)

두 개 초과 테이블 조인

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 입문