Choose a default theme

You can change your theme from the menu

WikiKO
DiscussionHistory

SQL

SQL은 관계형 데이터베이스의 구조와 데이터를 조회·변경하는 언어다. 이 문서는 고객과 주문 예제를 통해 조회·조인·집계·트랜잭션의 결과를 추적하고, NULL·중복·동시성·실행 계획에서 자주 생기는 오류와 해결 방법을 설명한다.

데이터베이스

관계형

MySQL · PostgreSQL · MariaDB · SQLite

NoSQL

Redis · MongoDB · Apache Cassandra

스토리지 엔진

InnoDB · RocksDB · MyRocks

개요: SQL은 원하는 결과를 기술하는 언어

SQL은 관계형 데이터베이스에서 자료 구조를 정의하고 데이터를 조회·변경하며 접근과 트랜잭션을 다루는 언어다. 테이블에서 필요한 행과 열을 고르고, 여러 테이블의 관계를 연결하고, 그룹별 합계나 순위를 계산할 수 있다. 일반적인 절차형 프로그램이 반복 순서와 자료 접근 방법을 직접 작성하는 데 비해 SQL 질의는 어떤 결과가 필요한지를 선언하는 성격이 강하다.

예를 들어 “결제 완료 주문의 고객별 총액을 구하라”는 요구를 SQL로 표현할 때 파일의 첫 바이트부터 읽는 루프를 작성하지 않는다. 필요한 테이블, 연결 조건, 필터, 집계와 출력 순서를 지정한다. 어떤 인덱스를 사용할지, 어떤 순서로 테이블을 연결할지는 데이터베이스의 계획기가 결정할 수 있다. 같은 SQL이라도 데이터량과 통계, 인덱스에 따라 실제 수행 방법은 달라진다.

SQL을 잘 작성하려면 문법뿐 아니라 결과 한 행이 무엇을 의미하는지 알아야 한다. 주문 한 행인지, 고객 한 행인지, 고객과 상품 조합 한 행인지가 불명확하면 조인 뒤 중복을 잘못 없애거나 매출을 여러 번 합산하기 쉽다. 이 문서는 작은 고객·주문 자료를 일관되게 사용하여 질의 결과가 왜 그렇게 나오는지 설명한다.

SQL과 DBMS, 제품별 방언의 구분

SQL은 언어이고 PostgreSQL·MySQL·SQLite 등은 이를 구현하여 데이터를 관리하는 시스템이다. SQL 표준이 있어도 제품마다 자료형, 함수 이름, 자동 번호 생성, 날짜 연산, 문자열 처리, 오류와 트랜잭션 동작이 다를 수 있다. 한 제품에서 실행된다는 이유만으로 다른 제품에서도 같은 결과가 나온다고 보아서는 안 된다.

이 문서의 실행 예제는 SQLite 3에서 검증했다. 창 함수 예제에는 해당 기능을 지원하는 버전이 필요하다. SQL 개념의 설명에는 PostgreSQL 공식 문서도 참고하지만, PostgreSQL이나 MySQL에서 모든 예제를 직접 실행해 검증했다는 뜻은 아니다. 특히 PRAGMA는 SQLite 전용 설정이고 DB 드라이버의 매개변수 표기도 제품과 드라이버에 따라 달라진다.

제품을 바꿀 때는 문법 오류가 나는 부분만 고치는 것으로 끝내지 않는다. NULL 정렬, 문자열 대소문자 비교, 정수와 소수 계산, 시간대, 제약 조건과 격리 수준처럼 결과와 운영 동작을 바꾸는 차이도 검증해야 한다. 공통 SQL을 사용하면 이식 비용을 줄일 수 있지만, 중요한 제품 기능을 모두 피하는 것이 항상 좋은 설계인 것은 아니다. 필요한 기능과 이식성 요구를 함께 정한다.

실습 자료: 고객과 주문의 관계 만들기

실습은 새 임시 데이터베이스에서 시작한다. 아래 코드는 customers와 orders 두 테이블을 만들고 고객 3명과 주문 4건을 넣는다. 한 고객은 여러 주문을 가질 수 있지만 각 주문은 하나의 고객을 가리킨다. customer_id와 order_id는 식별자이고 name은 표시 이름이다. 이름이 같을 수 있는 실제 서비스에서 이름을 고객 식별자로 가정하지 않는다.

Mina는 결제 완료 3000과 대기 2000 주문을, Jun은 결제 완료 5000과 취소 1000 주문을 가지고 있다. Hana는 주문이 없고 city도 알려져 있지 않아 NULL이다. 의도적으로 주문이 없는 고객과 결제되지 않은 주문을 넣었으므로 뒤의 조인·집계 예제에서 누락과 과대 합산을 확인할 수 있다. 금액은 설명을 위해 정수 단위로 저장하며 세금·환율·반올림 규칙은 포함하지 않는다.

NOT NULL은 필수 값, CHECK는 허용 상태와 비음수 금액, REFERENCES는 고객 연결의 유효성을 표현한다. SQLite는 연결에서 외래 키 검사를 활성화해야 하므로 PRAGMA를 트랜잭션 시작 전에 실행한다. 아래 초기화는 한 번만 실행하고, 뒤의 SELECT 예제들은 같은 연결에서 실행한다. 변경 예제는 ROLLBACK으로 되돌리도록 작성했다. 이미 같은 이름의 업무 테이블이 있는 데이터베이스에 실행하지 않는다.

예제 코드

PRAGMA foreign_keys = ON;
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT
);
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    status TEXT NOT NULL CHECK (status IN ('paid', 'pending', 'cancelled')),
    amount INTEGER NOT NULL CHECK (amount >= 0)
);
INSERT INTO customers VALUES (1, 'Mina', 'Seoul'), (2, 'Jun', 'Busan'), (3, 'Hana', NULL);
INSERT INTO orders VALUES
    (101, 1, 'paid', 3000),
    (102, 1, 'pending', 2000),
    (103, 2, 'paid', 5000),
    (104, 2, 'cancelled', 1000);

