응용 SQL 작성

SQL 명령어는 세 무리로 나뉩니다

SQL(Structured Query Language)은 관계형 데이터베이스를 다루는 표준 언어이고, 명령어는 하는 일에 따라 묶어 부릅니다. 데이터 정의어 DDL(Data Definition Language)은 테이블·뷰·인덱스 같은 그릇을 만들고(CREATE), 고치고(ALTER), 없애는(DROP) 명령입니다. 데이터 조작어 DML(Data Manipulation Language)은 그릇에 담긴 데이터를 조회(SELECT)·삽입(INSERT)·수정(UPDATE)·삭제(DELETE)합니다. 데이터 제어어 DCL(Data Control Language)은 누가 무엇을 할 수 있는지 권한을 주고(GRANT) 회수합니다(REVOKE).

트랜잭션을 확정하는 COMMIT, 되돌리는 ROLLBACK, 중간 저장점을 만드는 SAVEPOINT는 자료에 따라 DCL에 넣기도 하고 트랜잭션 제어어 TCL(Transaction Control Language)로 따로 떼기도 합니다. 'DCL에 속하는 명령'을 묻는다면 GRANT와 REVOKE는 어느 기준으로도 DCL이고, COMMIT과 ROLLBACK은 문제가 어떤 분류를 쓰는지 함께 확인하는 것이 안전합니다.

DDL의 모양도 알아 둡니다. CREATE TABLE 직원 (사번 INT PRIMARY KEY, 이름 VARCHAR(20) NOT NULL, 부서코드 CHAR(2) REFERENCES 부서(부서코드));처럼 열과 제약 조건을 함께 정의합니다. 있는 테이블에 열을 더할 때는 ALTER TABLE 직원 ADD 입사일 DATE;, 열을 지울 때는 ALTER TABLE 직원 DROP COLUMN 입사일;을 씁니다. DROP TABLE 뒤에 CASCADE를 붙이면 그 테이블에 기대는 제약이나 뷰까지 함께 정리하고, RESTRICT를 붙이면 참조하는 곳이 있을 때 삭제를 거부합니다(표준 SQL 기준이며, Oracle은 CASCADE CONSTRAINTS로 참조 제약만 지우고 뷰는 무효 상태로 남깁니다).

뷰와 인덱스도 DDL로 만듭니다. CREATE VIEW 개발부 AS SELECT 사번, 이름 FROM 직원 WHERE 부서 = '개발';은 조회문을 이름 붙여 저장한 가상 테이블을, CREATE INDEX idx_부서 ON 직원(부서);는 부서 열로 빨리 찾기 위한 색인을 만듭니다. 중복 값을 막는 인덱스는 CREATE UNIQUE INDEX로 만듭니다. 행만 모두 지우고 테이블 구조는 남기는 TRUNCATE TABLE도 DDL로 분류되는데, 조건에 맞는 행을 지우고 ROLLBACK으로 되돌릴 수 있는 DELETE(DML)와 구별해야 합니다.

DML: 조회·삽입·수정·삭제

SELECT 문은 SELECT 열 FROM 테이블 WHERE 조건 GROUP BY 열 HAVING 그룹조건 ORDER BY 열 순서로 적습니다. 그런데 DBMS가 처리하는 순서는 적는 순서와 다릅니다. FROM으로 테이블을 정하고, WHERE로 행을 거르고, GROUP BY로 묶고, HAVING으로 묶음을 거르고, SELECT로 보여 줄 열을 고른 뒤, 마지막에 ORDER BY로 정렬합니다. 그래서 SELECT에서 붙인 별칭은 ORDER BY에서는 쓸 수 있지만 WHERE에서는 쓸 수 없습니다.

WHERE에서 자주 쓰는 조건이 있습니다. BETWEEN 300 AND 400은 300 이상 400 이하처럼 양 끝을 포함합니다. IN ('개발', '영업')은 목록 중 하나와 같으면 참입니다. LIKE에서 %는 길이와 상관없는 아무 문자열, _는 정확히 한 글자를 뜻합니다. 그래서 LIKE '김%'는 김으로 시작하는 모든 값을, LIKE '_수'는 두 글자이면서 수로 끝나는 값을 찾습니다. 결과에서 중복된 행을 하나로 줄일 때는 SELECT DISTINCT를 씁니다.

NULL은 '값이 없음'을 뜻하므로, NULL과 비교한 결과는 참도 거짓도 아닌 '알 수 없음'이 됩니다. 급여 = NULL이라고 쓰면 어떤 행도 고르지 못하므로 반드시 급여 IS NULL, 급여 IS NOT NULL로 씁니다. NULL과 산술 연산을 하면 결과도 NULL이 된다는 점도 결과 계산 문제에서 자주 함정이 됩니다.

