Notes

DB 04. 관계 대수와 SQL

관계 대수의 기본 연산부터 SQL의 DDL, SELECT, DML, trigger, assertion, embedded SQL까지 정리한 데이터베이스 학습 노트

Published: May 31, 2026
Updated: May 31, 2026
Area: Databases
Type: concept
Series: Database System
Category: Notes

relational-algebrasqlddlselectnested-querytriggerembedded-sql

개요

이 글은 관계형 데이터베이스에서 사용하는 관계 대수 relational algebra와 SQL을 한 흐름으로 정리한 노트다. 관계 대수는 SQL의 이론적 기반이고, SQL은 관계 대수와 관계 해석을 바탕으로 실제 DBMS에서 사용할 수 있도록 확장된 실용 언어다.

큰 흐름은 다음과 같다.

관계 대수
→ SQL 개요
→ 데이터 정의어와 무결성 제약조건
→ SELECT문
→ INSERT, DELETE, UPDATE문
→ Trigger와 Assertion
→ Embedded SQL

관계 대수는 주로 검색 질의의 이론적 표현에 가깝고, SQL은 여기에 집단 함수, 그룹화, 정렬, 데이터 갱신, 제약조건, 트랜잭션 제어, 응용 프로그램 연동까지 포함한다.

관계 대수의 기본 관점

관계 대수는 기존 릴레이션으로부터 새로운 릴레이션을 생성하는 절차적 질의 언어다. 릴레이션 또는 관계 대수식에 연산자를 적용하여 새로운 릴레이션을 만들고, 그 결과를 다시 다른 연산자의 입력으로 사용할 수 있다.

관계 해석과 비교하면 다음처럼 구분할 수 있다.

구분	설명
Relational calculus	원하는 데이터가 무엇인지 명시하는 선언적 언어
Relational algebra	데이터를 어떻게 구할지 연산 순서로 표현하는 절차적 언어
SQL	사용자 관점에서는 선언적 언어지만, DBMS 내부에서는 관계 대수 기반으로 최적화됨

관계 대수의 핵심은 모든 연산의 입력과 출력이 릴레이션이라는 점이다.

릴레이션 R
→ 관계 대수 연산자 적용
→ 결과 릴레이션 S
→ 다시 다른 관계 대수 연산자의 입력으로 사용 가능

관계 대수 연산자 요약

연산자	기호	의미	입력
Selection	`σ`	조건을 만족하는 tuple 선택	단항
Projection	`π`	필요한 attribute 선택	단항
Union	`∪`	합집합	이항
Intersection	`∩`	교집합	이항
Difference	`-`	차집합	이항
Cartesian product	`×`	가능한 모든 tuple 조합	이항
Join	`⋈`	관련 있는 tuple 결합	이항
Natural join	`*` 또는 `⋈`	공통 attribute 기준 join 후 중복 attribute 제거	이항
Division	`÷`	“모든 ~에 대해” 형태의 질의	이항

필수적인 관계 대수 연산자는 다음 다섯 가지다.

selection
projection
union
difference
Cartesian product

다른 연산자들은 이 기본 연산자들을 조합해서 표현할 수 있다. 예를 들어 join은 Cartesian product와 selection으로 표현할 수 있다.

R ⋈조건 S = σ조건(R × S)

어떤 질의어가 이 필수 연산자들만큼의 표현력을 가지면 relationally complete하다고 한다.

Selection: 행을 고르는 연산

selection은 릴레이션에서 조건을 만족하는 tuple만 남기는 연산이다.

σ<조건>(릴레이션)

예를 들어 EMPLOYEE에서 2번 부서에 속한 사원만 고르면 다음과 같다.

σDNO=2(EMPLOYEE)

selection의 성질은 다음과 같다.

항목	설명
대상	tuple, 즉 행
결과 degree	입력 릴레이션과 같음
결과 cardinality	입력 릴레이션보다 작거나 같음
조건	predicate라고도 부름

예를 들어 R(a, b, c)에서 다음과 같은 조건을 사용할 수 있다.

σa=3(R)
σa=3 AND c=2(R)

주의할 점은 selection이 열을 줄이지 않는다는 것이다. 조건을 만족하지 않는 행만 제거한다.

입력: 6개 attribute, 10개 tuple
selection 결과: 6개 attribute, 0~10개 tuple

Projection: 열을 고르는 연산

projection은 릴레이션에서 필요한 attribute만 선택하는 연산이다.

π<attribute-list>(릴레이션)

예를 들어 모든 사원의 직급만 보고 싶다면 다음처럼 표현한다.

πTITLE(EMPLOYEE)

projection은 attribute를 줄이기 때문에 degree가 감소할 수 있다. 다만 관계 대수의 릴레이션은 집합이므로, projection 결과에서 중복 tuple은 제거된다.

예를 들어 TITLE만 projection했을 때 중간 결과가 다음과 같다고 하자.

대리
과장
부장
과장
사원
사원
사장

관계 대수의 최종 projection 결과는 중복을 제거한다.

대리
과장
부장
사원
사장

SQL의 일반 SELECT TITLE FROM EMPLOYEE;는 중복을 유지한다. 관계 대수의 projection과 같은 결과를 얻으려면 SQL에서는 DISTINCT를 사용해야 한다.