SELECT: 필요한 열과 행 고르기

SELECT 목록은 결과에 포함할 열이나 계산식을 지정하고 FROM은 자료의 출처를 지정한다. WHERE는 행의 조건이다. 아래 질의는 orders 중 status가 paid인 행에서 주문 번호와 금액만 선택한다. 결과는 101·3000과 103·5000 두 행이다. 대기와 취소 주문이 존재해도 조건을 만족하지 않으므로 포함되지 않는다.

SELECT *는 실험할 때 편리하지만 애플리케이션에서 필요한 열을 명시하면 전송량과 코드의 의도를 파악하기 쉽다. 테이블에 새로운 민감한 열이 생겼을 때 무심코 반환하는 위험도 줄일 수 있다. 열을 명시했다고 권한 검사가 해결되는 것은 아니며, 접근 가능한 사용자와 행의 범위는 별도 정책이다.

SQL은 조건에 맞는 행들의 결과를 반환한다. WHERE를 생략하면 모든 행이 대상이고, 조건을 만족하는 행이 없으면 일반적인 조회 결과는 빈 결과다. 이 경우를 SQL 실행 오류와 혼동하지 않는다. 애플리케이션은 “조회 성공이지만 데이터 없음”과 “DB 접속 실패”를 다르게 처리해야 한다.

예제 코드

SELECT order_id, amount
FROM orders
WHERE status = 'paid'
ORDER BY order_id;

결과 행(열 구분은 |, NULL은 값 없음)

101 | 3000
103 | 5000

ORDER BY: 입력 순서와 출력 순서는 다르다

테이블에 넣은 순서나 기본 키 순서로 결과가 항상 나온다고 가정하지 않는다. 결과 순서를 요구하려면 ORDER BY를 지정한다. 아래는 금액을 내림차순으로, 금액이 같으면 주문 번호를 오름차순으로 정렬한다. 결과는 103, 101, 102, 104 순서다. 두 번째 기준은 동점 행의 순서를 안정적으로 정하는 역할을 한다.

표시 순서가 중요한 보고서나 페이지네이션에서는 완전한 정렬 기준이 특히 중요하다. 금액만 정렬하고 같은 금액의 행 순서를 정하지 않으면 페이지 경계에서 같은 결과가 다른 위치에 나타날 수 있다. 문자열은 정렬 규칙(collation)의 영향을 받으므로 대소문자와 언어별 비교 방식도 확인한다. NULL의 기본 위치도 제품에 따라 다를 수 있다.

ORDER BY가 있다고 항상 별도의 큰 정렬이 발생하는 것은 아니다. 적합한 인덱스의 순서를 사용할 수도 있다. 반대로 인덱스가 있다고 정렬 비용이 무조건 사라지지도 않는다. 필요한 출력 순서를 먼저 정확하게 작성하고 계획에서 그 순서를 어떻게 만드는지 확인한다.

예제 코드

SELECT order_id, amount
FROM orders
ORDER BY amount DESC, order_id ASC;

결과 행(열 구분은 |, NULL은 값 없음)

103 | 5000
101 | 3000
102 | 2000
104 | 1000

조건식: 괄호로 의도를 드러내기

AND와 OR를 섞으면 연산자 우선순위 때문에 사람이 의도한 조건과 다른 질의가 될 수 있다. “결제 완료 또는 대기 상태이면서 금액이 2500 이상”을 표현하려면 상태 조건을 괄호로 묶는 것이 명확하다. 아래 질의는 101과 103만 돌려준다. 대기 주문 102는 금액이 2000이므로 제외된다.

조건을 작성할 때 각 조건이 참인 예와 거짓인 예를 표로 생각해 보면 오류를 줄일 수 있다. 복잡한 업무 조건을 한 줄에 압축하기보다 줄바꿈과 괄호로 의미 단위를 표현한다. BETWEEN은 일반적으로 양쪽 경계를 포함하므로 날짜·시간 범위에서 끝 시점까지 포함할지 주의해야 한다. 하루를 고를 때 다음 날 0시 미만처럼 반열린 구간을 쓰는 설계가 더 명확한 경우가 많다.

사용자 입력 문자열의 부분 검색에는 LIKE 등이 쓰이지만 %, _ 같은 패턴 문자의 의미와 이스케이프 규칙을 확인해야 한다. 매개변수 바인딩은 입력이 SQL 문법으로 끼어드는 것을 막는 데 쓰이고, 입력 안의 와일드카드를 문자 그대로 검색할지 결정하는 일은 별도 문제다.

예제 코드

SELECT order_id
FROM orders
WHERE (status = 'paid' OR status = 'pending') AND amount >= 2500
ORDER BY order_id;

결과 행(열 구분은 |, NULL은 값 없음)

101
103

NULL: 모르는 값은 0이나 빈 문자열과 다르다

NULL은 값이 없거나 알 수 없음을 표현하는 표식이다. 0, 빈 문자열, 거짓과 동일하지 않다. 일반적인 비교에서 city = NULL을 사용하면 예상처럼 NULL인 행을 찾지 못한다. NULL 여부는 IS NULL 또는 IS NOT NULL로 검사한다. 아래 질의는 city가 알려지지 않은 Hana를 반환한다.

NULL이 관여한 비교는 참이나 거짓으로 단순화되지 않는 경우가 있고, WHERE는 참으로 판정되는 행을 선택한다. 따라서 city <> 'Seoul'이라는 조건은 city가 NULL인 고객을 자동으로 포함하지 않는다. “서울이 아니거나 도시를 모르는 고객”이라는 요구라면 OR city IS NULL 같은 조건을 명시해야 한다.

