Chapter4 - 조인식

이 장에서는 계속해서 SQL 에 대해서 다룰 것이다. 뷰 정의, 트랜잭션, 무결성 조건 등과 같은 좀 더 복잡한 형태의 SQL 질의에 대해서 다루고 SQL 데이터 정의, 권한 허가에 대해서 자세히 다룰 것이다.

조인식

3.3.3절에서 자연 조인(natural join)에 대해서 알아보았다. SQL은 특정 조인 조건(join predicate)을 지정할 수 있는 것을 다른 형태의 조인 연산을 제공한다. 이러한 연산은 자연 조인에 의해서 제거된 투플을 결과에 포함할 수도 있다.  이 절에서 이러한 조인의 형태에 대해서 알아볼 것이다.

    이 절에 포함된 예제들은 그림 4.1과 그림 4.2에 나타난 student 릴레이션과 takes 릴레이션에 관한 것이다. ID 가 98988인 학생은 2010년 여름에 수강한 BIO-301, 분반 1 수업의 grade 속성이 널 값임을 알 수 있다 널 값은 해당 성적이 아직 정해지지 않았음을 의미한다.

4.1.1 조인 조건

3.3.3절에서 자연 조인을 어떻게 표현하는지 알아보았다. 또한, join... using 절을 이용해서 특정 속성에 대해서 일치되는 값들만들 이용한 자연 조인을 하는 방법을 알아보았다. SQL은 임의의 조인 조건을 정할 수 있는 다른 조인의 형태를 지원한다.

    on 조건은 조인될 릴레이션에 대한 일반적인 조건을 정할 수 있다. 이러한 조건은 SQL 문에서 where 절과 마찬가지로 사용될 수 있다. 차이점은 where 라는 단어 대신에 on 이라는 단어를 사용한다는 것뿐이다. using 조건과 같이 on 조건은 조인식의 가장 마지막 부분에 나타난다. 

    조인식에 on 조건을 포함하고 있는 다음의 질의를 생각해보자.

select *

from student join takes on student.ID = takes.ID;

위 질의에서 on 조건은 ID 값이 일치하는 student 릴레이션의 투플과 takes 릴레이션의 투플을 조인하게 된다. 이러한 경우의 조인식은 student natural join takes와 거의 같은 결과를 나타내게 된다. 왜냐하면 자연 조인 역시 student 투플과 takes 투플에서 서로 일치하는 투플을 조인하기 때문이다. 단 하나의 차이점은 결과에 ID 속성이 student 에서 한 번, takes 에서 한 번, 총 두 번 나타난다는 것이다.

    사실 위 질의는 다음과 같은 질의와 동등하다(다시 말해서, 정확히 같은 결과를 생성한다.)

select *

from student, takes

where student.ID = takes.ID;

    앞서 살펴본 바와 같이, 속성 ID 가 두 릴레이션에서 같은 이름으로 사용되기 때문에 이를 구별하기 위해서 릴레이션의 이름이 사용되었다. 이러한 경우, student.ID, takes.ID 와 같이 사용함으로써 구별이 가능하다. 이 질의는 다음과 같이 표현함으로써 결과에 ID 속성을 단 하나만 나타나도록 할 수 있다.

select student.ID as ID, name, dept_name, tot_cred,

course_id, sec_id, semester, year, grade

from student join takes on student.ID = takes.ID;

이 질의에 대한 결과는 그림 4.3 에 나타나 있다.

    on 조건은 모든 SQL 조건을 표현할 수 있다. 그렇기 때문에 on 조건을 사용한 조인식은 자연 조인에 비해서 훨씬 다양한 표현을 할 수 있다. 하지만 바금 전의 예제에서 볼 수 있듯이 on 조건을 사용한 조인식은 on 절을 where 절로 치환함으로써 on 조건을 사용하지 않은 동등한 식으로 표현될 수 있따. 결과적으로 on 조건은 SQL에 있어서 중복되는 요소일 수 있다.

    하지만 이러한 on 조건을 언급하는 두 가지 이유가 있다. 첫 번째, 외부 조인과 같은 경우에 on 조건은 where 조건과 다르게 사용될 수 있따. 주 번째, 조인 조건은 on 절에 명시되고 나머지 부분은 where 절에 나타난다면 사람들은 SQL 질의를 좀 더 쉽게 해석할 수도 있다.

4.1.2 외부 조인

가령 모든 학생들의 ID, name, dept_name, tot_cred 와 그 학생들이 수강한 수업의 목록을 출력하고 싶다고 하자. 다음의 SQL 질의가 이러한 결과를 출력할 수 있을 것이다.