SELECT DISTINCT TITLE
FROM EMPLOYEE;

집합 연산과 합집합 호환

관계 대수에서 릴레이션은 tuple의 집합이므로 집합 연산을 적용할 수 있다.

R ∪ S
R ∩ S
R - S

단, 집합 연산을 적용하려면 두 릴레이션이 union compatible이어야 한다.

합집합 호환 조건은 다음과 같다.

두 릴레이션의 attribute 개수가 같아야 한다.
대응되는 attribute들의 domain이 같거나 호환되어야 한다.

예를 들어 다음 두 릴레이션이 있다고 하자.

R(A1, A2, A3)
S(B1, B2, B3)

이때 A1과 B1, A2와 B2, A3와 B3의 domain이 각각 호환되어야 한다. 이름이 반드시 같을 필요는 없지만, 의미와 자료형이 대응되어야 한다.

Union, Intersection, Difference

Union

R ∪ S는 R 또는 S에 속한 tuple을 모두 모은 결과다. 중복 tuple은 제거된다.

|R ∪ S| = |R| + |S| - |R ∩ S|

예를 들어 개발 부서 또는 기획 부서에 근무하는 사원의 부서번호를 구한다면, 각각의 결과를 구한 뒤 union할 수 있다.

πDNO(σ조건1(EMPLOYEE)) ∪ πDNO(σ조건2(EMPLOYEE))

Intersection

R ∩ S는 R과 S 모두에 속한 tuple만 남긴다.

R ∩ S

두 릴레이션에 공통으로 존재하는 값을 찾을 때 사용한다.

Difference

R - S는 R에는 있지만 S에는 없는 tuple만 남긴다.

R - S

차집합은 “없는 것”을 찾을 때 자주 사용된다.

예를 들어 소속 직원이 한 명도 없는 부서를 찾는다면 다음과 같은 패턴을 사용한다.

πDEPTNO(DEPARTMENT) - πDNO(EMPLOYEE)

이 패턴은 매우 중요하다.

전체 후보 집합 - 실제 사용된 집합 = 사용되지 않은 집합

예시는 다음과 같다.

질의	관계 대수 패턴
예약되지 않은 비디오	전체 비디오 ID - 예약된 비디오 ID
수강생이 없는 강의	전체 강의 ID - 수강 신청된 강의 ID
소속 직원이 없는 부서	전체 부서번호 - 직원이 참조하는 부서번호

Cartesian Product

Cartesian product는 두 릴레이션의 모든 가능한 tuple 조합을 만든다.

R × S

R의 tuple 수가 n, S의 tuple 수가 m이면 결과 tuple 수는 n × m이다. R의 attribute 수가 a, S의 attribute 수가 b이면 결과 attribute 수는 a + b이다.

예를 들어 R에 {a, b, c}, S에 {x, y}가 있으면 결과는 다음과 같다.

(a, x)
(a, y)
(b, x)
(b, y)
(c, x)
(c, y)

Cartesian product 자체는 결과가 매우 커질 수 있어 단독으로는 실용성이 낮다. 하지만 join의 이론적 기반이다.

join = Cartesian product + selection

Join

join은 두 릴레이션에서 관련 있는 tuple을 결합하는 연산이다. 관계형 데이터베이스에서 여러 테이블을 연결할 때 가장 중요한 연산이다.

Theta Join

theta join은 비교 조건 θ를 사용한 join이다.

R ⋈θ S = σθ(R × S)

θ에는 다음과 같은 비교 연산자가 올 수 있다.

=, <>, <, <=, >, >=

Equijoin

equijoin은 theta join 중에서 비교 연산자가 =인 경우다.

EMPLOYEE ⋈DNO=DEPTNO DEPARTMENT

이 경우 결과 릴레이션에는 조인에 사용된 두 attribute가 모두 남을 수 있다.

DNO
DEPTNO

값은 같지만 attribute 이름이 다르면 둘 다 유지될 수 있다.

Natural Join

natural join은 두 릴레이션의 공통 attribute를 기준으로 equijoin을 수행한 뒤, 중복된 조인 attribute 중 하나를 제거한다.

예를 들어 다음 두 릴레이션이 있다고 하자.

R(A, B, C)
S(C, D, E)

공통 attribute는 C이다. Natural join 결과는 다음 형태가 된다.

RESULT(A, B, C, D, E)

C가 두 번 나오지 않고 한 번만 남는다.

구분	조인 attribute 처리
Equijoin	조인 attribute가 둘 다 남을 수 있음
Natural join	중복 조인 attribute를 하나만 남김

Division

division은 “모든 ~에 대해” 형태의 질의를 표현할 때 사용한다.

R(A, B) ÷ S(B)

결과는 A 값들이다. 어떤 A가 결과에 포함되려면, S의 모든 B 값과 짝을 이루는 tuple이 R에 존재해야 한다.

R(A, B)
S(B)
R ÷ S = S의 모든 B와 연결된 A

예를 들어 다음처럼 연결되어 있다고 하자.

b1: a1, a4
b2: a1, a2, a4
b3: a3, a4

S = {b1, b2}이면 결과는 a1, a4다. 두 값 모두 b1, b2와 빠짐없이 연결되어 있기 때문이다.

Division은 SQL에서 직접 쓰기보다 NOT EXISTS 구조로 변환하는 경우가 많다.