나머지 DML은 형태가 단순합니다. INSERT INTO 직원(사번, 이름, 부서) VALUES (108, '수아', '기획');는 행을 넣으며, 열 목록을 빼면 테이블의 모든 열에 정의된 순서대로 값을 줘야 합니다. UPDATE 직원 SET 급여 = 급여 + 10 WHERE 부서 = '영업';은 조건에 맞는 행의 값을 바꾸고, DELETE FROM 직원 WHERE 사번 = 104;는 조건에 맞는 행을 지웁니다. UPDATE와 DELETE에서 WHERE를 빠뜨리면 테이블의 모든 행이 바뀌거나 지워진다는 점을 꼭 기억하세요.

그룹 함수: 묶어서 세고 더하기

그룹 함수(집계 함수)는 여러 행을 모아 값 하나를 만듭니다. COUNT(개수), SUM(합계), AVG(평균), MAX(최댓값), MIN(최솟값)이 기본입니다. 결과 계산에서 가장 중요한 규칙은 NULL을 어떻게 다루느냐입니다. COUNT(*)는 행 자체를 세므로 NULL이 있는 행도 셉니다. 반면 COUNT(급여), SUM(급여), AVG(급여)는 급여가 NULL인 행을 아예 빼고 계산합니다. 그래서 AVG(급여)는 'NULL이 아닌 급여의 합 ÷ NULL이 아닌 행의 수'가 됩니다.

GROUP BY 부서처럼 묶으면 그룹 함수가 부서마다 따로 계산됩니다. 이때 SELECT에는 GROUP BY에 쓴 열과 그룹 함수만 올 수 있습니다. 묶은 결과에 조건을 걸 때는 HAVING을 씁니다. WHERE는 묶기 전에 개별 행을 거르고, HAVING은 묶은 뒤에 그룹을 거른다는 차이입니다. '평균 급여가 330 이상인 부서'처럼 그룹 함수가 들어간 조건은 HAVING AVG(급여) >= 330으로만 쓸 수 있습니다.

보고서에는 세부 합계 아래에 소계와 전체 합계가 붙는 경우가 많습니다. 이런 소계·중계·총합계를 한 번에 구하는 것이 GROUP BY의 확장 기능입니다. ROLLUP(부서, 직급)은 (부서, 직급)별 합계, 부서별 소계, 전체 총합계를 만듭니다. 오른쪽 열부터 하나씩 떼며 올라가는 계층형이라 괄호 안 열의 순서가 바뀌면 결과도 달라집니다. CUBE(부서, 직급)은 가능한 모든 조합, 즉 (부서, 직급)별, 부서별, 직급별, 전체 합계를 모두 만듭니다. GROUPING SETS(부서, 직급)은 적어 준 묶음별 결과만 만들고 총합계는 넣지 않습니다.

ROLLUP이나 CUBE로 만든 소계 행에서는 묶이지 않은 열이 NULL로 표시됩니다. 원래 데이터에 있던 NULL과 구분하려면 GROUPING(열) 함수를 씁니다. 그 열이 소계를 내느라 합쳐진 행이면 1, 아니면 0을 돌려주므로, CASE와 함께 써서 '소계', '총계' 같은 글자로 바꿔 보여 줄 수 있습니다.

윈도우 함수: 행을 줄이지 않고 순위·누계 붙이기

그룹 함수는 여러 행을 한 행으로 줄여 버립니다. 그런데 '직원마다 부서 안에서의 급여 순위를 옆에 붙여 달라'처럼 행은 그대로 두고 계산 결과만 옆에 붙이고 싶을 때가 있습니다. 이때 쓰는 것이 윈도우 함수입니다. 형태는 함수() OVER (PARTITION BY 묶을 열 ORDER BY 정렬 열)입니다. PARTITION BY는 계산 범위를 나누는 칸막이, ORDER BY는 그 칸 안에서 순서를 정하는 기준입니다. PARTITION BY를 빼면 전체가 하나의 묶음이 됩니다.

순위 함수는 셋을 구별해야 합니다. 점수가 90, 90, 80이라면 RANK는 1, 1, 3을 줍니다. 공동 1위가 두 명이니 그다음은 3위가 됩니다. DENSE_RANK는 1, 1, 2를 줍니다. 동점이 있어도 다음 순위를 건너뛰지 않습니다. ROW_NUMBER는 동점이어도 1, 2, 3처럼 겹치지 않는 일련번호를 줍니다. 동점끼리 누가 먼저 번호를 받을지는 ORDER BY에 기준을 더 적지 않으면 보장되지 않습니다.

