Skip to content

[ SQL 첫걸음 ] 제 3장 정렬과 연산

참고

SQL 첫걸음을 참고로 공부한 내용입니다. 따라서 아래 명령어를 통해 나오게 되는 결괏값은 전부 해당 책을 통해 다운로드한 파일의 결과물입니다.

정렬 - ORDER BY

SELECT 구에서 ORDER BY를 사용하여 검색 결과의 행 순서를 바꿀 수 있습니다. 다시 말해 정렬(Sort)이 가능합니다. 만약 ORDER BY를 사용하지 않을 경우 데이터베이스 내부에 저장된 순서대로 반환됩니다.

ORDER BY로 검색 결과 정렬하기

ORDER BY 구로 정렬하고 싶은 열을 지정하여 정렬할 수 있습니다. 그 형태는 SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명과 같습니다.

다음과 같은 테이블 sample31이 있다고 가정해봅시다.

+------+------+---------------------------+
| name | age  | address                   |
+------+------+---------------------------+
| A씨  |   36 | 대구광역시 중구               |
| B씨  |   18 | 부산광역시 연제구              |
| C씨  |   25 | 서울특별시 중구               |
+------+------+---------------------------+

age를 기준으로 정렬하는 방법은 아래와 같습니다.

$ mysql > SELECT * FROM sample31 ORDER BY age;

+------+------+---------------------------+
| name | age  | address                   |
+------+------+---------------------------+
| B씨  |   18 | 부산광역시 연제구              |
| C씨  |   25 | 서울특별시 중구               |
| A씨  |   36 | 대구광역시 중구               |
+------+------+---------------------------+
3 rows in set (0.00 sec)

만약 address를 기준으로 정렬한다면 아래와 같습니다.

$ mysql > SELECT * FROM sample31 ORDER BY address;

+------+------+---------------------------+
| name | age  | address                   |
+------+------+---------------------------+
| A씨  |   36 | 대구광역시 중구               |
| B씨  |   18 | 부산광역시 연제구              |
| C씨  |   25 | 서울특별시 중구               |
+------+------+---------------------------+
3 rows in set (0.00 sec)

ORDER BY DESC로 내림차순으로 정렬하기

앞선 방법은 전부 오름차순으로 정렬하였습니다. DESC를 사용하여 내림차순으로 정렬할 수도 있습니다. 앞서 address를 기준으로 정렬한 것을 내림차순으로 정렬하는 방법은 아래와 같습니다.

$ mysql > SELECT * FROM sample31 ORDER BY address DESC;

+------+------+---------------------------+
| name | age  | address                   |
+------+------+---------------------------+
| C씨  |   25 | 서울특별시 중구               |
| B씨  |   18 | 부산광역시 연제구              |
| A씨  |   36 | 대구광역시 중구               |
+------+------+---------------------------+
3 rows in set (0.00 sec)

정보

아무것도 붙이지 않았음에도 오름차순으로 정렬되는 이유는 ASC가 생략되어 있기 때문입니다.

대소관계

정렬을 위해서는 대소관계가 중요합니다.

  • 수치형 데이터 : 숫자의 크기로 판별합니다.
  • 날짜시간형 데이터 : 숫자의 크기로 판별합니다.
  • 문자열 데이터 : 알파벳, 한글 순이며 한글의 경우 자음, 모음 순서로 판별합니다. 다시 말해 사전식 순서에 의해 결정됩니다.

사전식 순서에서 주의할 점

다음과 같은 테이블 sample311이 존재한다고 가정해봅시다.

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a     | varchar(2) | YES  |     | NULL    |       |
| b     | int        | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

만약 a열을 기준으로 정렬할 경우 아래와 같은 결과를 얻을 수 있습니다.

$ mysql > SELECT * FROM sample311 ORDER BY a;

+------+------+
| a    | b    |
+------+------+
| 1    |    1 |
| 10   |   10 |
| 11   |   11 |
| 2    |    2 |
+------+------+
4 rows in set (0.00 sec)

a열이 varchar기 때문에 사전식으로 정렬되어 1 다음에 2가 아닌 10이 오게 됩니다. 이처럼 문자열형 열에 숫자 데이터를 넣을 수 있는데 이때 값이 문자이기 때문에 사전식으로 정렬된다는 걸 주의해야 합니다.

ORDER BY는 테이블에 영향을 주지 않는다

ORDER BY를 이용해 행 순서를 바꾼 것은 결과값에만 영향을 끼칙 때문에 저장장치, 다시 말해 데이터베이스에 저장되어 있는 데이터의 행 순서를 변경하지는 않습니다.

SELECT 명령은 결국 데이터를 검색 (혹은 조회)하는 명령이기 때문에 데이터를 참조만할 뿐 변경하지는 않습니다.