select *

from student natural join takes;

불행히도 위 질의는 원래의 의도대로 실행되지 않는다. student 릴레이션의 학생 중에 수업을 듣지 않는 학생이 있다면 student 릴레이션의 학생 중에 수업을 듣지 않은 학생이 있다면 takes 릴레이션의 어떤 투플과도 자연 조인 조건을 만족시키지 못하게 되므로 질의의 결과에 나타나지 않을 것이다. 따라서 수업을 듣지 않은 학생의 정보는 질의의 결과로 얻지 못하게 될 것이다. 

    예를 들어, 그림 4.1 과 그림 4.2의 student 릴레이션과 takes 릴레이션에서 ID가 70557인 Snow 학생을 살펴보면 수업을 전혀 듣지 않았다. Snow는 student 릴레이션에는 나타나지만 takes 리레이션에는 Snow의 ID가 나타나지 않는다. 이러한 경우, Snow는 자연 조이의 결과에는 나타나지 않을 것이다.

    일반적으로 몇몇 투플은 이러한 방식의 조인에서느 겨와에서 빠지게 된다. 외부 조인(outer join) 연산은 이미 언급한 조인 연산과 비슷한 방식으로 동작한다. 하지만 조인의 결과에서 빠질 수 있는 투플을 널 값을 이용해서 보존하게 된다.

예를 들어, 방금 전의 예제에서 Snow가 결과에 나타나도록 하기 위해서, student 릴레이션으로 부터 Snow가 가지고 있는 정보를 결과에 더하고 course_id, sec_id, semester,year 와 같이 takes 릴레이션에 나타나 있는 정보는 널 값으로 입력하게 된다. 이러한 방식으로 외부 조인에서 Snow 학생의 투플이 보존되게 된다.

    외부 조인은 다음과 같은 세 가지 형태가 존재한다.

-왼쪽 외부 조인(left outer join) 은 왼쪽 외부 조인 연산의 왼쪽에 나타난 릴레이션의 투플을 보존하게 된다.

-오른쪽 외부 조인(right outer join) 은 오른쪽 외부 조인 연산의 오른쪽에 나타난 릴레이션의 투플만을 보존하게 된다.

-전체 외부 조인(full outer join) 은 두 릴레이션의 모든 투플을 보존한다.

반대로, 이전에 살펴본 조인 연산과  같이 서로 같은 값을 가지고 있지 않은 투플은 보존하지 않는 조인을 외부 조인과 구별하기 위해서 내부 조인(inner join)연산이라고 한다.

     지금까지 외부 조인이 어떻게 동작하는지 알아보았다. 왼쪽 외부 조인 연산은 다음과 같이 수행할 수 있다. 우선, 내부 조인의 결과를 수행한다. 그런 후 조인 연산의 왼쪽에 위치한 릴레이션에 존재하는 투플 중에서 내부 조인에서 조인 연산의 오른쪽에 위치한 릴레이션의 투플과 연결되지 않은 모든 투플 r을 다음과 같이 추가한다.

- 조인 연산의 왼쪽에 있는 릴레이션의 투플 r의 속성은 투플 r의 값으로 그대로 채워진다.

- 투플 r의 나머지 속성은 모두 널 값으로 채운다.

     그림 4.4는 다음 질의의 결과를 나타낸다.

select*

from student natural left outer join takes;

이 결과에는 내부 조인의 결과와는 다르게 ID 가 70557인 Snow 학생이 포함되어 있다. Snow 의 투플은 takes 릴레이션의 스키마에 나타난느 속성에 대해서는 널 값을 포함하고 있다.

    외부 조인 연산을 사용한 또 다른 예제로써 "수업을 한번도 수강하지 않은 모든 학생을 찾아라." 라는 질의는 다음과 같이 작성할 수 있다.

select ID

from student natural left outer join takes

where course_id is null;

오른쪽 외부 조인(right outer join) 은 왼쪽 외부 조인과 대칭적이다. 조인 연산의 오른쪽에 위치한 릴레이션에 존재하는 투플 중에서 조인 연산의 왼쪽에 위치한 릴레이션에 존재하는 투플 주에서 조인 연산의 왼쪽에 위치한 릴레이션의 투플과 연결되지 않은 투플을 널 값을 이용해서 오른쪽 외부 조인의 결과에 추가된다. 위 질의를 오른쪽 외부 조인을 사용해서 같은 결과를 나타내기 위해서는 릴레이션의 위치를 다음과 같이 바꿔주면 된다.

select*

from takes natural right outer join student;

