[SQL] 정렬과 연산

SQL 첫걸음

위 책으로 공부한 내용을 정리. 

---

2. 정렬과 연산

 

 

1. 정렬 - ORDER BY

select 명령의 order by 구를 사용해 검색결과의 행 순서를 바꿀 수 있다.

select 열명 from 테이블명 where 조건식 order by 열명

order by 구를 지정하지 않을 경우 데이터베이스내부에 저장된 순서로 반환이 된다. 언제나 정해진 순서로 결괏값을 얻기 위해서는 order by 구를 지정해야한다.

 

1. ORDER BY로 검색결과 정렬하기

SELECT 명령의 ORDER BY 구로 정렬하고 싶은 열을 지정한다. 지정된 열의 값에 따라 행순서가 변경된다. 이때 ORDER BY 구는 WHERE 구 뒤에 지정한다.

SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명

검색조건이 필요없는 경우 WHERE구를 생략하는데 이때 ORDER BY 구는 FROM 구의 뒤에 저장

SELECT 열명 FROM 테이블명 ORDER BY 열명

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

순서를 바꿀 수 있다.

mysql> select * from sample31 order by age;
+------+------+-------------------+
| name | age  | address           |
+------+------+-------------------+
| B씨  |   18 | 부산광역시 연제구 |
| C씨  |   25 | 서울특별시 중구   |
| A씨  |   36 | 대구광역시 중구   |
+------+------+-------------------+
3 rows in set (0.01 sec)

 

2. order by 내림차순으로 정렬

select 열명 from 테이블명 order by 열명 desc

오름차순은 내림차순과 달리 생략 가능하고, ASC로 지정하기도 한다.

mysql> select * from sample31 order by age desc;
+------+------+-------------------+
| name | age  | address           |
+------+------+-------------------+
| A씨  |   36 | 대구광역시 중구   |
| C씨  |   25 | 서울특별시 중구   |
| B씨  |   18 | 부산광역시 연제구 |
+------+------+-------------------+
3 rows in set (0.00 sec)

 

3. 대소관계

order by 로 정렬할때는 값의 대소관계가 중요하다. 수치형 데이터라면 대소관계는 숫자의 크기로 판별하므로 이해하기 쉽다. 날짜 시간형 데이터도 수치형 데이터와 마찬가지로 숫자 크기로 판단이 된다. 문제는 문자열형 데이터이다. 알파벳, 한글 순으로 나열이 되며 사전식 순서에 의해 결정이 된다.

💡 문자열형 데이터의 대소관계는 사전식 순서에 의해 결정

 

사전식 순서에서 주의할 점

mysql> select * from sample311;
+------+------+
| a    | b    |
+------+------+
| 1    |    1 |
| 2    |    2 |
| 10   |   10 |
| 11   |   11 |
+------+------+
4 rows in set (0.00 sec)

sample311은 a열이 문자열형으로 b열이 수치형으로 이루어진 테이블이다. 각 행의 열은 동일한 값으로 지정이 되어있다. 여기서 a열을 오름차순으로 정렬해보겠다.

mysql> select * from sample311 order by a;
+------+------+
| a    | b    |
+------+------+
| 1    |    1 |
| 10   |   10 |
| 11   |   11 |
| 2    |    2 |
+------+------+
4 rows in set (0.01 sec)

몬가 이상!!...😭 a열이 문자열 형으로 되어있어 대소관계를 사전식 순서로 비교했기 때문에 발생하는 문제이다.

💡 수치형 문자열형 데이터는 대소관계의 계산 방법이 다르다.

 

4. order by 는 테이블에 영향을 주지 않는다.

서버에서 클라이언트로 행 순서를 바꾸어 결과를 반환하는거지 저장장치에 저장된 데이터의 행 순서를 변경하는 것은 아님.

 

 

2. 복수의 열을 지정해 정렬

 

SELECT 열명 FROM 테이블명 WHERE 조건식
ORDER BY 열명1 [ASCIDESC], 열명2 [ASCIDESC].....

1. 복수열로 정렬 지정