SUM, AVG, COUNT 같은 그룹 함수도 OVER를 붙이면 윈도우 함수가 됩니다. SUM(급여) OVER (PARTITION BY 부서)는 각 행 옆에 그 부서의 급여 합계를 붙이고, OVER 안에 ORDER BY를 넣으면 보통 첫 행부터 현재 행까지 더한 누적 합계가 됩니다(정렬 값이 같은 행은 기본 범위가 RANGE라 같은 누적값을 받습니다). 범위를 직접 정하려면 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW처럼 적습니다. 이 밖에 앞 행과 뒤 행의 값을 가져오는 LAG와 LEAD, 묶음의 첫 값과 마지막 값을 가져오는 FIRST_VALUE와 LAST_VALUE, 전체를 n등분해 몇 번째 무리인지 알려 주는 NTILE, 백분율로 순위를 나타내는 PERCENT_RANK와 CUME_DIST가 있습니다.

윈도우 함수는 SELECT 단계에서 계산되므로 WHERE에서 바로 쓸 수 없습니다. '부서별 급여 1위만 골라라'라면 윈도우 함수로 순위를 매긴 조회를 FROM 절의 서브쿼리(인라인 뷰)로 감싼 뒤, 바깥 WHERE에서 순위가 1인 행을 거르는 방식으로 씁니다.

조인: 여러 테이블을 옆으로 잇기

조인은 두 테이블의 행을 조건에 맞춰 옆으로 붙여 하나의 결과로 만드는 연산입니다. 가장 흔한 내부 조인(INNER JOIN)은 FROM 고객 C INNER JOIN 주문 O ON C.고객번호 = O.고객번호처럼 쓰고, 조건에 맞는 짝이 있는 행만 남깁니다. 같은 값끼리 잇는 것을 동등 조인, BETWEEN이나 부등호로 잇는 것을 비동등 조인이라고 합니다. 두 테이블에서 이름이 같은 열을 자동으로 찾아 동등 조인하는 NATURAL JOIN도 있는데, 이때 공통 열은 결과에 한 번만 나옵니다.

외부 조인(OUTER JOIN)은 짝이 없는 행도 버리지 않습니다. LEFT OUTER JOIN은 왼쪽 테이블의 행을 모두 남기고, 오른쪽에 짝이 없으면 오른쪽 열을 NULL로 채웁니다. RIGHT OUTER JOIN은 그 반대이고, FULL OUTER JOIN은 양쪽의 짝 없는 행을 모두 남깁니다. 결과 행 수는 '짝이 맞아 만들어진 행 수 + 남기기로 한 쪽의 짝 없는 행 수'로 셉니다. 짝이 여러 개면 그만큼 행이 늘어나므로, 주문을 세 번 한 고객은 결과에 세 번 나온다는 점에 주의합니다.

교차 조인(CROSS JOIN)은 조건 없이 모든 행을 서로 짝지어, 4행 테이블과 5행 테이블을 교차 조인하면 4 × 5 = 20행이 나옵니다. 수학의 곱집합(카티션 곱)과 같습니다. 한 테이블을 별칭 두 개로 불러 자기 자신과 잇는 것은 셀프 조인이라고 하며, [직원] 테이블에서 각 직원의 상사 이름을 구할 때처럼 같은 테이블 안의 행끼리 관계가 있을 때 씁니다.

서브쿼리와 집합 연산자

서브쿼리는 SQL 문 안에 괄호로 넣은 또 하나의 SELECT입니다. 들어가는 자리에 따라 이름이 다릅니다. WHERE 절에 넣어 조건값을 만드는 서브쿼리, FROM 절에 넣어 임시 테이블처럼 쓰는 인라인 뷰, SELECT 열 목록에 넣어 값 하나를 돌려주는 스칼라 서브쿼리가 있습니다. 서브쿼리가 바깥 쿼리의 열을 참조하면 상관 서브쿼리라고 하며, 바깥 행 하나하나마다 다시 실행되는 것처럼 동작합니다.

서브쿼리 결과가 한 행이면 =, >, < 같은 비교 연산자를 쓸 수 있지만, 여러 행이 나올 수 있으면 다중 행 연산자를 써야 합니다. IN은 목록 중 하나와 같으면 참입니다. > ALL은 목록의 모든 값보다 커야, 즉 최댓값보다 커야 참입니다. > ANY(또는 SOME)는 목록 중 하나보다만 커도, 즉 최솟값보다 크면 참입니다. EXISTS는 서브쿼리 결과에 행이 하나라도 있으면 참이 되며, 값이 무엇인지는 보지 않고 있는지 없는지만 봅니다.