모든 x가 F를 만족한다
= F를 만족하지 않는 x가 존재하지 않는다
= NOT EXISTS (위반 사례)

관계 대수의 한계와 확장

기본 관계 대수는 다음을 직접 표현하기 어렵다.

한계	설명
산술 연산	급여 10% 인상값 계산 등
Aggregate function	`AVG`, `SUM`, `COUNT` 등
정렬	결과 순서 지정
데이터 갱신	삽입, 삭제, 수정
중복 tuple 표현	projection 결과의 중복 유지

이 한계 때문에 SQL은 관계 대수를 기반으로 하면서도 여러 기능을 추가한다.

대표적인 확장 연산은 다음과 같다.

AVG
SUM
MIN
MAX
COUNT
GROUP BY
OUTER JOIN

SQL 개요

SQL은 관계형 DBMS의 표준 질의어다. IBM의 System R 프로젝트에서 관계 대수와 관계 해석을 기반으로 개발되었고, 이후 ANSI 표준을 통해 널리 사용되었다.

SQL의 특징은 다음과 같다.

항목	설명
성격	비절차적, 선언적 언어
사용자가 명시하는 것	원하는 결과, 즉 `what`
DBMS가 담당하는 것	처리 방법, 즉 `how`
기반	관계 대수와 관계 해석
확장 기능	집단 함수, 그룹화, 갱신 연산, 제약조건 등

SQL의 중요한 특징 중 하나는 중복 tuple을 기본적으로 허용한다는 것이다.

SELECT TITLE
FROM EMPLOYEE;

이 질의는 같은 직급이 여러 번 나오면 중복된 값을 그대로 출력한다. 중복을 제거하려면 DISTINCT를 명시해야 한다.

SELECT DISTINCT TITLE
FROM EMPLOYEE;

SQL의 구성요소

SQL은 기능에 따라 다음처럼 나눌 수 있다.

분류	대표 명령	기능
데이터 검색	`SELECT`	데이터 조회
DML	`INSERT`, `DELETE`, `UPDATE`	데이터 삽입, 삭제, 수정
DDL	`CREATE`, `ALTER`, `DROP`	데이터베이스 구조 정의
TCL	`COMMIT`, `ROLLBACK`, `SAVEPOINT`	트랜잭션 제어
DCL	`GRANT`, `REVOKE`	권한 제어

SELECT는 관계 대수의 selection과 이름이 비슷하지만 의미가 다르다. SQL의 SELECT문은 검색문 전체이고, 관계 대수의 selection은 tuple을 고르는 σ 연산이다.

SELECT EMPNAME, SALARY
FROM EMPLOYEE
WHERE DNO = 2;

이를 관계 대수 관점으로 보면 다음과 같다.

SELECT EMPNAME, SALARY  → projection
FROM EMPLOYEE           → 대상 릴레이션
WHERE DNO = 2           → selection

DDL과 무결성 제약조건

DDL은 데이터베이스 구조를 정의하는 SQL이다.

CREATE
ALTER
DROP

예를 들어 EMPLOYEE 테이블은 다음처럼 정의할 수 있다.

CREATE TABLE EMPLOYEE
(
    EMPNO    NUMBER      NOT NULL,
    EMPNAME  CHAR(10),
    TITLE    CHAR(10),
    MANAGER  NUMBER,
    SALARY   NUMBER,
    DNO      NUMBER,
    PRIMARY KEY(EMPNO),
    FOREIGN KEY(MANAGER) REFERENCES EMPLOYEE(EMPNO),
    FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DEPTNO)
);

여기서 MANAGER는 같은 EMPLOYEE 테이블의 EMPNO를 참조한다. 즉, 직원의 관리자는 또 다른 직원이다. 이런 구조는 자기참조 외래 키로 볼 수 있다.

주요 제약조건

제약조건	의미
`NOT NULL`	NULL 금지
`UNIQUE`	중복 금지
`DEFAULT`	기본값 지정
`CHECK`	조건 검사
`PRIMARY KEY`	기본 키, 중복 금지 + NULL 금지
`FOREIGN KEY`	다른 테이블의 키 참조

예시는 다음과 같다.

CREATE TABLE EMPLOYEE
(
    EMPNO    NUMBER   NOT NULL,
    EMPNAME  CHAR(10) UNIQUE,
    TITLE    CHAR(10) DEFAULT '사원',
    MANAGER  NUMBER,
    SALARY   NUMBER   CHECK (SALARY < 6000000),
    DNO      NUMBER   CHECK (DNO IN (1,2,3,4,5,6)) DEFAULT 1,
    PRIMARY KEY(EMPNO),
    FOREIGN KEY(MANAGER) REFERENCES EMPLOYEE(EMPNO),
    FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DEPTNO)
        ON DELETE CASCADE
);

Constraint 이름 지정

제약조건은 이름 없이 만들 수도 있지만, 관리하려면 이름을 붙이는 것이 좋다.

CREATE TABLE EMPLOYEE
(
    EMPNO  NUMBER,
    SALARY NUMBER,
    CONSTRAINT EMPLOYEE_SALARY_CK CHECK (SALARY < 6000000)
);

나중에 삭제할 때 명확하게 지정할 수 있다.

ALTER TABLE EMPLOYEE
DROP CONSTRAINT EMPLOYEE_SALARY_CK;

