0x08. 고급 SQL - Join, View, Window Function, Key

Join

Join 연산은 두 개의 릴레이션을 결합하여 하나의 새로운 릴레이션을 반환하는 연산이다. 본질적으로 Cartesian Product에 매칭 조건을 건 것이며, 결과에 포함할 **속성(attribute)**도 지정할 수 있다. 보통 FROM 절의 서브쿼리 표현식으로 사용된다.

Join을 이해하려면 두 가지 축을 구분해야 한다.

Join Type -- 매칭되지 않는 튜플을 어떻게 처리하는가:

• INNER JOIN: 양쪽 테이블에서 매칭되는 데이터만 반환한다.
• OUTER JOIN: 매칭되는 데이터에 더해, 매칭되지 않는 데이터도 함께 반환한다.

Join Condition -- 두 릴레이션의 튜플이 어떤 기준으로 매칭되는가:

• NATURAL: 동일한 이름과 타입의 컬럼을 기준으로 자동 매칭한다.
• ON <predicate>: 조인 조건을 명시적으로 지정한다.
• USING ($A_1, A_2, ..., A_n$): 공통 컬럼 이름을 직접 나열하여 매칭한다.

Inner Join

Inner Join은 매칭되지 않는 튜플을 보존하지 않는다. ON 또는 USING 절에 명시된 공통 컬럼을 기준으로 테이블을 결합한다.

SELECT * FROM course
INNER JOIN prereq ON course.course_id = prereq.prereq_id;

반면, Natural Join은 조인 조건을 동일한 이름의 컬럼이 모두 일치하는 것으로 자동 가정한다. Natural Join에서는 ON이나 USING을 사용할 수 없으며, 결과에서 중복 컬럼이 제거된다.

SELECT * FROM course NATURAL JOIN prereq;

USING 절은 NATURAL과 ON의 중간 지점이다. NATURAL보다 명시적이고, ON보다는 간결하다.

SELECT * FROM course JOIN prereq USING (course_id);

Outer Join

Outer Join은 정보 손실을 방지하기 위한 Join의 확장이다. Inner Join에서 탈락하는 튜플을 NULL 값으로 채워 결과에 포함시킨다.

세 가지 종류가 있다:

• LEFT (OUTER) JOIN: 왼쪽 테이블의 모든 레코드를 유지한다. 오른쪽에 매칭되는 값이 없으면 NULL로 채운다.
• RIGHT (OUTER) JOIN: 오른쪽 테이블의 모든 레코드를 유지한다. 왼쪽에 매칭되는 값이 없으면 NULL로 채운다.
• FULL (OUTER) JOIN: 양쪽 테이블의 모든 레코드를 유지한다. 어느 쪽이든 매칭이 없으면 NULL로 채운다.

참고로 MySQL은 FULL OUTER JOIN을 지원하지 않는다. LEFT JOIN과 RIGHT JOIN을 UNION하여 동일한 효과를 낼 수 있다.

NATURAL과 조합할 때는 키워드 순서에 주의해야 한다. NATURAL LEFT OUTER JOIN 순서로 작성해야 하며, 순서가 바뀌면 동작하지 않는다.

Natural Join의 주의사항

NATURAL JOIN은 동일한 이름의 모든 공통 속성을 기준으로 매칭하고, 공통 컬럼의 사본 하나만 결과에 남긴다.

FROM 절에서 여러 릴레이션을 순차적으로 Natural Join할 수 있다:

SELECT A1, A2, ... An
FROM r1 NATURAL JOIN r2 NATURAL JOIN ... NATURAL JOIN rn
WHERE P;

그러나 의도하지 않은 컬럼 매칭이 발생할 수 있어 주의가 필요하다. 다음은 대표적인 오류 사례다.

잘못된 쿼리:

-- student, takes, course를 모두 NATURAL JOIN하면
-- 관계없는 동명 컬럼(dept_name 등)이 잘못 매칭될 수 있다
SELECT name, title
FROM student NATURAL JOIN takes NATURAL JOIN course;

