Nested Subquery 문서 원본 보기

상위 문서: [[SQL]]

==개요==
'''Subquery'''란, 다른 쿼리 안에 포함된 select-from-where 쿼리를 의미한다. 예를 들면 아래와 같다.
<syntaxhighlight lang="sql">
select name
from instructor
where dept_name = (select dept_name from department where building = 'Watson');
</syntaxhighlight>
일반적인 SQL의 쿼리는 다음과 같은 형태를 가진다. 
<syntaxhighlight lang="sql">
select A1, A2, ..., An
from r1, r2, ..., rm
where P
</syntaxhighlight>
이때, subquery는 select, from, where 절에서 사용될 수 있다.

===From 절에서의 Subquery===
ri 자리에 하나의 서브쿼리를 넣을 수 있으며, 마치 테이블처럼 다룰 수 있다. 이때 subquery에는 as 키워드를 사용하여 별칭 주는 것이 일반적이다. 이는 아래와 같이 사용된다.
<syntaxhighlight lang="sql">
select *
from (select * from instructor where salary > 50000) as high_paid;
</syntaxhighlight>

===Where 절에서의 Subquery===
P 자리에 조건식을 쓸 수 있는데, 그 조건식 안에 서브쿼리를 넣을 수 있다. 이때 조건식은 다음과 같이 작성된다.
<syntaxhighlight lang="sql">
B <operation> (subquery)
</syntaxhighlight>
이때 B는 속성에 해당하며, <operation>은 비교 연산 (=, >, <, in, exists 등)에 해당한다. 이를 바탕으로 where 절에서 subquery는 아래와 같이 사용된다.
<syntaxhighlight lang="sql">
select name
from instructor
where salary > (select avg(salary) from instructor);
</syntaxhighlight>

===Select 절에서의 Subquery===
Ai 자리에 단일 값을 반환하는 서브쿼리를 넣을 수 있다. 이는 아래와 같이 사용된다.
<syntaxhighlight lang="sql">
select name, (select count(*) from teaches where teaches.ID = instructor.ID) as  um_courses
from instructor;
</syntaxhighlight>

<syntaxhighlight lang="sql">
</syntaxhighlight>

==Set Membership==
SQL은 튜플(값)이 어떤 relation(또는 집합)에 속하는지 여부를 검사하는 기능인 집합 멤버십('''set membership''')을 제공한다.  '''in''' 연결자는 집합 멤버십을 테스트하는데 사용되며, 이 집합은 select 절이 생성한 값들의 모음이다. '''not in''' 연결자는 집합에 속하지 않음을 테스트하는 데 사용된다. 예를 들어, “2017년 가을 학기와 2018년 봄 학기 두 학기 모두에 개설된 과목들을 찾아라.”과 같은 쿼리를 두 집합의 교집합을 이용해 작성하였다. 하지만 이와는 in 연결자를 사용하는 방식으로도 같은 질의를 표현할 수 있다. 즉, 2017년 가을 학기에 개설된 과목 중, 2018년 봄 학기에 개설된 과목들의 집합에 속하는 과목들만 찾는 방식이다. 이는 다음과 같이 나타내어진다.
<syntaxhighlight lang="sql">
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);
</syntaxhighlight>
이때 교집합 연산은 기본적으로 중복을 제거하므로, 교집합을 사용한 것과 동일한 결과를 얻기 위해서는 이 질의에서도 distinct 키워드를 사용해야 한다.<br>
in 연산자와 비슷하게, not in 연산자도 유사한 방식으로 사용할 수 있다. 예를 들어 "2017년 가을 학기에는 개설되었지만, 2018년 봄 학기에는 개설되지 않은 과목들을 찾기"와 같은 질의를 생각해보자. 우리는 이 질의를 앞에서 except 연산을 사용하여 표현했었지만, 이 질의를 not in을 사용하여 표현하면 다음과 같다:
<syntaxhighlight lang="sql">
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);
</syntaxhighlight>
또한, in 및 not in 연산자는 열거형 집합(enumerated sets) 에도 사용할 수 있다. 예를 들어, 이름이 
“Mozart”도 아니고 “Einstein”도 아닌 교수들을 찾는 쿼리는 다음과 같다:
<syntaxhighlight lang="sql">
select distinct name
from instructor
where name not in ('Mozart', 'Einstein');
</syntaxhighlight>
앞선 예제들에서는 속성이 하나인 relation(one-attribute relation)에 대해서만 멤버십을 테스트했다. 하지만, SQL에서는 임의의 relation에 대해 멤버십을 테스트하는 것도 가능하다. 예를 들어, “ID가 110011인 교수가 가르친 과목 섹션을 수강한 학생들의 (중복 없는) 총 인원 수를 구하라.”와 같은 쿼리는 아래와 같이 나타내어진다.
<syntaxhighlight lang="sql">
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');
</syntaxhighlight>

