공공부하자개발 · 영어 학습 노트
홈 › SQL 실무 › 02 / 9

동적 검색 조건

선택 조건, 바인드 변수, IN 목록, 정렬 선택
섹션 6진행 0 / 9

3. 코드 예제

소스: sql-src/work_02_dynamic_search/01_optional_cond.sql. emp 는 12행이고 부서는 개발·영업·인사 셋입니다. 강신입은 dept 가 NULL 이고 오인사는 status 가 NULL 입니다. 입사일 hired 컬럼은 03 예제에서 씁니다.

예제 1: 실행 전 emp 12행

SELECT id, name, dept, sal, status FROM emp ORDER BY id 로 보면 1번 김대표부터 12번 서영업까지 12행입니다. 개발 4명, 영업 4명, 인사 3명이고 10번 강신입은 dept 가 NULL, 9번 오인사는 status 가 NULL 입니다.

예제 2: 부서만 넣은 검색

파라미터 CTE p 에 조건값을 두고 emp 와 CROSS JOIN 합니다. 부서에 개발 을 넣고 상태는 비웁니다.

sql
WITH p AS (
    SELECT
           CAST('개발' AS VARCHAR(10)) AS p_dept
         , CAST(NULL AS VARCHAR(10)) AS p_status
)
SELECT
       e.id
     , e.name
     , e.dept
     , e.status
  FROM emp e
 CROSS JOIN p
 WHERE (p.p_dept IS NULL OR e.dept = p.p_dept)
   AND (p.p_status IS NULL OR e.status = p.p_status)
 ORDER BY e.id;
text
ID | NAME   | DEPT | STATUS
---+--------+------+-------
2  | 김개발 | 개발 | 재직
3  | 남개발 | 개발 | 재직
7  | 정개발 | 개발 | 재직
11 | 윤개발 | 개발 | 휴직
(4행)

상태 조건은 값이 NULL 이라 사라졌고 부서 조건만 걸렸습니다. CAST 는 NULL 의 타입을 정해 주려는 것입니다.

예제 3: 조건을 아무것도 안 넣으면

같은 문장에서 p_dept 를 NULL 로 바꾸기만 하면 두 조건이 모두 사라집니다.

sql
WITH p AS (
    SELECT
           CAST(NULL AS VARCHAR(10)) AS p_dept
         , CAST(NULL AS VARCHAR(10)) AS p_status
)
-- 이하 예제 2 와 같은 SELECT

두 파라미터가 모두 NULL 이라 12행이 전부 나오고, 강신입(dept NULL)과 오인사(status NULL)도 포함됩니다.

강신입(dept NULL)과 오인사(status NULL)까지 12행이 모두 나옵니다. 조건이 없으면 전체를 돌려주는 것이 검색 화면의 기대 동작입니다.

예제 4: 두 조건을 함께

sql
WITH p AS (
    SELECT
           CAST('개발' AS VARCHAR(10)) AS p_dept
         , CAST('재직' AS VARCHAR(10)) AS p_status
)
-- 이하 예제 2 와 같은 SELECT
text
ID | NAME   | DEPT | STATUS
---+--------+------+-------
2  | 김개발 | 개발 | 재직
3  | 남개발 | 개발 | 재직
7  | 정개발 | 개발 | 재직
(3행)

윤개발은 부서는 맞지만 상태가 휴직이라 빠졌습니다. 조건은 AND 로 이어져 값이 있는 조건끼리만 좁혀 갑니다.

예제 5: 함정, COALESCE 로 줄여 쓰면

아무 조건도 안 넣었는데 e.dept = COALESCE(p.p_dept, e.dept) 로 쓰면 결과가 어떻게 되는지 봅니다.

sql
WITH p AS (
    SELECT
           CAST(NULL AS VARCHAR(10)) AS p_dept
)
SELECT
       e.id
     , e.name
     , e.dept
  FROM emp e
 CROSS JOIN p
 WHERE e.dept = COALESCE(p.p_dept, e.dept)
 ORDER BY e.id;
text
ID | NAME   | DEPT
---+--------+-----
1  | 김대표 | 인사
2  | 김개발 | 개발
3  | 남개발 | 개발
4  | 이영업 | 영업
5  | 박영업 | 영업
6  | 최인사 | 인사
7  | 정개발 | 개발
8  | 한영업 | 영업
9  | 오인사 | 인사
11 | 윤개발 | 개발
12 | 서영업 | 영업
(11행)

10번 강신입이 없습니다. 조건 없이 전체를 봐야 하는데 dept 가 NULL 인 행이 조용히 빠졌습니다. 오류도 경고도 없어서 건수를 비교해야 알 수 있습니다.

text
OK_CNT | COALESCE_CNT
-------+-------------
12     | 11
(1행)

IS NULL OR 패턴은 12행, COALESCE 패턴은 11행입니다. NVL(Oracle)·IFNULL(MySQL)·ISNULL(MSSQL) 로 바꿔도 결과가 같습니다.