SQL로 관계형 데이터를 정의하고 조회하는 방법

SQL이 관계형 데이터베이스에서 맡는 역할

SQL(Structured Query Language)은 관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하고 조작하는 표준화된 프로그래밍 언어다. 1970년대 IBM의 레이몬드 보이스(Raymond Boyce)와 도널드 챔벌린(Donald Chamberlin)이 개발한 뒤 데이터베이스 환경의 핵심 도구로 자리 잡았다.

이 언어는 사용자가 원하는 결과를 선언하고, 그 결과를 얻는 처리 방식은 DBMS가 결정한다. 따라서 데이터 추출 과정을 절차적으로 모두 기술하지 않아도 된다. ANSI(American National Standards Institute)와 ISO 표준을 기반으로 해 여러 DBMS 사이의 호환성을 확보하며, 영어 문장에 가까운 구문은 비전문가도 접근하기 쉬운 편이다.

데이터 구조부터 트랜잭션까지 다루는 명령어

SQL 명령어는 데이터베이스 구조, 데이터 변경, 권한, 조회, 트랜잭션을 다루는 5가지 범주로 나뉜다.

데이터베이스 구조를 정의하는 DDL

DDL(Data Definition Language)은 테이블 같은 데이터베이스 구조를 만들고 바꾸거나 제거할 때 사용한다.

-- 테이블 생성
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    dept VARCHAR(30),
    salary DECIMAL(10,2)
);

-- 테이블 구조 변경
ALTER TABLE employees ADD email VARCHAR(100);

-- 테이블 삭제
DROP TABLE employees;

행 데이터를 변경하는 DML

DML(Data Manipulation Language)은 데이터를 삽입하고 수정하거나 삭제하는 명령어다.

-- 데이터 삽입
INSERT INTO employees (emp_id, name, dept, salary)
VALUES (1001, '홍길동', '개발팀', 5000000);

-- 데이터 수정
UPDATE employees
SET salary = 5500000
WHERE emp_id = 1001;

-- 데이터 삭제
DELETE FROM employees
WHERE emp_id = 1001;

접근 권한을 다루는 DCL

DCL(Data Control Language)은 사용자에게 데이터베이스 권한을 부여하거나 회수한다.

-- 권한 부여
GRANT SELECT, INSERT ON employees TO user1;

-- 권한 회수
REVOKE INSERT ON employees FROM user1;

결과 집합을 읽는 DQL

DQL(Data Query Language)은 데이터를 조회하는 데 쓰인다. 일부 분류에서는 DML의 일부로 취급한다.

-- 기본 조회
SELECT name, dept, salary FROM employees WHERE dept = '개발팀';

-- 집계 함수 사용
SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept
HAVING AVG(salary) > 4000000;

변경 단위를 확정하거나 되돌리는 TCL

TCL(Transaction Control Language)은 트랜잭션의 변경 사항을 확정하거나 취소한다.

-- 트랜잭션 시작
BEGIN TRANSACTION;

-- 데이터 수정
UPDATE accounts SET balance = balance - 1000000 WHERE account_id = 'A001';
UPDATE accounts SET balance = balance + 1000000 WHERE account_id = 'B001';

-- 변경사항 확정
COMMIT;

-- 변경사항 취소
ROLLBACK;

작성 순서와 다른 SELECT의 논리적 처리

SELECT 문은 코드에 적는 순서와 논리적으로 처리되는 순서가 다르다. FROM에서 대상 데이터를 정한 뒤 WHERE, GROUP BY, HAVING을 거쳐 SELECT 결과를 만들고, ORDER BY와 LIMIT/OFFSET이 뒤따른다.

⤢✕

작성할 때는 다음 구문 순서를 사용한다.

SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column1
HAVING group_condition
ORDER BY column1 [ASC|DESC]
LIMIT number OFFSET number;

테이블을 연결하고 쿼리를 중첩하는 방식

JOIN은 서로 다른 테이블의 행을 연결한다. INNER JOIN은 일치하는 행을 대상으로 하고, LEFT JOIN과 RIGHT JOIN은 각각 왼쪽과 오른쪽 테이블을 기준으로 결과를 남긴다. FULL JOIN은 양쪽 테이블의 행을 포함한다.

⤢✕

-- 내부 조인
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- 외부 조인
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

서브쿼리는 쿼리 안에 포함되는 쿼리다. 단일 값을 돌려주는 스칼라 서브쿼리, FROM 절에서 사용하는 인라인 뷰, WHERE 절의 중첩 서브쿼리처럼 목적에 따라 배치할 수 있다.

-- 스칼라 서브쿼리 (단일 값 반환)
SELECT name,
       (SELECT AVG(salary) FROM employees) AS avg_company_salary,
       salary
FROM employees;

-- 인라인 뷰 (FROM 절의 서브쿼리)
SELECT d.dept_name, e_count.emp_count
FROM departments d
JOIN (
    SELECT dept_id, COUNT(*) AS emp_count
    FROM employees
    GROUP BY dept_id
) e_count ON d.id = e_count.dept_id;

