eff3ct's st0rage Dev Blog

[DBMS] 04. Multi Table Query

30 Aug 2023
  • 한양대학교 컴퓨터소프트웨어학부 정형수 교수님의 2022년도 데이터베이스시스템및응용 강의를 듣고 정리한 내용임을 밝힙니다.
  • 내용에 오류가 있을 수 있으며, 발견 시 댓글로 알려주시면 감사하겠습니다.

4. Multi Table Query

  • 여러개의 테이블에서 사용하는 쿼리들을 알아보자.

Foreign Key Constraint

FK 제약조건은 참조되는 테이블의 특정한 칼럼이 참조하는 테이블의 특정한 칼럼과 동일한 값을 가져야 한다는 제약조건이다. 학생이 특정 강의에 등록되는 상황을 생각해보자. 학생 테이블은 Student(sid, sname, major)이고, 등록 테이블은 Enrolled(sid, cid) 라고 생각해보자. (cid는 course id의 줄임)

Enrolled의 sid는 Student를 reference 하고 있어야 하니 여기에 FK를 걸어줄 수 있다.

CREATE TABLE Enrolled (
    sid INTEGER,
    cid INTEGER,
    PRIMARY KEY (sid, cid),
    FOREIGN KEY (sid) REFERENCES Student
);

sid와 cid를 묶어서 PK로 설정하고, sid에 FK를 걸어준다. 이제 Enrolled 테이블에 sid가 Student 테이블에 없는 값이 들어가면 에러가 발생한다. 이런 제약을 걸어주는 것으로 데이터를 조금 더 안전하게 관리할 수 있다.

뜬금없지만 갑자기 어떤 학생이 자퇴를 했다고 생각해보자. 관리자는 이런 경우에 해당 학생이 듣는 모든 강의 데이터를 날리고 싶을 수 있다. 그렇다면, 일일히 삭제해야할까? SQL은 FK로 제약이 걸린 데이터가 날라갈 때 행동 규칙을 제공해준다.

현재 원하는 것은 Student 튜플이 하나 날라가면 Enrolled에 같은 sid가 포함된 모든 튜플을 삭제하고 싶은 것이다. 이런 경우에는 ON DELETE CASCADE를 사용하면 된다.

CREATE TABLE Enrolled (
    sid INTEGER,
    cid INTEGER,
    PRIMARY KEY (sid, cid),
    FOREIGN KEY (sid) REFERENCES Student ON DELETE CASCADE
);

헉! 나는 데이터를 그래도 가만히 두고 싶은데, 이렇게 하면 데이터가 다 날라간다. 그렇다면, 데이터가 날라가도 기록 자체는 냅둘 수 있을까? 이런 경우에는 ON DELETE SET NULL을 사용하면 된다.

CREATE TABLE Enrolled (
    sid INTEGER,
    cid INTEGER,
    PRIMARY KEY (sid, cid),
    FOREIGN KEY (sid) REFERENCES Student ON DELETE SET NULL
);

이렇게 하면, Student 테이블에서 sid가 삭제되면 Enrolled 테이블의 sid는 NULL로 바뀐다. 이렇게 하면 데이터는 날라가지 않고, 기록만 남길 수 있다. 이외에도 ON DELETE NO ACTION, ON DELETE SET DEFAULT 등이 있다. 자세한 내용은 postgreSQL 링크를 참고하자.

Join

다중 테이블을 다룰 때 아주 중요한 쿼리 중 하나이다. 두 개 이상의 테이블을 합쳐서 하나의 테이블처럼 사용하고 싶은 경우가 생긴다면 어떻게 해야할까? 이때, 바로 Join을 사용하면 된다.

Product(Pname, price, manufacturer)와 Company(Cname, country) 가 있다고 생각해보자. 어떤 프로덕트의 제조사가 한국 국적인 것들만 뽑아내고 싶다고 해보자. 기존의 Single Table Query는 해결할 수 없다. 어떻게든 두 테이블을 연관시켜야 가능해보인다. 이런 상황에 Join을 사용한다. 다짜고자 다음의 쿼리를 작성해보자.