==Set Comparison==
===Some clause===
중첩 서브쿼리가 집합을 비교하는 기능을 갖고 있음을 보여주는 예로, “급여가 생물학과(Biology department)의 어떤 교수보다도 더 높은 교수들의 이름을 찾아라.”라는 쿼리를 살펴보자. 이를 [[Additional Operation#Rename 연산|Rename 연산]]을 이용하면 아래와 같이 해결할 수 있다. 
<syntaxhighlight lang="sql">
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';
</syntaxhighlight>
하지만 하지만 SQL은 위의 질의를 '''some''' 구문을 통해서 나타낼 수 있도록 한다. some은 "at least"와 같은 의미로 사용되며, 수식으로는 아래와 같이 나타낼 수 있다.
 F <compOp> some Relation <math>\Leftrightarrow</math> <math>\exists</math>t <math>\in</math> Relation s.t(F <compOp> t)
 where <compOp> can be <math><, \le, >, \ge, =, \ne</math>
“greater than at least one(적어도 하나보다 크다)”라는 표현은 SQL에서 '''> some''' 구문을 통해서 나타낼 수 있다. 이때 <code>= some</code>은 <code>in</code>과 같은 의미이며, <code><> some</code> 은 <code>not in</code>과는 같지 않다. <>는 SQL에서 not equal을 의미하는 비교 연산자이다. 따라서 <code><> some</code>는 "x가 subquery 결과 중 어떤 값과라도 다르면 true"를 의미한다. 
some 구문을 통해 처음 제시된 코드를 아래와 같이 다시 작성할 수 있다.
<syntaxhighlight lang="sql">
select name
from instructor
--아래의 subquery는 생물학과에 소속된 모든 교수들의 급여 값들의 집합을 생성한다.
where salary > some (select salary 
                     from instructor
                     where dept_name = 'Biology');  
</syntaxhighlight>

===All clause===
'''All''' 구문은 '''some''' 구문의 반대의 의미를 가지고 있으며, 수식으로는 아래와 같이 나타낼 수 있다.
 F <compOp> all Relation <math>\Leftrightarrow</math> <math>\forall</math>t <math>\in</math> Relation s.t(F <compOp> t)
 where <compOp> can be <math><, \le, >, \ge, =, \ne</math>
이때 <code>= all</code>은 <code>in</code>과 같은 의미가 아니며, <code><> all</code> 은 <code>not in</code>과 같은 의미이다. 
예를 들어서, “평균 급여가 가장 높은 학과들의 이름을 찾아라.”라는 쿼리는 아래와 같은 코드를 통해 나타낼 수 있다.
<syntaxhighlight lang="sql">
select dept_name
from instructor
group by dept_name
having avg(salary) >= all (select avg(salary)
                           from instructor
                           group by dept_name);
</syntaxhighlight>


==Test for Empty Relations==
===Exist Clause===
SQL은 서브쿼리의 결과에 튜플이 존재하는지를 검사할 수 있는 기능을 제공한다. '''exists''' 구문은, 그 인자로 주어진 subquery의 결과 테이블 비어 있지 않다면 true를 반환한다. 예를 들어, “2017년 가을 학기와 2018년 봄 학기 두 학기 모두에 개설된 과목들을 찾아라.”라는 쿼리는 아래와 같이 나타내질 수 있다.
<syntaxhighlight lang="sql">
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);
</syntaxhighlight>
이 쿼리는 또한 SQL에서 외부 쿼리의 '''correlation name'''<ref>위 예제에서 S에 해당한다.</ref>을 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를 포함한다"를 <code>not exists (B except A)</code> 로 표현할 수 있다. 예를 들어, “생물학과(Biology department)에서 개설된 모든 과목을 수강한 학생들을 찾아라.”와 같은 쿼리는 아래와 같이 나타내어진다.
<syntaxhighlight lang="sql">
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)
);
</syntaxhighlight>