Oracle 문자열 타입 주의

Oracle에서는 VARCHAR보다 VARCHAR2 사용이 일반적으로 권장된다.

주의할 점은 다음과 같다.

항목	설명
Empty string	Oracle에서는 `''`를 `NULL`로 취급할 수 있음
`VARCHAR`	표준과 차이가 있으며 semantics 변경 가능성이 언급됨
`VARCHAR2`	Oracle에서 문자열 컬럼에 일반적으로 사용 권장

또한 고정 길이와 가변 길이 문자열은 다음처럼 구분할 수 있다.

타입	특징	적합한 경우
`CHAR(n)`	고정 길이	학번, 코드처럼 길이가 거의 일정한 값
`VARCHAR2(n)`	가변 길이	이름, 주소, 제목처럼 길이가 달라지는 값

DBMS별 문자열 처리 차이는 확인 필요하다.

참조 무결성 옵션

외래 키가 참조하는 부모 tuple이 삭제되거나 수정될 때의 동작을 지정할 수 있다.

옵션	의미
`NO ACTION`	참조 중이면 삭제 또는 수정 거부
`CASCADE`	부모 삭제 시 자식 tuple도 함께 삭제
`SET NULL`	부모 삭제 시 자식 외래 키를 `NULL`로 변경
`SET DEFAULT`	부모 삭제 시 자식 외래 키를 기본값으로 변경

예를 들어 DEPARTMENT.DEPTNO = 3을 EMPLOYEE.DNO가 참조 중이라고 하자.

DELETE FROM DEPARTMENT
WHERE DEPTNO = 3;

이때 옵션에 따라 다음처럼 동작한다.

옵션	결과
`ON DELETE NO ACTION`	삭제 거부
`ON DELETE CASCADE`	`DNO = 3`인 직원 tuple도 삭제
`ON DELETE SET NULL`	해당 직원들의 `DNO`를 `NULL`로 변경
`ON DELETE SET DEFAULT`	해당 직원들의 `DNO`를 기본값으로 변경

SELECT문의 기본 구조

SQL의 SELECT문 기본 형식은 다음과 같다.

SELECT [DISTINCT] attribute-list
FROM relation-list
[WHERE condition]
[GROUP BY attribute-list]
[HAVING condition]
[ORDER BY attribute-list [ASC | DESC]];

각 절의 의미는 다음과 같다.

절	관계 대수 관점	의미
`SELECT`	Projection	출력할 attribute 선택
`FROM`	대상 릴레이션	데이터를 가져올 테이블 지정
`WHERE`	Selection	tuple 조건 지정
`GROUP BY`	Grouping	그룹화 기준 지정
`HAVING`	Group condition	그룹에 대한 조건 지정
`ORDER BY`	정렬	결과 순서 지정

문법상 순서는 SELECT → FROM → WHERE지만, 개념적 처리 순서는 보통 다음처럼 이해하는 것이 좋다.

FROM
→ WHERE
→ GROUP BY
→ HAVING
→ SELECT
→ ORDER BY

SELECT 예제와 조건식

모든 attribute 검색

SELECT *
FROM DEPARTMENT;

*는 모든 attribute를 의미한다.

필요한 attribute만 검색

SELECT DEPTNO, DEPTNAME
FROM DEPARTMENT;

관계 대수로는 다음과 같다.

πDEPTNO, DEPTNAME(DEPARTMENT)

중복 제거

SELECT DISTINCT TITLE
FROM EMPLOYEE;

DISTINCT를 사용하지 않으면 SQL은 기본적으로 중복을 유지한다.

조건 검색

SELECT *
FROM EMPLOYEE
WHERE DNO = 2;

WHERE절은 tuple 조건을 지정한다.

문자열 패턴 검색

SELECT EMPNAME, TITLE, DNO
FROM EMPLOYEE
WHERE EMPNAME LIKE '이%';

LIKE '이%'는 이로 시작하는 문자열을 의미한다.

패턴	의미
`'김%'`	김으로 시작
`'%철%'`	철을 포함
`'%민'`	민으로 끝남

범위 검색

SELECT EMPNAME, TITLE, SALARY
FROM EMPLOYEE
WHERE SALARY BETWEEN 3000000 AND 4500000;

BETWEEN A AND B는 양 끝값을 포함한다.

SALARY BETWEEN 3000000 AND 4500000
= SALARY >= 3000000 AND SALARY <= 4500000

리스트 검색

SELECT *
FROM EMPLOYEE
WHERE DNO IN (1, 3);

IN은 여러 OR 조건을 간단히 표현한 것이다.

DNO IN (1, 3)
= DNO = 1 OR DNO = 3

NULL 처리

NULL은 0이나 빈 문자열이 아니라, 값이 없거나 알 수 없는 상태를 의미한다.

NULL 처리에서 중요한 규칙은 다음과 같다.

구분	설명
산술 연산	`NULL`이 포함되면 결과는 `NULL`
비교 연산	`NULL = 3` 같은 비교는 `TRUE`가 아니라 `UNKNOWN`
NULL 검사	`IS NULL`, `IS NOT NULL` 사용
집단 함수	`COUNT(*)`를 제외하면 일반적으로 `NULL` 제거 후 계산

잘못된 예는 다음이다.