복수의 열을 지정해 정렬하기

단순히 하나의 열이 아닌 복수의 열을 지정해 정렬할 수 있습니다.

복수 열로 정렬 지정

아래와 같은 테이블 sample32가 있다고 가정해봅시다.

+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    2 |    2 |
|    1 |    3 |
|    1 |    2 |
+------+------+

주의

앞서 ORDER BY 구는 생략할 수 있다고 봤습니다. 이때 ORDER BY 구를 생략하면 데이터베이스 서버의 당시 상황에 따라 어떤 순서로 행을 반환할지 결정되기 때문에 결국 순서는 일정하지 않습니다. 따라서 언제나 같은 순서로 결과를 얻고 싶다면 ORDER BY 구로 순서를 지정해야 합니다.

ORDER BY로 복수 열 지정하기

SELECT 구에서 열을 지정한 것처럼 콤마(,)로 열명을 구분해 지정하여 복수 열의 순서를 지정할 수 있습니다. 이때 정렬 순서는 지정한 열명의 순서를 따릅니다.

a열, b열 순서대로 정렬하는 방법은 아래와 같습니다.

$ mysql > SELECT * FROM sample32 ORDER BY a, b;

+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    2 |    1 |
|    2 |    2 |
+------+------+
5 rows in set (0.00 sec)

그러면 이제 b열, a열 순서대로 정렬하면 아래와 같습니다.

$ mysql > SELECT * FROM sample32 ORDER BY b, a;

+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    1 |    2 |
|    2 |    2 |
|    1 |    3 |
+------+------+
5 rows in set (0.00 sec)

정렬방법 지정하기

복수 열을 지정할 때도 이전 내림차순과 오름차순을 결정했던 것처럼 개별적으로 열마다 그 방법을 지정할 수 있습니다. 이때 오름차순 정렬의 경우 생략할 수 있습니다.

예를 들어 이전 sample32 테이블을 a열은 오름차순, b열은 내림차순으로 정렬할 경우 방법은 아래와 같습니다.

$ mysql > SELECT * FROM sample32 ORDER BY a, b DESC;

+------+------+
| a    | b    |
+------+------+
|    1 |    3 |
|    1 |    2 |
|    1 |    1 |
|    2 |    2 |
|    2 |    1 |
+------+------+
5 rows in set (0.00 sec)

정보

복수의 열을 정렬할 때 ASC를 생략하는 것보다 명시적으로 적어주는 게 조금 더 직관적이고 가독성이 좋기 때문에 될 수 있으면 적어주는 게 좋습니다.

NULL 값의 정렬순서

NULL의 경우 대소비교를 할 수 없기 때문에 별도의 방법으로 정렬해야 합니다. 이때 사용하는 방법은 크게 두 가지 입니다.

  • 특정 값보다 큰 값
  • 특정 값보다 작은 값

ORDER BY로 지정한 열에서 NULL 값을 가지는 행은 가장 먼저 표시되거나 가장 나중에 표시됩니다. NULL에 대한 대소비교 방법은 표준 SQL에도 지정되어 있지 않아 데이터베이스 제품에 따라 기준이 다릅니다.

정보

MySQL의 경우 NULL 값을 가장 작은 값으로 취급하여 오름차순(ASC)에서는 가장 먼저, 내림차순(DESC)에서는 가장 나중에 표시합니다.

결과 행 제한하기 - LIMIT

SELECT 명령에서 LIMIT 구를 사용하여 결괏값으로 반환되는 행을 제한할 수 있습니다.

행수 제한

아래와 같은 테이블 sample33이 존재한다고 가정해봅시다.

+------+
| no   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+

이때 세 개의 행만 반환되도록 하는 방법은 아래와 같습니다.

$ mysql > SELECT * FROM sample33 LIMIT 3;

+------+
| no   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

유의할 점은 LIMIT가 지정하는 것이 최대 행수라는 것입니다. 만약 행이 하나만 존재하는데 LIMIT3으로 지정해도 존재하는 행은 하나이기 때문에 한 개의 행만 반환됩니다.

주의

LIMIT 구는 표준 SQL은 아니고 MySQL과 PostgreSQL에서 사용할 있는 문법입니다.

정렬한 후 제한하기

앞선 예시에서 사실 LIMIT를 사용하지 않더라도 WHERE no<=3과 같은 WHERE 구를 통해 동일한 결괏값을 반환받을 수 있습니다. 하지만 내부적으로 LIMITWHERE의 처리 순서가 다릅니다.

LIMIT의 경우 반환할 행수를 제한하는 기능으로 WHERE 구로 먼저 검색을 거친 다음 ORDER BY로 정렬된 뒤에 최종적으로 처리됩니다.

