동적 검색 조건

4. 응용 변형 예제

변형 1: LIKE 부분 검색

이름 검색은 LIKE 에 '%' || :p || '%' 를 씁니다. || 는 Oracle·표준 연결이고 MySQL 은 CONCAT, MSSQL 은 + 를 씁니다. 이름에 개발 을 넣으면 4명이 나옵니다.

WITH p AS (
    SELECT
           CAST('개발' AS VARCHAR(20)) AS p_name
)
SELECT
       e.id
     , e.name
  FROM emp e
 CROSS JOIN p
 WHERE (p.p_name IS NULL OR e.name LIKE '%' || p.p_name || '%')
 ORDER BY e.id;

ID | NAME
---+-------
2  | 김개발
3  | 남개발
7  | 정개발
11 | 윤개발
(4행)

IS NULL 검사를 빼고 입력이 NULL 이면 '%' || NULL || '%' 가 NULL 이라 LIKE 가 참이 되지 못합니다. 실행해 보면 건수가 0입니다.

CNT_WITHOUT_GUARD
-----------------
0
(1행)

입력이 빈 문자열이면 패턴이 '%%' 라서 이름이 있는 모든 행이 일치합니다. 아래 12는 빈 문자열을 넣고 IS NULL 검사를 유지한 문장의 결과입니다.

CNT_EMPTY
---------
12
(1행)

이 12행은 '' 가 NULL 이 아니라는 전제입니다. Oracle 에서는 '' 가 NULL 이라 p_name IS NULL 이 참이 되어 같은 12행이 나옵니다. 사용자가 %·_ 를 입력하면 와일드카드로 해석되므로 필요하면 ESCAPE 절로 막습니다.

변형 2: IN 목록

소스: sql-src/work_02_dynamic_search/02_in_list_sort.sql. 목록의 값이 둘이거나 하나여도 문장 모양은 같습니다. 화면에서 선택한 개수만큼 ? 를 만들어 넣습니다.

SELECT id, name, dept FROM emp WHERE dept IN ('개발', '영업') ORDER BY id;

위 문장은 개발 4명과 영업 4명, 8행이 나옵니다.

세 부서를 모두 넣어도 dept 가 NULL 인 강신입은 나오지 않습니다. 12명이 아니라 11명입니다.

IN_CNT
------
11
(1행)

변형 3: 빈 목록과 NOT IN

목록이 비면 IN () 이 되는데 표준 문법이 아니라 대부분의 DB 에서 문법 오류입니다. H2 는 이것을 받아 주므로 H2 에서 재현 안 됨, 문법 검토만 합니다. 애플리케이션이 목록이 비었는지 먼저 검사해 조건을 빼거나, 결과가 없어야 하면 1 = 0 을 넣습니다.

IN (NULL) 은 문법이 통과하고 항상 0행입니다. 반대로 NOT IN 목록에 NULL 이 섞이면 모든 행이 걸러지니 조심합니다.

dept NOT IN ('개발', NULL) 을 실행하면 0행입니다. 개발이 아닌 사원이 여럿인데도 0행입니다. dept <> '개발' AND dept <> NULL 이 되어 두 번째 항이 참이 되지 못합니다.

변형 4: 목록을 표로 만들어 IN 서브쿼리

값 개수가 변해도 문장이 같게 하려면 목록을 표로 넘깁니다. 임시 표나 배열 파라미터에 값을 넣고 IN (SELECT ...) 로 씁니다. Oracle 은 이 방식으로 리터럴 1000개 제한도 피합니다.

WITH p (dept) AS (
    VALUES ('개발'), ('영업')
)
SELECT
       e.id
     , e.name
     , e.dept
  FROM emp e
 WHERE e.dept IN (SELECT p.dept FROM p)
 ORDER BY e.id;

결과는 변형 2 의 8행과 같습니다. 여기서 WITH p (dept) AS (VALUES ...) 는 H2 문법 예시이고, 실무에서는 임시 표나 컬렉션 파라미터로 바꿉니다.

변형 5: 정렬 컬럼 선택

정렬 기준을 화면에서 고르는 경우 ORDER BY CASE 로 씁니다. 방향(오름·내림)마다 CASE 를 따로 두면 각 CASE 안의 타입이 하나로 유지됩니다. 아래는 sort = sal 일 때입니다.

WITH p AS (
    SELECT
           CAST('sal' AS VARCHAR(10)) AS p_sort
)
SELECT
       e.id
     , e.name
     , e.sal
  FROM emp e
 CROSS JOIN p
 ORDER BY CASE WHEN p.p_sort = 'sal' THEN e.sal END DESC
        , CASE WHEN p.p_sort = 'name' THEN e.name END ASC
        , e.id;

결과는 김대표 900, 정개발 520, 김개발 500 순으로 시작해 강신입 300 으로 끝나는 12행입니다.

선택되지 않은 CASE 는 모든 행에서 NULL 이라 순서에 영향을 주지 못합니다. 마지막 e.id 는 동점일 때 결과를 고정하는 안전장치입니다.

