데이터베이스 구축 5강. 조인·뷰 그리고 데이터 전환
정규화로 쪼갠 표를 다시 붙이는 일이 조인입니다. 쪼개는 것과 붙이는 것을 함께 배우지 않으면, 설계는 절반만 배운 셈이 됩니다.
풀고 시작
조인은 쪼갠 것을 다시 붙이는 일입니다
정규화로 테이블을 나눴으니 조회할 때는 다시 합쳐야 합니다. 그 연산이 조인(join)입니다.
내부 조인(INNER JOIN)은 양쪽에서 조건이 맞는 행만 남깁니다. 학생 테이블과 수강 테이블을 학번으로 내부 조인하면, 수강 신청을 하지 않은 학생은 결과에서 빠집니다.
외부 조인(OUTER JOIN)은 한쪽 또는 양쪽의 행을 조건과 무관하게 모두 남깁니다. 왼쪽 외부 조인은 왼쪽 테이블의 모든 행을 남기고 짝이 없는 자리는 널로 채웁니다. 수강하지 않은 학생까지 보고 싶을 때 쓰는 것이죠. 오른쪽 외부 조인은 반대이고, 완전 외부 조인은 양쪽 모두를 남깁니다.
교차 조인(CROSS JOIN)은 조건 없이 모든 조합을 만듭니다. 100행과 100행을 교차 조인하면 1만 행이 나오죠. 이 결과를 카티션 곱이라고 부르는데, 대개는 조인 조건을 빠뜨렸을 때 실수로 만나게 됩니다.
자연 조인(NATURAL JOIN)은 두 테이블에서 이름이 같은 컬럼을 자동으로 찾아 조건으로 씁니다. 편하지만 컬럼 이름이 우연히 겹치면 의도치 않은 조인이 되므로 실무에서는 조건을 명시하는 쪽을 선호합니다.
셀프 조인은 한 테이블을 자기 자신과 조인하는 것입니다. 사원 테이블에 상사의 사번이 들어 있을 때, 사원과 상사의 이름을 나란히 뽑으려면 같은 표를 두 번 부릅니다.
부속 질의와 집합 연산
부속 질의(subquery)는 질의문 안에 들어간 또 다른 질의문입니다. 평균 급여보다 많이 받는 사원을 찾을 때, 안쪽 질의로 평균을 구하고 바깥 질의가 그 값과 비교하는 식이죠.
부속 질의가 여러 행을 돌려줄 때는 특별한 연산자가 필요합니다. IN은 결과 목록 중 하나와 같으면 참이고, EXISTS는 결과가 하나라도 있으면 참입니다. ALL은 모든 값에 대해 조건이 참이어야 하고, ANY(또는 SOME)는 하나라도 참이면 됩니다.
집합 연산도 자주 나옵니다. UNION은 두 결과를 합치며 중복을 제거하고, UNION ALL은 중복을 그대로 둡니다. INTERSECT는 양쪽에 다 있는 행만, EXCEPT(또는 MINUS)는 앞쪽에만 있는 행을 남깁니다. UNION과 UNION ALL의 차이가 시험 단골인데, 중복 제거에는 정렬 비용이 들어 UNION ALL이 더 빠르다는 점까지 함께 기억해 두시면 좋습니다.
뷰는 저장된 질의문입니다
뷰(view)는 하나 이상의 테이블에서 유도된 가상 테이블입니다. 실제 데이터를 갖고 있지 않고 정의만 저장돼 있어서, 조회할 때마다 원본 테이블에서 값을 끌어옵니다.
| 장점 | 단점 |
|---|---|
| 복잡한 질의를 이름 하나로 감춘다 | 정의를 변경할 수 없어 지우고 다시 만들어야 한다 |
| 필요한 컬럼만 보여 줘 보안에 유리하다 | 독립적인 인덱스를 가질 수 없다 |
| 논리적 독립성을 제공한다 | 삽입·수정·삭제에 제약이 있다 |
뷰는 ALTER로 수정할 수 없다는 점이 자주 출제됩니다. 고치려면 DROP하고 CREATE를 다시 해야 합니다. 또 집계 함수나 GROUP BY가 들어간 뷰처럼 원본 행과 일대일 대응이 되지 않는 뷰에는 데이터를 넣거나 고칠 수 없습니다.
관련해서 시스템 카탈로그도 알아 두세요. 데이터베이스 자체에 대한 정보, 즉 테이블과 컬럼과 인덱스와 사용자 권한 같은 메타데이터를 담은 시스템 테이블입니다. 사용자는 조회할 수 있지만 직접 갱신할 수는 없고, DBMS가 알아서 관리합니다.
데이터 전환은 검증이 절반입니다
기존 시스템의 데이터를 새 시스템으로 옮기는 작업을 데이터 전환 또는 마이그레이션이라고 합니다. 흔히 ETL로 부르는 세 단계로 진행됩니다. 원천에서 데이터를 뽑는 추출(Extraction), 형식과 규칙을 맞추는 변환(Transformation), 목표 시스템에 넣는 적재(Loading)입니다.
핵심은 옮기고 나서의 검증입니다. 원천과 목표의 건수를 비교하는 정합성 검사, 금액 합계 같은 값을 대조하는 총계 검사, 표본을 뽑아 한 줄씩 비교하는 표본 검사를 수행합니다. 전환 과정에서 발생한 오류는 오류 목록으로 남기고 원인별로 처리합니다.
전환 실패에 대비해 원복 계획을 세워 두는 것도 필수입니다. 되돌릴 수 없는 전환은 계획이 아니라 도박이니까요. 여기까지가 데이터베이스 과목입니다. 다음 과목에서는 코드를 직접 읽는 프로그래밍 언어 활용으로 넘어갑니다.
인출 문제
생각해볼 질문 (정답 없음)
전환 검증에서 건수는 맞는데 값이 미묘하게 다른 경우가 있습니다. 어느 정도의 차이까지를 성공으로 볼지, 그 기준은 누가 정해야 할까요.
이전: 4강 SQL DDL과 DML · 다음: 4과목 1강 서버 프로그램 구현과 개발 환경