ORDER BY로 행을 정렬하는 경우 같은 값을 가진 행의 순서는 어떻게 정해지는걸까? 또 ORDER BY는 생략할 수 있는데 이때 순서는 어떻게 정해질까?

 

'순서는 일정하지 않다'

 

데이터베이스 서버의 당시 상황에 따라 어떤 순서로 행을 반환할지 결정한다. 따라서 언제나 같은 순서로 결과를 얻고 싶다면 반드시 ORDER BY로 순서를 지정해야한다.

mysql> SELECT * FROM SAMPLE32;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    2 |    2 |
|    1 |    3 |
|    1 |    2 |
+------+------+
5 rows in set (0.01 sec)

a,b 열 모두 INT형.

a열만 정렬하려고 한다.

mysql> SELECT * FROM sample32 order by a;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    3 |
|    1 |    2 |
|    2 |    1 |
|    2 |    2 |
+------+------+
5 rows in set (0.00 sec)

a열은 정렬이 되었으나 b열까지는 어떻게 할 수가 없네.. 그럼 어떻게 해야 할까?

 

order by로 복수 열 지정

select 열명 from 테이블명 order by 열명1,열명2....

지정한 열명의 순서를 따른다.

mysql> select * from sample32 order by a,b;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    2 |    1 |
|    2 |    2 |
+------+------+

💡 order by 구에 복수의 열을 지정할 수 있다.

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)

 

2. 정렬방법 지정하기

복수열을 지정한 경우에서 각 열에 대해 개별적으로 정렬방법을 지정할 수 있다. 이대는 각 열 뒤에 ASC나 DESC를 붙여준다.

mysql> select * from sample32 order by a ASC,b DESC;
+------+------+
| a    | b    |
+------+------+
|    1 |    3 |
|    1 |    2 |
|    1 |    1 |
|    2 |    2 |
|    2 |    1 |
+------+------+
5 rows in set (0.00 sec)

a열은 오름차순으로 b열은 내림차순으로 정렬되었다.

order by a DESC,b

일경우 b가 오름차순으로 될지 내림차순으로 될지 모른다.. 그러니까 꼭 정렬방법을 지정해줘야 한다.

3. NULL 값의 정렬 순서

특성상 대소 비교를 할 수 없어 정렬 시에는 별도의 방법으로 취급한다. 이때 특정 값보다 큰값 특정 값보다 작은 값 두 가지로 나뉘며 이중 하나의 방법으로 대소를 비교한다.

order by로 지정한 열에서 null값을 가지는 행은 가장 먼저 표시되거나 가장 나중에 표시된다. NULL의 대소비교 방법은 표준 SQL에도 규정되어있지 않아서 데베 제품에 따라 기준이 다르다.

 

3. 결과 행 제한하기 - LIMIT

select 열명 from 테이블명 limit 행수 [offset 시작행]

인터넷 쇼핑몰에서 한 페이지에 모든 상품을 몰아넣지 않고 페이지에 나눠 분배하는 것 처럼 limit 구를 사용해서 표시할 건수를 제한할 수 있다.

1. 행수 제한

💥 limit 구는 SQL의 표준은 아니다. mysql과 postgresql에서 사용할 수 있는 문법!!

limit 구는 select 명령의 마지막에 지정하는 것으로 where구나 order by구의 뒤에 지정한다.

select 열명 from 테이블명 where 조건식 order by 열명 limit 행수

mysql> select * from sample33;
+------+
| no   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+
7 rows in set (0.01 sec)

3행만 보이게 제한

mysql> select * from sample33 limit 3;
+------+
| no   |
+------+
|    1 |
|    2 |
|    3 |
+------+

정렬한 후 제한하기

where구에 조건을 지정할 수도 있다. 예를들어 where n≤3으로 조건붙이면 위 코드 출력값과 같다. 하지만 limit과 where은 기능과 내부처리순서가 전혀 다르다. limit은 반환할 행수를 제한하는 기능. where구로 검색한 후 order by로 정렬된 뒤 최종적으로 처리된다.

• sample33을 정렬 후 limit 3으로 상위 3건만 취득