올바른 쿼리:

SELECT name, title
FROM student NATURAL JOIN takes, course
WHERE takes.course_id = course.course_id;

위의 잘못된 쿼리는 학생이 자신의 학과가 아닌 다른 학과의 수업을 수강한 경우를 모두 누락시킨다. dept_name이라는 컬럼이 student와 course 양쪽에 존재하기 때문이다.

이런 위험을 피하려면 USING 절로 매칭할 컬럼을 명시하는 것이 좋다:

SELECT name, title
FROM (student NATURAL JOIN takes) JOIN course USING (course_id);

JOIN ... ON

ON 절은 WHERE 절처럼 일반적인 조건식(predicate)을 사용하여 조인 조건을 지정한다.

SELECT *
FROM student JOIN takes ON student.ID = takes.ID;

-- 위 쿼리는 아래와 동일하다:
SELECT name, course_id
FROM student, takes
WHERE student.ID = takes.ID;

Natural Join을 피하는 이유

실무에서 Natural Join은 자주 기피된다. 그 이유는 다음과 같다:

1. 가독성이 떨어진다: 대부분의 SQL 개발자에게 Natural Join은 직관적이지 않으며, 일부 도구나 라이브러리에서 지원하지 않기도 한다.
2. 정보가 부족하다: 스키마를 직접 확인하지 않으면 어떤 컬럼으로 조인되는지 알 수 없다.
3. 스키마 변경에 취약하다: 테이블에서 컬럼 하나가 제거되더라도 쿼리는 여전히 실행되지만, 결과가 조용히 달라질 수 있다. 이런 버그는 발견하기 매우 어렵다.

View

모든 사용자에게 데이터의 전체 논리적 모델을 보여주는 것이 항상 바람직한 것은 아니다. 예를 들어, 교수의 이름과 소속 학과만 필요한 사용자에게 급여 정보까지 노출할 필요는 없다.

View는 특정 사용자로부터 특정 데이터를 숨기는 메커니즘이다. View는 실제 저장된 테이블(Base Table)이나 다른 View를 기반으로 정의되는 **가상 릴레이션(Virtual Relation)**이다.

CREATE VIEW v AS <query expression>;

여기서 v는 View 이름이고, <query expression>은 유효한 SQL 표현식이다.

핵심적으로, View를 정의하는 것은 새로운 릴레이션을 생성하는 것이 아니다. View 정의는 표현식을 저장하는 것이며, View를 사용하는 쿼리에 해당 표현식이 **치환(substitute)**된다.

View 예시

급여 없이 교수 정보만 보여주는 View:

CREATE VIEW faculty AS
SELECT ID, name, dept_name
FROM instructor;

View에 대한 쿼리도 일반 테이블처럼 사용할 수 있다:

SELECT name FROM faculty WHERE dept_name = 'Biology';

View의 속성 이름을 명시적으로 지정할 수도 있다. SUM(salary)처럼 이름이 없는 표현식의 경우 특히 유용하다:

CREATE VIEW departments_total_salary(dept_name, total_salary) AS
SELECT dept_name, SUM(salary)
FROM instructor
GROUP BY dept_name;

View를 삭제하려면 DROP VIEW faculty;를 사용한다.

View Expansion

View Expansion은 다른 View를 참조하여 정의된 View의 의미를 해석하는 방법이다. 즉, 정의한 View를 또 다른 View 정의에 사용할 수 있다.

CREATE VIEW physics_fall_2017 AS
  SELECT course.course_id, sec_id, building, room_number
  FROM course, section
  WHERE course.course_id = section.course_id
    AND course.dept_name = 'Physics'
    AND section.semester = 'Fall'
    AND section.year = '2017';

-- 위 View를 참조하여 새 View를 정의
CREATE VIEW physics_fall_2017_watson AS
  SELECT course_id, room_number
  FROM physics_fall_2017
  WHERE building = 'Watson';

