[SQL 쿡북/011장] 고급 검색

정은아·2025년 5월 23일

SQL 쿡북

[도서] SQL 쿡북

목록 보기

13/13

11장. 고급 검색

✅ 11.1 결과셋을 페이지로 매기기

SQL에는 첫 번째, 마지막 또는 다음 개념이 없으므로 작업 중인 행에 순서를 지정해야합니다.
순서를 지정해야 행의 범위를 정확하게 반환할 수 있습니다.

윈도우 함수 ROW_NUMBER OVER를 사용하여 순서를 적용하고 WHERE 절에서 반환할 레코드의 윈도를 지정합니다. 예를 들어 다음과 같이 1행부터 5행까지 반환합니다.

select sal
from (
    select row_number() over (order by sal) as rn,
           sal
    from emp
) x
where rn between 1 and 5;

| SAL  |
|------|
| 800  |
| 950  |
| 1100 |
| 1250 |
| 1250 |

그런 다음, 다음과 같이 6~10행을 반환합니다.

select sal
from (
    select row_number() over (order by sal) as rn,
           sal
    from emp
) x
where rn between 6 and 10;

| SAL  |
|------|
| 1300 |
| 1500 |
| 1600 |
| 2450 |
| 2850 |

쿼리의 WHERE 절을 바꾸어 원하는 범위의 행을 반환할 수 있습니다.

인라인 뷰 X의 윈도우 함수 ROW_NUMBER OVER는 각 급여에 고유한 번호를 할당합니다. (1부터 시작하는 오름차순입니다.) 다음은 인라인 뷰 X의 결과셋입니다.

select row_number() over (order by sal) as rn,
       sal
from emp;

| RN  | SAL  |
|-----|------|
| 1   | 800  |
| 2   | 950  |
| 3   | 1100 |
| 4   | 1250 |
| 5   | 1250 |
| 6   | 1300 |
| 7   | 1500 |
| 8   | 1600 |
| 9   | 2450 |
| 10  | 2850 |
| 11  | 2975 |
| 12  | 3000 |
| 13  | 3000 |
| 14  | 5000 |

급여에 번호가 할당되면 RN 값을 지정하여 반환할 범위를 선택합니다. Oracle 사용자의 경우 대안으로 ROW NUMBER OVER 대신 ROWNUM을 사용하여 행의 일련번호를 생성할 수 있습니다.
```
```sql
select sal
from (
    select sal, rownum rn
    from (
        select sal
        from emp
        order by sal
    )
)
where rn between 6 and 10;

| SAL  |
|------|
| 1300 |
| 1500 |
| 1600 |
| 2450 |
| 2850 |
```
```

✅ 11.2 테이블에서 n개 행 건너뛰기

EMP 테이블의 다른 모든 사원을 반환하는 쿼리를 원합니다.

첫 번째 사원, 세 번째 사원 등을 반환해야 합니다.

| ENAME   |
|---------|
| ADAMS   |
| ALLEN   |
| BLAKE   |
| CLARK   |
| FORD    |
| JAMES   |
| JONES   |
| KING    |
| MARTIN  |
| MILLER  |
| SCOTT   |
| SMITH   |
| TURNER  |
| WARD    |

우리는 다음과 같은 결과를 반환하고자 합니다.
```
| ENAME   |
|---------|
| ADAMS   |
| BLAKE   |
| FORD    |
| JONES   |
| MARTIN  |
| SCOTT   |
| TURNER  |
```
- 결과셋에서 두 번째, 네 번째 또는 n번째 행을 건너뛰거나 특정 간격으로 행을 선택하려면 정렬된 순서가 필요합니다.
- 이를 위해 ROW_NUMBER() 함수와 모듈로 함수(MOD)를 활용할 수 있습니다.
  - Oracle과 PostgreSQL: MOD
  - SQL Server: % (나머지 연산자)