mysql> select * from sample33 order by no desc limit 3;
+------+
| no   |
+------+
|    7 |
|    6 |
|    5 |
+------+

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

표준이 아니라서 모든 sql 데이터베이스에서 사용할 수 없다. 그래서 sql server에서는 limit과 비슷한 기능을 하는 top을 사용할 수 있다.

select top 3 * from sample 33;

oracle에는 limit도 없고 top도 없다. 그래서 rownum이라는 열을 사용해 where 구로 조건을 지정해 행을 제한할 수 있다.

SELECT * FROM SAMPLE33 WHERE ROWNUM<=3;

ROWNUM은 클라이언트에게 결과가 반환될때 각 행에 할당되는 행 번호이다.

2. 오프셋 지정

쇼핑몰의 페이지 나누기 기능을 LIMIT으로 간단히 구현할 수 있다. 한 페이지 당 5건의 데이터를 표시하도록 한다면 첫번째 페이지의 경우 LIMIT 5로 결괏값을 표시하면 된다.

그다음 페이지에서는 6번째 행부터 5건의 데이터를 표시하도록 한다. 이때 6번째 행부터라는 표현은 결괏값으로 부터 데이터를 취득할 위치를 가리키는 것으로 LIMIT구의 OFFSET으로 지정할 수 있다.

한페이지당 3건식 총 3개의 페이지를 만들겠다.

mysql> SELECT * FROM sample33 limit 3 offset 0;
+------+
| no   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

첫번째 행부터 세번째 행까지 표시되었다. limit 3으로 했을 때와 같은 결과이다. limit 구의 offset은 생략가능하며 기본값은 0이다.

select 열명 from 테이블명 limit 행수 offset 위치

두번째 페이지 만들기

mysql> select * from sample33 limit 3 offset 3;
+------+
| no   |
+------+
|    4 |
|    5 |
|    6 |
+------+

 

4. 수치연산

 💡 +-*/%MOD

 

1. 사칙연산

• + : 덧셈.
• -  : 뺄셈
• *  : 곱셈
• / : 나눗셈
• % : 나머지

연산자의 우선순위

1 : * / %

2 : +-

2. select 구로 연산하기

select 식1,식2... from 테이블명

• sample34

mysql> select * from sample34;
+------+-------+----------+
| no   | price | quantity |
+------+-------+----------+
|    1 |   100 |       10 |
|    2 |   230 |       24 |
|    3 |  1980 |        1 |
+------+-------+----------+
3 rows in set (0.01 sec)

이걸로 가격과 수량으로 금액을 계산하는 경우를 생각해보자.

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)

 

3. 열의 별명

price*quantity 와 같이 열 이름이 길고 알아보기 어려운 경우는 별명을 붙여 열명을 재지정할 수 있다.

mysql> select *,price*quantity AS amount from sample34;
+------+-------+----------+--------+
| no   | price | quantity | amount |
+------+-------+----------+--------+
|    1 |   100 |       10 |   1000 |
|    2 |   230 |       24 |   5520 |
|    3 |  1980 |        1 |   1980 |
+------+-------+----------+--------+

별명은 예약어 AS를 사용해 지정한다. 별명을 중복해서 지정해도 에러는 없지만 결과값의 처리방식에 따라 문제가 발생하기 때문에 기본적으로 중복되지 않도록 지정한다. 키워드 AS는 생략할 수 있다.

select *,price*quantity amount from sample34;

라고 써도 무방.

단 별명으로 한글로 지정하는 경우. 여러가지로 오작동하는 경우가 많아서 ''로 둘러싸서 지정.

💡 이름에 ascii문자 이외의 것을 포함할 경우는 더블쿼츠로 둘러싸서 지정.

 