COALESCE(city, 'unknown')은 표시용 대체값을 만들 수 있지만 원본의 불명 상태를 없애는 것은 아니다. 집계 전에 NULL을 0으로 바꾸면 “관측되지 않음”을 “측정값 0”으로 바꿔 평균의 의미를 달리할 수 있다. 저장할 때 NULL을 허용할지와 조회 결과에서 어떻게 설명할지를 업무 의미에 맞게 결정한다.

예제 코드

SELECT name
FROM customers
WHERE city IS NULL
ORDER BY customer_id;

결과 행(열 구분은 |, NULL은 값 없음)

Hana

INNER JOIN: 행이 연결되는 단위를 이해하기

JOIN은 관계를 만족하는 행들을 연결한다. 아래 질의는 각 주문의 customer_id와 고객의 customer_id를 비교하고 결제 완료 주문만 고른다. Mina·101·3000, Jun·103·5000이 결과다. c와 o는 별칭으로, 어느 테이블의 열인지 짧고 명확하게 표시한다.

고객 한 명에 주문 여러 건이 있으면 조인 결과에도 그 고객이 여러 번 나타날 수 있다. 이것은 데이터가 잘못 복제되었다는 뜻이 아니라 결과 한 행의 단위가 고객이 아닌 고객과 주문의 조합이기 때문이다. 중복처럼 보인다고 DISTINCT를 바로 붙이면 실제로 다른 주문을 지울 수 있다. 먼저 결과 한 행이 무엇을 나타내야 하는지 정한다.

연결 조건을 빠뜨리면 모든 고객과 모든 주문이 조합되어 행 수가 늘어난다. 고객 3명과 주문 4건이라면 가능한 조합은 12개다. 큰 테이블에서는 이런 실수가 심각한 비용을 만들 수 있다. 조인할 키의 유일성, 외래 키 관계, 필터 전후 행 수를 확인하는 습관이 중요하다.

예제 코드

SELECT c.name, o.order_id, o.amount
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
ORDER BY o.order_id;

결과 행(열 구분은 |, NULL은 값 없음)

Mina | 101 | 3000
Jun | 103 | 5000

LEFT JOIN: 없는 주문도 결과에 남기기

LEFT JOIN은 왼쪽 행을 유지하고 오른쪽에 맞는 행이 없으면 오른쪽 열을 NULL로 채워 반환한다. 주문하지 않은 고객도 보고서에 표시하려면 이 의미가 필요하다. 아래는 고객을 기준으로 결제 완료 주문을 연결하므로 Mina와 Jun의 주문 외에 Hana·NULL 행도 나온다.

중요한 부분은 paid 조건이 ON 안에 있다는 것이다. 먼저 결제 완료 주문에 한해 연결을 시도하고, 없으면 고객을 남긴다. 이 조건을 WHERE o.status = 'paid'로 옮기면 Hana의 NULL로 채워진 행은 조건을 만족하지 않아 사라진다. 같은 조건문을 다른 절로 옮기는 것이 결과를 바꾸는 대표적인 사례다.

보고서 요구가 “전체 고객의 결제 현황”인지 “결제한 고객 목록”인지에 따라 두 질의 모두 목적에 맞을 수 있다. 조인 종류를 암기하는 대신 기준 집합이 무엇이며 연결 실패 행을 남길지 결정한다. 후속 집계에서 없는 주문을 셀 때도 COUNT(*)와 COUNT(o.order_id)의 차이를 확인해야 한다.

예제 코드

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.status = 'paid'
ORDER BY c.customer_id, o.order_id;

결과 행(열 구분은 |, NULL은 값 없음)

Mina | 101
Jun | 103
Hana | NULL

GROUP BY와 집계: 여러 행을 하나의 의미로 묶기

GROUP BY는 같은 키를 가진 행을 묶고 집계 함수로 그룹의 값을 계산한다. 아래는 주문을 고객 ID별로 묶어 주문 수와 금액 합계를 계산한다. Mina의 ID 1은 주문 2건·5000, Jun의 ID 2는 2건·6000이다. 이 질의는 상태를 걸러내지 않았으므로 대기와 취소 금액도 포함한다. 따라서 결과를 곧바로 “결제 매출”이라고 부르면 의미가 틀린다.

집계 전에 어떤 행을 포함할지 결정하는 것이 집계 함수 선택만큼 중요하다. 합계 열의 이름을 total_amount라고 썼다고 실제 의미가 매출로 보장되지는 않는다. 원자료가 주문 시점 금액인지 결제 확정 금액인지, 환불과 취소를 어떻게 반영하는지까지 보고서의 정의를 정해야 한다.

집계와 함께 출력하는 열은 그룹의 기준이거나 적절한 집계 결과여야 한다. customer_id로 묶고 임의의 order_id를 함께 출력하면 어떤 주문 번호인지 의미가 불명확하다. 일부 DBMS의 허용 문법에 기대어 우연히 나온 값을 사용하지 말고, 최소 주문 번호가 필요하면 MIN, 최신 주문의 전체 행이 필요하면 별도의 순위 질의를 사용한다.

예제 코드

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

결과 행(열 구분은 |, NULL은 값 없음)

1 | 2 | 5000
2 | 2 | 6000

WHERE와 HAVING: 행 조건과 그룹 조건

WHERE는 집계에 들어갈 행을 고르는 데, HAVING은 집계된 그룹의 조건을 고르는 데 사용한다. 아래 질의는 먼저 결제 완료 주문만 선택하고 고객별 합계를 만든 뒤 합계 4000 이상인 그룹만 반환한다. Jun의 고객 ID 2와 5000만 남는다. Mina의 결제 완료 합계는 3000이다.

WHERE amount >= 4000과 HAVING SUM(amount) >= 4000은 다르다. 한 고객이 2500짜리 결제 두 건을 했다고 가정하면 각 행은 4000 미만이지만 합계는 5000이다. 행 조건으로 걸러버리면 이 고객의 실적을 잃는다. 보고서에서 자주 생기는 오류는 조건이 개별 기록에 적용되는지 전체 합계에 적용되는지 혼동하는 데서 나온다.