View Expansion은 내부적으로 다음과 같이 동작한다. View 릴레이션 $v_i$를 발견하면 해당 정의 표현식으로 치환하는 과정을 View 릴레이션이 남아있지 않을 때까지 반복한다. View 정의가 재귀적이지 않다면 이 과정은 반드시 종료된다.

View 간 의존성 관계:

• $v_1$의 정의에 $v_2$가 사용되면 $v_1$은 $v_2$에 **직접 의존(depend directly)**한다.
• 직접 의존이거나 의존 경로가 존재하면 **의존(depend on)**한다고 한다.
• View가 자기 자신에 의존하면 **재귀적(recursive)**이다.

Materialized View

View에는 두 종류가 있다:

• Virtual View: 데이터베이스에 저장되지 않으며, 릴레이션을 구성하기 위한 쿼리만 존재한다. 매번 실행 시 연산이 필요하여 시간이 오래 걸릴 수 있다.
• Materialized View: 쿼리 결과를 물리적으로 저장한다. 일종의 캐싱과 같다.

Materialized View는 쿼리의 미리 계산된 결과를 테이블처럼 저장한다. 즉각적인 응답이 필요하거나 원본 쿼리의 실행 시간이 너무 긴 경우에 사용된다.

다만 원본 테이블이 갱신되어도 Materialized View는 자동으로 갱신되지 않으므로, **주기적인 갱신(refresh)**이 필요하다.

참고로 MySQL은 Materialized View를 지원하지 않는다.

View를 통한 갱신

View를 통해 데이터를 삽입할 수도 있다. 앞서 정의한 faculty View에 새 튜플을 삽입하는 경우를 보자:

INSERT INTO faculty VALUES ('30765', 'Green', 'Music');

이 삽입은 실제로는 instructor 테이블에 대한 삽입으로 변환되어야 한다. 그런데 instructor에는 salary 컬럼이 있다. 따라서 두 가지 중 하나가 일어난다:

1. salary가 NULL을 허용하지 않으면 삽입이 거부된다.
2. NULL을 허용하면 ('30765', 'Green', 'Music', NULL)이 instructor에 삽입된다.

유일하게 변환할 수 없는 경우도 있다. 여러 테이블을 조인하여 만든 View에 삽입을 시도하면, 어느 테이블에 어떤 값을 넣어야 할지 결정할 수 없다. MySQL에서는 이 경우 "Can not insert into join view" 에러가 발생한다.

또 다른 흥미로운 사례가 있다:

CREATE VIEW history_instructors AS
SELECT * FROM instructor WHERE dept_name = 'History';

-- Biology 학과 교수를 History View에 삽입하면?
INSERT INTO history_instructors
VALUES ('25566', 'Brown', 'Biology', 100000);

이 삽입은 실제로 실행된다. instructor 테이블에 Biology 학과 교수로 삽입되지만, history_instructors View에서는 조회되지 않는다. View의 WHERE 조건에 맞지 않기 때문이다.

대부분의 SQL 구현체에서 View를 통한 갱신은 단순한 View에서만 허용된다:

• FROM 절에 하나의 테이블만 있어야 한다.
• SELECT 절에 속성 이름만 포함해야 하며, 표현식, 집계 함수, DISTINCT가 없어야 한다.
• SELECT 절에 없는 속성은 NULL이 허용되어야 한다.
• GROUP BY나 HAVING 절이 없어야 한다.

Window Function

Window Function은 2003년에 SQL 표준에 도입된 기능으로, 레코드 간의 관계를 정의하는 내장 함수이다.

PostgreSQL 문서의 설명을 빌리면, Window Function은 현재 행과 어떤 식으로든 관련된 테이블 행 집합에 대해 계산을 수행한다. 일반 집계 함수와 달리, 현재 행뿐만 아니라 쿼리 결과의 다른 행에도 접근할 수 있다.

Window Function으로 순위, 백분위, 합계/평균, 행 번호 등을 계산할 수 있으며, 이동 합계(moving sum)이나 이동 평균(moving average)도 구현할 수 있다.