자주 나오는 함정이 NOT IN과 NULL입니다. id NOT IN (2, 4, NULL)은 'id <> 2 AND id <> 4 AND id <> NULL'과 같은데, NULL과의 비교는 늘 '알 수 없음'이라 전체가 참이 되지 못해 결과가 한 행도 나오지 않습니다. 반면 NOT EXISTS는 조건에 맞는 행이 있는지만 보므로, 서브쿼리 쪽에 NULL이 섞여 있어도 의도대로 동작합니다.

집합 연산자는 두 SELECT 결과를 위아래로 합칩니다. UNION은 합집합을 만들되 중복 행을 하나로 줄이고, UNION ALL은 중복까지 그대로 다 붙입니다. INTERSECT는 양쪽에 모두 있는 행(교집합), EXCEPT는 앞쪽에만 있는 행(차집합)을 남기며, Oracle에서는 EXCEPT 대신 MINUS라고 씁니다. 두 SELECT의 열 개수와 대응하는 열의 자료형이 맞아야 하고, 결과의 열 이름은 앞쪽 SELECT를 따르며, ORDER BY는 맨 마지막에 한 번만 쓸 수 있습니다.

DCL·트랜잭션 제어·오류 처리

권한을 줄 때는 GRANT SELECT, UPDATE ON 직원 TO kim;처럼 'GRANT 권한 ON 대상 TO 사용자' 순서로 씁니다. 끝에 WITH GRANT OPTION을 붙이면 kim이 받은 권한을 다른 사용자에게 다시 줄 수 있습니다. 회수는 REVOKE SELECT ON 직원 FROM kim;처럼 TO 대신 FROM을 씁니다. CASCADE를 붙이면 kim이 다른 사람에게 다시 준 권한까지 연쇄적으로 회수하고, 다시 줄 수 있는 자격만 빼앗을 때는 표준 SQL(PostgreSQL 등)에서는 REVOKE GRANT OPTION FOR SELECT ON 직원 FROM kim;처럼 쓰고, Oracle에는 이 절이 없어 권한을 거둔 뒤 옵션 없이 다시 줍니다.

사용자가 많아지면 한 명씩 권한을 주기 번거롭습니다. 그래서 CREATE ROLE 조회담당;으로 역할(롤)을 만들고, GRANT SELECT ON 직원 TO 조회담당;처럼 권한을 롤에 모은 뒤, GRANT 조회담당 TO kim;으로 사용자에게 롤을 줍니다. 사용자 계정은 CREATE USER로 만들고, ALTER USER로 비밀번호 같은 속성을 바꾸며, DROP USER로 지웁니다. 테이블 생성 권한 같은 시스템 권한을 남에게 다시 줄 수 있게 할 때는, Oracle에서는 WITH GRANT OPTION 대신 WITH ADMIN OPTION을 씁니다.

트랜잭션 제어도 결과를 묻는 문제로 자주 나옵니다. COMMIT은 지금까지의 변경을 확정하고, ROLLBACK은 마지막 COMMIT 이후의 변경을 모두 되돌립니다. SAVEPOINT 이름;으로 중간 지점을 표시해 두면 ROLLBACK TO 이름;으로 그 지점까지만 되돌릴 수 있습니다. 명령을 위에서부터 한 줄씩 따라가며 '확정된 값'과 '아직 확정 전인 값'을 나눠 적으면 헷갈리지 않습니다. Oracle이나 MySQL에서는 CREATE 같은 DDL을 실행하면 그 앞의 변경까지 자동으로 COMMIT된다는 점도 알아 두세요.

SQL을 실행하다 보면 오류가 납니다. 기본키 중복, 참조할 값이 없는 외래키, NOT NULL 열에 NULL 넣기, CHECK 조건 위반처럼 제약 조건을 어기거나, 자료형이 맞지 않거나, 0으로 나누는 경우가 대표적입니다. 여러 문장이 하나의 작업이라면 오류를 확인한 뒤 ROLLBACK으로 앞서 성공한 문장까지 되돌려야 데이터가 어긋나지 않습니다. 응용 프로그램은 실행 결과 코드로 성공 여부를 판단하는데, 표준 SQL의 SQLSTATE는 다섯 글자 코드로 '00000'이 성공을 뜻하고, 내장 SQL의 SQLCODE는 0이 성공, 100이 처리할 데이터 없음, 음수가 오류를 뜻합니다. 절차형 SQL에서는 EXCEPTION 절로 오류를 받아 처리합니다.