SELECT EMPNO, EMPNAME
FROM EMPLOYEE
WHERE DNO = NULL;

올바른 표현은 다음이다.

SELECT EMPNO, EMPNAME
FROM EMPLOYEE
WHERE DNO IS NULL;

NULL이 아닌 값을 찾을 때는 다음처럼 쓴다.

SELECT EMPNO, EMPNAME
FROM EMPLOYEE
WHERE DNO IS NOT NULL;

SQL은 NULL 때문에 TRUE, FALSE, UNKNOWN의 three-valued logic을 사용한다.

TRUE AND UNKNOWN    = UNKNOWN
FALSE AND UNKNOWN   = FALSE
TRUE OR UNKNOWN     = TRUE
FALSE OR UNKNOWN    = UNKNOWN
NOT UNKNOWN         = UNKNOWN

WHERE절은 결과가 TRUE인 tuple만 반환하므로, UNKNOWN은 결과에서 제외된다.

ORDER BY

ORDER BY는 결과를 정렬한다.

SELECT SALARY, TITLE, EMPNAME
FROM EMPLOYEE
WHERE DNO = 2
ORDER BY SALARY;

기본 정렬 방향은 오름차순이다.

ORDER BY SALARY ASC;
ORDER BY SALARY DESC;

여러 기준으로 정렬할 수도 있다.

SELECT D.DEPTNAME, E.EMPNAME, E.TITLE, E.SALARY
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DNO = D.DEPTNO
ORDER BY D.DEPTNAME, E.SALARY DESC;

이 경우 먼저 부서명으로 정렬하고, 같은 부서 안에서는 급여 내림차순으로 정렬한다.

NULL의 정렬 순서는 DBMS별 차이가 있을 수 있으므로 확인 필요하다.

Aggregate Function과 GROUP BY

대표적인 aggregate function은 다음과 같다.

함수	의미
`COUNT`	개수
`SUM`	합계
`AVG`	평균
`MAX`	최댓값
`MIN`	최솟값

예를 들어 전체 사원의 평균 급여와 최대 급여를 구하면 다음과 같다.

SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE;

COUNT(*)와 COUNT(attribute)는 다르다.

표현	의미
`COUNT(*)`	전체 tuple 수
`COUNT(attribute)`	해당 attribute가 `NULL`이 아닌 tuple 수
`COUNT(DISTINCT attribute)`	중복 제거 후 개수

GROUP BY

부서별 평균 급여를 구하려면 GROUP BY를 사용한다.

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE
GROUP BY DNO;

GROUP BY를 사용할 때 SELECT절에는 일반적으로 다음 두 종류만 올 수 있다.

GROUP BY에 등장한 attribute
aggregate function

잘못된 예는 다음과 같다.

SELECT DNO, EMPNAME, AVG(SALARY)
FROM EMPLOYEE
GROUP BY DNO;

DNO별 그룹 안에는 여러 EMPNAME이 존재할 수 있으므로 어떤 이름을 출력해야 할지 결정할 수 없다.

HAVING

HAVING은 그룹에 대한 조건이다.

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG(SALARY) >= 2500000;

WHERE와 HAVING은 다음처럼 구분한다.

절	조건 대상	적용 시점
`WHERE`	개별 tuple	그룹화 전
`HAVING`	group	그룹화 후

집단 함수 조건은 WHERE가 아니라 HAVING에 작성해야 한다.

SQL 집합 연산

SQL에서도 집합 연산을 사용할 수 있다.

SQL 연산자	관계 대수	의미
`UNION`	`∪`	합집합
`INTERSECT`	`∩`	교집합
`EXCEPT`	`-`	차집합

ALL이 붙지 않으면 기본적으로 중복을 제거한다.

SELECT DNO
FROM EMPLOYEE
WHERE EMPNAME = '김창섭'
UNION
SELECT DEPTNO
FROM DEPARTMENT
WHERE DEPTNAME = '개발';

UNION ALL은 중복을 유지한다.

SELECT DNO FROM EMPLOYEE
UNION ALL
SELECT DEPTNO FROM DEPARTMENT;

집합 연산을 쓰려면 두 SELECT 결과가 union compatible해야 한다.

SQL Join

전통적인 SQL 조인 문법은 FROM절에 여러 테이블을 나열하고, WHERE절에 조인 조건을 둔다.

SELECT E.EMPNAME, D.DEPTNAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DNO = D.DEPTNO;

여기서 E, D는 alias다.

Alias	의미
`E`	`EMPLOYEE`
`D`	`DEPARTMENT`
`E.DNO`	직원의 부서번호
`D.DEPTNO`	부서 테이블의 부서번호

조인 조건을 생략하면 Cartesian product가 생성된다.

SELECT E.EMPNAME, D.DEPTNAME
FROM EMPLOYEE E, DEPARTMENT D;

EMPLOYEE에 7개 tuple, DEPARTMENT에 4개 tuple이 있으면 결과는 7 × 4 = 28개가 된다.

Self Join

Self join은 같은 테이블을 자기 자신과 조인하는 것이다. 같은 테이블을 서로 다른 역할로 사용해야 하므로 alias가 필수적이다.

예를 들어 사원 이름과 직속 상사 이름을 함께 출력하려면 다음처럼 작성한다.

