SQL

상위 문서: 데이터베이스 시스템

개요

SQL(Stuctured Query Language)은 가장 널리 자용되는 데이터베이스 질의(query) 언어이다. SQL은 SQL query language라고 많이 불리지만, 단순히 데이터베이스의 질의를 하는 것 이상의 일, 데이터의 구조를 정의하고, 데이터베이스의 데이터를 수정하며, 보안 제약 조건을 명시할 수 있다.

SQL의 구성

SQL은 아래와 같은 여러 부분으로 구성된다:

• DDL(Data Definition Language): SQL DDL은 관계 스키마를 정의하고, 관계를 삭제하며, 관계 스키마를 수정하는 명령어들을 제공한다.
• DML(Data Manipulation Language): SQL DML은 데이터베이스에서 정보를 질의하고, 튜플을 삽입하고, 삭제하고, 수정하는 기능을 제공한다.
• 무결성(Integrity): SQL DDL에는 데이터베이스에 저장된 데이터가 반드시 만족해야 하는 무결성 제약 조건을 명시하는 명령어가 포함되어 있다.
• View definition: SQL DDL에는 뷰를 정의하는 명령어가 포함되어있다.
• Transaction protocol: SQL은 transaction의 시작점과 종료점을 지정하는 명령어들을 포함한다.
• Embedded SQL and dynamic SQL: 임베디드 SQL과 동적 SQL은 SQL 문장이 C, C++, Java와 같은 범용 프로그래밍 언어 안에 어떻게 포함되는지를 정의한다.
• 권한 부여(Authorization): SQL DDL은 관계와 뷰에 대한 접근 권한을 명시하는 명령어들을 포함한다.

SQL data definition

자세한 내용은 SQL data definition 문서를 참조하십시오.

Structure of SQL Queries

자세한 내용은 Structure of SQL Queries 문서를 참조하십시오.

Additional Operation

자세한 내용은 Additional Operation 문서를 참조하십시오.

Set Operation

SQL은 union, intersect, 그리고 except 연산자는 릴레이션에 대해 동작하며, 수학적인 집합 연산인 ∪(합집합), ∩(교집합), −(차집합)과 대응된다. 이 세 연산자는 아래와 같이 사용된다.

-- Fall 2017 또는 Spring 2018에 운영되었던 course를 찾기
(select course_id from section where sem = 'Fall' and year = 2017) 
union
(select course_id from section where sem = 'Spring' and year = 2018)

-- Fall 2017와 Spring 2018에 모두 운영되었던 course를 찾기
(select course_id from section where sem = 'Fall' and year = 2017) 
intersect
(select course_id from section where sem = 'Spring' and year = 2018)

-- Fall 2017에 운영되었지만 Spring 2018에는 운영되지 않았던 course를 찾기
(select course_id from section where sem = 'Fall' and year = 2017) 
except
(select course_id from section where sem = 'Spring' and year = 2018)

이때 union, intersect, 그리고 except 연산자는 모두 그 결과 테이블에서 중복되는 튜플을 허용하지 않는다. 중복되는 튜플을 허용하기 위해서는, union all, intersect all, 그리고 except all 연산자를 사용해야 한다.

Null Values

Null 값은 존재하지 않거나 알려지지 않은 값을 나타내는데 사용된다. is null 연산자는 null 값을 가지는 튜플들을 찾는데 사용된다. 이는 아래와 같다.

select name
from instructor where salary is null

이때 null 값은 산술 연산, 비교 연산, 집합 연산을 포함한 릴레이션 연산들에서 특별한 문제를 유발한다. 산술 표현식(+, −, ∗, ∕ 등을 포함하는 표현식)의 결과는, 입력값 중 하나라도 null이면 결과도 null이 된다.

Comparison with Null

null이 포함된 비교는 더 까다로운 문제를 만든다. 예를 들어, 비교식 "1 < null" 을 생각해 보자. 이 표현식이 true라고 말하는 것은 옳지 않다. 왜냐하면 null 값이 무엇을 의미하는지 모르기 때문이다. 그렇다고 해서 이 표현식이 false라고 단정짓는 것도 옳지 않다. 만약 그렇게 한다면 "not (1 < null)"은 true가 되어버리는데, 이것도 말이 되지 않는다.
그렇기 때문에 SQL은 널이 포함된 모든 비교 연사의 결과를 “unknown”으로 처리한다. 이를 구현하기 위해서 SQL은 true, false 외에 세 번째 논리 값인 unknown이 을 정의하였다.

Unknown with Logical Operator

where 절의 조건(predicate)은 and, or, not 등의 boolean operation을 포함할 수 있기 때문에, 이들 연산도 unknown 값을 처리할 수 있도록 확장되며, 다음과 같이 정의된다.

• and 연산:
  • true and unknown → unknown
  • false and unknown → false
  • unknown and unknown → unknown
• or 연산:
  • true or unknown → true
  • false or unknown → unknown
  • unknown or unknown → unknown
• not 연산:
  • not unknown → unknown

where 절의 계산된 조건의 결과값이 false이거나 unknown으로 평가되는 튜플은 select 절에서 선택되지 않는다.

Aggregate Functions

자세한 내용은 Aggregate Functions 문서를 참조하십시오.

Nested Subquery

자세한 내용은 Nested Subquery 문서를 참조하십시오.

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을 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;