no 열을 내림차순으로 정렬한 뒤 상위 3건 만을 반환받는 방법은 아래와 같습니다.

$ mysql > SELECT * FROM sample33 ORDER BY no DESC LIMIT 3;

+------+
| no   |
+------+
|    7 |
|    6 |
|    5 |
+------+
3 rows in set (0.00 sec)

no가 정렬이 된 후 LIMIT가 처리된 것을 확인할 수 있습니다.

LIMIT를 사용할 수 없는 데이터베이스에서의 행 제한

SQL Server와 같이 LIMIT가 존재하지 않는 데이터베이스의 경우 TOP을 사용할 수 있습니다. 이를 테면 SELECT TOP 3 * FROM sample33;과 같습니다.

Oracle의 경우 ROWNUM을 통해 이를 수행할 수 있습니다. 그 형태는 SELECT * FROM sample33 WHERE ROWNUM <=3;과 같습니다. 이때 ROWNUM의 경우 클라이언트에서 결과가 반환될 때 각 행에 할당되는 행 번호를 의미합니다.

정보

ROWNUM을 사용하여 행을 제한할 때 WHERE 구로 지정하므로 정렬하기 전에 처리되어 LIMIT와 다른 결괏값을 반환합니다.

이부분은 서브쿼리에서 더 자세히 다루도록 하겠습니다.

오프셋 지정

웹 페이지에서 게시판을 보면 보통 페이지가 구분되어 있는 걸 확인할 수 있습니다. 대량의 데이터를 하나의 페이지에 표시하는 건 기능적으로도 속도 측면에서도 효율적이지 못하기 때문에 게시판에 페이지 구분이 있는 것처럼 일반적으로 페이지 나누기(Pagination) 기능을 사용합니다.

이러한 페이지네이션 기능을 LIMIT를 사용하여 간단하게 구현할 수 있습니다. 이때 LIMIT가 끝값을 의미하기 때문에 시작값을 나타내는 것도 필요합니다. 예를 들어 1페이지가 1번 게시글부터 10번 게시글까지 표시한다면 다음 2페이지는 11번 게시글부터 20번 게시글까지 표시하기 때문입니다. 시작값의 경우 OFFSET을 통해 나타낼 수 있습니다.

만약 세 개의 게시글씩 표시하는 게시판이고 현재 2페이라 가정한다면 이를 나타내는 방법은 아래와 같습니다.

$ mysql > SELECT * FROM sample33 LIMIT 3 OFFSET 3;

+------+
| no   |
+------+
|    4 |
|    5 |
|    6 |
+------+
3 rows in set (0.00 sec)

이때 LIMIT 구의 OFFSET은 생략 가능하며 기본값은 0입니다. 유의할 점은 OFFSET의 값이 곧 시작할 행 - 1이라는 점입니다.

수치 연산

SQL은 데이터베이스 뿐만 아니라 컴퓨터를 조작하는 언어이기 때문에 기본적인 계산기능을 포함합니다.

사칙 연산

덧셈, 뺄셈, 곱셈, 나눗셈과 같은 기본적인 사칙 연산이 존재하며 추가적으로 나눗셈의 나머지 값만 얻을 수 있는 연산도 존재합니다. 그 예시와 사용법은 아래와 같습니다.

연산자 연산
+ 덧셈(가산) 1+2 -> 3
- 뺄셈(감산) 1-2 -> -1
* 곱셈(승산) 1*2 -> 2
/ 나눗셈(제산) 1/2 -> 0.5
% 나머지 1%2 -> 1

이때 유의할 점은 나눗셈(/)의 경우 정수값이 되도록 계산하기 때문에 몫이 0.5가 아닌 0이 됩니다.

정보

나머지 연산인 %의 경우 데이터베이스 제품에 따라 MOD 함수를 사용하는 경우도 있습니다.

연산자의 우선순위

연산자에 우선순위가 존재합니다. 그 우선순위는 아래와 같습니다.

  • 1순위 : *, /, %
  • 2순위 : +, -

만약 우선순위가 같은 경우 왼쪽에서 오른쪽의 순서로 계산합니다.

SELECT 구로 연산하기

SELECT 구 안에서도 연산이 가능합니다. 예를 들어 다음과 같은 테이블 sample34이 존재한다고 가정해봅시다.

+------+-------+----------+
| no   | price | quantity |
+------+-------+----------+
|    1 |   100 |       10 |
|    2 |   230 |       24 |
|    3 |  1980 |        1 |
+------+-------+----------+

SELECT를 사용할 때 price열과 quantity열을 곱한 결과를 반환할 수 있습니다. 그 방법은 아래와 같습니다.

$ mysql > SELECT *, price * quantity FROM sample34;

