eff3ct's st0rage Dev Blog

[DBMS] 07. Advanced SQL

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

7. Advanced SQL

  • 조금 더 많은 내용을 다루어보자.

View

쿼리 결과를 두고두고 꺼내쓰고 싶을 때가 있다. View는 그런상황에 사용하기 좋은 기능이다. View는 가상의 테이블을 저장하는 강력한 기능을 제공한다. 가상이라는 말이 붙는 이유는 쿼리 결과 데이터 자체를 어디에 저장하는게 아니라 View를 사용하면 그때마다 쿼리를 실행해 데이터를 돌려주기 때문이다. 즉, 저장되는게 쿼리문 자체라고 생각하면 좋다.

View 생성

CREATE VIEW view_name
AS SELECT columns
    FROM table
    WHERE condition;

이런 식으로 View를 생성하고 이름을 지정하면 AS 뒤의 쿼리문이 저장된다. 이걸 가상 테이블이라고 하는만큼 테이블처럼 사용할 수 있다.

View 사용

SELECT * 
FROM view_name
WHERE condition;

이런식으로 또 다른 쿼리를 날릴 수도 있는 것이다. 서브쿼리를 날리는 것처럼 사용할 수 있고, 테이블처럼도 사용할 수 있다. 다만, View가 가상 테이블인 이유로 삽입 삭제 등은 연산이 제한될 수도 있다. View는 삭제하지 않는 이상 계속 남아있기 때문에 DROP을 통해 삭제할 수 있다.

View 삭제

DROP VIEW view_name;

이렇게 쿼리를 작성하면 View가 삭제된다.

With (Common Table Expression)

With는 쿼리 결과를 저장하는 기능을 제공한다. 음? 그럼 View랑은 뭐가 다를까. With는 마치 변수처럼 동작한다. 그 말은 즉슨, 쿼리문 내에서만 결과가 살아있다. 즉, View와 가장 큰 차이점은 쿼리문이 종료되면 With로 생성된 쿼리 결과는 사라진다 점이다.

With 사용

WITH with_name AS (
    SELECT columns
    FROM table
    WHERE condition
)

SELECT *
FROM with_name
WHERE condition;

이 쿼리문이 종료되고 나서 With문에서 작성한 이름을 가지고 사용하면 오류가 발생한다. 쿼리가 종료되고 나서 사라졌기 때문이다. 임시 테이블 정도로 이해하면 될 것 같다.

중복 With

With로 정의한 결과값을 또 사용할 수 있다.

WITH with_name_1 AS (
        SELECT columns
        FROM table
        WHERE condition
    ),
    with_name_2 AS (
        SELECT columns
        FROM with_name_1
        WHERE condition
    )

SELECT *
FROM with_name_2
WHERE condition;

이런식으로 정의한 결과값을 또 다른 With문에서 사용할 수도 있다.

Inner Join

이전 글을 읽고 왔으면 Join은 알고 있을 것이다. 그럼 Inner Join은 무엇일까? 기본적으로 사용하는 Join이 바로 Inner Join이다. 즉, Join이라고 하면 일반적으로는 Inner Join을 말하는 것이다. Inner가 있으면 Outer도 있을 것 같다고 생각했다면 정답이다. Inner Join부터 알아보자.

Inner Join은 두 테이블 간 Join 조건을 동시에 만족하는 레코드만 반환하는 조인이라 생각하면 된다. 정말 당연하게 사용하면 된다. Inner라는 것을 명시하기 위해서는 다음과 같이 작성하면 된다.

SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

여기서 Inner는 생략 가능하고, table1과 table2의 column이 같은 것만 들고 오는 것이다. 평범한 Join이다.

Outer Join

Outer Join은 조건을 만족하지 않아도 살려주는 Join이다. Left Outer Join과 Right Outer Join, Full Outer Join이 있다. 각각 왼쪽, 오른쪽, 양쪽 테이블을 기준으로 조인을 수행한다. 무슨말일까?

Left Outer Join

왼쪽 테이블 데이터를 모두 가져온 뒤에 오른쪽 테이블 데이터와 조건을 비교한다. 조건을 만족하지 않는 데이터는 NULL로 채워서 반환한다. 이게 Left Outer Join이다. 즉, 조건을 만족하는 것을 찾되 데이터를 날리지 않고 싶을 때 사용하는 쿼리이다.

SELECT a, Y.b
FROM X
LEFT OUTER JOIN Y 
    ON X.a = Y.a;

이런식으로 사용하면, X 테이블의 값을 모두 가져오고 Y 테이블의 값과 조건을 비교한다. X.a = Y.a를 만족하지 않는 레코드는 Y.b값이 모두 NULL로 반환된다. RIGHT OUTER JOIN이라고 명시하면 된다.

Right Outer Join

Left Outer Join과 방향만 반대가 된다고 생각하면 된다.

Full Outer Join

Cartesian Product를 반환한다. 즉, 두 테이블의 모든 레코드를 조합해서 반환한다. 물론 조건을 만족하지 않으면 NULL로 반환하게 된다. FULL OUTER JOIN이라고 명시하면 된다.

Another..

이외에도 with recursive 같은 쿼리나 rollup 같은 것도 있지만, Advanced Query는 여기서 마무리하고 SQL 글은 이제 끝내도록 하겠다. 다음 글부터는 DBMS의 설계와 구현을 다룰 예정이다.

요약

  • View: 가상 테이블. 쿼리문을 저장하는 기능. 테이블처럼 사용할 수 있다.
  • With: 쿼리 결과를 저장하는 기능. 변수처럼 사용할 수 있다.
  • Inner Join: 두 테이블 간 Join 조건을 동시에 만족하는 레코드만 반환하는 조인
  • Outer Join: 조건을 만족하지 않아도 값이 사라지지 않는 Join. Left, Right, Full Outer Join이 있다.

Related posts