그림 4.5에서 볼 수 있듯이 속성의 순서만이 다를 뿐, 같은 결과를 얻을 수 있다.

    전체 외부 조인(full outer join) 은 왼쪽 외부 조인과 오른쪽 외부 조인을 통합한 것이다. 내부 조인을 수행해서 결과를 얻어낸 후에, 조인 연산의 왼쪽에 위치한 릴레이션의 투플 중에서 오른쪽에 위치한 릴레이션의 투플과 연결되지 않은 투플을 널 값을 이용해서 추가한다. 비슷한 방식으로 조인 연사의 오른쪽에 위치한 릴레이션의 투플 중에서 왼쪽에 위치한 릴레이션의 투플과 연결되지 않은 투플을 널 값을 이용해서 추가한다.

    전체 외부 조인의 예제로 "Comp. Sci. 학과의 모든 학생의 목록을 출력하고, 그 학생 중에서 2009년 봄에 수업을 들은 것이 있다면 그 정보를 함께 출력하라. 그리고 Comp. Sci 학과의 학생이 수업을 듣지 않았더라고 2009년 봄에 열린 모든 수업을 출력하라."와 같은 질의는 다음과 같이 작성할 수 있다.

select*                                                                

from(select*                                                        

from student                                         

where dept_name = 'Comp. Sci')                

natural full outer join                             

(select*                                                  

from takes                                            

where semester = 'Spring' and year = 2009);

    외부 조인에서 on 조건을 사용할 수 있다. 다음의 질의는 ID 속성이 결과에 두 번 나타난다는 것을 제외하고는 "student natural left outer join takes" 와 정확히 일치한다.

select*                                                                     

from student left outer join takes on student.ID = takes.ID;

    전에 언급한 바와 같이 on 과 where는 외부 조인에서는 다르게 동작한다. 왜냐하면 외부 조인은 내부 조인에서 연결되지 못한 투플에 대해서만 널 값을 이용해서 결과에 추가하기 때문이다. on 조건은 외부 조인 명세의 한 부분이지만 where 절은 그렇지 않다. 이전의 예제에서 나온 ID 가 70557 인 "Snow"학생의 투플을 생각해보면 이러한 차이를 알 수 있다. 다음 질의와 같이 on 절의 조건을 where 절로 옮기ㄷ고 on 조건에는 true 를 사용해서 수정해 보자.

select*                                               

from student left outer join takes on true

where student.ID = takes.ID;                   

on 조건을 이용한 왼쪽 외부 조인을 사용한 이전의 질의의 결과는 (70557, Snow, Physics, 0, null, null, null, null, null, null) 투플을 포함하고 있다. 왜냐하면  takes 릴레이션에는 ID = 70557 을 만족하는 투플이 존재하지 않았기 때문이다. 하지만 방금 수정한 질의에서는 모든 투플이 조인 조건을 true 로써 만족하기 때문에 외부 조인 연산이 널 값을 이용한 투플 추가를 수행하지 않는다. 이러한 경우에 외부 조인은 카티션 곱과 같이 동작하게 된다. takes 릴레이션에는 ID = 70557 조건을 만족하는 투플이 존재하지 않기 때문에, name = "Snow" 로 외부 조인을 수행할 때마다 student.ID 와 takes.ID 는 서로 다른 값을 가지게 되고 이러한 투플은 where 절에 의해서 제거된다. 결과적으로 수정된 질의에서 Snow 학생은 결과에 나타나지 않는다.

4.1.3 조인의 종류와 조인 조건

일반적인 조인과 외부 조인을 구별하기 위해서 SQL에서는 일반적인 조인을 내부 조인(inner join)이라는 용어로 사용한다. 일반적인 조인 절은 외부 조인(outer join)을 의미하는 것이 아니라 내부 조인을 의미한다. 그러므로 내부(inner)라는 단어를 사용하는 것은 선택적인 것이다. 일반적인 조인의 종류에서 외부(outer)라는 단어가 붙지 않는 조인은 모두 내부 조인이다.

select*                                    

from student join takes using(ID);

앞의 질의는 다음과 같이 변경될 수 있다.

select*                                            

from student inner join takes using(ID);

이와 비슷하게, 자연 조인(natural join)은 자연 내부 조인(natural inner join)과 동등하다. 

    그림 4.6은 지금까지 다룬 다양한 종유릐 조인의 전체 목록을 보여준다. 위 그림에서 볼 수 있듯이 모든 종류의 조인(내부, 왼쪽 외부, 오른쪽 외부, 전체 외부) 은 모든 조인 조건(natural, using, on)과 조합될 수 있다.