SQL 조건식과 프로시저

1) CASE 문

CASE 문이란 흔히 타 프로그래밍 언어에서 IF/ELSE로 사용되는 조건문이다. 즉, 특정 조건을 충족했을 때 실행하도록 하는 구문이라고 볼 수 있다. CASE문을 사용하는 방법에는 두 가지가 있는데, 첫번째는 general CASE 방식, 두번째는 CASE expression 형태이다. 두 가지의 차이점에 대해서 차근차근 알아보자.

 

(1) general CASE 

먼저, general CASE 방식이다. 수도코드를 살펴보자.

CASE
	WHEN condition1 THEN result1
	WHEN condition2 THEN result2
	ELSE some_other_result
END

이해를 돕기 위해 예시를 들어 보겠다. 테이블에 x라는 이름을 가진 열이 있는데, 값으로 1, 2, 3, 4가 저장되어 있다고 해보자. 이때, CASE문을 사용해서 3보다 작은 수들은 3_under, 3인 수는 3_exact, 그 외의 수는 3_over 으로 standard_3라는 열로 저장하는 쿼리문을 작성한다고 해보자. 그렇다면 다음과 같이 적을 수 있다.

SELECT x,
CASE
	WHEN x < 3 THEN '3_under'
    WHEN x = 3 THEN '3_exact'
    ELSE '3_over' AS 'standard_3'
END
FROM table;

 

(2) CASE expression

다음은 CASE expression 방식을 살펴보자. CASE expression은 먼저 조건을 판별하고, 그 다음에 값을 순차적으로 비교한다. 따라서 어떤 값보다 작거나 크다의 조건을 판별하기에는 적절하지 않다. 그러나 어떤 값과 일치할 때 발생시켜야 할 구문이 있을 때에는 general CASE문의 방식보다 더 깔끔하다.

수도코드로는 다음과 같이 나타낼 수 있다.

CASE expression
	WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE some_other_result
END

이전 general CASE 방식과는 달리 CASE expression 방식으로는 이전 예제를 해결할 수 없다. 왜냐하면, CASE expression 방식으로는 어떤 값보다 작거나 크다는 조건을 판별하기에 적합하지 않기 때문이다. 그렇기에 다른 예제를 하나 들겠다.

테이블에 x라는 열이 있고, x의 값으로는 1, 2, 3, 4가 있다. 이때, x가 1과 같다면 one, x가 2와 같다면 two, 그 외에는 other 이라는 라벨을 붙힌다고 해보자. 그렇다면 다음과 같이 쿼리문을 작성할 수 있다.

SELECT x,
CASE x 
	WHEN 1 THEN 'one'
	WHEN 2 THEN 'two'
	ELSE 'other'
END
FROM table

상황에 따라서 각각의 적합한 CASE문 표현 방식을 사용하는 것이 좋겠다.

 

2) COALESCE

COALESCE 함수는 무수한 아규먼트 중에서 null이 아닌 첫번째 값을 반환하는 함수이다. 만약 모든 아규먼트의 값이 null이라면 함수는 null을 반환한다. 수도코드는 다음과 같다.

COALESCE(arg1, arg2, ...)

예제로도 확인해보자.

COALESCE(1, 2)
COALESCE(NULL, 3, 5)

위 코드의 첫번째 줄은 1을 반환하고, 두번째 줄은 3을 반환한다.

 

그렇다면 이 함수는 언제 유용하게 사용할 수 있을까. 

바로 null 값을 포함한 테이블에서 null 값을 다른 값으로 교체해야 하는 상황이 왔을 때 사용하면 유용하다.

예컨대, 상품 가격과 할인율에 대한 테이블을 다룬다고 해보자. 어떤 열들은 할인율이 존재하는 반면에, 할인을 하지 않는 품목에는 할인율로 null이 들어가있다고 해보자. 할인율을 계산한 상품의 값을 구하려고 할 때, null이 들어가 있는 열은 price * (1 - 0.01 * discount_rate)계산에 오류를 반환한다. 따라서, 이 null 값을 다른 값으로 변환해줘야 하는 상황이 발생한다. 이때, COALESCE 함수를 유용하게 사용할 수 있다.

 

한 번 위의 예시에서 COALESCE 함수를 사용해서 문제를 해결해보자.

SELECT item, (price * (1 - 0.01 * COALESCE(discount_rate, 0)) AS final FROM table

이렇게 사용하게 되면 만약 discount_rate이 null인 경우에는 0으로 값을 대체해주고, null이 아닌 경우에는 본래의 값을 그대로 사용할 수 있게 된다.

 

COALESCE 함수를 사용하면 원래의 표를 건드리지 않고도, 위의 상황과 같은 문제를 해결할 수 있도록 해준다. 그렇다면 왜 null 값을 그냥 0으로 바꾸지 않는걸까. 그 이유는 바로, null 값에게도 존재하는 의미가 있을 수 있기 때문이다. 예를 들어 할인율이 0인 것과 null인 것은 어느정도 다른 의미를 갖고 있다. 할인율이 null이라는 것은 할인 자체를 안하고 있다는 표시이기도 하기 때문에 할인율을 그저 0으로 표현하는 것과는 다른 의미를 가질 수 있다.

 

3) CAST