GROUP BY와의 차이점이 중요하다. PARTITION BY와 GROUP BY 모두 데이터를 분할하여 통계를 계산하지만, Window Function은 결과 행의 수를 줄이지 않는다. GROUP BY는 그룹당 하나의 행으로 축약하지만, Window Function은 원래 행을 모두 유지하면서 계산 결과를 추가 컬럼으로 붙인다.

Window Function의 종류

집계(Aggregate) Window Function: SUM(), MAX(), MIN(), AVG(), COUNT() 등

순위(Ranking) Window Function: RANK(), DENSE_RANK(), PERCENT_RANK(), ROW_NUMBER(), NTILE()

값(Value) Window Function: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), CUME_DIST(), NTH_VALUE()

구문(Syntax)

SELECT WINDOW_FUNCTION( [ALL] expression )
  OVER ( [PARTITION BY partition_list] [ORDER BY order_list] )
FROM table;

각 키워드의 역할:

• WINDOW_FUNCTION: 사용할 윈도우 함수 이름
• ALL (선택): 중복 값을 포함하여 모두 계산한다. 참고로 Window Function에서는 DISTINCT를 지원하지 않는다.
• OVER: 윈도우의 범위를 지정한다.
  • PARTITION BY: 윈도우 함수가 동작할 행 집합(파티션)을 정의한다. 생략하면 전체 테이블이 하나의 파티션이 된다.
  • ORDER BY: 각 파티션 내에서 행의 정렬 순서를 결정한다.

Ranking 예시

전체 급여 합계를 모든 행에 표시하는 기본 쿼리:

SELECT EMPNO, ENAME, SAL,
  SUM(SAL) OVER(ORDER BY SAL
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND UNBOUNDED FOLLOWING) TOTSAL
FROM EMP;

**누적 합계(Cumulative Sum)**를 구하려면 범위를 현재 행까지로 제한한다:

SELECT EMPNO, ENAME, SAL,
  SUM(SAL) OVER(ORDER BY SAL
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW) TOTSAL
FROM EMP;

RANK(), DENSE_RANK(), **ROW_NUMBER()**의 차이를 비교해보자:

SELECT ENAME, SAL,
  RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
  RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;

• RANK(): 동일 값이 있으면 같은 순위를 부여하고, 다음 순위를 건너뛴다 (1, 2, 2, 4).
• DENSE_RANK(): 동일 값에 같은 순위를 부여하되, 다음 순위를 건너뛰지 않는다 (1, 2, 2, 3).
• ROW_NUMBER(): 단순히 행 번호를 매긴다. 동일 값이라도 순번이 다르다 (1, 2, 3, 4).

SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUM,
  ENAME, SAL,
  RANK() OVER (ORDER BY SAL DESC) ALL_RANK
FROM EMP;

Aggregation 예시

각 직무(JOB)별 평균 급여를 모든 행에 표시:

SELECT ENAME, SAL, JOB,
  AVG(SAL) OVER (PARTITION BY JOB) AS AVG_SAL_JOB
FROM EMP;

GROUP BY와 비교해보면 차이가 명확하다. GROUP BY는 직무당 하나의 행만 반환하지만, Window Function은 모든 행을 유지한다:

-- GROUP BY: 직무당 하나의 행
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB;

-- Window Function: 모든 행 유지 + 평균 컬럼 추가
SELECT ENAME, SAL, JOB,
  AVG(SAL) OVER (PARTITION BY JOB) AS AVG_SAL_JOB
FROM EMP;

매니저별 급여 합계:

SELECT ENAME, SAL, MGR,
  SUM(SAL) OVER (PARTITION BY MGR) SUM_MGR
FROM EMP;

순위 함수 심화

PARTITION 없이 전체 테이블을 하나의 파티션으로 취급하는 경우:

SELECT ENAME, SAL, JOB, HIREDATE,
  ROW_NUMBER() OVER (ORDER BY SAL) AS ROW_NUMBER_SAL,
  RANK() OVER (ORDER BY SAL) AS RANK_SAL,
  DENSE_RANK() OVER (ORDER BY SAL) AS DENSE_RANK_SAL