+------+-------+----------+------------------+
| no   | price | quantity | price * quantity |
+------+-------+----------+------------------+
|    1 |   100 |       10 |             1000 |
|    2 |   230 |       24 |             5520 |
|    3 |  1980 |        1 |             1980 |
+------+-------+----------+------------------+
3 rows in set (0.00 sec)

열의 별명

이때 열의 이름이 수식 그대로 price * quantity처럼 나오는 게 아닌 AS를 사용하여 원하는 대로 출력할 수 있습니다. 그 예시는 아래와 같습니다.

$ mysql > SELECT *, price * quantity AS amount FROM sample34;

+------+-------+----------+--------+
| no   | price | quantity | amount |
+------+-------+----------+--------+
|    1 |   100 |       10 |   1000 |
|    2 |   230 |       24 |   5520 |
|    3 |  1980 |        1 |   1980 |
+------+-------+----------+--------+
3 rows in set (0.00 sec)

이때 AS 키워드는 생략하여 SELECT *, price * quantity amount라고 써도 결괏값은 동일합니다. 또한 영어 뿐만 아니라 숫자, 한글 등으로 지정할 수 있으나 한글의 경우 오작동하는 경우를 방지하기 위해 더블쿼트("")로 둘러싸서 지정합니다. 이 룰은 데이터베이스 객체의 이름에 ASCII 문자 이외의 것을 사용할 경우에 해당합니다.

정보

ASCII는 영문 알파벳을 사용하는 대표적인 문자 인코딩입니다.

한글 인코딩의 종류로는 대표적으로 UTF-8 또는 EUC-KR이 있습니다.

이때 더블쿼트로 둘러싸면 명령구문을 분석할 때 데이터베이스 객체의 이름이라고 간주합니다. 반대로 싱글쿼트로 둘러싸면 문자열 상수입니다. 예약어와 같은 이름의 경우 기본적으로 객체의 이름으로 사용이 불가능하지만 더블쿼트로 둘러싸서 "SELECT"와 같은 형태로 사용 가능합니다. 예를 들어 SELECT price * quantity AS "SELECT" FROM sample34;와 같이 명령문을 입력하면 정상 작동합니다.

또한 이름을 붙일 때는 숫자로 시작할 수 없습니다.

주의

SELECT 구에서 콤마(,)를 사용하여 복수의 식을 지정할 수 있으며 각각의 식에 별명을 붙일 수 있씁니다.

MySQL에서는 별명이 중복되더라도 오류가 발생하지 않지만 프로그래밍 언어에서 결괎값을 처리하는 방식에 따라 문제가 발생할 수 있기 때문에 중복되지 않도록 지정해야 합니다.

WHERE 구에서 연산하기

WHERE 구에서도 동일하게 연산할 수 있습니다. 예를 들어 price*quantity의 결괏값이 2000 이상에 해당하는 행만을 검색하는 방법은 아래와 같습니다.

$ mysql > SELECT *, price * quantity AS amount FROM sample34 where price * quantity >= 2000;

+------+-------+----------+--------+
| no   | price | quantity | amount |
+------+-------+----------+--------+
|    2 |   230 |       24 |   5520 |
+------+-------+----------+--------+
1 row in set (0.00 sec)

이때 유의할 점은 SELECT 구에서 amount라는 별명을 붙였다고 WHERE에서도 WHERE amount >= 2000과 같이 사용하면 존재하지 않는 열이기 때문에 오류가 발생합니다.

WHERE 구와 SELECT 구의 내부처리 순서

데이터베이스 서버 내부에서 WHERE 구 다음으로 SELECT 구가 처리됩니다. 이는 표준 SQL에 정해진 사항은 아니지만 대부분의 데이터베이스가 그런 순서로 처리를 합니다.

해당 순서가 중요한 이유는 AS를 사용하여 별명을 붙일 때 발생합니다. ASSELECT 구문을 내부 처리할 때 비로소 처리됩니다. 따라서 WHERE 구는 SELECT 구보다 선행되므로 WHERE 구에서 사용한 별칭은 아직 내부적으로 지정되지 되어 오류가 발생하는 것입니다.

NULL 값의 연산

NULL 값에 NULL + 1과 같이 연산을 하면 SQL에서는 NULL 값을 0으로 치환하여 1을 결괏값으로 반환하는 것이 아닌 그대로 NULL 값을 반환합니다. 왜냐하면 SQL에서는 NULL은 유효한 값이 없는 상태이기 때문입니다.

이때 유의할 점은 보통 1 / 0 연산의 경우 0으로 나누려 했기 때문에 division by zero 오류를 반환합니다. 그러나 1 / NULL 연산의 경우 NULL0이 아니기 때문에 오류가 발생하지 않고 결괏값으로 NULL을 반환합니다.

ORDER BY 구에서 연산하기

