[SQL#6] 서브쿼리(Subquery) (2) - join, natural join, outer join

참고: Database Systems: The Complete Book (2nd edition) - 6.3 Subqueries

이번 글에서는 서브쿼리 중 SQL의 join에 대해 알아볼 것이다.

두 개의 relation은 product, theta join, natural join, outerjoin으로 join될 수 있다. 예시를 바탕으로 하나씩 알아보자.

Movies(title, year, length)
StarsIn(movieTitle, movieYear, starName)

Movies	title	year	length	StarsIn	movieTitle	movieYear	starName
	t1	y1	l1		t1	y1	s1
	t1	y2	l2		t2	y2	s2

Cross Join (product)

가장 간단한 join의 형태는 cross join이다. 다른 말로 cartesian product나 product라 부른다.

Movies CROSS JOIN StarsIn;

CROSS JOIN은 문법상 여러 relation을 comma로 연결하는 것과 동일하다.

Movies, StarsIn;

위와 같은 CROSS JOIN의 결과는 다음과 같다. Movies의 모든 튜플과 StarsIn의 모든 튜플의 가능한 모든 조합이 결과값이 된다.

title	year	length	movieTitle	movieYear	starName
t1	y1	l1	t1	y1	s1
t1	y1	l1	t2	y2	s2
t2	y2	l2	t1	y1	s1
t2	y2	l2	t2	y2	s2

Theta join

ON 키워드와 함께 쓰는 theta join은 ON 뒤에 오는 조건을 만족하는 R×S의 product를 의미한다.

Movies JOIN StarsIn ON
	title = MovieTitle AND year = movieYear;

이러한 쿼리의 경우 리턴 결과는 다음과 같다.

title	year	length	movieTitle	movieYear	starName
t1	y1	l1	t1	y1	s1
t2	y2	l2	t2	y2	s2

Natural join

Natural join은 단순히 공통된 attribute를 찾는 연산으로, 두 개의 relation이 동일한 이름의 attribute를 갖는 경우라는 점에서 theta-join과는 차이점이 있다. attribute의 교집합 연산이라고 이해해도 될 것 같다.

예시로 사용하는 Movies와 StarsIn relation을 서로 같은 이름의 attribute를 갖도록 다음과 같이 바꿔보자.

Movies	title	year	length	StarsIn	title	year	starName
	t1	y1	l1		t1	y1	s1
	t1	y2	l2		t2	y2	s2

이 경우, natural join 쿼리는 다음과 같이 표현되며,

Movies NATURAL JOIN StarsIn;

이때의 결과는 아래와 같다.

title	year	length	starName
t1	y1	l1	s1
t2	y2	l2	s2

Outer join

outer join은 join의 결과를 null 값으로 패딩된 dangling tuple을 이용해 확장시키는 방식이다. 다음과 같이 StarsIn relation의 두 번째 튜플을 (t3, y3, s3)으로 변화시켜 생각해 보자.

Movies	title	year	length	StarsIn	title	year	starName
	t1	y1	l1		t1	y1	s1
	t1	y2	l2		t3	y3	s3

NATURL LEFT OUTER JOIN

Movies NATURL LEFT OUTER JOIN StarsIn;

이 쿼리는 Movies relation의 모든 tuple을, StarsIn 및 Movies relation의 모든 attribute과 연결시킨다. StarsIn의 attribute가 없는 Movies의 tuple (t1, y2, l2)에는 해당 attribute에 NULL 값을 넣어 리턴한다.

title	year	length	starName
t1	y1	l1	s1
t2	y2	l2	NULL

NATURL RIGHT OUTER JOIN

Movies NATURL RIGHT OUTER JOIN StarsIn;

left와 동일한 방식으로, 오른쪽에 있는 StarsIn relation의 모든 tuple을 확장하는 연산이다.

title	year	length	starName
t1	y1	l1	s1
t3	y3	NULL	s3

NATURAL FULL OUTER JOIN

Movies NATURAL FULL OUTER JOIN StarsIn;

양쪽 모두, 즉 Movies와 StarsIn relation의 모든 tuple을 각 attribute에 대응하여 확장한다.

title	year	length	starName
t1	y1	l1	s1
t2	y2	l2	NULL
t3	y3	NULL	s3

FULL OUTER JOIN ... ON

natural join이 아닌 theta join으로도 (full) outer join을 구현할 수 있다. 즉, attribute의 이름이 relation 간 동일하지 않은 경우에도 조건을 통해서 만족하는 product의 값을 추출해낼 수 있다. 다시 attribute의 이름을 처음처럼 되돌려 생각해보자.

Movies	title	year	length	StarsIn	moiveTitle	movieYear	starName
	t1	y1	l1		t1	y1	s1
	t2	y2	l2		t3	y3	s3

Movies FULL OUTER JOIN StarsIn ON
	title = movieTitle AND year = movieYear;

이러한 쿼리의 경우, Movies와 StarsIn의 모든 튜플이 ON 이후의 조건(title = movieTitle과 year = movieYear) 만족하는 경우 그대로, 아닌 경우 패딩된 NULL과 함께 결과로 리턴된다. 

title	year	length	movieTitle	movieYear	starName
t1	y1	l1	t1	y1	s1
t2	y2	l2	NULL	NULL	NULL
NULL	NULL	NULL	t3	y3	l3

Redundancy

어떤 join 연산들은 select-from-where로 대체될 수 있다.

• R CROSS JOIN S는 SELECT * FROM R, S 와 동일하다.
• R NATURAL JOIN S는 SELECT A, B, C FROM R, S WHERE R.A = S.A 와 동일하다. (R은 A,B, S는 A,C의 attribute을 가진다고 가정)
• R FULL OUTER JOIN S는 select-from-where로 대체될 수 없다.