SELECT Pname
FROM Product, Company
WHERE manufacturer = Cname 
    AND country = 'Korea';

엥 Join이라는 키워드가 없이 SQL을 작성해도 ‘Join’이라는 연산이 되는건가? 그렇다. 이번에는 명시적으로 Join을 사용해서 SQL을 작성해보자.

SELECT Pname
FROM Product 
JOIN Company ON manufacturer = Cname
WHERE country = 'Korea';

위 두 SQL은 동일한 Join을 수행한다. SQL을 살펴보면 서로 다른 칼럼인 manufacturer와 Cname이 같은 값을 가지는 경우를 만들면서 두 테이블을 연관시키고 있다. 이런식으로 여러 테이블을 연결시키는 것을 Join 연산이라고 한다.

Ambiguity Problem

흠 만약 Product와 Company의 Pname과 Cname이 같은 경우(둘 다 name이라면?)에는 name=name 처럼 해주면 되는 것일까? 이 경우에는 DBMS가 어떤 칼럼을 사용해야할지 모르기 때문에 에러가 발생한다. 이런 경우에는 다음과 같이 칼럼명을 명시해주면 된다.

SELECT Product.name 
FROM Product
JOIN Company ON Product.name = Company.name
WHERE country = 'Korea';

/* OR */

SELECT Product.name
FROM Product, Company
WHERE Product.name = Company.name
    AND country = 'Korea';

나는 테이블 풀 네임을 쓰기 싫은데요? 그런 당신을 위해 AS(alias)라는 키워드가 있다. 다음과 같이 사용하면 된다.

SELECT P.name
FROM Product AS P, Company AS C
WHERE P.name = C.name
    AND country = 'Korea';

개인적으로 나는 이렇게 AS를 사용해 나타내는 것을 선호한다. 이렇게 같은 이름의 칼럼이 존재하는 경우에는 모호함을 없애야한다.

Join의 내부 구현은 어떻게 되어있을까? 뭔가 두 테이블을 합치는 것 같은데, 잘은 모르겠다. 다음 예시를 한 번 생각해보자. Table X(A)와 Y(B, C)가 있고, X.A = Y.B인 X.A를 찾고 싶다고 해보자. 이 경우에는 다음과 같은 쿼리를 작성할 수 있다.

SELECT X.A
FROM X, Y
WHERE X.A = Y.B;

Join 연산은 내부적으로 다음과 같은 의미(실제로 실행하는 순서가 아닐 수 있음에 유의)로 해석된다.

Join 연산의 의미론적 해석

  1. X와 Y의 Cartesian Product를 구한다.
  2. Cartesian Product의 각 튜플에 대해 X.A = Y.B를 만족하는 튜플만 남긴다. (Selection)
  3. X.A만 남긴다. (Projection)

Cartesian Product는 곱집합을 의미하는데, 그냥 만들 수 있는 모든 조합을 만드는 것과 같다. 따라서, X의 튜플 수가 n개, Y의 튜플 수가 m개라면, Cartesian Product는 n * m개의 튜플을 가진다. 이렇게 Cartesian Product를 구하고, Selection을 수행하고, Projection을 수행하는 것이 Join 연산이다.

join-explain

Cross Product를 구하는 것은 아주 비용이 크기 때문에 Join 연산은 무거운 연산들 중 하나이다. Join 순서를 어떻게 하느냐에 따라 비용이 크게 달라지는 경우도 있다. DBMS는 이런 Join 순서도 Query Optimizer를 통해 최적화해준다. 여튼, 복잡한 연산이기 때문에 남발하는 것은 시스템의 성능에 크게 영향을 미칠 수 있다.

요약

  • FK 제약을 통해 테이블을 서로 연결할 수 있다.
  • ON DELETE [options] 를 통해 삭제 시 행동을 지정할 수 있다.
  • Join을 통해 두 테이블을 연결할 수 있다.
  • Join은 Cartesian Product를 구하고, Selection을 수행하고, Projection을 수행하는 것이다.
  • Join은 비용이 크다.

Related posts