ORDER BY 구에서도 연산할 수 있고 그 결괏값들을 정렬할 수 있습니다. 예를 들어 price*quantity 값의 결과를 기준으로 오름차순으로 정렬하는 방법은 아래와 같습니다.

$ mysql > SELECT *, price * quantity AS amount FROM sample34 ORDER by price * quantity DESC;

+------+-------+----------+--------+
| no   | price | quantity | amount |
+------+-------+----------+--------+
|    2 |   230 |       24 |   5520 |
|    3 |  1980 |        1 |   1980 |
|    1 |   100 |       10 |   1000 |
+------+-------+----------+--------+
3 rows in set (0.00 sec)

보시는 바와 같이 amount 값이 내림차순으로 정렬되었습니다. 이때 유의할 점은 ORDER BY는 서버에서 내부적으로 가장 나중에 처리되기 때문에 SELECT *, price * quantity AS amout FROM sample34 ORDER BY amount DESC;와 같이 SELECT 구에서 사용한 별명을 ORDER BY에서도 사용할 수 있습니다.

함수

연산자 외에도 함수를 사용하여 연산할 수 있습니다. 이때 함수는 계산 대상을 인수로 지정하며 이때 인수는 매개변수(Parameter)라고도 부릅니다. 그리고 함수를 통해 반환된 결괏값을 함수의 반환값이라 합니다.

ROUND 함수

예를 들어 아래와 같은 sample341 테이블이 존재한다고 가정해봅시다.

+---------+
| amount  |
+---------+
| 5961.60 |
| 2138.40 |
| 1080.00 |
+---------+

다음과 같이 ROUND 함수를 사용하여 반올림할 수 있습니다.

$ mysql > SELECT *, ROUND(amount) FROM sample341;

+---------+---------------+
| amount  | ROUND(amount) |
+---------+---------------+
| 5961.60 |          5962 |
| 2138.40 |          2138 |
| 1080.00 |          1080 |
+---------+---------------+
3 rows in set (0.00 sec)

정보

amount 열의 경우 INTERGER 형은 정수밖에 저장할 수 없기 때문에 DECIMAL 형으로 정의하였습니다. DECIMAL 형의 경우 정수부와 소수부의 자릿수를 지정할 수 있는 자료형입니다.

반올림 자릿수 지정

ROUND 함수는 기본적으로 소수점 첫째 자리를 기준으로 반올림한 값을 반환합니다. 두 번째 인수로 반올림할 자릿수를 지정할 수 있는데 이를 생략할 경우 0으로 간주되어 소수점 첫째 자리를 반올림합니다. 소수점 둘째 자리를 반올림하는 방법은 아래와 같습니다.

$ mysql > SELECT *, ROUND(amount, 1) FROM sample341;

+---------+------------------+
| amount  | ROUND(amount, 1) |
+---------+------------------+
| 5961.60 |           5961.6 |
| 2138.40 |           2138.4 |
| 1080.00 |           1080.0 |
+---------+------------------+
3 rows in set (0.00 sec)

또한 음스로 지정해 정수부의 반올림할 자릿수도 지정할 수 있습니다. 예를 들어 -1의 경우 1단위, -2의 경우 10단위를 반올림할 수 있습니다. 10단위를 반올림하는 방법은 아래와 같습니다.

$ mysql > SELECT *, ROUND(amount, -2) FROM sample341;

+---------+-------------------+
| amount  | ROUND(amount, -2) |
+---------+-------------------+
| 5961.60 |              6000 |
| 2138.40 |              2100 |
| 1080.00 |              1100 |
+---------+-------------------+
3 rows in set (0.00 sec)

반올림 외에도 TRUNCATE 함수를 통해 버림을 하거나 SIN, COS 등의 삼각함수 또는 SQRT와 같이 루트를 계산하는 등 여러 함수가 존재합니다.

문자열 연산

수치형처럼 문자열형 또한 연산이 가능합니다.

문자열 결합

문자열 데이터를 결합하는 연산입니다. 이때 유의할 점은 데이터베이스 제품마다 연산자가 다른데 아래와 같습니다.

연산자/함수 연산 데이터베이스
+ 문자열 결합 SQL Server
|| 문자열 결합 Oracle, DB2, PostgreSQL
CONCAT 문자열 결합 MySQL

아래와 같은 테이블 sample35가 존재한다고 가정해봅시다.

+------+-------+----------+------+
| no   | price | quantity | unit |
+------+-------+----------+------+
|    1 |   100 |       10 |    |
|    2 |   230 |       24 |    |
|    3 |  1980 |        1 |    |
+------+-------+----------+------+

quantity열과 unit열을 결합하는 방법은 아래와 같습니다.

$ mysql > SELECT CONCAT(quantity, unit) FROM sample35;

