[SQL] Join 시 두번째 테이블의 가장 최근 데이터 조회

회사에서 조금 복잡한 쿼리를 작성할 일이 생겨서 정리도 할 겸 글을 남겨봅니다.

전 회사에서는 개발을 하면서 직접 쿼리를 다루는 일이 많지 않고 간단한 쿼리만 사용하는 경우가 많았는데 이번에는 제 기준 조금 복잡한 쿼리를 작성하게 되었습니다. 🥲

 

상황을 살펴보면 다음과 같습니다. (실제 회사에서 사용하는 DB를 토대로 한 간단한 예제입니다...)

 

• 미션을 생성하게 되는데 해당 미션을 저장하는 DB와 미션에 대한 상세 사진을 저장하는 DB가 별도로 존재합니다.
• 하나의 미션에는 여러 장의 사진을 등록할 수 있습니다.
• 미션 목록 조회 시 미션 정보와 함께 미션별 가장 처음에 올린 사진 하나만을 조회해야 합니다.

 

먼저 DB간 ERD를 살펴보면 다음과 같습니다.

 

 

미션 정보를 조회하면서 해당 미션에 맞는 사진을 하나만 조회하는 쿼리는 다음과 같이 작성하였습니다.

SELECT m.id
     , m.start_at
     , m.end_at
     , m.pay
     , a.file_path
FROM mission m
     LEFT OUTER JOIN attachment a ON a.id = (
        SELECT id
        FROM attachment AS a2
        WHERE a2.mission_id = m.id
        ORDER BY a2.id
        LIMIT 1
    )
ORDER BY m.created_at desc;

 

결과를 살펴보면 다음과 같습니다.

 

mission table data

attachment table data

미션 조회 시 처음 등록된 파일만 같이 조회되도록 한 쿼리 결과

 

첫번째 미션에 대한 이미지 파일은 2개, 두번째 미션에 대한 이미지 파일은 3개지만 결과적으로는 각각 처음 올린 이미지의 file_path만 같이 조회되었습니다.

 

만약, 처음 올린 사진이 아니라 가장 마지막(최근)에 올린 사진을 조회하고 싶은 경우에는 아래와 같이 ORDER BY 절을 변경해주면 됩니다.

SELECT m.id
     , m.start_at
     , m.end_at
     , m.pay
     , a.file_path
FROM mission m
     LEFT OUTER JOIN attachment a ON a.id = (
        SELECT id
        FROM attachment AS a2
        WHERE a2.mission_id = m.id
        ORDER BY a2.id DESC
        LIMIT 1
    )
ORDER BY m.created_at desc;

OR

SELECT m.id
     , m.start_at
     , m.end_at
     , m.pay
     , a.file_path
FROM mission m
     LEFT OUTER JOIN attachment a ON a.id = (
        SELECT id
        FROM attachment AS a2
        WHERE a2.mission_id = m.id
        ORDER BY a2.created_at DESC
        LIMIT 1
    )
ORDER BY m.created_at desc;

 

이밖에도 다양한 쿼리를 통해 같은 결과를 낼 수 있는 것 같지만 해당 쿼리가 가장 간단하다고 생각합니다...

(더 좋은 방법이나 해당 쿼리에 대한 좀 더 자세한 설명을 공유해주시면 감사하겠습니당...🙏)

 

[조인 참고 사진]