집계 별칭을 HAVING이나 다른 절에서 사용할 수 있는 범위는 DBMS마다 다를 수 있다. 이 예제는 집계식을 직접 적어 의미를 드러낸다. 복잡한 식이 여러 번 반복되면 공통 테이블 식이나 하위 질의로 중간 결과에 이름을 붙이는 방법을 검토할 수 있다.

예제 코드

SELECT customer_id, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) >= 4000
ORDER BY customer_id;

결과 행(열 구분은 |, NULL은 값 없음)

2 | 5000

COUNT와 빈 그룹: 0을 만드는 방법

COUNT(*)는 결과 행을 세고 COUNT(column)은 해당 열이 NULL이 아닌 행을 센다. 왼쪽 조인에서 연결된 주문이 없는 고객도 NULL로 채워진 결과 행 하나를 가지므로 COUNT(*)를 쓰면 주문 수가 1처럼 보일 수 있다. 아래는 o.order_id를 세어 실제 연결된 주문 수를 계산한다. 결과는 Mina 2, Jun 2, Hana 0이다.

SUM은 입력이 없는 경우 NULL을 돌려줄 수 있다. 전체 고객의 결제 합계를 표시할 때 COALESCE(SUM(o.amount), 0)을 사용하면 결제 없는 고객을 0으로 표시할 수 있다. 이때 0이 업무적으로 “결제가 없음”을 나타내는 데 적절한지 확인한다. 측정 실패와 구별해야 하는 자료에서는 무조건 대체하지 않는다.

빈 데이터베이스, 주문 없는 고객, NULL 금액을 가진 자료는 서로 다른 상황이다. 예제의 amount는 NOT NULL이므로 세 번째 경우가 저장 단계에서 금지된다. 어떤 제약을 걸었는지 알고 있으면 조회에서 처리해야 할 경우의 수를 줄일 수 있다. 스키마와 질의를 따로 보지 않는 것이 중요한 이유다.

예제 코드

SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

결과 행(열 구분은 |, NULL은 값 없음)

Mina | 2
Jun | 2
Hana | 0

EXISTS와 하위 질의: 관련 행의 존재를 묻기

하위 질의는 다른 질의 안에서 값을 계산하거나 행의 존재를 검사한다. 아래 NOT EXISTS는 각 고객에 대해 연결된 주문이 하나라도 있는지 확인하고, 없는 고객만 반환한다. 결과는 Hana다. SELECT 1의 1은 주문 데이터에서 실제로 필요한 열이 있다는 뜻이 아니라 존재 여부만 관심 있다는 관용적인 표현이다.

주문이 여러 건 있어도 고객을 한 번만 출력하고 싶을 때 EXISTS는 의도를 잘 드러낸다. JOIN으로 여러 행을 만든 다음 DISTINCT로 고객을 다시 줄이는 것과 달리 처음부터 “존재하는가”라는 질문을 표현한다. 실제 수행 방식은 계획기가 바꿀 수 있으므로 겉으로 하위 질의가 보인다는 이유만으로 매 고객마다 전체 주문을 반드시 반복 스캔한다고 단정하지 않는다.

NOT IN은 하위 결과에 NULL이 포함될 때 예상과 다르게 참이 되지 않는 경우가 생길 수 있다. 값의 부재와 비교 결과가 결합하기 때문이다. 불일치나 존재하지 않음을 찾는 질의는 NULL 가능성을 확인하고 NOT EXISTS처럼 원하는 관계를 명시하는 방식을 비교한다. “행이 없다”와 “열 값이 NULL이다”는 다른 조건이다.

예제 코드

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id)
ORDER BY c.customer_id;

결과 행(열 구분은 |, NULL은 값 없음)

Hana

CASE: 조건에 따라 값을 계산하기

CASE는 조건에 따라 결과 값을 선택하는 표현식이다. 아래는 결제 완료 금액과 대기 금액을 서로 다른 열로 집계한다. paid_total은 8000, pending_total은 2000이다. 취소 주문은 각 CASE의 ELSE 0으로 처리되어 두 합계에 들어가지 않는다. 같은 원자료를 서로 다른 조건으로 계산할 때 쓸 수 있는 조건부 집계다.

각 분기의 결과 타입이 어떻게 결합되는지도 고려해야 한다. 어떤 분기는 숫자, 다른 분기는 설명 문자열을 반환하면 DBMS의 타입 변환 규칙에 따라 오류나 예상치 못한 변환이 생길 수 있다. 금액 열은 숫자로 유지하고 표시 문자열은 별도 열이나 화면 계층에서 처리하는 편이 명확한 경우가 많다.

CASE를 통해 업무 규칙을 SQL에 넣으면 보고서에서 일관되게 적용할 수 있지만 규칙이 여러 질의에 복사되면 변경 시 불일치가 생길 수 있다. 뷰나 공통 처리 계층으로 관리할지 검토한다. 분기가 많아질 때는 각 상태의 의미와 빠진 상태의 처리 정책을 먼저 정한다. ELSE 0이 잘못된 상태를 조용히 숨기지 않는지도 확인한다.

예제 코드

SELECT SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_total,
       SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_total
FROM orders;

결과 행(열 구분은 |, NULL은 값 없음)

8000 | 2000

공통 테이블 식: 중간 계산에 이름 붙이기

WITH로 정의하는 공통 테이블 식(CTE)은 질의 안에서 사용할 중간 결과에 이름을 붙인다. 아래 paid_totals는 결제 완료 주문을 고객별로 집계하고, 바깥 질의가 고객과 연결하여 이름과 합계를 출력한다. 결과는 Mina 3000, Jun 5000이다. 계산을 단계별로 읽을 수 있어 복잡한 보고서를 설명하기 쉽다.