+------------------------+
| CONCAT(quantity, unit) |
+------------------------+
| 10개                   |
| 24통                   |
| 1장                    |
+------------------------+
3 rows in set (0.00 sec)

이때 유의할 점은 quantity 열은 INTEGER 형의 열입니다. 그러나 CONCAT 함수를 이용해 두 열을 결합할 경우 그 결과는 문자열형이 됩니다.

SUBSTRING 함수

SUBSTRING 함수의 경우 문자열의 일부분을 계산해서 반환해주는 함수입니다. 데이터베이스에 따라서는 함수명이 SUBSTR인 경우도 있습니다. 예를 들어 YYYYMMDD와 같은 형식의 문자열 데이터에서 연도만 추출하고 싶은 경우 SUBSTRING('20140125', 1, 4)와 같으며 그 결괏값은 '2014'입니다. 이때 두 번째 매개변수와 세 번째 매개변수는 곧 시작 인덱스 값과 끝 인덱스 값을 의미합니다.

TRIM 함수

TRIM 함수는 문자열의 앞뒤로 여분의 공백이 있을 경우 이를 제거해주는 함수입니다. 그러나 문자열 중간에 존재하는 공백은 제거되지 않습니다. 고정길이 문자열형, 다시 말해 CHAR 형의 문자열형에서 많이 사용됩니다. 예를 들어 TRIM('ABC ')의 경우 그 결괏값으로 공백이 제거되어 'ABC'가 반환됩니다. 이때 인수를 별도로 지정하면 스페이스 이외의 문자를 제거할 수 있습니다.

CHARACTER_LENGTH 함수

CHARACTER_LENGTH 함수의 경우 문자열의 길이를 계산해 돌려줍니다. VARCHAR 형의 경우 가변 길이이므로 길이가 서로 다르기 때문에 CHARACTER_LENGTH 함수를 통해 그 길이를 계산하고 대소를 비교할 수 있습니다. 이때 함수명은 CHAR_LENGTH와 같이 줄여서 사용가능합니다.

이와 유사하게 OCTET_LENGTH라는 함수도 존재합니다. OCTET_LENGTH 함수의 경우 문자열의 길이를 바이트 단위로 계산해 돌려줍니다.

이때 바이트(Byte)는 데이터 단위를 의미하며 SQL에서 문자 하나의 데이터가 몇 바이트의 저장공간을 필요로 하는 지는 인코드 방식에 따라 결정됩니다.

이때 유의할 점은 VARCHAR 형 또한 최대 길이 역시 바이트 단위로 지정하지만 문자세트(Character Set)에 따라 길이가 문자 수로 간주되기도 한다는 것입니다.

문자세트(Character Set)

인코드 방식은 데이터베이스나 테이블을 정의할 때 변경할 수 있습니다. 이를 RDBMS에서는 문자세트라 합니다. 예를 들어 CREATE DATABASE test CHARACTER SET utf8; 명령문은 문자세트를 UTF-8로 지정하여 test라는 데이터베이스를 만듭니다. 데이터베이스의 문자세트를 확인하면 다음과 같이 utf8인 것을 알 수 있습니다.

$ mysql > SHOW variables LIKE "character_set_database";

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.01 sec)

알파벳이나 숫자, 기호는 ASCII 인코드 방식을 사용하며 한글의 경우 주로 EUC-KR, UTF-8 방식을 사용합니다. OCTET_LENGTH 함수를 사용할 경우 바이트 단위로 길이를 계산하기 때문에 이러한 인코드 방식의 차이에 따라 값이 달라질 수 있기 때문에 주의해야 합니다.

예를 들어 EUC-KR 인코딩 방식과 UTF-8 인코딩 방식의 바이트 수 차이는 다음과 같습니다.

문자세트 문자수 바이트 수
EUC-KR 12 19
UTF-8 12 26

이렇게 차이가 발생하는 이유는 EUC-KR에서 ASCII 문자는 1바이트, 한글은 2바이트의 용량을 가지는 반면 UTF-8에서는 ASCII 문자는 동일하게 1바이트의 용량을 가지지만 한글은 3바이트의 용량을 가지기 때문입니다.

결론적으로 문자열 조작 함수로 문자 단위가 아닌 바이트 단위를 지정할 경우 문자세트에 주의해야합니다.

날짜 연산

날짜나 시간 데이터 또한 수치 데이터와 마찬가지로 사칙 연산을 수행할 수 있습니다. 이때 그 결괏값으로 동일한 날짜시간 유형의 데이터를 반환하는 경우도 있으며 그 기간(간격)의 차를 나타내는 기간형(Interval) 데이터를 반환하는 경우도 존재합니다.

SQL에서의 날짜

시스템 날짜