==Test for the Absence of Duplicate Tuples==
===Unique Clause===
SQL은 서브쿼리의 결과에 중복된 튜플이 존재하는지 여부를 검사하기 위한 Boolean 함수를 제공한다. '''Unique''' 구문은 인자로 주어진 서브쿼리에 중복된 튜플이 없다면 true를 반환한다. 예를 들어,  “2017년에 최대 한 번만 개설된 모든 과목(course)을 찾아라.”와 같은 쿼리는 아래와 같이 작성된다.
<syntaxhighlight lang="sql">
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);
</syntaxhighlight>
여기서 만약 어떤 과목이 2017년에 개설되지 않았다면, 서브쿼리는 빈 결과(empty result) 를 반환하고, unique 조건은 빈 집합에 대해 true로 평가된다. 위와 동일하지만, unique 구문을 사용하지 않은 형태는 다음과 같다.
<syntaxhighlight lang="sql">
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);
</syntaxhighlight>
<br>일반적으로, 어떤 relation에 대해 unique 테스트가 false를 출력하는 경우는 해당 relation이 서로 같은 두개의 튜플을 가지고 있을 때이다. 하지만 두 튜플의 값이 서로 같더라도, 어떤 속성값이 두 튜플 다 null이라면, SQL에서 unique 테스트는 true를 출력한다.(null = null은 unknown이기 때문) 즉, 튜플이 여러 번 등장하더라도, 그 튜플 안에 하나라도 null이 있으면 SQL은 중복이라고 판단하지 않고, unique 테스트는 true를 반환할 수 있다.

===Not unique Clause===
서브쿼리 내에 중복된 튜플이 존재하는지를 검사하고 싶다면, '''not unique''' 구문을 사용하면 된다. 예를 들어,  “2017년에 두 번 이상 개설된 모든 과목을 찾아라.”와 같은 쿼리는 아래와 같이 작성된다.
<syntaxhighlight lang="sql">
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);
</syntaxhighlight>

==Subqueries in the From Clause==
SQL에서는 from 절 안에 subquery expression를 사용할 수 있도록 허용한다. 이는 어떤 select-from-where 표현식이든 결과로 하나의 릴레이션을 반환하기 때문에, 이 결과 릴레이션은 다른 select-from-where 절 내에서 릴레이션이 들어갈 수 있는 곳이라면 어디든 삽입될 수 있기 때문이다. 예를 들어 “평균 급여가 $42,000을 초과하는 학과들의 교수 평균 급여를 구하라.”라는 쿼리는 [[Aggregate Functions#개요#The Having Clause|having]] 절을 사용하여 작성할 수 있다. 하지만 아래와 같이 having 절을 사용하지 않고도 작성할 수 있다.
<syntaxhighlight lang="sql">
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;
</syntaxhighlight>
위에서는 having 절을 사용할 필요가 없는데, from 절의 서브쿼리에서 이미 평균 급여를 계산했기 때문에
원래 having 절에 있던 조건은 이제 바깥 쿼리의 where 절에 넣으면 되기 때문이다. 또한, from 절 내의 subquery의 결과 relation에 이름을 붙이고(as 절 사용), 그 속성들도 새로 이름 지을 수 있다. 이는 아래와 같다.
<syntaxhighlight lang="sql">
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로 변경
</syntaxhighlight>
위에서, subquery 결과 relation의 이름은 dept_avg이고, 그 속성 이름은 dept_name, avg_salary이다.

==The With Clause==
'''with''' 절은 일시적인 relation을 정의할 수 있는 방법을 제공하며, 이 relation 정의는 with 절이 사용된 해당 쿼리 내에서만 유효하다.  “예산이 가장 큰 학과들을 찾아라.”과 같은 쿼리는 with 절을 이용하여 아래와 같이 나타낼 수 있다.
<syntaxhighlight lang="sql">
with max_budget(value) as (
  select max(budget)
  from department
)
select budget
from department, max_budget
where department.budget = max_budget.value;
</syntaxhighlight>
이 질의의 with 절은 max_budget이라는 임시 relation을 정의하며, max_budget은 해당 질의의 나머지 부분에서만 사용 가능하다. 우리는 위 쿼리를 from 절이나 where 절 안에 중첩 서브쿼리를 사용하여도 작성할 수 있지만, 하지만 중첩 subquery를 사용하면 쿼리가 읽고 이해하기 어려워지게 된다. 하지만 with 절을 사용하면 쿼리의 논리를 더 명확하게 표현할 수 있으며, 정의된 임시 relation을 쿼리 내 여러 곳에서 재사용할 수도 있다. 예를 들어  “전체 학과들의 급여 총합 평균보다 더 많은 급여를 지급하는 학과들을 찾아라.”라는 질의는 with 절을 사용하여 아래와 같이 작성할 수 있다.
<syntaxhighlight lang="sql">
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;
</syntaxhighlight>
이와 동일한 질의를 with 절 없이도 작성할 수 있지만, 그 경우 훨씬 복잡하고 이해하기 어려운 형태가 된다.

==각주==
[[분류:데이터베이스 시스템]]