[SQL#5] 서브쿼리(Subquery) (1) - 스칼라 값을 생산하는 서브쿼리, Relation/Tuple과 관련된 조건들, correlated 서브쿼리, FROM/SELECT에서의 서브쿼리

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

이번 글에서는 쿼리 안의 쿼리인 서브쿼리에 대해 알아볼 것이다. 하나의 서브쿼리는 또 서브쿼리를 가질 수 있고, 계속해서 여러 개의 서브쿼리를 가질 수 있다. 앞선 글에서 살펴보았던 집합 연산이 서브쿼리의 사용 예시이다. 아래 표현에서 (SELECT A FROM R)과 (SELECT A FROM S)가 각각 서브쿼리이다.

(SELECT A FROM R) UNION (SELECT A FROM S);

서브쿼리의 특징은 다음과 같다.

• 서브쿼리는 하나의 단일 상수를 리턴할 수 있고, 이 상수는 WHERE 구문 안에서 다른 값과 비교될 수 있다.  
• 서브쿼리는 WHERE 구문에서 사용될 수 있는 relation을 리턴할 수 있다.
• 서브쿼리는 tuple 변수와 함께 사용되어 FROM에서도 나타날 수 있다.

스칼라(scalar) 값을 생산하는 서브쿼리

tuple의 구성 요소 형태로 나타나는 atomic value를 스칼라(scalar)라고 한다. select-from-where 표현은 해당 schema 내 어떤 수의 attribute도 생산해낼 수 있고, 이 relation 내에는 어떤 수의 tuple도 존재할 수 있다. 하지만 보통 우리는 한 가지의 attribute의 값에만 관심이 있는 경우가 많다. 또한, key에 대한 정보나 다른 정보로부터 해당 attribute에 딱 하나의 값만 존재할 것이라고 추론해 낼 수도 있다. 즉, 이 경우 괄호로 둘러싸인 select-from-where 표현(서브쿼리)을 상수인 것처럼 사용할 수 있다. 보통 이 경우 상수나 tuple의 컴포넌트를 나타내는 attribute의 자리가 있을 것으로 기대되는 WHERE 구문에서 나타난다. 예를 들어, 이러한 서브쿼리의 결과값을 한 상수나 attribute와 비교할 수 있다.

 다음과 같은 두 개의 relation이 있다고 해 보자.

Movies(title, year, length, genre, studioName, producerC#)
MovieExec(name, address, cert#, netWorth)

이때 다음과 같은 쿼리는

SELECT	name
FROM	Movies, MovieExec
WHERE	title = 'Star Wars' AND producerC# = cert#;

다음과 같이 서브쿼리를 포함한 쿼리와 동일하다. (괄호 안의 부분이 서브쿼리이다.)

SELECT	name
FROM	MovieExec
WHERE	cert# =
	(SELECT	producerC#
    	FROM	Movies
        WHERE	title = 'Star Wars'
        );

이때 서브쿼리는 하나의 값을 리턴할 것으로 기대된다. 만약 이 서브쿼리에 의해 0개 혹은 2개 이상의 튜플이 리턴된다면 런타임 에러가 발생한다.

Relation과 관련된 조건들

relation R에 적용되거나 boolean 결과를 생산하는 SQL 연산자들은 다음과 같다. 이때 relation R은 반드시 서브쿼리로 표현되어야 한다. 이 경우 (SELECT * FROM Foo)와 같이 사용하는 것도 가능하다. 다음 식에서 s는 스칼라, R은 서브쿼리로 표현된 relation이다.

• EXISTS R : R이 empty가 아니라면 true
• s IN / NOT IN R : s가 R에 존재하거나/존재하지 않는다면 true
• s >  ALL R : s가 R의 모든 값보다 커야 함 (R의 최댓값보다 크면 true)
• s > ANY R : s가 R의 적어도 하나의 값보다 크면 됨 (R의 최솟값보다 크면 true)

이때, 각 연산자의 앞에 NOT을 붙이는 것도 가능하다: NOT EXIST R, NOT s>= ALL R, NOT s > ANY R

Tuple과 관련된 조건들

SQL에서 tuple은 (123, 'foo')와 같이 scalar 값들의 괄호 리스트로 표현된다. 만약 tuple t가 relation R과 동일한 수의 컴포넌트를 가진다면, 위 'relation과 관련된 조건들'에서 나온 표현을 사용해 t IN R과 같이 t와 R을 비교할 수 있다. 예시를 통해 살펴보자.

다음과 같이 MovieExec, Movies, StarsIn 3개의 relation이 있다.

MovieExec	name	cert#	Movies	producerC#	title	year	StarsIn	starName	movieTitle	movieYear
	n1	c1		c1	t1	y1		s1	t1	y1
				c1	t2	y2		s1	t2	y2

이 상황에서 starName이 s1인 배우가 나오는 영화의 제작사가 만든 영화 이름을 MovieExec에서 찾는다고 해보자.

SELECT	name
FROM	MovieExec
WHERE	cert#	IN
	(SELECT	producerC#
    	FROM	Movies
    	WHERE	(title, year)	IN
            (SELECT	movieTitle, movieYear
            FROM	StarsIn
            WHERE	starName = 's1')
        )
    );

 cert#는 IN을 통해 첫 번째 서브쿼리의 결과인 producerC# 튜플과 연산되며, (title, year)는 두 번째 서브쿼리의 결과 튜플과 연산된다. 아래에서부터 생각해보면 탐색 흐름은 s1 → (t1, y1), (t2, y2) → c1 → n1 이 된다.

만약 서브쿼리 없이 다음과 같이 AND를 통해서만 쿼리를 작성하는 경우,

SELECT	name
FROM	MovieExec, Movies, StarsIn
WHERE	cert# = producerC# AND
	title = movieTitle AND
    	year = movieYear AND
    	starName = 's1';

결과는 다음과 같이 2개의 n1을 리턴하게 된다.

name	cert#	producerC#	title	year	StarName	movieTitle	movieYear
n1	c1	c1	t1	y1	s1	t1	y1
n1	c1	c1	t2	y2	s1	t2	y2

SQL은 기본적으로 duplicate을 허용한다. 만약 이러한 duplicate을 없애고 싶다면 DISTINCT를 사용하면 된다. 이 경우 1개의 n1만을 리턴하게 된다.

SELECT	DISTINCT name
FROM	MovieExec, Movies, StarsIn
WHERE	cert# = producerC# AND
	title = movieTitle AND
    	year = movieYear AND
    	starName = 's1';

Correlated 서브쿼리

가장 단순한 서브쿼리는 한번에 한 번만 평가되고evaluated 결과가 상위 쿼리에서 사용될 수 있는 것이다. nested 서브쿼리의 더 복잡한 사용은 서브쿼리가 여러 번 평가되어야 하며, 서브쿼리 외부의 튜플 변수에서 온 값에 대해 값이 할당될 때마다 한 번씩 평가된다. 즉, 튜플 변수 외부에서 온 값의 할당마다 매번 평가되는 서브쿼리를 correlated 서브쿼리라 한다.

다음 예시를 살펴보자.

SELECT	title
FROM	Movies Old
WHERE	year < ANY
	(SELECT	year
    	FROM	Movies
    	WHERE	title = Old.title
    	);

이 쿼리는 같은 title의 any movie보다 작은 year인 경우를 리턴하는 쿼리이다. Movies relation에 대해 다음과 같은 튜플이 있는 경우 이 쿼리는 t1을 두 번 리턴할 것이다(year 1과 2).

Movies	title	year
	t1	1
	t1	2
	t1	3
	t2	1

correlated 쿼리를 작성할 때 중요한 것은 이름에 대한 scoping rule이다. 보통 서브쿼리의 attribute는 (어떤 튜플 변수가 schema 안에 그 attribute를 가지는 경우) 해당 서브쿼리의 FROM 구문 안의 튜플 변수 중 하나에 속한다. 만약 그렇지 않다면 그 밖을 둘러싸는 서브쿼리를 하나씩 확인해 가면서 속하는 튜플이 있을 때까지 올라간다. 즉, 위 예시에서 4번째 줄의 year와 6번째 줄의 title은 5번째 줄에 제시된 Movies relation의 복사본(즉, 4~6번째 줄의 서브쿼리에 의해 도입된 Movies relation의 복사본)의 모든 튜플에 걸쳐 있는 튜플 변수의 attribute를 참조한다. 

FROM에서의 서브쿼리

FROM 구문에서 이미 있는 relation 대신 괄호로 둘러싸인 서브쿼리를 다음과 같이 사용할 수 있다. 서브쿼리의 결과에 대한 이름이 없기 때문에, 반드시 tuple-variable alias를 사용해야 한다(아래 예시에서 Prod).

SELECT	name
FROM	MovieExec, (SELECT producerC# 
			FROM Movies) Prod
WHERE cert# = Prod.producerC#;

아래 표의 왼쪽과 같이 Movies와 MovieExec이 주어진 경우, 쿼리의 결과는 오른쪽과 같은 Result가 나오게 된다.

Movies	title	producerC#	MovieExec	name	cert#	Result	name
	t1	p1		n1	p1		n1
	t2	p2		n2	p2		n2

SELECT에서의 서브쿼리

FROM에서와 동일하게, SELECT에도 서브쿼리를 넣을 수 있다.

SELECT	title, (SELECT MovieExec.name
		FROM MovieExec
        	WHERE cert# = Movies.producerC#)
FROM Movies

이때 결과는 다음과 같다.

Movies	title	producerC#	MovieExec	name	cert#	Result	title	name
	t1	p1		n1	p1		t1	n1
	t2	p2		n2	p2		t2	n2

이때, 서브쿼리가 하나 이상의 값을 리턴하게 되면 런타임 에러가 발생하거나 나머지 값들을 무시하게 된다.

 

* 중첩된 쿼리는 읽기엔 편하지만 느릴 수 있다. 또한, 각 서브쿼리가 여러 튜플에 대해(위 예시에서는 각 영화에 대해) 실행될 경우 쿼리는 expensive해질 수 있다. 이 경우, 쿼리를 unnest할 수 있다. 예를 들어 위 SELECT 서브쿼리를 다음과 같이 unnest할 수 있다.

SELECT	title, name
FROM	Movies, MovieExec
WHERE	cert# = Movies.producerC#