예약어와 같은 이름은 지정할 수 없지만 더블쿼츠(')로 둘러싸서 지정하면 사용할 수 있다. 예를 들면 select는 예약어이므로

select price*quantity as select from sample34;

이때 별명 select를 더블쿼츠로 둘러싸면 문제없이 사용가능

select price*quantity as "select" from sample34;
+--------+
| select |
+--------+
|   1000 |
|   5520 |
|   1980 |
+--------+
3 rows in set (0.00 sec)

💡 이름을 지정하는 경우 숫자로 시작되지 않도록 주의

 

4. WHERE구에서 연산

sample34 테이블을 사용해 가격*수량으로 금액을 계산해 2000원 이상에 해당하는 행을 검색해보겠다.

mysql> select *,price*quantity as amount from sample34
    -> where price*quantity>=2000;
+------+-------+----------+--------+
| no   | price | quantity | amount |
+------+-------+----------+--------+
|    2 |   230 |       24 |   5520 |
+------+-------+----------+--------+

근데 이건 왜 안될까?

mysql> select *,price*quantity as amount from sample34
    -> where amount>=2000;
ERROR 1054 (42S22): Unknown column 'amount' in 'where clause'

 

where 구와 select 구의 내부 처리순서

where구→ select 구 순서로 처리.

💡 
⭐⭐⭐⭐⭐중요⭐⭐⭐⭐⭐
select 구에서 지정한 별명은 where 구 안에서 사용할 수 없다.

 

5. NULL 값의 연산

sql에서는 null+1=null

💡 null로 연산하면 결과는 null이 된다.!!

 

6. order by 구에서 연산하기

order by구에서도 연산할 수 있고 그 결괏값들을 정렬할 수 있다~!! sample34 테이블을 사용해 구체적인 예를 들어 설명하겠다. 가격*수량으로 금액을 계산해 값이 큰 순서대로 정렬해보겠다.

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)

💡 ⭐⭐⭐⭐⭐중요⭐⭐⭐⭐⭐
 order by 구에서는 select 구에서 지정한 별명을 사용할 수 있다.

 

 

order by 구에서는 amount라고 지정해준 컬럼 이름을 사용해서 정렬할 수 있다.

select *,price*quantity as amount from sample34 order by amount;

 

7. 함수

함수명(인수1,인수2...)

💡 10%3→1 MOD(10,3)→1

 

8. round 함수

반올림할때 사용하는 것이다.

mysql> select * from sample341;
+---------+
| amount  |
+---------+
| 5961.60 |
| 2138.40 |
| 1080.00 |
+---------+
3 rows in set (0.01 sec)

sample341 데이터를 사용할 것이다.

mysql> select amount, round(amount) from sample341;
+---------+---------------+
| amount  | round(amount) |
+---------+---------------+
| 5961.60 |          5962 |
| 2138.40 |          2138 |
| 1080.00 |          1080 |
+---------+---------------+
3 rows in set (0.00 sec)

amount열은 Decimal 형으로 정의했다. decimal 형은 열을 정의할 때 정수부와 소수부의 자릿수를 지정할 수 있는 자료형이다.

 

반올림 자릿수 지정

round 함수는 기본적으로 소수점 첫째 자리를 기준으로 반올림한 값을 반환한다.

이제 round함수의 두번째 인수로 반올림할 자릿수를 지정할 수 있다. 해당 인수를 생략하는 경우는 0으로 간주되어, 소수점 첫째 자리를 반올림한다. 1을 지정하면 소수점 둘째자리를 반올림한다.

mysql> select amount, 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)

해당인수를 생략하는 경우는 0으로 간주되어 소수점 첫째자리를 반올림한다. 1을 지정하면 소수점 둘째 자리를 반올림한다.

• 0 : 소수점 첫째 자리
• 1 : 소수점 둘째 자리

음수로 지정해 정수부의 반올림할 자릿수도 지정할 수 있다. -1을 지정하면 1단위, -2를 지정하면 10단위를 반올림할 수 있다. 그 밖에도 반올림 외에 버림을 하는 경우도 있는데 이는 truncate함수로 계산할 수 있다.

• 10단위를 반올림하기

mysql> select amount,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)

 

5. 문자열 연산

• 문자열 연산;

+
||
concat
substring
trim
character_length

 

1. 문자열 결합

문자열 데이터를 결합하는 연산

ex)

'ABC' || '1234' → 'ABC1234'