다음으로는 데이터의 자료형을 바꿔주는 CAST 연산자에 대해서 알아보자.

CAST 연산자는 데이터의 자료형을 바꿔주는 연산을 수행하지만, 모든 데이터가 다른 자료형으로 바뀔 수 있는 것은 아니다. CAST 변환을 하는데에는 합리적인 이유가 있어야 하고, 그것이 논리적으로 수행가능해야 한다. 예컨대, 문자 'three'를 정수형 3으로 바꾸는 것은 불가능하다.

 

그렇다면 이제 수도코드를 살펴보자. 두 가지 유형의 수도코드를 살펴볼 것인데 첫번째 수도코드는 일반적인 시퀄 구문이고, 두번째 수도코드는 postgreSQL에서 지원하는 형태이다.

(1) general SQL syntax

SELECT CAST('5' AS INTEGER)

(2) postgreSQL syntax

SELECT '5'::INTEGER

두 방식의 차이점은 CAST 연산자를 명시적으로 선언하냐 안하냐의 차이이다. 일반적인 시퀄 구문에서는 CAST() 안에 변환할 값을 집어넣는 반면에, postgreSQL 방식은 CAST문을 적지 않고, 변환하고자 하는 값을 적은 후 콜론(:)을 두 번 적고 변환할 타입을 적는다. 

물론 당연히 특정 값 뿐만 아니라 특정한 열 전체를 형변환하는 것도 가능하다. 그때는 값 대신에 열의 이름을 넣는다.

 

4) NULLIF

NULLIF 함수는 두 값을 받아, 두 값이 일치하면 NULL을 반환하고, 일치하지 않으면 두 값 중에 앞에 있는 값을 반환한다. 수도코드는 다음과 같다.

NULLIF(arg1, arg2)

NULLIF 함수는 NULL값이 에러를 발생시킬 수 있는 구문에서 유용하게 사용된다. 예컨대, 두 숫자를 나눠야 하는 상황을 생각해보자. 수학에서는 0으로 다른 수를 나눌 수 없다. 그렇기 때문에 만약 나누는 수가 0이면 division by zero라는 오류를 반환한다. 이러한 문제를 해결하기 위해서 NULLIF문을 사용할 수 있다. 나뉘는 수를 a, 나누는 수를 b라고 할 때 다음과 같이 적을 수 있다. 

a / NULLIF(b, 0)

'a를 b로 나누는데, 만약 b가 0과 같다면 NULL을 반환하라' 라는 의미이다. NULL이 반환되면 그 계산은 에러가 나지 않고 무조건적으로 결과가 NULL이 되므로 오류를 예방할 수 있다.

 

5) VIEW

쿼리문을 작성하다보면 특정 테이블과 특정 열들의 조합을 자주 사용하게 될 때가 있다. 이럴 때 사용할 수 있는 것이 바로 VIEWS다. VIEW를 이용하면 똑같은 쿼리를 계속해서 다시 작성할 필요가 없고, 간단한 호출로 확인할 수 있게 된다.

 

VIEW는 데이터베이스 객체로, 저장된 쿼리이다. 그리고 가상 테이블로 접근할 수 있다. VIEW는 새로 테이블을 저장하는 것이 아니라, 단순 쿼리를 저장한다는 것을 알아두어야 한다. 또한 이미 존재하는 뷰들을 바꾸거나, 업데이트할 수도 있다.

 

그렇다면 뷰를 만드는 법에 대해서 알아보자. 수도코드는 다음과 같다. 

CREATE VIEW view_name AS
SELECT * FROM table1
INNER JOIN table2
ON table1.col1 = table2.col2

이 저장된 뷰를 보려면 일반 테이블에서 열을 호출하는 것처럼 하면 된다.

SELECT * FROM view_name

view나 혹은 기저의 쿼리를 바꿀 때에는 CREATE OR REPLACE VIEW 명령어를 사용한다.

CREATE OR REPLACE VIEW view_name AS

위와 같이 적고 아래에다가는 쿼리 히스토리에서 이전에 view를 만들 때 사용했던 쿼리를 찾아서 붙여넣고, 수정할 내용을 수정한다. 결국 다음과 같은 형식이 된다.

CREATE OR REPLACE VIEW view_name AS
SELECT col1, col2, col3 FROM table1
INNER JOIN table2
ON table1.col1 = table2.col2

 

뷰를 없애는 방식은 다음과 같다.

DROP VIEW IF EXISTS view_name

IF EXISTS를 써주는 이유는 혹시 해당하는 뷰가 존재하지 않을 때 에러를 발생시키지 않기 위해서, 만약 뷰가 존재할 때 그 뷰를 삭제하라는 의미로 적어준다.

 

뷰의 이름을 바꿀 때에는 이렇게 적을 수 있다.

ALTER VIEW view_name RENAME TO new_name