질의
질의 (Chapter 7. Queries)
이전 장들에서 테이블을 만들고 데이터를 채우고 조작하는 방법을 다뤘어요. 이제 드디어 데이터베이스에서 데이터를 가져오는 방법을 이야기할 차례예요. PostgreSQL에서 데이터를 꺼내는 가장 기본적인 도구가 바로 SELECT 질의인데, 이 페이지에서는 그 질의가 어떻게 구성되고 각 절이 어떤 역할을 하는지 한 흐름으로 정리해 드릴게요.
개요 (Overview)
데이터베이스에서 데이터를 검색하는 과정, 또는 그 명령을 **질의(query)**라고 불러요. SQL에서는 SELECT 명령으로 질의를 지정하죠. SELECT의 일반 문법은 이래요.
[ WITH with_queries ] SELECT select_list FROM table_expression [ sort_specification ]
아래에서 select list와 table expression, 정렬 지정(sort specification)의 세부를 하나씩 다룰게요. WITH 질의는 고급 기능이라 가장 마지막에 다뤄요.
가장 단순한 질의 형태는 이래요.
SELECT * FROM table1;
table1이라는 테이블이 있다고 가정하면, 이 명령은 table1의 모든 행과 모든 사용자 정의 컬럼을 가져와요. 검색 방법은 클라이언트 애플리케이션에 따라 달라져요. 예를 들어 psql 프로그램은 화면에 ASCII-아트 테이블로 표시하고, 클라이언트 라이브러리는 질의 결과에서 개별 값을 추출하는 함수를 제공하죠. select list의 *는 table expression이 제공하는 모든 컬럼을 의미해요.
select list는 사용 가능한 컬럼의 일부만 뽑거나 컬럼을 이용해 계산을 할 수도 있어요.
SELECT a, b + c FROM table1;
이 예시는 b와 c가 숫자 타입이라고 가정한 질의예요.
FROM table1은 단순한 table expression이에요. 한 테이블만 읽죠. 일반적으로 table expression은 base table, 조인, 서브쿼리의 복잡한 구조가 될 수 있어요. table expression을 아예 생략하고 SELECT를 계산기처럼 쓸 수도 있어요.
SELECT 3 * 4;
select list의 표현식이 실행마다 다른 결과를 내는 경우엔 이게 더 유용해요. 예를 들어 함수를 이렇게 호출할 수 있죠.
SELECT random();
테이블 표현식 (Table Expressions)
테이블 표현식은 하나 이상의 테이블에서 파생된 테이블을 만들어요. 단순한 테이블 표현식은 디스크에 있는 테이블(소위 base table)만 참조하지만, 더 복잡한 표현식으로 base table을 여러 방식으로 수정하거나 결합할 수 있어요.
FROM 절
FROM 절은 콤마로 구분된 테이블 참조 목록에서 하나 이상의 테이블로 테이블을 파생해요.
FROM table_reference [ , table_reference [ , ... ] ]
테이블 참조는 테이블 이름(스키마 한정 가능)일 수도 있고, 서브쿼리 같은 파생 테이블, JOIN 구조, 또는 이것들의 복잡한 조합일 수도 있어요.
조인 테이블 (Joined Tables)
조인은 두 테이블을 지정된 규칙에 따라 결합한 테이블이에요. T1과 T2 행의 모든 가능한 조합(즉 카테시안 곱)에 대해, 조인된 테이블은 T1의 모든 컬럼에 이어 T2의 모든 컬럼으로 이루어진 행을 포함해요. 두 테이블이 각각 N행, M행이라면 조인된 테이블은 N × M행이 돼요.
조인의 형태는 크게 이렇게 갈라져요.
- 교차 조인 (
CROSS JOIN) — 가능한 모든 행 조합을 만들어요. - 한정 조인 (Qualified joins) —
ON boolean_expression으로 조인 조건을 주는 형식이에요. USING (join column list)조인 — 같은 이름의 컬럼을 결합해요.NATURAL조인 — 두 테이블에 같은 이름으로 존재하는 모든 컬럼을 암시적으로 결합해요.
T1 { [ INNER ] | { LEFT | RIGHT | FULL } [ OUTER ] } JOIN T2 ON boolean_expression
T1 { [ INNER ] | { LEFT | RIGHT | FULL } [ OUTER ] } JOIN T2 USING ( join column list )
T1 NATURAL { [ INNER ] | { LEFT | RIGHT | FULL } [ OUTER ] } JOIN T2
INNER와 OUTER는 모든 형태에서 선택 사항이에요. INNER가 기본이고, LEFT, RIGHT, FULL은 outer join을 뜻해요.
- LEFT OUTER JOIN —
T2에서T1의 어떤 행과도 조인 조건을 만족하지 않는 각 행에 대해,T1컬럼이 null인 조인 행이 추가돼요. - RIGHT OUTER JOIN — 그 반대예요.
T1에서T2의 어떤 행과도 만족하지 않는 각 행에 대해,T2컬럼이 null인 조인 행이 추가돼요. 결과 테이블은 항상T2의 각 행에 대해 하나의 행을 가져요. - FULL OUTER JOIN — 먼저 inner join을 수행하고, 그다음 양쪽에 매칭되지 않는 행을 null로 채워 넣어요.
JOIN ON은 T1의 모든 컬럼에 이어 T2의 모든 컬럼을 만들어내는 반면, JOIN USING은 열거된 컬럼 쌍마다 하나의 출력 컬럼을 만들고, 그다음 T1의 남은 컬럼, 이어서 T2의 남은 컬럼을 붙여요.
참고
USING은 열거된 컬럼만 결합하므로 조인된 릴레이션의 컬럼 변경에 비교적 안전해요. NATURAL은 훨씬 위험한데, 어느 릴레이션의 스키마 변경으로 새로 매칭되는 컬럼 이름이 생기면 조인이 그 새 컬럼까지 결합해 버리거든요.
FULL JOIN 예시를 볼게요.
SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | 3 | yyy
| | 5 | zzz
(4 rows)
테이블·컬럼 별칭 (Table and Column Aliases)
테이블 참조에는 별칭(alias)을 줄 수 있어요. 별칭은 현재 질의에 한해 테이블 참조의 새 이름이 돼요. 질의의 다른 곳에서 원래 이름으로 테이블을 참조하는 것은 허용되지 않아요.
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
테이블 함수 (Table Functions)
테이블 함수는 ROWS FROM 문법으로도 결합할 수 있어요. 결과는 병렬 컬럼으로 반환되고, 결과 행 수는 가장 큰 함수 결과의 행 수이며, 더 작은 결과는 null로 채워져 맞춰져요.
특별한 테이블 함수 UNNEST는 임의 개수의 배열 파라미터로 호출할 수 있고, 각 파라미터에 대해 개별적으로 UNNEST를 호출한 뒤 ROWS FROM으로 결합한 것처럼 대응하는 개수의 컬럼을 반환해요.
WHERE 절
WHERE 절의 문법은 이래요.
WHERE search_condition
search_condition은 boolean 타입을 반환하는 값 표현식이에요. FROM 절에서 파생된 각 행에 대해 search condition이 평가돼요. 조건이 참이면 그 행이 출력에 유지되고, 그렇지 않으면 버려져요. search condition은 보통 =나 > 같은 비교 연산자를 포함하지만, IS NULL 같은 것도 쓸 수 있죠. FROM 절에 콤마로 구분된 여러 테이블이 있다면, WHERE는 각 행 조합마다 한 번씩 평가돼요.
행을 어떻게 골라낼지는 WHERE 조건에 의존하지만, 행이 출력으로 전달되는 순서를 보장하지는 않아요. 순서를 정렬하려면 ORDER BY를 써야 해요.
GROUP BY와 HAVING 절
GROUP BY와 HAVING은 행을 그룹으로 묶는 데 쓰여요. 공통 값을 가진 각 행 집합을 그룹을 대표하는 하나의 그룹 행으로 결합해요. 출력의 중복을 없애거나 그 그룹에 적용되는 집계(aggregate)를 계산하기 위해서죠. 예를 들어:
SELECT * FROM test1;
x | y
---+---
a | 3
c | 2
b | 5
a | 1
(4 rows)
SELECT x FROM test1 GROUP BY x;
x
---
a
b
c
(3 rows)
GROUP BY로 그룹화한 뒤 조건을 걸려면 HAVING을 써요. GROUP BY가 있으면 그룹 행들이 만들어지고, HAVING 조건이 참인 그룹 행만 출력에 남아요. WHERE가 그룹화 전에 행을 걸러내는 반면, HAVING은 그룹화 후에 그룹을 걸러내는 방식이에요.
GROUPING SETS, CUBE, ROLLUP
단일 질의에서 여러 그룹화 집합을 지정하려면 GROUP BY 안에 GROUPING SETS, ROLLUP, CUBE를 쓸 수 있어요. 각각 서로 다른 조합의 그룹화를 한 번에 계산하게 해 줘요.
ROLLUP (a, b)—(a, b),(a),()그룹을 만들어요.CUBE (a, b)— 가능한 모든 조합(a, b),(a),(b),()를 만들어요.GROUPING SETS (...)— 원하는 그룹화 집합을 명시적으로 나열해요.
윈도우 함수 처리 (Window Function Processing)
GROUP BY 절이 수행되면 결과가 윈도우 함수(window function)에 대한 입력이 돼요. SELECT 목록에 있는 윈도우 함수는 각 행에 대해, 그 행이 속한 "윈도우"의 행 집합을 봐서 계산해요. GROUP BY가 행 집합을 하나의 그룹 행으로 접는 것과 달리, 윈도우 함수는 각 행의 개별 정체성을 유지하면서 주변 행들을 참조해 계산하죠. OVER 절이나 FILTER로 윈도우 범위를 지정해요.
선택 목록 (Select Lists)
앞 절에서 봤듯이, SELECT 명령의 table expression은 테이블·뷰 결합, 행 제거, 그룹화 등을 통해 중간 가상 테이블을 만들고, 이 테이블이 마지막으로 선택 목록(select list) 처리로 넘어와요.
선택 목록 항목 (Select-List Items)
가장 단순한 선택 목록은 *로, table expression이 만들어내는 모든 컬럼을 출력해요. 그 외에는 선택 목록이 값 표현식의 콤마 구분 목록이에요. 예를 들어 컬럼 이름의 목록일 수 있죠.
SELECT a, b, c FROM ...
둘 이상의 테이블이 같은 이름의 컬럼을 가진다면 테이블 이름도 함께 줘야 해요.
SELECT tbl1.a, tbl2.a, tbl1.b FROM ...
여러 테이블을 다룰 때 특정 테이블의 모든 컬럼을 요구하는 것도 유용해요.
SELECT tbl1.*, tbl2.a FROM ...
컬럼 레이블 (Column Labels)
선택 목록의 항목에 이름을 붙여 출력 컬럼 이름을 바꿀 수 있어요. 기본적으로 출력 컬럼 이름은 select list 표현식의 이름에서 결정되지만, AS로 직접 지정할 수 있어요.
SELECT a AS value, b + c AS sum FROM ...
DISTINCT
SELECT DISTINCT는 결과에서 중복 행을 제거해요. (반대로 ALL 키워드를 쓰면 기본 동작인 모든 행 유지로 지정할 수 있어요.) 두 행이 적어도 하나의 컬럼 값에서 다르면 서로 구별되는 것으로 간주되고, null 값은 이 비교에서 같다고 취급돼요.
SELECT DISTINCT select_list ...
표현식을 기준으로 중복을 판단하고 싶다면 DISTINCT ON (expression [, ...])을 쓸 수 있어요. 지정한 모든 표현식이 같은 행 집합이 중복으로 간주되고, 그 집합의 첫 행만 출력에 유지돼요.
질의 결합 (UNION, INTERSECT, EXCEPT)
두 질의의 결과는 합집합(union), 교집합(intersection), 차집합(difference) 같은 집합 연산으로 결합할 수 있어요. 문법은 이래요.
query1 UNION [ ALL ] query2
query1 INTERSECT [ ALL ] query2
query1 EXCEPT [ ALL ] query2
UNION은query2의 결과를query1의 결과에 효과적으로 덧붙여요.DISTINCT처럼 중복 행을 제거하지만,UNION ALL을 쓰면 제거하지 않아요.INTERSECT는query1과query2결과에 모두 있는 행을 반환해요.INTERSECT ALL을 쓰지 않으면 중복이 제거돼요.EXCEPT는query1결과에는 있지만query2결과에는 없는 행을 반환해요. 가끔 두 질의의 *차이(difference)*라고도 불러요. 역시EXCEPT ALL을 쓰지 않으면 중복이 제거돼요.
두 질의의 합집합·교집합·차집합을 계산하려면 두 질의가 "union compatible"이어야 해요. 즉 같은 수의 컬럼을 반환하고, 대응하는 컬럼의 데이터 타입이 호환되어야 하죠.
집합 연산은 결합할 수 있어요. 예를 들어:
query1 UNION query2 EXCEPT query3
이것은 다음과 동등해요.
( query1 UNION query2 ) EXCEPT query3
괄호로 평가 순서를 제어할 수 있어요. 괄호가 없으면 UNION과 EXCEPT는 왼쪽에서 오른쪽으로 결합하지만, INTERSECT는 그 두 연산자보다 더 강하게(더 tightly) 결합해요. 그래서:
query1 UNION query2 INTERSECT query3
는 다음을 뜻해요.
query1 UNION ( query2 INTERSECT query3 )
행 정렬 (ORDER BY)
ORDER BY 절이 정렬 순서를 지정해요.
SELECT select_list
FROM table_expression
ORDER BY sort_expression1 [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
[ , sort_expression2 [ ASC | DESC ] [ NULLS { FIRST | LAST } ] ... ]
정렬 표현식은 질의의 선택 목록에서 유효한 어떤 표현식이든 될 수 있어요. 예를 들어:
SELECT a, b FROM table1 ORDER BY a + b, c;
정렬 방향은 ASC(오름차순, 기본) 또는 DESC(내림차순)로 지정해요. null 값은 기본적으로 오름차순에서는 마지막에, 내림차순에서는 처음에 와요. NULLS FIRST/NULLS LAST로 명시적으로 정할 수 있어요.
ORDER BY는 UNION, INTERSECT, EXCEPT의 결과에도 적용할 수 있는데, 이 경우에는 표현식이 아니라 출력 컬럼 이름이나 번호로만 정렬할 수 있어요.
LIMIT와 OFFSET
LIMIT와 OFFSET은 질의의 나머지 부분이 만들어내는 행 중 일부만 가져올 수 있게 해줘요.
SELECT select_list
FROM table_expression
[ ORDER BY ... ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
- limit count가 주어지면 그보다 많은 행은 반환되지 않아요(질의 자체가 더 적은 행을 만들면 더 적게 반환될 수도 있어요).
LIMIT ALL은LIMIT절을 생략한 것과 같고,LIMIT에 NULL 인자를 줘도 마찬가지예요.
LIMIT를 쓸 때는 결과 행을 고유한 순서로 제약하는 ORDER BY를 쓰는 게 중요해요. 그렇지 않으면 질의 행의 예측 불가능한 부분집합을 얻게 돼요. "열 번째에서 스무 번째 행"을 요구한다고 해도, 열 번째에서 스무 번째가 어떤 순서 기준에서인지가 불분명하거든요.
질의 최적화기는 질의 계획을 만들 때 LIMIT를 고려하므로, LIMIT/OFFSET 값에 따라 다른 계획(따라서 다른 행 순서)을 얻을 가능성이 높아요. 그래서 ORDER BY로 예측 가능한 결과 순서를 강제하지 않으면, 다른 LIMIT/OFFSET 값으로 다른 부분집합을 선택하는 것은 일관되지 않은 결과를 낳을 수 있어요. SQL은 ORDER BY로 순서를 제약하지 않는 한 질의 결과를 특정 순서로 돌려주겠다고 약속하지 않기 때문이에요. 이것은 버그가 아니라 그런 언어적 성질의 자연스러운 결과예요.
또 하나 기억할 점은, OFFSET 절이 건너뛴 행들도 서버 안에서 계산되어야 한다는 거예요. 그래서 큰 OFFSET은 비효율적일 수 있어요.
VALUES 목록 (VALUES Lists)
VALUES는 실제로 디스크에 테이블을 만들고 채우지 않고도 질의에서 쓸 수 있는 **"상수 테이블"**을 생성하는 방법이에요. 문법은 이래요.
VALUES ( expression [, ...] ) [, ...]
각 괄호로 묶인 표현식 목록이 테이블의 한 행을 생성해요. 목록들은 모두 같은 수의 요소(즉 테이블의 컬럼 수)를 가져야 하고, 각 목록의 대응 항목은 호환 가능한 데이터 타입이어야 해요. 결과의 각 컬럼에 할당되는 실제 데이터 타입은 UNION과 같은 규칙으로 결정돼요.
기본적으로 PostgreSQL은 VALUES 테이블의 컬럼 이름을 column1, column2 등으로 지정해요. 컬럼 이름은 SQL 표준이 정하지 않아서 시스템마다 다르게 처리하므로, 테이블 별칭 목록으로 기본 이름을 덮어쓰는 게 보통 더 좋아요.
SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
num | letter
-----+--------
1 | one
2 | two
3 | three
(3 rows)
문법적으로 VALUES 다음에 표현식 목록이 오는 형태는 SELECT select_list FROM table_expression과 동등하게 취급되고, SELECT가 올 수 있는 어디든 나타날 수 있어요. 예를 들어 UNION의 일부로 쓰거나 ORDER BY, LIMIT, OFFSET 같은 정렬 지정을 붙일 수 있죠. VALUES는 가장 흔히 INSERT 명령의 데이터 소스로, 그다음으로는 서브쿼리로 쓰여요.
WITH 질의 (Common Table Expressions)
WITH는 더 큰 질의에서 사용할 **보조 문장(auxiliary statement)**을 작성하는 방법을 제공해요. 흔히 Common Table Expression(줄여서 CTE)이라고 부르는 이 문장들은, 한 질의를 위해서만 존재하는 임시 테이블을 정의한다고 생각할 수 있어요.
WITH 절의 각 보조 문장은 SELECT, INSERT, UPDATE, DELETE, 또는 MERGE일 수 있고, WITH 절 자체는 역시 SELECT, INSERT, UPDATE, DELETE, MERGE가 될 수 있는 주 문장에 붙어요.
WITH 안의 SELECT
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
...
이렇게 하면 복잡한 질의를 더 읽기 쉽게 나누고, 같은 부분 결과를 반복해 재사용할 수 있어요.
재귀 질의 (Recursive Queries)
WITH RECURSIVE는 CTE가 자기 자신을 참조할 수 있게 해줘요. 계층 구조(조직도, 카테고리 트리)를 다룰 때 유용하죠. 재귀 질의는 비재귀 항(non-recursive term)과 재귀 항(recursive term)의 UNION(또는 UNION ALL)으로 구성돼요.
구체적인 반복 과정을 보면, 작업 테이블(working table)의 현재 내용으로 재귀적 자기 참조를 치환해 재귀 항을 평가해요. UNION(그리고 UNION ALL이 아닌 경우)에서는 중복 행과 이전 결과 행을 중복하는 행을 버려요. 남은 모든 행을 재귀 질의 결과에 포함하고, 임시 중간 테이블(intermediate table)에도 넣죠. 그다음 작업 테이블의 내용을 중간 테이블의 내용으로 바꾸고 중간 테이블을 비워요. 이 과정을 작업 테이블이 빌 때까지 반복해요.
CTE 머티리얼라이제이션 (CTE Materialization)
WITH 쿼리가 실제로 실행되는 방식은 상황에 따라 달라져요. 어떤 경우에는 결과를 머티리얼라이즈(임시 저장)하고, 어떤 경우에는 인라인(내포)돼요. WITH 질의를 여러 번 참조하면 중복 계산 위험이 있지만, 각 사용이 WITH 질의 전체 출력의 작은 일부만 필요로 한다면 순이익이 될 수 있어요. 특정 형식의 CTE는 항상 머티리얼라이즈되고, 다른 형식은 인라인될 수 있어요 (확인 필요).
WITH 안의 데이터 수정 문장
WITH 안에서는 데이터를 수정하는 문장(INSERT, UPDATE, DELETE)도 쓸 수 있어요. RETURNING과 함께 쓰면 특히 강력해요. 예를 들어:
WITH moved_rows AS (
DELETE FROM products
WHERE
"date" >= '2010-10-01' AND
"date" < '2010-11-01'
RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;
이 질의는 products에서 products_log로 행을 효과적으로 이동시켜요. WITH 안의 DELETE가 products에서 지정된 행을 삭제하고 RETURNING 절로 그 내용을 반환하면, 주 질의가 그 출력을 읽어 products_log에 삽입하는 구조죠.
주의할 점이 있어요. 데이터 수정 문장이 포함된 WITH에서 데이터 수정은 정확히 한 번 수행되고 주 질의에 완전히 반영되지만, 데이터 수정의 부수 효과나 그 문장이 읽는 테이블의 내용이 주 질의의 다른 부분에서 보이는 시점은 보장되지 않아요. 예를 들어:
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM products;
여기서 바깥 SELECT는 UPDATE의 동작 이전의 원래 가격을 반환할 수 있어요. 즉 주 질의가 데이터 수정과 같은 시점의 상태를 보지 못할 수 있다는 점을 알아두세요.