문자열을 결합하는 연산자에는 데이터 베이스 제품마다 방언이 있으며 다음과 같은 차이를 가진다.

+ 문자열 결합 SQL server

+	문자열 결합	SQL server
concat	문자열 결합	MySQL
||	문자열 결합	oracle, DB2, PostgreSQL

SQL server는 문자열 결합에 + 연산자를 사용한다. oracle이나 DB2, postgreSQL에서는 || 연산자를 사용해서 문자열을 결합한다. MySQL에서는 CONCAT 함수로 문자열을 결합

🍏 + 연산자, || 연산자, CONCAT 함수로 문자열을 결합할 수 있음

 

mysql> select * from sample35;
+------+-------+----------+------+
| no   | price | quantity | unit |
+------+-------+----------+------+
|    1 |   100 |       10 | 개   |
|    2 |   230 |       24 | 통   |
|    3 |  1980 |        1 | 장   |
+------+-------+----------+------+
3 rows in set (0.01 sec)

mysql> select concat(quantity,unit) from sample35;
+-----------------------+
| concat(quantity,unit) |
+-----------------------+
| 10개                  |
| 24통                  |
| 1장                   |
+-----------------------+
3 rows in set (0.00 sec)

quantity는 integer형의 열이고, unit열은 문자열형이다. 문자열 결합이지만 수치데이터도 문제없이 연산할 수있다. 단 문자열로 결합한 결과는 문자열형이 된다.

• oracle에서는 quantity||unit 로 결합
• sql server에서는 quantity+unit 로 결합.

2. Substring 함수

substring 함수는 문자열의 일부분을 계산해서 반환해주는 함수이다. 데이터베이스에 따라서는 함수명이 substr인 경우도 있다.

블로그 시스템 등에서는 사용자가 업로드한 데이터를 특정지을 수 있도록 날짜와 연속된 번호를 사용해 유일한 ID를 지정해주는 경우가 많다. 이러한 ID를 하나의 열에 저장하는 경우도 있지만 보통 다루기 쉽도록 날짜와 연속된 번호라는 두개의 열로 나누는 경우가 많다.

또한 연월일을 YYYYMMDD와 같은 형식의 문자열 데이터로 저장하는 경우도 아주 많다. 해당 날짜 데이터에서 년, 월,일을 각각 따로 추출해내고 싶은 경우가 있을 것이다. 이런 경우 substring 함수를 이용해 간편하게 문자열을 추출해낼 수 있다.

• 앞 4자리(년) 추출

substring('20140125001',1,4)-> '2014'

• 앞 5자리부터 2자리(월) 추출

substring('20170125001',5,2)->'01'

3. trim 함수

trim 함수는 문자열의 앞뒤로 여분의 스페이스가 있을 경우 이를 제거해주는 함수로 문자열 도중에 존재하는 스페이스는 제거되지 않는다. 고정길이 문자열형에 대해 많이 사용하는 함수이다.

앞에서 설명했듯이 char 형의 문자열형에서는 문자열은 길이가 고정되며 남은 공간은 스페이스로 채워진다. 한편 인수를 지정하는 것으로 스페이스 이외의 문자를 제거할 수도 있다.

trim('ABC    ')->'ABC'

4. character_length 함수

character_length 함수는 문자열의 길이를 계산해 돌려주는 함수.

char_length로 줄여서 사용할 수 있다.

💡 문자열 데이터의 길이는 문자세트에 따라 다르다.

 

6. 날짜연산

 

• 날짜연산

current_timestamp
current_date interval

날짜는 date 형, 시간은 time 형, 날짜와 시간은 datetime 형과 같이 세분화해 지원하는 데이터베이스 제품도 있다.

1. sql에서의 날짜

날짜나 시간 데이터는 수치 데이터와 같이 사칙 연산을 할 수 있다. 날짜 시간 데이터를 연산하면 결괏값으로 동일한 날짜 시간 유형의 데이터를 반환하는 경우도 있으며 기간의 차를 나타내는 기간형(interval) 데이터를 반환하는 경우도 있다.