컴퓨터에는 반드시 시계가 내장되어 있습니다. 시스템 날짜란 이처럼 하드웨어 상의 시계로부터 실시간으로 얻을 수 있는 일시적인 데이터를 말합니다.

표준 SQL에서는 CURRENT_TIMESTAMP 함수를 실행했을 때를 기준으로 시간을 표시합니다. 예를 들어 다음과 같습니다.

$ mysql > SELECT CURRENT_TIMESTAMP;

+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2021-12-05 21:32:58 |
+---------------------+
1 row in set (0.00 sec)

이와 유사하게 Oracle에서는 SYSDATE 함수, SQL Server에서는 GETDATE 함수가 존재하여 이를 통해 시스템 날짜를 확인할 수 있지만 CURRENT_TIMESTAMP 함수가 표준 SQL이므로 이를 사용하는 게 좋습니다.

주의

위 예에서는 FROM 구를 생략했지만 Oracle과 같은 데이터베이스에서는 FROM 구를 생략할 수 없다는 것에 주의하시기 바랍니다.

날짜 서식

날짜 데이터를 데이터베이스에 저장할 때 CURRENT_TIMESTAMP를 사용해 시스템 상의 날짜를 저장할 수 있습니다. 다만 임의의 날짜를 저장하고 싶다면 직접 날짜 데이터를 지정해야 합니다.

이때 날짜 서식은 국가별로 다른데 해당 서식을 함수를 사용하여 임의로 지정, 변환할 수 있습니다. Oracle의 경우 TO_DATE 함수를 사용해 문자열 데이터를 날자형 데이터로 변환할 수 있습니다. 반대로 TO_CHAR 함수를 사용해 날짜형 데이터를 서식에 맞춰 문자열 데이터로 출력할 수도 있습니다.

날짜의 덧셈과 뺄셈

날짜시간형 데이터는 기간형 수치데이터와 덧셈 및 뺄셈을 할 수 있습니다. 앞서 CURRENT_TIMESTAMP 함수를 통해 얻게 된 시스템 날짜는 2021-12-05 21:32:58였습니다. INTERVAL를 통해 1일 후를 계산하는 방법은 다음과 같습니다.

$ mysql > SELECT CURRENT_TIMESTAMP + INTERVAL 1 DAY;

+------------------------------------+
| CURRENT_TIMESTAMP + INTERVAL 1 DAY |
+------------------------------------+
| 2021-12-06 21:39:29                |
+------------------------------------+
1 row in set (0.00 sec)

주의

INTERVAL 1 DAY는 1일 후를 의미하는 기간형 상수입니다. 이때 기간형 상수의 기술방법은 데이터베이스마다 조금씩 다르며 표준화가 이루어지지 않았기 때문에 주의해야 합니다.

날짜형 간의 뺄셈

날짜시간형 데이터 간에 뺄셈을 할 수 있습니다. 예를 들어 2021-12-062021-12-05 날짜 사이의 차이가 얼마나 발생하는지 계산하고 싶다면 DATEDIFF 함수를 사용하여 DATEDIFF('2021-12-06', '2021-12-05')와 같이 입력해 결괏값을 알 수 있습니다.

CASE 문으로 데이터 변환하기

임의의 조건에 따라 독자적으로 변환 처리를 지정해 데이터를 변환하고 싶은 경우 CASE 문을 이용할 수 있습니다. 대표적으로 NULL 값을 0으로 간주하여 계산하고 싶은 경우입니다.

CASE

아래와 같이 WHEN, THEN, ELSE 키워드를 이용하여 조건을 서술할 수 있습니다. WHEN 조건에 참이 되는 경우 THEN 절에 기술한 식이 처리되고 결과적으로 어떠한 조건도 만족하지 못하면 ELSE 절에 기술한 식이 처리됩니다. 이때 ELSE는 생략 가능하며 생략했을 경우 ELSE NULL로 간주됩니다.

CASE WHEN 조건식1 THEN 1
WHEN 조건식2 THEN 2
ELSE 3
END

아래와 같은 테이블 sample37이 존재한다고 가정해봅시다.

+------+
| a    |
+------+
|    1 |
|    2 |
| NULL |
+------+

a열의 값이 NULL일 때 0으로 출력하는 CASE 문은 다음과 같습니다.

$ mysql > SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END AS "a(null=0)" FROM sample37;

+------+-----------+
| a    | a(null=0) |
+------+-----------+
|    1 |         1 |
|    2 |         2 |
| NULL |         0 |
+------+-----------+
3 rows in set (0.00 sec)

COALESCE

NULL 값을 반환하는 경우에는 COALESCE 함수를 사용하는 게 더 간편합니다. 앞의 예시를 COALESCE 함수를 사용하면 다음과 같습니다.