-- 중첩 서브쿼리 (WHERE 절의 서브쿼리)
SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

인덱스와 실행 계획으로 성능을 확인한다

인덱스는 B-tree 구조로 구현되어 O(log n) 시간 복잡도로 검색할 수 있다. 쿼리 조건과 데이터 접근 방식에 맞는 인덱스 설계는 성능에 큰 영향을 준다.

-- 인덱스 생성
CREATE INDEX idx_employee_dept ON employees(dept);

-- 인덱스를 활용한 쿼리
SELECT * FROM employees WHERE dept = '개발팀';

실행 계획은 쿼리가 실제로 어떤 방식으로 처리되는지 보여주며, 병목 지점을 파악하는 데 사용한다.

-- Oracle, PostgreSQL
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE dept = '개발팀';

-- MySQL
EXPLAIN SELECT * FROM employees WHERE dept = '개발팀';

인덱스를 고려할 때는 컬럼에 함수를 적용하는 조건을 주의해야 한다. 다음과 같은 조건은 인덱스를 사용하지 못하게 해 전체 테이블 스캔을 발생시킬 수 있다.

SELECT *
FROM orders o
WHERE SUBSTRING(o.order_date, 1, 4) = '2023';

원본 컬럼을 직접 조건으로 사용하면 다음처럼 작성할 수 있다.

SELECT *
FROM orders o
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31';

분석·이관·보고서에 쓰는 SQL

집계와 조인을 조합하면 부서별·월별 매출 추이를 분석할 수 있다.

-- 부서별, 월별 매출 추이 분석
SELECT
    d.dept_name,
    EXTRACT(YEAR_MONTH FROM s.sale_date) AS ym,
    SUM(s.amount) AS total_sales
FROM
    sales s
JOIN
    departments d ON s.dept_id = d.id
GROUP BY
    d.dept_name, EXTRACT(YEAR_MONTH FROM s.sale_date)
ORDER BY
    d.dept_name, ym;

INSERT ... SELECT 구문은 기존 데이터를 변환해 새 테이블로 옮기는 데이터 마이그레이션에 활용할 수 있다.

-- 데이터 마이그레이션 예시
INSERT INTO new_employees (emp_id, full_name, department, annual_salary)
SELECT
    emp_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    dept,
    salary * 12 AS annual_salary
FROM
    old_employees
WHERE
    active = 1;

CTE와 윈도우 함수는 월별 매출, 누적 매출, 성장률을 함께 보여주는 보고서 쿼리에 사용할 수 있다.

-- 연간 매출 보고서
WITH monthly_sales AS (
    SELECT
        EXTRACT(MONTH FROM sale_date) AS month,
        SUM(amount) AS sales_amount
    FROM
        sales
    WHERE
        EXTRACT(YEAR FROM sale_date) = 2023
    GROUP BY
        EXTRACT(MONTH FROM sale_date)
)
SELECT
    month,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY month) AS cumulative_sales,
    sales_amount / LAG(sales_amount) OVER (ORDER BY month) - 1 AS growth_rate
FROM
    monthly_sales
ORDER BY
    month;

관계형 모델 밖의 요구와 SQL 확장

페타바이트 규모 데이터 처리에서는 성능 제약이 생길 수 있고, JSON이나 XML 같은 비정형 데이터 처리에는 제한적인 기능을 제공한다. 수평적 확장(scale-out)에도 한계가 있다.

관계형 DB는 SQL, 고정된 스키마, ACID 준수를 기반으로 한다. 반면 NoSQL은 다양한 쿼리 언어와 유연한 스키마, BASE 원칙을 사용한다.

⤢✕

관계형 DB에는 MySQL, Oracle, PostgreSQL, MS SQL Server가 있고, NoSQL에는 MongoDB(문서형), Redis(키-값형), Cassandra(컬럼형), Neo4j(그래프형)가 있다.

PostgreSQL과 MySQL 8.0 이상은 JSON 데이터 타입과 함수를 제공한다.

SELECT json_extract(data, '$.name') FROM users;

윈도우 함수는 분석 쿼리를 위한 강력한 기능을 제공한다.

SELECT name, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
FROM employees;

재귀 쿼리는 계층적 데이터를 다룰 때 유용하다.

WITH RECURSIVE org_hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oh.level + 1
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT * FROM org_hierarchy ORDER BY level, id;

SQL은 50년 가까이 데이터 관리의 핵심 언어로 사용되어 왔다. 관계형 데이터베이스가 제공하는 데이터 일관성, 트랜잭션 처리, 표준화된 접근 방식은 많은 기업 애플리케이션에서 여전히 필요하다. 빅데이터, 클라우드 컴퓨팅, 인공지능 환경에서도 SQL은 확장 기능과 함께 적응하고 있다.