상위 문서: SQL
개요
Subquery란, 다른 쿼리 안에 포함된 select-from-where 쿼리를 의미한다. 예를 들면 아래와 같다.
select name
from instructor
where dept_name = (select dept_name from department where building = 'Watson');
일반적인 SQL의 쿼리는 다음과 같은 형태를 가진다.
select A1, A2, ..., An
from r1, r2, ..., rm
where P
이때, subquery는 select, from, where 절에서 사용될 수 있다.
From 절에서의 Subquery
ri 자리에 하나의 서브쿼리를 넣을 수 있으며, 마치 테이블처럼 다룰 수 있다. 이때 subquery에는 as 키워드를 사용하여 별칭 주는 것이 일반적이다. 이는 아래와 같이 사용된다.
select *
from (select * from instructor where salary > 50000) as high_paid;
Where 절에서의 Subquery
P 자리에 조건식을 쓸 수 있는데, 그 조건식 안에 서브쿼리를 넣을 수 있다. 이때 조건식은 다음과 같이 작성된다.
B <operation> (subquery)
이때 B는 속성에 해당하며, <operation>은 비교 연산 (=, >, <, in, exists 등)에 해당한다. 이를 바탕으로 where 절에서 subquery는 아래와 같이 사용된다.
select name
from instructor
where salary > (select avg(salary) from instructor);
Select 절에서의 Subquery
Ai 자리에 단일 값을 반환하는 서브쿼리를 넣을 수 있다. 이는 아래와 같이 사용된다.
select name, (select count(*) from teaches where teaches.ID = instructor.ID) as um_courses
from instructor;
Set Membership
SQL은 튜플(값)이 어떤 relation(또는 집합)에 속하는지 여부를 검사하는 기능인 집합 멤버십(set membership)을 제공한다. in 연결자는 집합 멤버십을 테스트하는데 사용되며, 이 집합은 select 절이 생성한 값들의 모음이다. not in 연결자는 집합에 속하지 않음을 테스트하는 데 사용된다. 예를 들어, “2017년 가을 학기와 2018년 봄 학기 두 학기 모두에 개설된 과목들을 찾아라.”과 같은 쿼리를 두 집합의 교집합을 이용해 작성하였다. 하지만 이와는 in 연결자를 사용하는 방식으로도 같은 질의를 표현할 수 있다. 즉, 2017년 가을 학기에 개설된 과목 중, 2018년 봄 학기에 개설된 과목들의 집합에 속하는 과목들만 찾는 방식이다. 이는 다음과 같이 나타내어진다.
select distinct course_id
from section
where semester = 'Fall' and year = 2017 and
course_id in (select course_id
from section
where semester = 'Spring' and year = 2018);
이때 교집합 연산은 기본적으로 중복을 제거하므로, 교집합을 사용한 것과 동일한 결과를 얻기 위해서는 이 질의에서도 distinct 키워드를 사용해야 한다.
in 연산자와 비슷하게, not in 연산자도 유사한 방식으로 사용할 수 있다. 예를 들어 "2017년 가을 학기에는 개설되었지만, 2018년 봄 학기에는 개설되지 않은 과목들을 찾기"와 같은 질의를 생각해보자. 우리는 이 질의를 앞에서 except 연산을 사용하여 표현했었지만, 이 질의를 not in을 사용하여 표현하면 다음과 같다:
select distinct course_id
from section
where semester = 'Fall' and year = 2017 and
course_id not in (select course_id
from section
where semester = 'Spring' and year = 2018);
또한, in 및 not in 연산자는 열거형 집합(enumerated sets) 에도 사용할 수 있다. 예를 들어, 이름이 “Mozart”도 아니고 “Einstein”도 아닌 교수들을 찾는 쿼리는 다음과 같다:
select distinct name
from instructor
where name not in ('Mozart', 'Einstein');
앞선 예제들에서는 속성이 하나인 relation(one-attribute relation)에 대해서만 멤버십을 테스트했다. 하지만, SQL에서는 임의의 relation에 대해 멤버십을 테스트하는 것도 가능하다. 예를 들어, “ID가 110011인 교수가 가르친 과목 섹션을 수강한 학생들의 (중복 없는) 총 인원 수를 구하라.”와 같은 쿼리는 아래와 같이 나타내어진다.
select count(distinct ID)
from takes
where (course_id, sec_id, semester, year) in
(select course_id, sec_id, semester, year
from teaches
where teaches.ID = '10101');
Set Comparison
Some clause
중첩 서브쿼리가 집합을 비교하는 기능을 갖고 있음을 보여주는 예로, “급여가 생물학과(Biology department)의 어떤 교수보다 높은 교수들의 이름을 찾아라.”라는 쿼리를 살펴보자. 이를 Rename 연산을 이용하면 아래와 같이 해결할 수 있다.
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';
하지만 하지만 SQL은 위의 질의를 some 구문을 통해서 나타낼 수 있도록 한다. some은 "at least"와 같은 의미로 사용되며, 수식으로는 아래와 같이 나타낼 수 있다.
F <compOp> some Relation [math]\displaystyle{ \Leftrightarrow }[/math] [math]\displaystyle{ \exists }[/math]t [math]\displaystyle{ \in }[/math] Relation s.t(F <compOp> t) where <compOp> can be [math]\displaystyle{ \lt , \le, \gt , \ge, =, \ne }[/math]
“greater than at least one(적어도 하나보다 크다)”라는 표현은 SQL에서 > some 구문을 통해서 나타낼 수 있다. 이때 = some은 in과 같은 의미이며, <> some 은 not in과는 같지 않다. <>는 SQL에서 not equal을 의미하는 비교 연산자이다. 따라서 <> some는 "x가 subquery 결과 중 어떤 값과라도 다르면 true"를 의미한다.
some 구문을 통해 처음 제시된 코드를 아래와 같이 다시 작성할 수 있다.
select name
from instructor
--아래의 subquery는 생물학과에 소속된 모든 교수들의 급여 값들의 집합을 생성한다.
where salary > some (select salary
from instructor
where dept_name = 'Biology');
All clause
All 구문은 some 구문의 반대의 의미를 가지고 있으며, 수식으로는 아래와 같이 나타낼 수 있다.
F <compOp> all Relation [math]\displaystyle{ \Leftrightarrow }[/math] [math]\displaystyle{ \forall }[/math]t [math]\displaystyle{ \in }[/math] Relation s.t(F <compOp> t) where <compOp> can be [math]\displaystyle{ \lt , \le, \gt , \ge, =, \ne }[/math]
이때 = all은 in과 같은 의미가 아니며, <> all 은 not in과 같은 의미이다.
예를 들어서, “평균 급여가 가장 높은 학과들의 이름을 찾아라.”라는 쿼리는 아래와 같은 코드를 통해 나타낼 수 있다.
select dept_name
from instructor
group by dept_name
having avg(salary) >= all (select avg(salary)
from instructor
group by dept_name);
Test for Empty Relations
Exist Clause
SQL은 서브쿼리의 결과에 튜플이 존재하는지를 검사할 수 있는 기능을 제공한다. exists 구문은, 그 인자로 주어진 subquery의 결과 테이블 비어 있지 않다면 true를 반환한다. 예를 들어, “2017년 가을 학기와 2018년 봄 학기 두 학기 모두에 개설된 과목들을 찾아라.”라는 쿼리는 아래와 같이 나타내질 수 있다.
select course_id
from section as S
where semester = 'Fall' and year = 2017 and
exists (select *
from section as T
where semester = 'Spring' and year = 2018 and S.course_id = T.course_id);
이 쿼리는 또한 SQL에서 외부 쿼리의 correlation name[1]을 subquery 안의 where 절에서 사용할 수 있다는 것을 보여준다. 이와 같이 외부 쿼리의 correlation name을 참조하는 subquery를 correlated subquery 라고 부른다. 이때, SQL에서는 correlation name에 대한 scoping rule 이 적용된다. 이는 다음과 같다:
- 하나의 subquery 내에서는:
- 해당 subquery 내에서 정의된 correlation name이나
- 해당 subquery를 포함하는 외부의 query에서 정의된 correlation name만 사용가능하다.
- 만약 하나의 correlation name이 subquery의 내부와 외부 양쪽에서 정의되어 있다면 subquery 내부의 정의가 우선으로 적용된다.
Not exist Clause
Not exists 구문은, 그 인자로 주어진 subquery의 결과 테이블 비어 있지 않다면 true를 반환한다. 이때 not exists는 집합 포함관계를 시뮬레이션 하는 데 사용할 수 있다. 예를 들어, "relation A가 relation B를 포함한다"를 not exists (B except A) 로 표현할 수 있다. 예를 들어, “생물학과(Biology department)에서 개설된 모든 과목을 수강한 학생들을 찾아라.”와 같은 쿼리는 아래와 같이 나타내어진다.
select S.ID, S.name
from student as S
where not exists (
--첫 번째 subquery는 생물학과에서 개설된 모든 과목의 집합을 구한다.
(select course_id from course where dept_name = 'Biology')
except
--두 번째 subquery는 학생 S가 수강한 모든 과목의 집합을 구한다.
(select T.course_id from takes as T where S.ID = T.ID)
);
Test for the Absence of Duplicate Tuples
Unique Clause
SQL은 서브쿼리의 결과에 중복된 튜플이 존재하는지 여부를 검사하기 위한 Boolean 함수를 제공한다. Unique 구문은 인자로 주어진 서브쿼리에 중복된 튜플이 없다면 true를 반환한다. 예를 들어, “2017년에 최대 한 번만 개설된 모든 과목(course)을 찾아라.”와 같은 쿼리는 아래와 같이 작성된다.
select T.course_id
from course as T
where unique (select R.course_id
from section as R
where T.course_id = R.course_id
and R.year = 2017);
여기서 만약 어떤 과목이 2017년에 개설되지 않았다면, 서브쿼리는 빈 결과(empty result) 를 반환하고, unique 조건은 빈 집합에 대해 true로 평가된다. 위와 동일하지만, unique 구문을 사용하지 않은 형태는 다음과 같다.
select T.course_id
from course as T
where 1 >= (select count(R.course_id)
from section as R
where T.course_id = R.course_id
and R.year = 2017);
일반적으로, 어떤 relation에 대해 unique 테스트가 false를 출력하는 경우는 해당 relation이 서로 같은 두개의 튜플을 가지고 있을 때이다. 하지만 두 튜플의 값이 서로 같더라도, 어떤 속성값이 두 튜플 다 null이라면, SQL에서 unique 테스트는 true를 출력한다.(null = null은 unknown이기 때문) 즉, 튜플이 여러 번 등장하더라도, 그 튜플 안에 하나라도 null이 있으면 SQL은 중복이라고 판단하지 않고, unique 테스트는 true를 반환할 수 있다.
Not unique Clause
서브쿼리 내에 중복된 튜플이 존재하는지를 검사하고 싶다면, not unique 구문을 사용하면 된다. 예를 들어, “2017년에 두 번 이상 개설된 모든 과목을 찾아라.”와 같은 쿼리는 아래와 같이 작성된다.
select T.course_id
from course as T
where not unique (select R.course_id
from section as R
where T.course_id = R.course_id
and R.year = 2017);
Subqueries in the From Clause
SQL에서는 from 절 안에 subquery expression를 사용할 수 있도록 허용한다. 이는 어떤 select-from-where 표현식이든 결과로 하나의 릴레이션을 반환하기 때문에, 이 결과 릴레이션은 다른 select-from-where 절 내에서 릴레이션이 들어갈 수 있는 곳이라면 어디든 삽입될 수 있기 때문이다. 예를 들어 “평균 급여가 $42,000을 초과하는 학과들의 교수 평균 급여를 구하라.”라는 쿼리는 having 절을 사용하여 작성할 수 있다. 하지만 아래와 같이 having 절을 사용하지 않고도 작성할 수 있다.
select dept_name
--아래 from 절의 subquery에서는 dept_name, avg_salary relation을 생성한다.
from (select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name)
where avg_salary > 42000;
위에서는 having 절을 사용할 필요가 없는데, from 절의 서브쿼리에서 이미 평균 급여를 계산했기 때문에 원래 having 절에 있던 조건은 이제 바깥 쿼리의 where 절에 넣으면 되기 때문이다. 또한, from 절 내의 subquery의 결과 relation에 이름을 붙이고(as 절 사용), 그 속성들도 새로 이름 지을 수 있다. 이는 아래와 같다.
select dept_name, avg_salary
from (select dept_name, avg(salary)
from instructor
group by dept_name)
as dept_avg(dept_name, avg_salary) --avg(salary) 속성 이름을 avg_salary로 변경
위에서, subquery 결과 relation의 이름은 dept_avg이고, 그 속성 이름은 dept_name, avg_salary이다.
The With Clause
with 절은 일시적인 relation을 정의할 수 있는 방법을 제공하며, 이 relation 정의는 with 절이 사용된 해당 쿼리 내에서만 유효하다. “예산이 가장 큰 학과들을 찾아라.”과 같은 쿼리는 with 절을 이용하여 아래와 같이 나타낼 수 있다.
with max_budget(value) as (
select max(budget)
from department
)
select budget
from department, max_budget
where department.budget = max_budget.value;
이 질의의 with 절은 max_budget이라는 임시 relation을 정의하며, max_budget은 해당 질의의 나머지 부분에서만 사용 가능하다. 우리는 위 쿼리를 from 절이나 where 절 안에 중첩 서브쿼리를 사용하여도 작성할 수 있지만, 하지만 중첩 subquery를 사용하면 쿼리가 읽고 이해하기 어려워지게 된다. 하지만 with 절을 사용하면 쿼리의 논리를 더 명확하게 표현할 수 있으며, 정의된 임시 relation을 쿼리 내 여러 곳에서 재사용할 수도 있다. 예를 들어 “전체 학과들의 급여 총합 평균보다 더 많은 급여를 지급하는 학과들을 찾아라.”라는 질의는 with 절을 사용하여 아래와 같이 작성할 수 있다.
with dept_total(dept_name, value) as (
select dept_name, sum(salary)
from instructor
group by dept_name
),
dept_total_avg(value) as (
select avg(value)
from dept_total
)
select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value;
이와 동일한 질의를 with 절 없이도 작성할 수 있지만, 그 경우 훨씬 복잡하고 이해하기 어려운 형태가 된다.
Scalar Subqueries
SQL에는 scalar subquery라는 개념이 있는데 이는 오직 하나의 속성과 하나의 튜플로 이루어진 relation이다. 예를 들어,
(select count(*) from instructor)
는 정확히 숫자 하나만 반환하므로 scalar subquery에 해당한다. Scalar subquery는 일반적인 값이 들어갈 수 있는 곳이면 어디든지[2] 사용가능하다. 예를 들어 "각 학과와 그 학과에 속한 교원의 수를 함께 나열하라"라는 쿼리는 아래 select 절에 scalar subquery가 사용되는 방법을 보여준다.
select dept_name,
(select count(*)
from instructor
where department.dept_name = instructor.dept_name) as num_name
from department;
이 예제의 subquery는 group by 없이 count(*)를 사용하고 있기 때문에 하나의 값만 반환하는 것이 보장된다. 이 예제는 또한 correlation variables의 사용을 보여주는데, 이는 바깥 쿼리의 from 절에 있는 relation의 속성들이다. 위 예제의 department.dept_name이 그 예이다. 만약 해당 scalar subquery가 실행될 때 하나 이상의 튜플을 반환하면 런타임 오류가 발생한다.
Scalar Without a From Clause
Scalar Without a From Clause란, 어떤 from 절을 포함하는 subquery로 구성된, from 절이 없는 최상위 쿼리이다. 예를 들어, "강의된 전체 분반 수를 강의한 교수 수로 나눈 평균 값은?"이라는 쿼리는 아래와 같은 from 절이 없는 scalar로 나타낼 수 있다.
(select count(*) from teaches) * 1.0 / (select count(*) from instructor);
위는 subquery 두 개로 이루어진 scalar 값을 구하는 쿼리를 보여준다. 각 subquery 내에는 from 절이 존재하지만, 최종적인 결과를 얻는 query에서는 from 절이 존재하지 않는다.