```
select ename
from (
    select row_number() over (order by ename) rn,
           ename
    from emp
) x
where mod(rn, 2) = 1;
```
- 설명
  - ROW_NUMBER() over (order by ename) → ename을 기준으로 정렬한 후 각 행에 순번을 부여합니다.
  - MOD(rn, 2) = 1 → 순번이 홀수인 행만 추출합니다.
  - DBMS별 모듈로 함수
    - Oracle, PostgreSQL, MySQL: MOD()
    - SQL Server: % (예: rn % 2 = 1)

select row_number() over (order by ename) rn, ename
from emp;

| RN  | ENAME  |
|-----|--------|
| 1   | ADAMS  |
| 2   | ALLEN  |
| 3   | BLAKE  |
| 4   | CLARK  |
| 5   | FORD   |
| 6   | JAMES  |
| 7   | JONES  |
| 8   | KING   |
| 9   | MARTIN |
| 10  | MILLER |
| 11  | SCOTT  |
| 12  | SMITH  |
| 13  | TURNER |
| 14  | WARD   |

마지막 단계는 단순히 계수를 사용하여 다른 모든 행을 건너뛰는 것입니다.

✅ 11.3 외부 조인을 사용할 때 OR 로직 통합하기

부서 30 및 40에 대한 부서 정보와 함께 , 부서 10 및 20의 모든 사원명 및 부서 정보를 반환하려고 합니다. (부서 30, 40은 사원이 없기 때문에 사원명은 없음)

select e.ename, d.deptno, d.dname, d.loc
from dept d, emp e
where d.deptno = e.deptno
  and (e.deptno = 10 or e.deptno = 20)
order by 2;

| ENAME  | DEPTNO | DNAME      | LOC       |
|--------|--------|------------|-----------|
| CLARK  | 10     | ACCOUNTING | NEW YORK  |
| KING   | 10     | ACCOUNTING | NEW YORK  |
| MILLER | 10     | ACCOUNTING | NEW YORK  |
| SMITH  | 20     | RESEARCH   | DALLAS    |
| ADAMS  | 20     | RESEARCH   | DALLAS    |
| FORD   | 20     | RESEARCH   | DALLAS    |
| SCOTT  | 20     | RESEARCH   | DALLAS    |
| JONES  | 20     | RESEARCH   | DALLAS    |

🧾 해설

이 쿼리는 dept와 emp 테이블을 내부 조인(INNER JOIN) 했기 때문에,

사원이 없는 부서 (예: 부서 30, 40)는 결과에 포함되지 않음.

e.deptno = 10 or e.deptno = 20 조건을 사용하여 사원 정보만 필터링됨.

따라서 부서 30, 40에 대한 부서 정보는 출력되지 않음.

💎 이 문제를 해결하려면?

→ DEPT 기준으로 외부 조인(LEFT OUTER JOIN) 을 사용하고,
e.deptno가 null이어도 d.deptno가 30 또는 40이면 포함되도록 해야 합니다.

select e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
  on (d.deptno = e.deptno)
where e.deptno = 10
   or e.deptno = 20
order by 2;
| ENAME  | DEPTNO | DNAME      | LOC       |
|--------|--------|------------|-----------|
| CLARK  | 10     | ACCOUNTING | NEW YORK  |
| KING   | 10     | ACCOUNTING | NEW YORK  |
| MILLER | 10     | ACCOUNTING | NEW YORK  |
| SMITH  | 20     | RESEARCH   | DALLAS    |
| ADAMS  | 20     | RESEARCH   | DALLAS    |
| FORD   | 20     | RESEARCH   | DALLAS    |
| SCOTT  | 20     | RESEARCH   | DALLAS    |
| JONES  | 20     | RESEARCH   | DALLAS    |

궁극적으로 결과셋은 다음과 같습니다.

select e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
  on (d.deptno = e.deptno
      and (e.deptno = 10 or e.deptno = 20))
order by 2;