SELECT E.EMPNAME, M.EMPNAME
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER = M.EMPNO;

역할은 다음과 같다.

Alias	역할
`E`	사원 역할
`M`	관리자 역할
`E.MANAGER`	사원의 관리자 번호
`M.EMPNO`	관리자의 사원번호

MANAGER가 NULL인 사원은 일반 조인 결과에서 제외된다.

Nested Query

Nested query는 SQL문 안에 포함된 또 다른 SQL문이다. 안쪽 질의를 subquery, 바깥쪽 질의를 outer query라고 한다.

SELECT EMPNAME
FROM EMPLOYEE
WHERE DNO = (
    SELECT DEPTNO
    FROM DEPARTMENT
    WHERE DEPTNAME = '개발'
);

부질의 결과 형태에 따라 사용할 수 있는 연산자가 달라진다.

Subquery 결과	사용 가능한 대표 연산자
스칼라값 하나	`=`, `<`, `>`, `<=`, `>=`
한 attribute 여러 tuple	`IN`, `ANY`, `ALL`, `EXISTS`
여러 attribute 릴레이션	tuple 비교, `EXISTS`, `FROM`절 derived table

IN

SELECT EMPNAME
FROM EMPLOYEE
WHERE DNO IN (
    SELECT DEPTNO
    FROM DEPARTMENT
    WHERE DEPTNAME = '영업'
       OR DEPTNAME = '개발'
);

IN은 집합 안에 값이 포함되는지 검사한다.

ANY와 ALL

ANY는 하나 이상의 값과 조건을 만족하면 참이고, ALL은 모든 값과 조건을 만족해야 참이다.

SALARY > ANY (
    SELECT SALARY
    FROM EMPLOYEE
    WHERE DNO = 1
)

SALARY > ALL (
    SELECT SALARY
    FROM EMPLOYEE
    WHERE DNO = 1
)

직관은 다음과 같다.

> ANY = 집합의 최소값보다 크면 참
> ALL = 집합의 최대값보다 커야 참
< ANY = 집합의 최대값보다 작으면 참
< ALL = 집합의 최소값보다 작아야 참

EXISTS

EXISTS는 부질의 결과가 비어 있지 않으면 참이다.

SELECT EMPNAME
FROM EMPLOYEE E
WHERE EXISTS (
    SELECT *
    FROM DEPARTMENT D
    WHERE E.DNO = D.DEPTNO
      AND (D.DEPTNAME = '영업' OR D.DEPTNAME = '개발')
);

EXISTS에서는 반환 attribute 자체보다 “조건을 만족하는 tuple이 존재하는가”가 중요하다.

Correlated Nested Query

상관 중첩 질의는 부질의가 외부 질의의 attribute를 참조하는 질의다.

SELECT EMPNAME, DNO, SALARY
FROM EMPLOYEE E
WHERE SALARY > (
    SELECT AVG(SALARY)
    FROM EMPLOYEE
    WHERE DNO = E.DNO
);

안쪽 부질의의 E.DNO는 외부 질의의 alias를 참조한다. 따라서 부질의는 독립적으로 한 번만 실행되는 것이 아니라, 외부 질의의 각 tuple마다 반복 실행될 수 있다.

INSERT, DELETE, UPDATE

INSERT

INSERT는 기존 릴레이션에 새 tuple을 삽입한다.

INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, FLOOR)
VALUES (5, '연구', NULL);

Oracle에서는 ''가 NULL처럼 취급될 수 있으므로 다음과 같은 입력은 주의가 필요하다.

INSERT INTO DEPARTMENT
VALUES (5, '연구', '');

여러 tuple은 SELECT 결과로 삽입할 수 있다.

INSERT INTO HIGH_SALARY(ENAME, TITLE, SAL)
SELECT EMPNAME, TITLE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000;

DELETE

DELETE는 조건을 만족하는 tuple을 삭제한다.

DELETE FROM DEPARTMENT
WHERE DEPTNO = 4;

WHERE절을 생략하면 모든 tuple이 삭제된다.

DELETE FROM DEPARTMENT;

DELETE는 테이블 구조를 삭제하지 않는다. 구조 자체를 삭제하는 것은 DROP TABLE이다.

UPDATE

UPDATE는 기존 tuple의 attribute 값을 수정한다.

UPDATE EMPLOYEE
SET DNO = 3, SALARY = SALARY * 1.05
WHERE EMPNO = 2106;

WHERE절을 생략하면 모든 tuple이 수정된다.

UPDATE EMPLOYEE
SET SALARY = SALARY * 1.05;

DML과 참조 무결성의 관계는 다음처럼 정리할 수 있다.

동작	참조 무결성 관점
부모 테이블에 `INSERT`	보통 위반 없음
자식 테이블에 `INSERT`	외래 키 값이 부모에 없으면 위반
부모 테이블에서 `DELETE`	자식이 참조 중이면 위반 가능
자식 테이블에서 `DELETE`	보통 위반 없음
기본 키 또는 외래 키 `UPDATE`	위반 가능

Trigger

Trigger는 특정 데이터베이스 이벤트가 발생할 때 DBMS가 자동으로 실행하는 사용자 정의 동작이다.

Trigger는 ECA 규칙으로 이해한다.

