sql, 페이징처리를 할 때 자식 테이블을 필터링할 경우 어떻게 쿼리하는가?

부모 테이블을 페이징처리하되, 조건에는 자식테이블이 있다.

• 아래는 부서(department)와 내선번호(phone)테이블이 있다. 내선번호는 부서에 의존한다.
• 페이징의 대상이 내선번호일 경우 큰 문제가 없다. 하지만 부서를 기준으로 페이징처리하되, 내선번호의 필터링이 필요한 경우 어떻게 하는가?
• ddl과 dml은 아래와 같다.

create table department (
    no int auto_increment primary key,
    name varchar(100)
);

create table phone (
    no int auto_increment primary key,
    department_no int,
    phone varchar(100),
    foreign key (department_no) references department(no)
);


insert into department (no, name)
values (1, 'cs')
     , (2, 'hr')
     , (3, 'dev');

insert into phone (department_no, phone)
values (1, '1111')
      ,(1, '1114') 
      ,(1, '1124')
      ,(2, '2222')
      ,(2, '2224')
      ,(3, '3333')
      ,(3, '3335');

select * from department;

select * from phone;

select *
from phone p
join department m on p.department_no = m.no;

• 만약 위의 조건에서 개발팀과 인사팀 중 내선번호가 4로 끝나는 부서를 페이징처리하면 어떻게 쿼리를 해야 할까?

해소

• 나의 경우 아래와 같이 풀어봤다. (best practice나 혹시 더 좋은 방법이 있으면 공유 부탁드립니다!)

select *
from department m
where m.name in ('cs', 'hr', 'dev')
      and m.no in (select distinct department_no
                    from phone
                    where phone like '%4')
order by m.no asc
limit 1 offset 0;

• 부모 테이블을 기준으로 페이징을 할 경우 자식을 join을 하여서는 안된다. 하나의 부모가 여러 자식을 가지고 있거나, inner join일 때 자식이 없는 경우, 부모 레코드가 정확하게 표현되지 않는다. 그러므로 부모 테이블만을 조회하고 페이징처리(limit, offset)한다.

• 자식 테이블에 대한 필터링은 where로 처리한다. 그리고 불가피하게 자식 테이블에는 limit과 offset을 걸지 못하고 전체 탐색을 수행한다. limit이 10이었고 자식 테이블에서 10개를 꺼냈으나, 부모 테이블의 검색 결과가 그것보다 작은 경우, 기대한 것보다 적은 레코드를 꺼내기 때문이다.

• 부모 테이블에 조건절이 아무것도 없을 경우 다음과 같이 처리할 수 있다.
• 참고로 mysql, mariadb는 서브쿼리에 limit을 허용하지 않으므로 아래와 같이 임시 테이블을 생성해야 한다.

select *
from department m
where m.no in (select *
               from(
                   select distinct department_no
                   from phone
                   where phone like '%4'
                   order by department_no asc
                   limit 1 offset 0) tb
               )
order by m.no asc;