$ mysql > SELECT a, COALESCE(a, 0) FROM sample37;

+------+----------------+
| a    | COALESCE(a, 0) |
+------+----------------+
|    1 |              1 |
|    2 |              2 |
| NULL |              0 |
+------+----------------+
3 rows in set (0.00 sec)

이때 COALESCE 함수는 여러 개의 인수를 지정할 수 있는데 주어진 인수 가운데 NULL이 아닌 값에 대해서는 가장 먼저 지정된 인수의 값을 반환합니다. 그래서 위 예시에서는 a를 반환하고 그렇지 않은 경우, 다시 말해 NULL인 경우 0을 반환했습니다.

또 하나의 CASE

숫자로 이루어진 코드를 알아보기 더 쉽게 문자열로 변환하고 싶은 경우에도 CASE 문을 많이 사용합니다. 이와 같이 문자화하는 것을 디코드(Decode), 반대로 수치화하는 것을 인코드(Encode)라 부릅니다.

예를 들어 위 sample37 테이블 a열의 값 중 1남자, 2여자, 끝으로 NULL은 미지정으로 결괏값을 반환하고 싶으면 CASE 문을 사용해 다음과 같이 입력하면 됩니다.

$ mysql > SELECT a, CASE WHEN a = 1 THEN '남자' WHEN a = 2 THEN '여자' ELSE '미지정' END AS "성별" FROM sample37;

+------+-----------+
| a    | 성별      |
+------+-----------+
|    1 | 남자      |
|    2 | 여자      |
| NULL | 미지정     |
+------+-----------+
3 rows in set (0.00 sec)

위와 같은 방식을 검색 CASE라 합니다. 반대로 CASE 뒤에 식을 기술하고 WHEN 뒤에 조건식이 아닌 식을 기술하는 CASE 문을 단순 CASE라하며 그 방법은 아래와 같습니다. 결괏값은 위 검색 CASE와 동일합니다.

$ mysql > SELECT a, CASE a WHEN 1 THEN '남자' WHEN 2 THEN '여자' ELSE '미지정' END AS "성별" FROM sample37;

+------+-----------+
| a    | 성별      |
+------+-----------+
|    1 | 남자      |
|    2 | 여자      |
| NULL | 미지정    |
+------+-----------+
3 rows in set (0.00 sec)

CASE를 사용할 경우 주의사항

CASE 문은 SELECT 구 뿐만 아니라 WHERE, ORDER BY 등 다양한 곳에서도 사용 가능합니다. 이때 유의해야 할 사항이 몇 가지 있습니다.

ELSE 생략

ELSE를 생략하면 ELSE NULL이 된다는 것에 유의해야 합니다.

WHEN에서 NULL 지정하기

단순 CASE에서는 WHEN 뒤에 1과 같이 상수값을 지정하는 경우가 많습니다. 이때 NULL 값을 판별하기 위해 WHEN NULL과 같이 조건식을 기술하면 정상적으로 처리되지 않습니다. 왜냐하면 비교 연산자(=)로는 NULL 값과 같은지 비교할 수 없기 때문입니다. 따라서 해당 조건식은 자동으로 넘어가져 ELSE에서 그 값이 처리됩니다. 예를 들어 아래와 같습니다.

$ mysql > SELECT a, CASE a WHEN 1 THEN '남자' WHEN 2 THEN '여자' WHEN NULL THEN 'NULL입니다.' ELSE '미지정' END AS "성별" FROM sample37;

+------+-----------+
| a    | 성별      |
+------+-----------+
|    1 | 남자      |
|    2 | 여자      |
| NULL | 미지정    |
+------+-----------+
3 rows in set (0.00 sec)

WHEN NULL THEN 'NULL입니다.' 부분을 통해 a열의 값이 NULL인 경우 'NULL입니다.'가 반환될 것으로 예상했으나 '미지정'이 반환되었습니다. 왜냐하면 비교 연산자(=)로 NULL 값을 비교할 수 없기 때문입니다. 이를 위해서는 IS NULL을 사용해야 합니다.

결론적으로 단순 CASE가 아닌 검색 CASE에서 IS NULL을 활용하여 NULL 값을 비교해야 합니다.

DECODE NVL

Orcle에서는 이러한 디코드를 위해 DECODE라는 함수가 내장되어 있습니다. 이는 CASE 문과 동일한 용도로 사용할 수 있습니다.

또한 NULL 값을 변환하는 함수도 Oracle에서는 NVL 함수, SQL Server에서는 ISNULL 함수로 존재합니다.

이러한 함수들은 특정 데이터베이스 제품에만 국한되어 있기 때문에 표준 SQL로 규정되어 있는 CASE 문과 COALESCE 함수를 사용하는 게 좋습니다.