구성	의미
Event	트리거를 활성화하는 사건
Condition	실행 여부를 결정하는 조건
Action	조건이 참일 때 수행할 동작

예를 들어 새 사원이 삽입될 때 급여가 1,500,000 미만이면 10% 인상하는 trigger는 다음처럼 해석된다.

Event     = EMPLOYEE에 INSERT 발생
Condition = newEmployee.SALARY < 1500000
Action    = 해당 사원의 SALARY를 10% 인상

SQL3 형식은 다음과 같이 이해할 수 있다.

CREATE TRIGGER trigger_name
AFTER INSERT ON EMPLOYEE
WHEN condition
BEGIN
    SQL statements
END;

Trigger는 실행 시점에 따라 나뉜다.

종류	의미
`BEFORE` trigger	이벤트가 실제로 일어나기 전에 실행
`AFTER` trigger	이벤트가 실제로 일어난 후 실행
`FOR EACH ROW`	영향을 받은 각 tuple마다 실행

Trigger가 실행한 SQL이 다른 trigger를 다시 활성화할 수도 있다. 이런 연쇄 trigger는 강력하지만 실행 흐름을 추적하기 어려우므로 순환 호출과 부작용을 주의해야 한다.

Assertion

Assertion은 데이터베이스가 항상 만족해야 하는 일반적인 무결성 제약조건이다.

CREATE ASSERTION assertion_name
CHECK condition;

Trigger와 assertion은 다음처럼 구분한다.

구분	Trigger	Assertion
핵심	이벤트 발생 시 동작 수행	조건 위반 상태 금지
구조	Event + Condition + Action	Check condition
용도	비즈니스 규칙 자동 처리	전역 무결성 제약조건

대부분의 assertion은 NOT EXISTS 구조를 포함한다.

모든 x가 F를 만족한다
= F를 만족하지 않는 x가 존재하지 않는다
= NOT EXISTS (위반 사례)

예를 들어 ENROLL에 있는 학번은 반드시 STUDENT에 존재해야 한다는 조건은 다음처럼 표현할 수 있다.

CREATE ASSERTION EnrollStudentIntegrity
CHECK (
    NOT EXISTS (
        SELECT *
        FROM ENROLL
        WHERE STNO NOT IN (
            SELECT STNO
            FROM STUDENT
        )
    )
);

이 조건은 다음을 의미한다.

STUDENT에 없는 STNO가 ENROLL에 나타나는 경우는 존재하지 않아야 한다.

다만 CREATE ASSERTION은 SQL 표준에 포함되어도 실제 상용 DBMS에서 지원되지 않는 경우가 많다. 지원 여부는 DBMS별 확인 필요하다.

Embedded SQL

Embedded SQL은 C 같은 host language 안에 SQL문을 포함시키는 방식이다.

전체 처리 흐름은 다음과 같다.

.pc 파일
→ Pro*C precompiler
→ .c 파일
→ C compiler
→ .obj
→ linker + SQL library
→ .exe

C compiler는 SQL문을 직접 이해하지 못하므로, precompiler가 EXEC SQL ... 문장을 DBMS library function call 형태로 바꿔준다.

Host Variable

Host variable은 SQL문과 host program 사이에서 값을 주고받는 변수다.

EXEC SQL BEGIN DECLARE SECTION;
    int no;
    varchar title[10];
EXEC SQL END DECLARE SECTION;

SQL문 안에서는 host variable 앞에 :를 붙인다.

EXEC SQL
    SELECT title INTO :title
    FROM EMPLOYEE
    WHERE empno = :no;

구분 기준은 다음과 같다.

표현	의미
`title`	DB 테이블의 attribute
`:title`	C 프로그램의 host variable
`:no`	C 프로그램의 host variable

Static SQL과 Dynamic SQL

구분	설명
Static SQL	SQL 구조가 컴파일 전에 정해져 있음
Dynamic SQL	SQL문을 문자열로 만들고 실행 시점에 준비/실행

Dynamic SQL 예시는 다음과 같다.

strcpy(hostVarStmtDyn,
       "UPDATE staff SET salary = salary + 1000 WHERE dept = :v");

EXEC SQL PREPARE StmtDyn FROM :hostVarStmtDyn;
EXEC SQL EXECUTE StmtDyn USING :dept;

EXECUTE IMMEDIATE는 prepare와 execute를 한 번에 처리하는 방식으로 이해할 수 있다.

EXEC SQL EXECUTE IMMEDIATE :hostVarStmtDyn USING :dept;

Cursor

SQL은 결과를 집합 단위로 반환할 수 있지만, C 같은 host language는 보통 변수 또는 record 단위로 처리한다. 이 불일치를 해결하기 위해 cursor를 사용한다.

Cursor는 결과 집합에서 한 번에 한 tuple씩 가져오는 수단이다.

Cursor 사용 절차는 다음 네 단계다.

DECLARE CURSOR
→ OPEN
→ FETCH
→ CLOSE

예시는 다음과 같다.

EXEC SQL
    DECLARE title_cursor CURSOR FOR
    SELECT title FROM employee WHERE empname = :name;

EXEC SQL OPEN title_cursor;

EXEC SQL FETCH title_cursor INTO :title;

EXEC SQL CLOSE title_cursor;

여러 tuple을 가져올 때는 FETCH를 반복한다. 더 이상 가져올 tuple이 없으면 NOT FOUND가 발생하며, WHENEVER NOT FOUND로 처리할 수 있다.