FROM EMP;

PARTITION BY를 사용하면 각 파티션 내에서 순위를 매긴다. 실행 순서는 1) PARTITION, 2) ORDERING이다:

SELECT ENAME, SAL, JOB, HIREDATE,
  RANK() OVER (PARTITION BY JOB ORDER BY HIREDATE DESC) AS RANK_HIREDATE
FROM EMP;

동일한 윈도우 정의를 여러 번 재사용할 때는 WINDOW 절로 이름을 붙일 수 있다:

SELECT ENAME, SAL, JOB, HIREDATE,
  RANK() OVER w AS RANK_HIREDATE
FROM EMP
WINDOW w AS (PARTITION BY JOB ORDER BY HIREDATE DESC);

백분위(Percentile) 계산에는 CUME_DIST()와 PERCENT_RANK()를 사용한다:

SELECT ENAME, SAL, JOB, HIREDATE,
  RANK() OVER w AS RANK_SAL,
  CUME_DIST() OVER w AS CUME_DIST_SAL,
  PERCENT_RANK() OVER w AS PERCENT_RANK_SAL
FROM EMP
WINDOW w AS (ORDER BY SAL);

Value Window Function 예시

FIRST_VALUE()와 LAST_VALUE()는 각 파티션의 첫 번째와 마지막 값을 반환한다:

SELECT ID, CITY, ORD_DATE,
  FIRST_VALUE(ORD_DATE) OVER(PARTITION BY CITY) AS FIRST_VAL,
  LAST_VALUE(ORD_DATE) OVER(PARTITION BY CITY) AS LAST_VAL
FROM ORDERS;

LAG()와 LEAD()는 현재 행 기준으로 이전/이후 행의 값을 가져온다. 두 번째 인자는 오프셋(몇 행 전/후)을 지정한다:

-- 1행 전/후
SELECT ID, CUSTOMER_NAME, CITY, ORD_AMT, ORD_DATE,
  LAG(ORD_DATE, 1) OVER(ORDER BY ORD_DATE) AS PREV_ORD_DAT,
  LEAD(ORD_DATE, 1) OVER(ORDER BY ORD_DATE) AS NEXT_ORD_DAT
FROM ORDERS;

-- 2행 전/후
SELECT ID, CUSTOMER_NAME, CITY, ORD_AMT, ORD_DATE,
  LAG(ORD_DATE, 2) OVER(ORDER BY ORD_DATE) AS PREV_ORD_DAT,
  LEAD(ORD_DATE, 2) OVER(ORDER BY ORD_DATE) AS NEXT_ORD_DAT
FROM ORDERS;

Frame Specification

Frame은 현재 파티션의 부분 집합이다. Frame 절을 통해 현재 행 기준으로 윈도우 함수가 참조하는 행의 범위를 세밀하게 지정할 수 있다.

• 파티션 시작부터 현재 행까지로 정의하면 **누적 합계(running total)**를 계산할 수 있다.
• 현재 행 전후 N행으로 정의하면 **이동 평균(rolling average)**을 계산할 수 있다.

Frame을 지정하는 키워드:

• ROWS: 물리적 행 위치로 범위를 정의한다 (physical window).
• RANGE: 값의 범위로 범위를 정의한다 (logical window).
• BETWEEN ... AND ...: Frame의 시작점과 끝점을 지정한다.
  • UNBOUNDED PRECEDING: 파티션의 첫 번째 행
  • UNBOUNDED FOLLOWING: 파티션의 마지막 행
  • CURRENT ROW: 현재 행 (ROWS의 경우 현재 행 자체, RANGE의 경우 현재 행과 동일한 값을 가진 행들)

파티션 전체의 평균:

SELECT ID, CITY, ORD_AMT, ORD_DATE,
  AVG(ORD_AMT) OVER (PARTITION BY CITY ORDER BY ORD_DATE
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND UNBOUNDED FOLLOWING
  ) AS AVG_AMT
FROM ORDERS;

2건 이동 평균 (현재 행 포함, 이전 1행):

