SQL where절 처리법

SQL where절 처리법

이 번에는 이러한 SQL을 사용함에 있어 프로그램 코드와 SQL을 좀 더 효율적으로 만드는 방법을 설명해 보겠습니다.

최근의 여러 애플리케이션 프레임워크들에서 SQL은 프로그램과 분리가 되어 있습니다.

SQL 코드를 프로그램 코드 밖으로 분리시키는 이유는 먼저 프로그램 개발자와 초기 SQL 작성자가 다른 경우가 대부분이고 두번째는 SQL은 프로그램보다 잦은 변경이 예상되기 때문에 이를 프로그램 코드와 분리하면 SQL변경에 따른 프로그램 재컴파일등의 번거러운 작업을 줄일 수 있기 때문에 장점이 많다고 할 수 있습니다.

이렇게 SQL을 프로그램 코드와 분리하는 경우에도 where 절 처리 부분은 여전히 까다로운 점으로 남게 됩니다.

예를들어 보면,

먼저 검색조건이 있다고 가정하고 이 검색조건에는 날짜 필드 (date) 와 아이디 (id) 필드 두가지 조건이 있습니다. 이러한 검색 조건에 따른 SQL where 문 작성은 아마 다음과 같을 것입니다.

select * from table_name where date = ? and id = ?

문제는 date 또는 id에 아무런 값이 없을 경우가 있기 때문에 SQL을 수행하기 전에 프로그램에서는 date 또는 id 가 null 값인지를 확인해야 된다는 것입니다. 왜냐하면 where 문에 null 이 들어가게 되면 결과가 제대로 나오지 않기 때문입니다.

where date = null and id = 'aaa'  이렇게 될 경우 데이터베이스의 null 지원 여부에 따라 결과가 달라집니다.

이런 경우를 막아야 되기 때문에 SQL 문장을 수행하기 전에는 프로그램 코드에서 반드시 if date == null 등으로 확인을 해야 하고,  만약 date가 null 인 경우에는 where 문은 다음과 같이 바꿔서 수행 해 주어야 합니다.

where id = 'aaa'  

마찬가지로 id가 null 인 경우도 처리해 줘야 합니다.

where date='20091108'

그래서 2가지 검색조건을 처리해 주기 위해서는 결국 3가지 종류의 where 문이 필요하게 됩니다.

where date=? and id=?

where date=?

where id=?

각각의 상황에 맞게 where 문을 조립해줘야 되는 번거러운 코딩작업이 들어가야 된다는 것입니다.

문제는 검색조건이 좀 더 복잡한 경우 대단이 성가신 코딩 작업이 되지 않을 수 없다는 것입니다.

극단적인 예로 20개의 검색조건이 들어가는 경우를 한 번 생각해 보시면 가능한 조합이 얼마나 많겠습니까.

또한 위에서 언급한 SQL 코드가 프로그램 코드와 분리가 된 상황에서는 프로그래머가 느끼는 SQL 난이도는 엄청날 것입니다.

프로그램 코딩의 대부분의 시간이 이런 작업에 소요되는 현실이 많기 때문에 이에 대한 개선은 실무적으로도

대단히 중요한 의미가 있을 것입니다.

그래서 이러한 문제를 파악하고 개선의 방법을 생각해 보았는데 , 아래는 그에 대한 설명입니다.

기본적인 아이디어는 위의 예에서와 같은 검색의 경우라도 SQL 문은 하나만을 사용할 수 있는 방법은 없는가 하는 것입니다.

대부분의 데이터베이스에서는 ANSI SQL 외에도 Null Value 관련 내장 함수들을 가지고 있습니다.

데이터베이스에서 null 값을 체크하는 것이지요.

위의 두가지를 조합해 보니 아래와 같은 SQL 문장으로 해결이 될 것 같습니다.

select * from table_name where date = ifnull ( ? , date ) and id = ifnull ( ? , id )

위의 SQL에서 만약에 date 값에 null 이 오게 되면 "ifnull ( ? , date )" 부분이 date 가 됩니다.

그래서 SQL 수행시에는 아래와 같이 됩니다.

select * from table_name where date = date and id = '20091108'

여기서 date = date 부분이 항상 참이 되기 때문에 위의 문장은 아래의 문장과 동일한 결과가 나오게 됩니다.

select * from table_name where id = '20091108'

where 절에 들어가는 여러가지 경우를 생각해 보니 아래의 다섯가지 정도로 요약이 되는데 각각에 대한 설명을 해보겠습니다.

1. 지정문( = )

    WHERE a = ifnull( ? , a ) and b = ifnull( ? , b ) and c = ifnull( ? , c )

   a 에 null 이 들어오는 경우 where 문은 다음과 같다.

    WHERE a = a and b = 'b' and c = 'c'

   여기서 a = a 라는 부분은 항진으로 처리되므로

    WHERE b = 'b' and c = 'c' 과 동일한 결과를 가져온다. b , c 가 null 인 경우대해서도 결과는 동일하다.

2. BETWEEN a AND b

    WHERE sql_name between ifnull( ? , sql_name ) and ifnull( ? , sql_name )

    a = null 이 된다면

    where a between a and 'b' 이 된다.

 

3. 부등호 ( < , > )

    WHERE sql_name < ifnull( ? , 'field_max' )

    WHERE sql_name > ifnull( ? , 'field_min' )

4. LIKE

    WHERE sql_name like '%' || ifnull( ? , '' ) || '%'

5. IN

    WHERE sql_name IN ( ifnull( ? , 'sql_name' ) , .... )

where 문을 구성하는 요소는 위의 다섯가지 경우가 아마 대부분 일 것입니다.

그리고 이를 이용한다면 위에서 잠깐 나왔던 20개의 검색조건 SQL은 다음과 같이 where 문이 구성 될 수 있을 것이다.

where a1=ifnull(?,a1)

   and a2=ifnull(?,a2)

          .......

   and a20=ifnull(?,a20)

이젠 더이상 SQL where 문을 이리저리 조합하는 머리 아픈 코드는 없애도록 해봅시다.