CTE를 썼다고 반드시 임시 테이블이 디스크에 저장되는 것은 아니다. 계획기가 본문에 합치거나 별도로 계산하는 방식은 제품과 버전, 질의 구조, 힌트에 따라 달라질 수 있다. CTE는 우선 가독성과 의미 분리를 위한 도구로 이해하고 실제 실행 비용은 계획에서 확인한다.

재귀 CTE는 계층이나 그래프 탐색에도 사용될 수 있지만 종료 조건과 순환 자료, 결과 크기 제한을 주의해야 한다. 이 문서의 예제는 일반 CTE이며 재귀 기능을 사용하지 않는다. 작은 예제에서 CTE를 여러 겹 추가하는 것보다 계산 단계의 이름이 독자의 이해를 돕는지 판단한다.

예제 코드

WITH paid_totals AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
)
SELECT c.name, p.total
FROM paid_totals AS p
JOIN customers AS c ON c.customer_id = p.customer_id
ORDER BY c.customer_id;

결과 행(열 구분은 |, NULL은 값 없음)

Mina | 3000
Jun | 5000

창 함수: 원래 행을 남기면서 순위 계산하기

GROUP BY는 여러 입력 행을 그룹별 결과로 줄이는 반면 창 함수는 각 행을 유지하면서 관련 행들의 계산 결과를 붙일 수 있다. 아래 ROW_NUMBER는 고객별로 주문을 나누고 금액 내림차순으로 번호를 매긴다. Mina의 3000 주문은 1, 2000 주문은 2이고 Jun의 5000 주문은 1, 1000 주문은 2다.

PARTITION BY는 계산을 나눌 그룹이고 OVER 안의 ORDER BY는 창 함수의 계산 순서다. 질의 맨 끝의 ORDER BY는 최종 출력 순서를 정한다. 두 위치의 역할이 다르므로 창 함수 안에서 정렬했다고 전체 결과가 원하는 순서로 출력된다고 가정하지 않는다. 금액이 같은 경우를 위해 주문 번호도 순위 기준에 넣었다.

ROW_NUMBER는 동점에도 서로 다른 번호를 준다. RANK나 DENSE_RANK는 동점 처리와 다음 번호의 규칙이 다르므로 “상위 세 명”이 정확히 세 행인지, 동점자를 모두 포함하는지에 따라 선택이 달라진다. 누적 SUM 같은 창 집계에서는 프레임 범위도 중요하다. ORDER BY만 쓰고 기본 프레임의 의미를 모르면 동점 행을 묶어 처리하는 결과가 기대와 다를 수 있다.

예제 코드

SELECT customer_id, order_id, amount,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_id) AS position
FROM orders
ORDER BY customer_id, position;

결과 행(열 구분은 |, NULL은 값 없음)

1 | 101 | 3000 | 1
1 | 102 | 2000 | 2
2 | 103 | 5000 | 1
2 | 104 | 1000 | 2

UNION과 중복: 집합처럼 합칠지 모두 남길지

UNION은 두 결과를 결합하며 기본적으로 중복 행을 제거하고 UNION ALL은 중복을 남긴다. 아래 예제는 같은 이름을 두 번 선택해 UNION ALL로 합치므로 Mina가 두 행 나온다. UNION으로 바꾸면 한 행이다. 이 차이는 고객 목록의 합집합에는 유용할 수 있지만 거래 기록을 합칠 때는 같은 금액의 다른 거래를 잃게 만들 수 있다.

중복 제거는 선택된 모든 열의 값이 같은 결과 행을 대상으로 한다. 서로 다른 주문이라도 이름과 금액 두 열만 선택했다면 동일한 결과처럼 보일 수 있다. 데이터를 구별할 식별자를 결과에 포함할지, 실제로 중복 제거가 필요한지 먼저 정한다. 두 SELECT의 열 개수와 대응 타입도 호환되어야 한다.

UNION ALL이 중복 제거 작업을 하지 않는다는 점은 성능에 영향을 줄 수 있지만, 성능을 이유로 요구한 중복 제거를 생략해서는 안 된다. 의미가 맞는 연산을 선택한 뒤 실행 비용을 검토한다. 합친 결과의 순서가 필요하면 최종 결과에 ORDER BY를 적용한다.

예제 코드

SELECT name FROM customers WHERE customer_id = 1
UNION ALL
SELECT name FROM customers WHERE customer_id = 1;

결과 행(열 구분은 |, NULL은 값 없음)

Mina
Mina

변경 질의: UPDATE의 대상 범위를 확인하기

INSERT는 행을 추가하고 UPDATE는 선택한 행의 값을 바꾸며 DELETE는 행을 제거한다. 조회보다 위험도가 높은 이유는 조건 실수가 저장된 상태를 직접 바꾸기 때문이다. UPDATE orders SET amount = 0처럼 WHERE가 없으면 모든 행이 대상이다. 운영 변경에서는 동일 조건의 SELECT로 대상과 건수를 확인하고 변경 전후 검증과 복구 계획을 갖춰야 한다.

아래는 실습용 트랜잭션에서 102번 주문만 결제 완료로 바꾼 뒤 조회하고 취소한다. 결과는 102·paid이고 ROLLBACK 뒤에는 원래 pending으로 돌아간다. 앞의 예제와 데이터가 달라지지 않도록 변경을 확정하지 않았다. 다른 환경에서는 자동 커밋 설정과 DDL의 트랜잭션 동작이 다를 수 있으므로 복구 가능한 범위를 실제 제품에서 확인한다.

어떤 드라이버가 돌려주는 영향받은 행 수가 “조건에 맞은 행”인지 “실제 값이 바뀐 행”인지도 제품별로 확인한다. 갱신 대상이 1행이어야 한다는 업무 규칙이 있으면 그 기대를 검증하고, 0행이나 여러 행일 때 처리 정책을 정한다. SQL이 오류 없이 끝났다는 사실만으로 업무 변경이 성공했다고 보고하지 않는다.