SELECT ID, CITY, ORD_AMT, ORD_DATE,
  AVG(ORD_AMT) OVER (PARTITION BY CITY ORDER BY ORD_DATE
    ROWS BETWEEN 1 PRECEDING
    AND CURRENT ROW
  ) AS AVG_AMT
FROM ORDERS;

참고로 0 PRECEDING은 CURRENT ROW와 동일하다.

3일 이동 평균 (RANGE 사용):

SELECT ID, ORD_DATE, ORD_AMT,
  AVG(ORD_AMT) OVER(ORDER BY ORD_DATE
    RANGE BETWEEN INTERVAL 2 DAY PRECEDING
    AND CURRENT ROW
  ) AS AVG_AMT
FROM ORDERS;

ROWS는 행의 개수를 기준으로 하고, RANGE는 값의 범위를 기준으로 한다는 차이가 있다. 위 예시에서 RANGE를 사용했기 때문에 날짜 기준 2일 이내의 모든 행이 Frame에 포함된다.

Key

Key는 릴레이션에서 튜플을 고유하게 식별하는 속성 또는 속성의 집합이다.

Key가 필요한 이유:

• 데이터의 **정체성(identity)**을 보장한다.
• 데이터의 **무결성(integrity)**을 유지한다.
• 릴레이션 간의 **관계(relationship)**를 설정한다. (PK-FK 참조 무결성)

Super Key

모든 가능한 고유 식별자를 Super Key라 한다. 릴레이션에서 튜플을 식별할 수 있는 속성 또는 속성 조합 모두가 해당된다.

예를 들어, 학생 테이블에서 {학번}, {학번, 이름}, {학번, 이름, 학과} 모두 Super Key가 될 수 있다. 유일성만 보장하면 되므로, 불필요한 속성이 포함되어도 상관없다.

Candidate Key

Candidate Key는 Super Key의 최소 부분 집합이다. 즉, Super Key에서 어떤 속성을 하나라도 제거하면 더 이상 고유 식별이 불가능한 경우, 그것이 Candidate Key다.

위 예시에서 {학번}은 Candidate Key이지만, {학번, 이름}은 학번만으로도 식별 가능하므로 Candidate Key가 아니다.

Primary Key

Primary Key는 Candidate Key 중에서 대표로 선택된 키다.

• 중복 불가: 두 행이 같은 PK 값을 가질 수 없다.
• NULL 불가: 모든 행은 반드시 PK 값을 가져야 한다.

Alternate Key

Alternate Key는 Primary Key로 선택되지 않은 나머지 Candidate Key를 의미한다.

Foreign Key

Foreign Key는 다른 릴레이션과의 관계를 정의하는 데 사용되는 속성이다. Foreign Key를 통해 테이블 간 **참조 무결성(referential integrity)**을 유지할 수 있다.

Composite Key와 Compound Key

Composite Key는 두 개 이상의 속성으로 구성된 키를 말한다.

Compound Key는 Composite Key 중에서 적어도 하나의 속성이 Foreign Key인 경우를 말한다. 예를 들어, 복합 키 (FileCD, Branch)에서 Branch가 다른 테이블의 FK라면 Compound Key다.

정리하면:

• 모든 Compound Key는 Composite Key이다.
• 일부 Composite Key만 Compound Key이다.

Surrogate Key (인조 키)

릴레이션에 자연적으로 키로 사용할 속성이 없는 경우, 인위적인 속성을 만들어 키로 사용한다. 이것이 Surrogate Key(인조 키)다.

데이터 자체에 의미를 부여하지 않으며, 튜플을 고유하게 식별하는 것이 유일한 목적이다. 대표적으로 _ID 컬럼에 AUTO INCREMENT를 걸어 사용하는 패턴이 이에 해당한다.

HGU 전산전자공학부 홍참길 교수님의 23-1 Database System 수업을 듣고 작성한 포스트이며, 첨부한 모든 사진은 교수님 수업 PPT의 사진 원본에 필기를 한 수정본입니다.