| ENAME  | DEPTNO | DNAME       | LOC       |
|--------|--------|-------------|-----------|
| CLARK  | 10     | ACCOUNTING  | NEW YORK  |
| KING   | 10     | ACCOUNTING  | NEW YORK  |
| MILLER | 10     | ACCOUNTING  | NEW YORK  |
| SMITH  | 20     | RESEARCH    | DALLAS    |
| JONES  | 20     | RESEARCH    | DALLAS    |
| SCOTT  | 20     | RESEARCH    | DALLAS    |
| ADAMS  | 20     | RESEARCH    | DALLAS    |
| FORD   | 20     | RESEARCH    | DALLAS    |
| NULL   | 30     | SALES       | CHICAGO   |
| NULL   | 40     | OPERATIONS  | BOSTON    |

또는 인라인 뷰에서 먼저 EMP.DEPTNO를 필터링한 다음,
외부 조인을 필터링할 수 있습니다.

select e.ename, d.deptno, d.dname, d.loc
from dept d
left join (
    select ename, deptno
    from emp
    where deptno in (10, 20)
) e on (e.deptno = d.deptno)
order by 2;

🎨 DB2, MySQL, PostgreSQL, SQL Server

1. OR 조건을 `JOIN` 절로 이동

JOIN의 일부로 조건을 작성해 필터링된 EMP와 모든 DEPT를 조인
이 방식으로 DEPTNO 30, 40도 누락되지 않음

2. 필터링을 인라인 뷰로 분리

EMP에서 조건을 먼저 적용한 후, 해당 결과를 외부 조인
EMP를 필터링한 후 조인하므로, DEPT는 30, 40을 포함한 모든 부서가 반환됨

✅ 11.4 역수 행 확인하기

두 테스트의 결과가 포함된 테이블에서 어떤 점수 쌍이 역수
(서로 반대의 나눗셈 관계)를 이루는지 확인
뷰 V 에서 결과를 조회:

select *
from V;

| TEST1 | TEST2 |
|-------|-------|
| 20    | 20    |
| 50    | 25    |
| 60    | 30    |
| 70    | 90    |
| 80    | 130   |
| 90    | 70    |
| 100   | 55    |
| 110   | 60    |
| 120   | 60    |
| 130   | 80    |
| 140   | 70    |

의도한 결과: 역수 관계 1쌍만 표시

역수 관계란 (A, B)와 (B, A)가 모두 존재할 때 이를 하나의 쌍으로만 간주함.

| TEST1 | TEST2 |
|-------|-------|
| 20    | 20    |
| 70    | 90    |
| 80    | 130   |

(20, 20): 자기 자신
(70, 90) + (90, 70): 역수 → 하나만 선택
(80, 130) + (130, 80): 역수 → 하나만 선택

의도하지 않은 결과(중복된 역수 쌍 포함)

| TEST1 | TEST2 |
|-------|-------|
| 20    | 20    |
| 20    | 20    |
| 70    | 90    |
| 90    | 70    |
| 80    | 130   |
| 130   | 80    |

(a, b)와 (b, a)가 모두 존재하면 한 쌍만 선택
조건: TEST1 < TEST2 형태로 제약 걸어 중복 제거

해법: TEST1, TEST2의 점수쌍 중 서로 역수인 행을 찾아 한 쌍만 반환

(예: (70, 90), (90, 70) → 하나만)

select distinct v1.*
from V v1, V v2
where v1.test1 = v2.test2
  and v1.test2 = v2.test1
  and v1.test1 <= v1.test2;

셀프 조인은 모든 TEST1 점수를 모든 TEST2 점수와 비교할 수 있는 데카르트 곱을 생성하며 그 반대의 경우도 마찬가지 입니다.

select v1.*
from V v1, V v2
where v1.test1 = v2.test2
  and v1.test2 = v2.test1;

| TEST1  | TEST2  |
|--------|--------|
| 20     | 20     |
| 20     | 20     |
| 70     | 90     |
| 90     | 70     |
| 80     | 130    |
| 130    | 80     |

>>> 이 결과는 의도한 바가 아님 (중복 제거 안 됨)

| TEST1  | TEST2  |
|--------|--------|
| 20     | 20     |
| 70     | 90     |
| 80     | 130    |