예제 코드

BEGIN;
UPDATE orders SET status = 'paid' WHERE order_id = 102;
SELECT order_id, status FROM orders WHERE order_id = 102;
ROLLBACK;
SELECT order_id, status FROM orders WHERE order_id = 102;

결과 행(열 구분은 |, NULL은 값 없음)

결과 1
102 | paid

결과 2
102 | pending

트랜잭션: 여러 변경을 하나의 작업으로 묶기

트랜잭션은 관련 변경을 한 작업으로 다루는 경계다. 주문 생성과 재고 감소를 별도 확정하면 둘 중 하나만 성공해 상태가 어긋날 수 있다. 같은 데이터베이스 트랜잭션으로 묶으면 필요한 조건을 모두 충족했을 때 COMMIT하고 실패하면 ROLLBACK하는 구조를 만들 수 있다. 단, 외부 결제 서비스나 이메일 전송 같은 다른 시스템의 작업까지 자동으로 같은 원자성에 포함되지는 않는다.

아래는 두 주문의 금액을 각각 500 조정한다. 트랜잭션 안에서는 101이 3500, 103이 4500이지만 ROLLBACK 후에는 3000과 5000으로 돌아간다. 이 예제는 원자적인 변경 묶음의 관찰용이며 실제 주문 금액을 임의로 옮기는 업무 규칙을 권장하는 예는 아니다. 같은 연결이 자신의 미확정 변경을 보는 것과 다른 연결이 보는 것은 구별한다.

트랜잭션을 오래 열어 두면 잠금, 오래된 버전 유지, 다른 작업의 대기 같은 비용이 생길 수 있다. 사용자 응답이나 외부 API를 기다리는 동안 트랜잭션을 유지할 필요가 있는지 검토한다. 실패 후 재시도가 필요할 수 있으므로 중복 실행에 안전한 식별자와 업무 설계도 중요하다. 재시도는 오류를 반복해서 덮는 것이 아니라 전체 작업의 의미를 보존하도록 설계해야 한다.

예제 코드

BEGIN;
UPDATE orders SET amount = amount + 500 WHERE order_id = 101;
UPDATE orders SET amount = amount - 500 WHERE order_id = 103;
SELECT order_id, amount FROM orders WHERE order_id IN (101, 103) ORDER BY order_id;
ROLLBACK;
SELECT order_id, amount FROM orders WHERE order_id IN (101, 103) ORDER BY order_id;

결과 행(열 구분은 |, NULL은 값 없음)

결과 1
101 | 3500
103 | 4500

결과 2
101 | 3000
103 | 5000

제약 조건: 잘못된 자료를 저장 단계에서 막기

애플리케이션이 입력을 검사해도 다른 관리 도구나 배치 프로그램이 같은 테이블을 바꿀 수 있다. 데이터베이스의 제약 조건은 이런 여러 쓰기 경로에 공통 규칙을 적용하는 데 도움이 된다. 기본 키는 행 식별과 중복 방지, 외래 키는 연결 대상의 존재, NOT NULL은 필수 값, CHECK는 행 조건을 표현한다.

초기 스키마에서 amount는 NOT NULL이면서 0 이상이어야 한다. 아래 INSERT는 금액이 -1이어서 CHECK 제약으로 실패해야 한다. 문법은 맞지만 저장할 수 없는 자료인 것이다. 실패가 예상되는 예제이므로 다른 초기화 문장과 한 덩어리로 실행하여 모두 성공할 것으로 기대하지 않는다. 제약 위반 후 트랜잭션 상태와 후속 명령 허용 여부는 사용하는 DBMS에서 확인한다.

CHECK(amount >= 0)만으로 NULL까지 거부한다고 생각하면 안 된다. NULL에 대한 검사 의미 때문에 필수 값이면 NOT NULL도 명시해야 한다. 한편 “전체 주문 합계가 예산 이하”처럼 여러 행에 걸친 조건은 단순한 행 CHECK 하나로 충분하지 않을 수 있다. 잠금이나 격리, 다른 데이터 모델을 포함한 동시성 설계가 필요하다.

실패해야 하는 예제

INSERT INTO orders VALUES (105, 1, 'paid', -1);

동시성: 읽고 쓴 사이에 다른 변경이 들어올 수 있다

한 연결에서 예제가 맞았다고 여러 요청이 동시에 실행되는 서비스에서도 같은 결과가 보장되는 것은 아니다. 두 요청이 재고 1개를 읽고 각각 구매 가능하다고 판단한 뒤 따로 감소시키면 초과 판매나 갱신 유실 같은 문제가 생길 수 있다. 트랜잭션 사용 여부와 함께 격리 수준, 잠금, 조건부 갱신을 설계해야 한다.

재고 감소를 UPDATE stock SET quantity = quantity - 1 WHERE product_id = ? AND quantity > 0처럼 조건과 갱신을 한 문장에 결합하고 영향받은 행을 확인하는 방법이 있다. 하지만 전체 구매 과정에 다른 테이블과 외부 결제가 포함되면 이 문장 하나만으로 모든 일관성이 해결되지는 않는다. 무엇을 원자적으로 유지해야 하는지 먼저 정한다.

격리 수준 이름이 같아도 제품별 구현과 허용 현상이 완전히 동일하다고 가정하지 않는다. PostgreSQL의 MVCC와 직렬화 실패, MySQL의 잠금 방식, SQLite의 한 번에 하나인 쓰기 트랜잭션 같은 조건은 각각 확인해야 한다. 메모리나 SQL 문법 오류가 없어도 업무 경쟁 조건은 남을 수 있다. 단일 연결 테스트 외에 실제 동시 실행 시나리오를 재현하는 검증이 필요하다.

인덱스: 읽기 비용과 쓰기 비용의 교환