기간형은 10일간, 2시간 10분과 같이 시간의 간격을 표현할 수있다.

• 시스템 날짜

시스템 날짜를 확인하는 방법

표준 SQL에서는 current_timestamp라는 긴 이름의 함수로 실행했을 때를 기준으로 시간을 표시한다. 일반적인 함수와는 달리 인수를 지정할 필요가 없으므로 괄호를 사용하지 않는 특수한 함수이다.

시스템 날짜 확인하기

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2021-11-05 01:38:50 |
+---------------------+
1 row in set (0.00 sec)

current_timestamp는 표준 sql로 규정되어있는 함수이다. oracle에서는 sysdate함수, sql server에서는 getdate 함수를 사용해도 시스템 날짜를 확인할 슁ㅆ다. 이들은 표준화되기전에 구현된 함수인만큼 사용하지 않는게 좋다.

• 날짜 서식

날짜 데이터를 데이터베이스에 저장할 경우 current_timestamp를 사용해 시스템 상의 날짜를 저장할 수 있다. 다만 임의이 날짜를 저장하고 싶을 경우에는 직접 날짜 데이터를 지정해야한다.

데이터베이스는 날짜 데이터의 서식을 임의로 지정, 변환할 수 있는 함수를 지원한다.

to_date('2014/01/05','yyyy/mm/dd')

💡 날짜 데이터는 서식을 지정할 수 있다.

 

2. 날짜의 덧셈과 뺄셈

날짜시간형 데이터는 기간형 수치데이터와 덧셈 및 뺼셈을 할 수있다. 날짜 시간형 데이터에 기간형 수치데이터를 더하거나 빼면 날짜 시간형 데이터가 반환된다.

특정일로부터 1일 후 : a+1day

특정일로부터 1일 전 : a-1day

select current_date + interval 1 day;

시스템 날짜의 1일 후를 계산하기

mysql> select current_date+interval 1 day;
+-----------------------------+
| current_date+interval 1 day |
+-----------------------------+
| 2021-11-06                  |
+-----------------------------+
1 row in set (0.00 sec)

interval 1 day 는 1일 후 라는 의미의 기간형 상수이다.

• 날짜형 간의 뺼셈

날짜 시간형 데이터 간의 뱰셈을 할 수 있다.

mysql : datediff('2014-02-28'-'2014-01-01')로 계산

 

 

7. CASE 문으로 데이터 변환

 

CASE 문을 이용해 데이터를 변환할 수있다.

case when 조건식1 then 식1
 [when 조건식2 then 식2...]
 [else 식3 ]
end

임의의 조건에 따라 독자적으로 변환 처리를 지정해 데이터를 변화낳고 싶은 경우 case문을 이용할 수 있다.

1. case 문

when 절에는 참과 거짓을 반환하는 조건식을 기술한다. 해당 조건을 만족하여 참이 되는 경우는 then 절에 기술한 식이 처리된다. 이때 when과 when을 한데 조합해 지정할 수 있다.

mysql> select a from sample37;
+------+
| a    |
+------+
|    1 |
|    2 |
| NULL |
+------+
3 rows in set (0.02 sec)

when절의 조건식을 차례로 평가해 나가다가 가장 먼저 조건을 만족한 when절과 대응하는 then 절 식의 처리결과를 case 문의 결괏값으로 반환한다. 그 어떤 조건식도 만족하지 못한 경우에는 else 절에 기술한 식이 채택된다. else는 생략 가능하며 생략했을 경우 else null로 간주된다.

mysql> select a,case when a is null then 0 else a end "a(null=0)" from sample37;
+------+-----------+
| a    | a(null=0) |
+------+-----------+
|    1 |         1 |
|    2 |         2 |
| NULL |         0 |
+------+-----------+
3 rows in set (0.00 sec)

a열 값이 null일 때 when a is null은 참이 되므로 case 문은 then 절의 '0'을 반환한다. null이 아닌 경우에는 else 절의 'a' 즉 a열의 값을 반환한다.

coalesce

