상위 문서: SQL
개요
- Sample gallery
-
Figure 1. The student relation
-
Figure 2. The takes relation
Join 연산은 두 개의 릴레이션(relation)을 입력으로 받고 그 결과로 또 다른 릴레이션을 만들어내는 연산이다. Join은 데카르트 곱(Cartesian product)의 일종으로, 두 릴레이션에 있는 튜플들을 모든 가능한 쌍으로 조합한 다음 특정 조건을 만족하는 튜플 쌍만 선택해서 결과로 내보낸다. 또한 릴레이션에 포함될 속성(attribute)들도 지정된다. Join 연산은 보통 SQL 쿼리(query)에서 from 절의 subquery 표현식으로 사용된다. Join 연산의 종류는 다음 세가지가 있다:
- Natural Join: 두 테이블에서 이름이 같은 속성들끼리 자동으로 비교해서 join 한다.
- Inner Join: 조건을 만족하는 쌍만 결과에 포함된다. (null이 있는 건 제외)
- Outer Join: 조건에 맞지 않아도 한쪽 테이블의 데이터를 모두 유지한다. → Left / Right / Full Outer Join으로 나뉜다.
해당 문서에서 사용되는 모든 예제들은 figure 1, 2로 제공되는 두 릴레이션인 student와 takes에 대한 것이다.
The Natural Join
다음의 “모든 학생들 중에서 어떤 과목이라도 수강한 학생들의 이름과 수강한 과목의 ID를 구하라”라는 SQL 쿼리[1]를 보자:
select name, course_id
from student, takes
where student.ID = takes.ID;
위 쿼리는 where 절의 조건은 student와 takes 테이블에서 student.ID가 takes.ID와 같아야 한다는 것이다. 이와 같은 조건은 매우 흔하게 사용된다. 즉, from 절에 있는 일치 조건은 이름이 같은 모든 속성들을 등호(=)로 비교하는 것이 대부분이다. 이러한 일반적인 경우를 위해서 SQL은 natural join이라는 연산을 지원한다. Natural join 연산은 두 릴레이션에 대해 동작하고, 양쪽 릴레이션 스키마에 공톡으로 존재하는 속성의 값이 동일한 튜플 쌍만을 고려하여 하나의 릴레이션을 생성한다. 예를 들어,
student natural join takes
이 연산은 두 튜플의 공통 속성(ID)의 값이 같을 경우에만 그 튜플 쌍을 고려한다. 이때, 두 릴레이션의 스키마에 공통으로 존재하는 속성들은 한 번만 나타나며 반복되지 않는다. 또한 속성들의 순서는 1) 공통 속성, 2) 첫번째 릴레이션에만 있는 속성들, 3) 두번째 릴레이션에만 있는 속성들 순으로 고정된다. 이를 바탕으로 figure 1의 student와 takes 간의 natural join 연산 결과를 이해할 수 있다.
“모든 학생들 중에서 어떤 과목이라도 수강한 학생들의 이름과 수강한 과목의 ID를 구하라”라는 쿼리는 natural join을 사용하여 다음과 같이 더 간단하게 작성할 수 있다.
select name, course_id
from student natural join takes;
Dangerous in Natural Join
SQL의 from 절에는 다음과 같이 여러 릴레이션을 natural join으로 연결할 수 있다.
select A1, A2, ..., An
from r1 natural join r2 natural join ... natural join rm
where P;
--보다 일반적인 형태. Ei는 하나의 릴레이션이거나 natural relation을 포함하는 표현식
from E1, E2, ..., En
예를 들어, “학생들의 이름과, 그들이 수강한 과목들의 제목(title)을 나열하라”는 질의를 SQL로 다음과 같이 쓸 수 있다:
select name, title
from student natural join takes, course
where takes.course_id = course.course_id;
해당 코드에서는 먼저 student natural join takes를 수행한 후 해당 결과와 course 테이블의 데카르트 곱을 계산한다.[2] 그리고 그 결과 테이블에서 where 절에 따라 course_id가 같은 튜플만 추출한다.
반면, 다음 쿼리는 동일한 결과를 생성하지 않는다.
select name, title
from student natural join takes natural join course;
이유를 이해하기 위해, 먼저 student natural join takes의 결과를 보자. 이 결과에는 다음과 같은 속성들이 있다:
(ID, name, dept_name, tot_cred, course_id, sec_id)
따라서 student natural join takes이 course와 join할 때, dept_name 속성도 동일해야 한다는 조건이 추가로 필요하다. 즉, 학생의 전공 학과와 과목의 학과가 같아야만 join이 이루어지는 것이다. 따라서 학생이 자신의 학과가 아닌 다른 학과의 과목을 수강한 경우는 (name, title) 결과 쌍에서 누락된다.
Natural Join with Using Clause
Natural join을 사용하면서도 불필요한 속성 간의 자동 비교로 인한 오류를 피하기 위해 SQL은 using 절을 사용하여 어떤 속성들끼리만 같아야 하는지를 명시적으로 지정할 수 있는 natural join 형식을 제공한다. 아래는 그 예시이다.
select name, title
from (student natural join takes) join course using (course_id);
join ... using 연산은 두 릴레이션의 공통 속성 목록을 명시해야 한다. 예를 들어 r1 join r2 using (A1, A2)은 다음과 같은 동작을 한다.
- 튜플 t1과 t2가 매치되기 위한 조건은
t1.A1 = t2.A1및t1.A2 = t2.A2이어야 하며, - 만약 r1과 r2가 공통으로 A3 속성을 가지고 있더라도
t1.A3 = t2.A3일 필요는 없다.
따라서 위의 SQL 질의에서는, 학생의 dept_name과 과목의 dept_name이 달라도 상관없으며, 의도대로의 결과를 출력한다.
Join Condition
SQL은 join ... using와 같이 임의의 join 조건을 지정할 수 있는 또 다른 형태의 join도 지원한다. on 조건은 조인되는 릴레이션들에 대한 where 절과 같은 일반적인 조건식(predicate)을 허용한다. 다음은 on 조건을 포함하는 join 표현식을 가진 쿼리의 예시이다:
select *
from student join takes on student.ID = takes.ID;
위의 on 조건은 student의 튜플과 takes의 튜플이 ID 값이 같을 경우 일치한다는 것을 명시한다. 해당 join 표현식은 student natural join takes와 거의 동일하다. 유일한 차이점은 결과 릴레이션에 ID 속성이 student의 것과 takes의 것 하나씩, 두 번 나타난다는 것이다. 비록 두 속성의 값은 같으나, 두 속성 모두 결과에 포함된다. 실제로, 앞의 쿼리는 다음 쿼리와 동일한 작업을 수행한다:
select *
from student, takes
where 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;
위 쿼리의 결과는 student와 takes의 natural join의 결과와 정확하게 동일하다.
여담
on 조건은 SQL의 모든 조건식(predicate)을 표현할 수 있으므로, on 조건을 사용하는 join 표현식은 natural join보다 더 풍부한 조건의 join을 표현할 수 있다. 그러나 on 조건을 사용한 조인 표현식은 동등한 표현식으로 on 조건을 제거하고, 그 조건을 where 절로 옮기는 방식으로 대체할 수 있다. 이 때문에 on 조건은 SQL의 중복 기능 처럼 보일 수 있다. 하지만 그럼에도 on 조건을 사용하는 데에는 두 가지 이유가 있다:
- outer join에서는, on 조건이 where 조건과는 다르게 동작한다.
- join 조건은 on 절에, 나머지 조건은 where 절에 분리되어 있는 것이 더 가독성이 좋고 이해하기 쉽다.
Outer Join
Outer join 연산은 일반적인 join 연산과는 달리, join 연산과정에서 손실된 튜플들을 보존(preserve)한다. 이전 문단에서 다룬 일반적인 join 연산은 일치하지 않는 튜플들을 보존 시키지 않고, 이를 inner join이라고 부른다. 이때 보존된 튜플들은 null 값을 포함하는 튜플들로 만들어진다. 예를 들어, student 릴레이션에는 단 하나의 수업도 듣지 않은 Snow 학생이 존재하는데, 이 학생을 join 연산의 결과에도 포함시키기 위해 outer join을 사용할 수 있다. Outer join은 아래와 같은 세 가지 형태가 있다:
- Left Outer Join: left outer join 앞(왼쪽)에 명시된 릴레이션의 튜플만 보존된다.
- Right Outer Join: right outer join 뒤(오른쪽)에 명시된 릴레이션의 튜플만 보존된다.
- Full Outer Join: 양쪽 릴레이션의 튜플 모두 보존된다.
Left Outer Join
Left outer join 연산은 기존 join 연산과 동일하게 내부 join 결과를 계산한다. 그런 다음 왼쪽 릴레이션의 튜플 중 내부 join에서 조건이 일치하지 않는 튜플들에 대해 새로운 튜플 r을 생성하여 결과에 추가한다. 이때 튜플 r의 왼쪽 릴레이션에서 온 속성들은 해당 튜플의 실제 값으로 채워지고, 나머지 속성들은 null 값으로 채워진다. 예를 들어 figure 2는 아래 쿼리의 결과를 보여준다.
select *
from student natural left outer join takes;
Figure 2에는 내부 join 결과와는 달리 학생인 Snow가 포함된다. 이때 Snow의 튜플 중 takes 릴레이션에만 존재하는 속성들의 값들은 null로 표시된다. 또한 이를 응용하여 "과목을 수강하지 않은 모든 학생을 찾아라"는 쿼리는 다음과 같이 작성할 수 있다.
select ID
from student natural left outer join takes
where course_id is null;
Right Outer Join
Right Outer Join은 Left Outer Join과 대칭적이다. 오른쪽 릴레이션의 튜플 중 내부 join에서 조건이 일치하지 않는 튜플들들은 null로 채워져 결과에 포함된다. 예를 들어 이전의 쿼리를 right outer join으로 다시 작성하면 다음과 같다:
select *
from takes natural right outer join student;
위 코드의 결과는 속성들의 순서만 다를 뿐 이전과 동일한 결과를 생성한다.
Full Outer Join
Full Outer Join은 Left Outer Join과 Right Outer Join을 결합한 것이다. 따라서 내부 조인을 수행하고 조건에 맞지 않는 왼쪽 릴레이션의 튜플들을 null로 확장하여 결과에 추가하고, 다음으로 조건에 맞지 않는 오른쪽 릴레이션의 튜플들을 null로 확장하여 결과에 추가한다. 즉, full outer join은 left outer join과 대응하는 right outer join의 합집합이라고 볼 수 있다. Full Outer Join은 “Comp. Sci. 학과의 모든 학생들과, 그들이 2017년 봄 학기에 수강한 과목 섹션들을 출력하라. 단, 해당 학기에 개설된 모든 과목 섹션도 출력되어야 하며, Comp. Sci. 학생이 수강하지 않은 과목 섹션도 포함되어야 한다.”라는 쿼리를 아래와 같이 작성할 수 있게 해준다.
select *
from (select * from student where dept_name = 'Comp_Sci.')
natural full outer join
(select * from takes where semester = 'Spring' and year = 2017);
On cluase in Outer join
on 절은 outer join과 함께 사용될 수 있다. 예를 들어 다음 쿼리는 student natural left outer join takes 쿼리와 동일하지만 ID 속성이 결과에 두번 나타난다.
select *
from student left outer join takes on student.ID = takes.ID;
이때, outer join에서 on과 where는 서로 다르게 동작한다. 그 이유는 outer join은 내부 join의 조건에 맞지 않은 튜플들에 대해 null로 채워진 튜플을 생성하는데, on 절은 outer join의 조건을 정의하는 반면, where 절은 그렇지 않기 때문이다. 예를 들어 다음과 같이 쿼리를 수정해보자:
--on 절의 조건을 true로 바꾸고, 원래의 비교 조건은 where 절로 이동
select *
from student left outer join takes on true
where student.ID = takes.ID;
이전 질의에서는 on 절 조건에 의해 ID = 70557인 Snow 학생이 takes에 매칭되는 튜플이 없었기 때문에 null로 채워진 튜플이 결과에 포함되었었다. 하지만 위 쿼리에서는 join 조건이 on true이므로 모든 튜플이 join 대상이 되며, null로 채운 튜플이 생성되지 않는다. 즉, 데카르트 곱을 수행한다. 이후 where student.ID = takes.ID 조건으로 서로 다른 ID를 가진 튜플들을 제거한다. 그 결과, takes에는 Snow 학생의 ID가 없기 때문에 Snow는 그 결과 테이블에서 사라진다.
Join Types and Conditions
일반적인 join을 outer join과 구분하기 위해, 일반 적인 join은 SQL에서 inner join이라고 불린다. 따라서 조인 절에서는 outer join이 아닌 일반적인 join을 사용한다는 것을 명시하기 위해 inner join을 사용할 수 있다. 하지만 inner라는 키워드는 선택 사항(optional)이다. 즉, 조인 절에서 outer 접두어 없이 사용될 경우, 기본 join 유형은 inner join이다. 아래의 쿼리는 그 예시이다:
select *
from student join takes using (ID);
--위와 동일함!
select *
from student inner join takes using (ID);