인덱스는 특정 조건이나 순서에 맞는 자료를 찾는 보조 구조다. customer_id로 주문을 자주 찾는다면 해당 열의 인덱스를 검토할 수 있다. 인덱스가 없다면 많은 행을 검사해야 하는 질의가 더 적은 범위를 읽도록 바뀔 수 있지만, 인덱스 저장 공간과 쓰기 시 갱신 비용도 생긴다.

복합 인덱스에서는 열의 순서와 조건 형태가 중요하다. customer_id와 status를 함께 사용하는 질의, status만 사용하는 질의, amount 순서까지 필요한 질의는 같은 인덱스로 동일하게 해결된다고 보장되지 않는다. 선택도와 결과 비율, 인덱스가 포함한 열, 정렬 조건에 따라 효용이 달라진다. 모든 열 조합의 인덱스를 만드는 것은 관리 비용과 쓰기 부하를 키운다.

실습 데이터 4행에서는 성능 차이를 일반화할 수 없다. 충분한 크기와 현실적인 값 분포를 가진 자료에서 계획과 실행 시간을 비교한다. 테이블 행 대부분을 읽어야 하면 인덱스보다 전체 스캔이 합리적일 수 있으므로 “인덱스를 안 썼다”는 사실만으로 오류라고 단정하지 않는다. 목적은 특정 계획 모양을 강제하는 것이 아니라 요구한 결과를 적절한 비용으로 얻는 것이다.

실행 계획: 추정과 실제를 구별하기

SQLite의 EXPLAIN QUERY PLAN은 질의가 어떤 테이블이나 인덱스를 어떤 방식으로 접근하는지 살펴보는 도구다. 아래는 실습용 인덱스를 만든 뒤 customer_id 조건의 계획을 확인한다. 결과 문자열과 계획 형식은 SQLite 버전에 따라 달라질 수 있어 문서에 하나의 고정 출력과 성능 수치를 정답으로 제시하지 않는다.

PostgreSQL의 EXPLAIN은 계획을 보여 주고 EXPLAIN ANALYZE는 실제 실행 통계도 얻는다. ANALYZE를 붙인 변경문은 실제 변경을 수행할 수 있으므로 읽기 전용 검사와 혼동하지 않는다. 추정 행 수와 실제 행 수가 크게 다르면 통계, 값 분포, 조건의 상관관계 등을 검토할 단서가 된다. 단순히 비용 숫자가 크다는 이유만으로 실제 지연 시간을 직접 환산하지 않는다.

계획을 읽을 때는 가장 큰 자료 증가 지점, 반복 실행되는 내부 작업, 불필요한 정렬과 넓은 스캔을 찾는다. 정확하지 않은 조인으로 행 수가 폭증했다면 인덱스를 추가하기 전에 질의 의미를 고쳐야 한다. 캐시가 이미 따뜻한 한 번의 실행 결과를 실제 서비스의 모든 요청 성능으로 일반화하지 않는다.

예제 코드

CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN SELECT order_id FROM orders WHERE customer_id = 1;

페이지네이션: 정렬과 데이터 변화 고려하기

LIMIT은 반환 행 수를 제한하고 OFFSET은 앞의 행을 건너뛰는 데 쓰인다. 아래는 주문 번호 순서에서 앞의 두 행을 건너뛰어 103과 104를 반환한다. ORDER BY가 없으면 “두 번째 페이지”가 어떤 순서의 다음 페이지인지 정의되지 않는다. 동점이 있는 정렬에서는 유일한 식별자를 보조 기준으로 포함하는 것이 필요하다.

큰 OFFSET은 많은 행을 찾아 건너뛰는 비용이 들 수 있다. 마지막으로 본 정렬 키 이후를 조건으로 삼는 키셋 페이지네이션은 이런 경우 대안이 될 수 있지만 임의 페이지 이동과 복합 정렬, NULL, 방향 전환을 설계해야 한다. 예를 들어 order_id > 마지막ID 조건은 해당 정렬과 업무 요구가 맞을 때만 적절하다.

페이지를 넘기는 동안 새 행이 삽입되거나 기존 행의 정렬 값이 바뀌면 중복이나 누락이 생길 수 있다. 보고서처럼 고정된 스냅샷이 필요한지, 실시간 목록처럼 변화가 허용되는지에 따라 일관성 요구를 정한다. 단순히 LIMIT을 붙이는 것은 전송량 제한일 뿐 페이지의 안정성을 모두 보장하지 않는다.

예제 코드

SELECT order_id
FROM orders
ORDER BY order_id
LIMIT 2 OFFSET 2;

결과 행(열 구분은 |, NULL은 값 없음)

103
104

매개변수 바인딩: 값을 SQL 문장과 분리하기

애플리케이션에서 사용자 값을 문자열 연결로 SQL 문장에 끼워 넣으면 입력이 문법으로 해석될 위험이 생긴다. 매개변수 바인딩은 SQL의 구조와 데이터 값을 분리한다. 아래 Python 예제는 새 인메모리 데이터베이스에 Mina라는 이름을 저장하고, 조회 값에 물음표 플레이스홀더를 사용한다. 출력은 [(Mina,)] 형태의 튜플 목록이다.

(name,)의 마지막 쉼표는 원소 하나인 튜플을 만드는 Python 문법이다. 드라이버의 플레이스홀더는 Python sqlite3에서 ?를 사용하지만 다른 드라이버는 표기가 다를 수 있다. 값을 수동으로 따옴표 처리하거나 이스케이프하는 방식과 혼용하지 않고 해당 드라이버의 바인딩 API를 사용한다.

일반적인 값 바인딩으로 테이블 이름이나 ORDER BY의 열 이름까지 선택할 수 있는 것은 아니다. 사용자가 정렬 열을 고르는 기능이라면 허용한 열 이름 목록에서 프로그램이 선택해 질의 구조를 구성한다. 바인딩을 사용해도 접근 권한, 입력 길이 제한, 비싼 질의의 실행 제한 등은 별도 책임이다. SQL 주입 방지와 업무 인가를 하나의 문제로 합치지 않는다.