p_sort 를 name 으로 바꾸면 급여 CASE 가 NULL 이 되어 이름 오름차순이 됩니다. 결과는 강신입, 김개발, 김대표, 남개발 순으로 시작해 한영업으로 끝납니다. 전체 12행은 sql 파일을 실행해 확인합니다.

변형 6: 타입이 다른 값을 한 CASE 에 섞으면

CASE 한 개에 숫자와 문자를 함께 넣으면 결과 타입을 하나로 맞추다 오류가 납니다. 이 문장만 한 줄로 씁니다.

SELECT id, name, sal FROM emp ORDER BY CASE WHEN id > 0 THEN name ELSE sal END;

예상 오류: Data conversion error converting "김대표"

오류 문구는 H2 것입니다. 다른 DB 는 오류가 나거나 조용히 문자열·숫자로 변환하는 등 동작이 갈리므로, CASE 하나에는 같은 타입만 넣고 정렬 컬럼별로 CASE 를 나눕니다.

변형 7: DB 별 NULL 치환 함수와 날짜 구간

소스: sql-src/work_02_dynamic_search/03_dialects.sql. 함정 패턴은 DB 가 달라도 결과가 같습니다. 세 DB 의 NULL 치환 함수 이름만 다르고, 자세한 비교는 중급 06 NULL 처리 함수에 있습니다.

-- Oracle · Tibero
SET MODE Oracle;
WITH p AS (
    SELECT
           CAST(NULL AS VARCHAR(10)) AS p_dept
)
SELECT
       COUNT(*) AS nvl_trap_cnt
  FROM emp e
 CROSS JOIN p
 WHERE e.dept = NVL(p.p_dept, e.dept);

NVL_TRAP_CNT
------------
11
(1행)

MySQL 모드의 IFNULL 과 MSSQL 모드의 ISNULL 도 같은 문장 모양으로 11행이 나옵니다. 실행 결과는 sql 파일에서 볼 수 있습니다. 세 DB 모두 IS NULL OR 패턴이 안전합니다.

날짜 구간은 >= 시작 AND < 끝 + 1일 로 씁니다. Oracle DATE 는 시·분·초를 가지므로 BETWEEN 시작 AND 끝 을 쓰면 끝날 자정 이후 시각이 빠집니다. 중급 07 날짜 함수에서 본 경계 함정 그대로입니다.

-- Oracle · Tibero
SET MODE Oracle;
WITH p AS (
    SELECT
           DATE '2022-01-01' AS p_from
         , DATE '2023-11-13' AS p_to
)
SELECT
       e.id
     , e.name
     , e.hired
  FROM emp e
 CROSS JOIN p
 WHERE e.hired >= p.p_from
   AND e.hired < p.p_to + 1
 ORDER BY e.id;

ID | NAME   | HIRED
---+--------+-----------
6  | 최인사 | 2022-02-14
7  | 정개발 | 2023-03-06
8  | 한영업 | 2023-11-13
12 | 서영업 | 2022-10-05
(4행)

MSSQL 모드에서 e.hired < DATEADD(DAY, 1, p.p_to) 로 바꿔도 같은 4행입니다. MySQL 은 DATE_ADD(p.p_to, INTERVAL 1 DAY) 를 쓰고 H2 에서는 실행하지 않았습니다. 끝날 2023-11-13 입사자인 한영업이 포함되는 것이 < 끝 + 1일 의 효과입니다.

빈 문자열은 H2 가 Oracle 모드에서 NULL, MySQL 모드에서 NULL 아님으로 흉내 냅니다. 이것은 H2 흉내이고 실제 DB 는 위 표대로 이해합니다.

변형 8: MyBatis 동적 SQL 조각과 #{} · ${}

애플리케이션에서 조건이 많은 화면은 값이 있는 조건만 붙이는 동적 SQL 을 씁니다. <where> 는 첫 조건 앞의 AND 를 알아서 지우고 조건이 하나도 없으면 WHERE 를 생략합니다.

<select id="search" resultType="Emp">
  SELECT id, name, dept FROM emp
  <where>
    <if test="dept != null and dept != ''">AND dept = #{dept}</if>
    <if test="status != null">AND status = #{status}</if>
    <if test="depts != null and !depts.isEmpty()">
      AND dept IN <foreach collection="depts" item="d" open="(" separator="," close=")">#{d}</foreach>
    </if>
  </where>
  ORDER BY ${sortCol}
</select>

#{} 는 바인드 변수라 값이 ? 로 전달되어 안전합니다. ${} 는 문자열 그대로 치환하므로 사용자 입력을 넣으면 SQL 인젝션 구멍이 됩니다. 정렬 컬럼처럼 식별자는 바인드할 수 없어 ${} 를 쓰게 되는데, 이때 화이트리스트로 검사한 값만 넘깁니다.

팁

정렬 컬럼은 Map 이나 enum 으로 허용 목록을 두고, 화면 값을 그 목록에서 찾아 넘깁니다. 목록에 없으면 기본 정렬을 씁니다. 방향은 ASC·DESC 만 허용합니다.