EXEC SQL WHENEVER NOT FOUND GOTO NotFoundLabel;

for (;;) {
    EXEC SQL FETCH c1 INTO :eno, :name, :title, :manager, :salary, :dno;
    printf("%d %s %s\n", eno, name, title);
}

NotFoundLabel:
    EXEC SQL CLOSE c1;

현재 cursor가 가리키는 tuple을 수정하려면 CURRENT OF를 사용할 수 있다.

EXEC SQL DECLARE title_cursor CURSOR FOR
SELECT title FROM EMPLOYEE WHERE empname = :name
FOR UPDATE OF title;

EXEC SQL FETCH title_cursor INTO :title;

EXEC SQL UPDATE EMPLOYEE
SET title = '상무'
WHERE CURRENT OF title_cursor;

Embedded SQL의 에러 처리

WHENEVER

WHENEVER는 자동적인 에러 처리 구문이다.

WHENEVER <조건> <동작>

조건은 다음과 같다.

조건	의미
`NOT FOUND`	`SELECT INTO` 또는 `FETCH` 결과 없음
`SQLERROR`	SQL 실행 중 에러 발생
`SQLWARNING`	경고 발생

동작은 다음과 같다.

동작	의미
`CONTINUE`	계속 진행
`GOTO label`	지정 label로 이동
`DO function`	지정 함수 호출
`STOP`	프로그램 종료, commit되지 않은 work는 rollback

SQLCA와 SQLCODE

SQLCA는 SQL 실행 상태를 알려주는 통신 영역이다. 대표적으로 sqlcode를 확인한다.

sqlcode = 0     → 마지막 SQL문 성공
sqlcode != 0    → 에러 또는 특수 상황 확인 필요

사용 예시는 다음과 같다.

while (SQLCODE == 0) {
    EXEC SQL FETCH c1 INTO :eno, :name, :title, :manager, :salary, :dno;

    if (SQLCODE == 0) {
        printf("%d %s %s\n", eno, name, title);
    }
}

SQLSTATE

SQLSTATE는 SQL92 표준 상태 변수다. 총 5자리 코드로 구성된다.

CLASS CODE    = 2자리
SUBCLASS CODE = 3자리
총 5자리

C 문자열로 선언할 때는 null terminator까지 고려하여 6칸을 둔다.

char SQLSTATE[6];

Indicator Variable

Indicator variable은 host variable 값이 NULL인지, 값이 잘렸는지 등 추가 상태를 제공한다.

short indicator_var;

EXEC SQL SELECT xyz
INTO :host_var:indicator_var
FROM ...;

SELECT INTO에서 indicator variable 값은 다음처럼 해석한다.

값	의미
`-1`	host variable 값이 `NULL`
`0`	온전한 값
`> 0`	값이 잘렸고 원래 길이를 나타냄
`-2`	값이 잘렸지만 원래 길이 모름

INSERT 또는 UPDATE에서는 다음처럼 해석한다.

값	의미
`-1`	DB에 `NULL` 삽입 또는 수정
`>= 0`	정상 값 사용

자주 실수하기 쉬운 구분 기준

구분	올바른 이해
SQL `SELECT` vs 관계 대수 selection	SQL `SELECT문`은 검색문 전체, 관계 대수 selection은 `σ`
`SELECT`절 vs `WHERE`절	`SELECT`는 projection, `WHERE`는 selection
`WHERE` vs `HAVING`	`WHERE`는 tuple 조건, `HAVING`은 group 조건
`COUNT(*)` vs `COUNT(attribute)`	전체 tuple 수 vs `NULL`이 아닌 attribute 수
`= NULL` vs `IS NULL`	`NULL` 검사는 반드시 `IS NULL` 사용
Equijoin vs Natural join	Natural join은 중복 조인 attribute를 하나만 남김
`DELETE` vs `DROP`	`DELETE`는 tuple 삭제, `DROP`은 구조 삭제
Trigger vs Assertion	Trigger는 동작 수행, Assertion은 위반 상태 금지
Host variable vs DB attribute	SQL 안에서 `:`가 붙으면 host variable

요약

관계 대수는 SQL의 이론적 기반이다. selection, projection, union, difference, Cartesian product가 필수 연산자이고, join과 division은 이를 바탕으로 이해할 수 있다.

SQL은 관계 대수보다 실용적인 기능을 더 많이 제공한다. SELECT로 데이터를 검색하고, DDL로 구조를 정의하며, DML로 데이터를 삽입·삭제·수정한다. GROUP BY, HAVING, aggregate function은 기본 관계 대수의 한계를 보완한다.

무결성은 PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL, UNIQUE, DEFAULT 같은 제약조건으로 관리한다. 더 복잡한 비즈니스 규칙은 trigger로 자동화할 수 있고, 일반적인 전역 조건은 assertion으로 표현할 수 있지만 실제 DBMS 지원 여부는 확인이 필요하다.

응용 프로그램에서 SQL을 실행할 때는 Embedded SQL을 사용한다. 이때 host variable, cursor, WHENEVER, SQLCA, SQLSTATE, indicator variable을 통해 SQL과 host language 사이의 값 전달, 반복 처리, 에러 처리를 수행한다.