예제 코드

import sqlite3
connection = sqlite3.connect(":memory:")
try:
    connection.execute("CREATE TABLE people (name TEXT NOT NULL)")
    connection.execute("INSERT INTO people VALUES (?)", ("Mina",))
    name = "Mina"
    rows = connection.execute("SELECT name FROM people WHERE name = ?", (name,)).fetchall()
    print(rows)
finally:
    connection.close()

결과 행(열 구분은 |, NULL은 값 없음)

[('Mina',)]

실전 보고서: 주문 없는 고객까지 결제 합계 표시

앞의 개념을 합쳐 모든 고객의 결제 완료 주문 수와 합계를 구한다. 고객을 기준으로 왼쪽 조인하고 ON에서 paid만 연결한다. COUNT(o.order_id)는 연결된 결제 주문만 세며 COALESCE는 없는 합계를 표시용 0으로 바꾼다. 결과는 Mina 1건·3000, Jun 1건·5000, Hana 0건·0이다.

이 질의가 답하는 것은 “실습 데이터에 있는 모든 고객의 결제 완료 주문 현황”이다. 주문 발생 날짜, 환불, 여러 통화, 삭제된 고객 같은 실제 보고서 조건은 포함하지 않는다. 기능을 늘릴 때는 고객을 남길 필터와 주문을 연결할 필터를 구분해야 한다. 날짜 조건을 WHERE에 옮겨 주문 없는 고객을 실수로 제거하지 않는지 확인한다.

금액을 더하기 전에 조인으로 주문 행이 여러 번 복제되는지도 중요하다. 주문 상세와 결제 이력을 동시에 연결하면 각 주문의 금액이 여러 조합으로 반복될 수 있다. 필요한 단위로 먼저 집계한 결과를 연결하거나 보고서 한 행의 단위를 다시 설계한다. DISTINCT로 겉보기 중복만 없애는 것은 금액 과대 계산의 일반적인 해결책이 아니다.

예제 코드

SELECT c.customer_id, c.name, COUNT(o.order_id) AS paid_count,
       COALESCE(SUM(o.amount), 0) AS paid_total
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

결과 행(열 구분은 |, NULL은 값 없음)

1 | Mina | 1 | 3000
2 | Jun | 1 | 5000
3 | Hana | 0 | 0

검증 방법: 결과 건수와 합계의 불변 조건

질의 검증은 문법 검사에서 끝나지 않는다. 실전 보고서는 결과 고객 수가 3이고, 결제 주문 수의 합이 2이며, 결제 금액의 합이 8000이어야 한다. 원자료에서 paid만 직접 집계한 값과 보고서 합계를 비교하면 일부 조인 오류를 찾을 수 있다. 아래 질의의 결과가 2·8000이므로 위 보고서의 합계와 일치한다.

단, 전체 합계가 같다고 고객별 배분까지 맞다는 보장은 아니다. 한 고객의 금액이 다른 고객으로 잘못 옮겨가도 총합은 같을 수 있다. 고객별 결과, 주문 없는 고객, 동일 이름의 다른 고객, 여러 결제 주문을 가진 고객, 날짜 경계, NULL과 중복 키 가능성을 함께 확인한다. 스키마가 금지한 상태와 실제로 가능한 상태를 구분해 테스트 데이터를 만든다.

성능 검증에는 실제 규모의 자료와 대표 질의 조건을 사용한다. 기능 검증의 작은 예제는 결과를 손으로 추적하기 좋지만 인덱스 효율이나 동시성 부하를 대변하지 않는다. 읽기 결과가 맞는지, 변경 대상이 맞는지, 동시 요청에서도 조건이 유지되는지, 필요한 시간 안에 끝나는지를 서로 다른 검증으로 다루면 문제를 더 정확하게 찾을 수 있다.

예제 코드

SELECT COUNT(*) AS paid_count, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid';

결과 행(열 구분은 |, NULL은 값 없음)

2 | 8000

학습 순서와 흔한 실수 정리

먼저 한 테이블에서 열 선택, 행 조건, 정렬을 익히고 NULL의 의미를 확인한다. 다음으로 조인의 결과 한 행이 무엇인지 설명해 보고, GROUP BY와 집계로 행의 단위가 어떻게 바뀌는지 이해한다. 그 뒤 CTE와 창 함수로 복잡한 질의를 나누고, 트랜잭션과 제약 조건으로 변경의 정확성을 다룬다. 인덱스와 실행 계획은 올바른 질의를 적절한 비용으로 실행하기 위한 다음 단계다.

자주 생기는 실수는 ORDER BY 없이 결과 순서를 믿는 것, NULL을 =로 찾는 것, LEFT JOIN 뒤 WHERE로 없는 행을 지우는 것, COUNT(*)로 없는 주문을 세는 것, 취소 주문까지 매출에 더하는 것, 조인으로 늘어난 행을 그대로 합산하는 것, UPDATE의 조건을 빠뜨리는 것이다. 각각의 오류는 실행 자체가 성공할 수 있어 더 위험하다. 질의가 답하는 질문을 말로 설명하고 작은 반례 데이터를 만드는 연습이 중요하다.

SQL의 강점은 데이터 가까이에서 집합 단위 처리를 표현할 수 있다는 점이다. 그러나 모든 계산을 거대한 SQL 하나로 몰아넣는 것이 항상 좋은 구조는 아니다. 질의의 재사용, 변경 책임, 테스트 가능성, 데이터 전송량과 트랜잭션 경계를 함께 보고 애플리케이션과 데이터베이스의 역할을 나눈다. 문법을 많이 쓰는 것보다 정확한 의미와 검증 가능한 결과를 만드는 것이 우선이다.

참고 자료