✅ 11.5 상위 n개 레코드 선택하기

특정 순서 기준으로 결과셋에서 상위 N개의 레코드만 조회하고자 할 때, 예를 들어 급여 상위 5위 사원의 급여를 반환하고자 한다.

해법: DENSE_RANK() 사용

select ename, sal
from (
    select ename, sal,
           dense_rank() over (order by sal desc) dr
    from emp
) x
where dr <= 5;

dense_rank()는 동일한 급여를 동일 순위로 처리
dr <= 5는 급여 순위가 5위 이내인 모든 사원 포함

총 행 수는 5개 이상일 수 있음 (동점 포함)

| ENAME  | SAL  | DR  |
|--------|------|-----|
| KING   | 5000 | 1   |
| SCOTT  | 3000 | 2   |
| FORD   | 3000 | 2   |
| JONES  | 3000 | 2   |
| BLAKE  | 2975 | 3   |
| CLARK  | 2450 | 5   |
| ALLEN  | 1600 | 6   |
| TURNER | 1500 | 7   |
| MILLER | 1300 | 8   |
| WARD   | 1250 | 9   |
| MARTIN | 1250 | 9   |
| ADAMS  | 1100 | 11  |
| JAMES  | 950  | 12  |
| SMITH  | 800  | 12  |

동점을 허용하지 않고 정확히 5행만 출력하려면 ROW_NUMBER() 사용
ROW_NUMBER()는 동일한 값이라도 순위를 고유하게 매김 → 무조건 정확히 N행만 출력 가능

✅ 11.6 최댓값과 최솟값을 가진 레코드 찾기

EMP 테이블에서 가장 높은 급여와 가장 낮은 급여를 받는 사원 정보를 찾고자 합니다.

해법: MIN OVER, MAX OVER 윈도우 함수를 활용해 한 번의 스캔으로 최댓값·최솟값과 비교

🎨 DB2, Oracle, SQL Server

select ename
from (
    select ename, sal,
           min(sal) over() min_sal,
           max(sal) over() max_sal
    from emp
) x
where sal in (min_sal, max_sal);

정은아

꾸준함의 가치를 믿는 개발자

이전 포스트

[SQL 쿡북/011장] 고급 검색

[도서] SQL 쿡북

11장. 고급 검색

✅ 11.1 결과셋을 페이지로 매기기

✅ 11.2 테이블에서 n개 행 건너뛰기

✅ 11.3 외부 조인을 사용할 때 OR 로직 통합하기

🧾 해설

💎 이 문제를 해결하려면?

🎨 DB2, MySQL, PostgreSQL, SQL Server

1. OR 조건을 `JOIN` 절로 이동

2. 필터링을 인라인 뷰로 분리

✅ 11.4 역수 행 확인하기

✅ 11.5 상위 n개 레코드 선택하기

✅ 11.6 최댓값과 최솟값을 가진 레코드 찾기

🎨 DB2, Oracle, SQL Server

[SQL 쿡북/09장] 13절. 중복 날짜 범위 식별하기

0개의 댓글

[SQL 쿡북/011장] 고급 검색

[도서] SQL 쿡북

11장. 고급 검색

✅ 11.1 결과셋을 페이지로 매기기

✅ 11.2 테이블에서 n개 행 건너뛰기

✅ 11.3 외부 조인을 사용할 때 OR 로직 통합하기

🧾 해설

💎 이 문제를 해결하려면?

🎨 DB2, MySQL, PostgreSQL, SQL Server

1. OR 조건을 JOIN 절로 이동

2. 필터링을 인라인 뷰로 분리

✅ 11.4 역수 행 확인하기

✅ 11.5 상위 n개 레코드 선택하기

✅ 11.6 최댓값과 최솟값을 가진 레코드 찾기

🎨 DB2, Oracle, SQL Server

[SQL 쿡북/09장] 13절. 중복 날짜 범위 식별하기

0개의 댓글

1. OR 조건을 `JOIN` 절로 이동