NULL 값을 변환하는 경우라면 coalesece 함수를 사용하는게 더 쉽다. 앞의 select 명령 예제를 coalesce 함수를 사용해 구현하면 다음과 같다. 앞의 select 명령 예제를 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이 아니면 a값을 그대로 출력하고 그렇지 않으면 0을 출력

 

2. 또 하나의 case문

숫자로 이루어진 코드를 알아보기 더 쉽게 문자열로 변환하고 싶은 경우 case문을 많이 사용한다. 예를들어 '1은 남자/2는 여자'라는 코드체계가 있다면 이를 모르는 사람에게는 1/2로 표시하는 것보다 남자/여자 라고 표시하는게 알아보기 쉬울 것이다.

이와같이 문자화하는 것을 디코드라고 하고 반대로 수치화하는 것을 인코드라 부른다.

when a=1 then '남자'
when a=2 then '여자'

💡 case문에는 2개의 구문이 있다.

 

case문은 검색 case와 단순 case의 두개 구문으로 나눌 수 있다. 검색 case는 앞서 설명한 case when 조건식, when 식 구문이다. 한편 단순 case는 case 식 when식, then 식.. 구문

case 식1
 when 식2 then 식3
 [when 식4 then 식5...]
 [else 식6]
end

식1의 값이 when의 식2값과 동일하지 비교하고 값이 같다면 식3의 값이 case문 전체의 결괏값이 된다. 값이 같지 않으면 그 뒤에 기술한 when 절과 비교하는 식으로 진행이 된다.

즉 식1의 값과 식4의 값이 같은지를 비교하고 같다면 식4의 값이 case문의 결괏값이 되는 것이다. 비교 결과 일치하는 when절이 하나도 없는 경우에는 else 절이 적용된다.

• 성별 코드 변환하기 검색 case

select a as "코드",
case
when a=1 then "남자"
when a=2 then "여자"
else "미지정"
end 
as "성별" from sample37;

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

• 성별 코드 변환하기 단순 case

case 뒤에는 대상을 적는다. when 뒤에는 값만 적는다.

mysql> select a as "코드",
    -> case a
    -> when 1 then '남자'
    -> when 2 then '여자'
    -> else '미지정'
    -> end as '성별' from sample37;
+------+--------+
| 코드 | 성별   |
+------+--------+
|    1 | 남자   |
|    2 | 여자   |
| NULL | 미지정 |
+------+--------+
3 rows in set (0.00 sec)

 

3. case를 사용할 경우 주의사항

case문은 어디에나 사용가능. where 구에서 조건식의 일부로 사용가능. order by나 select 구에서도 사용가능하다.

else 생략 시 주의

else 를 생략하면 else null이 되는 것에 주의하자. 대응하는 when이 하나도 없으면 else 절이 사용된다. 왠만하면 else를 생략하지 않고 지정하는 편이 낫다.

💡 case문의 else는 생략하지 않는 편이 낫다

 

when에 null 지정하기

데이터가 null인 경우를 고려해 when null then '데이터 없음'과 같이 지정해도 문법적으로는 문제가 없지만 정상적으로 처리되지 않는다.

mysql> case a
    -> when 1 then '남자'
    -> when 2 then '여자'
    -> when null then '데이터 없음'
    -> else '미지정'
    -> end

이 예제에서는

1. a=1
2. a=2
3. a=NULL

과 같은 순서로 조건식을 처리한다. 비교연산자 = 로는 null 값과 같은지 아닌지를 비교할 수 없다. 따라서 a 열의 값이 null이라고 해도 a=null 은 참이 되지 않는다. 즉 '데이터없음'은 대신 '미지정'이라는 결괏값이 나온다. 단순 case문으로는 null을 비교할 수 없다는 문제점이 있다.

이때 null값인지 아닌지를 판정하기 위해서는 is null을 사용한다. 다만 null값인지를 판정하려면 검색 case문을 사용해야한다.

• 검색 case문으로 null 판정하기

-> case
    -> when a=1 then '남자'
    -> when a=2 then '여자'
    -> when a is null then '데이터없음'
    -> else '미지정'
    -> end

💡 단순 case 문으로는 null값을 비교할 수 없다.