읽기 전용 DTO 조회 시 JOIN의 종류와 JPQL 구현 방법

07 Jun 2024 in Study Log / Jpa

JPA의 기본 사항을 정리한다.

• SQL JOIN의 차이점 정리
  • 간단한 엔티티 및 읽기 세트 전용 DTO 세팅
  • Author LEFT JOIN
  • Book LEFT JOIN
  • Author LEFT JOIN Excluding
  • Book LEFT JOIN Excluding
  • Author INNER JOIN
  • Book INNER JOIN
  • Author RIGHT JOIN
  • Book RIGHT JOIN
  • Author RIGHT JOIN Excluding
  • Book RIGHT JOIN Excluding
  • Author FULL JOIN
  • Author FULL JOIN with UNION ALL

SQL JOIN의 차이점 정리

JPA에서 추후 엔티티 수정이 없는 경우 읽기 전용 DTO + JOIN으로 처리하는게 가장 효율적이다.
JPA 에서 SQL에서 사용되는 다양한 JOIN 연산의 차이점을 예시와 함께 설명한다.
native query말고 JPQL로 구현하는 방법으로 작성했다.

간단한 엔티티 및 읽기 세트 전용 DTO 세팅

//file: `Author(1) Entity`
@Entity
public class Author implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String genre;
    private int age;

    @OneToMany(cascade = CascadeType.ALL,
            mappedBy = "author", orphanRemoval = true)
    private List<Book> books = new ArrayList<>();
}

//file: `Book(N) Entity`
@Entity
public class Book implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String title;
    private String isbn;
    private int price;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "author_id")
    private Author author;
}

//file: `읽기 전용 결과 세트 DTO`
public interface AuthorNameBookTitle {
  String getName();
  String getTitle();
}

---

Author LEFT JOIN

//file: `Author LEFT JOIN REPOSITORY`
@Query(value = "SELECT b.title AS title, a.name AS name "
        + "FROM Author a LEFT JOIN a.books b")
    List<AuthorNameBookTitle> findAuthorsAndBooksJpql_LEFTJOIN();

--file: `Author LEFT JOIN QUERY`
select
    b1_0.title,
    a1_0.name 
from
    author a1_0 
left join
    book b1_0 
        on a1_0.id=b1_0.author_id

• 설명 : author 테이블의 모든 레코드와 해당 저자가 작성한 book의 레코드를 결합합니다. 저자가 작성한 책이 없는 경우에도 author의 레코드는 유지되며, 이때 book 필드는 null이 됩니다.

결과 :

name	title
Mark Janel	null
Olivia Goy	Carrie
Quartis Young	null
Joana Nimar	A History of Ancient Prague
Joana Nimar	A Peoples History
Joana Nimar	A History of Ancient Prague2
Larisa Tomay	null

Book LEFT JOIN

//file: `Book LEFT JOIN REPOSITORY`
@Query(value = "SELECT b.title AS title, a.name AS name "
        + "FROM Book b LEFT JOIN b.author a")
    List<AuthorNameBookTitle> findBooksAndAuthorsJpql_LEFTJOIN();

select
    b1_0.title,
    a1_0.name 
from
    book b1_0 
left join
    author a1_0 
        on a1_0.id=b1_0.author_id

• 설명 : book 테이블의 모든 레코드와 해당 책의 저자를 결합합니다. 책의 저자가 없는 경우에도 book의 레코드는 유지되며, 이때 author 필드는 null이 됩니다.

결과 :

title	name
A History of Ancient Prague	Joana Nimar
A Peoples History	Joana Nimar
Carrie	Olivia Goy
Lost book	null
A History of Ancient Prague2	Joana Nimar

Author LEFT JOIN Excluding

//file: `Author LEFT JOIN EXCLUDING REPOSITORY`
@Query(value = "SELECT b.title AS title, a.name AS name "
        + "FROM Author a LEFT JOIN a.books b WHERE b.id IS NULL")
    List<AuthorNameBookTitle> findAuthorsAndBooksJpql_LEFTJOIN_Excluding();

select
    b1_0.title,
    a1_0.name 
from
    author a1_0 
left join
    book b1_0 
        on a1_0.id=b1_0.author_id 
where
    b1_0.id is null

• 설명 : author 테이블에서 book 레코드와 매칭되지 않는 레코드만 선택합니다.

결과 :

name	title
Mark Janel	null
Quartis Young	null
Larisa Tomay	null

Book LEFT JOIN Excluding

//file: `Book LEFT JOIN EXCLUDING`
@Query(value = "SELECT b.title AS title, a.name AS name "
        + "FROM Book b LEFT JOIN b.author a WHERE a.id IS NULL")
    List<AuthorNameBookTitle> findBooksAndAuthorsJpql_LEFTJOIN_Excluding();

select
    b1_0.title,
    a1_0.name 
from
    book b1_0 
left join
    author a1_0 
        on a1_0.id=b1_0.author_id 
where
    a1_0.id is null

• 설명 : book 테이블에서 author 레코드와 매칭되지 않는 레코드만 선택합니다.

  결과 :

  title	name
  Lost book	null

Author INNER JOIN

//file: `Author INNER JOIN`
@Query(value = "SELECT b.title AS title, a.name AS name "
        + "FROM Author a INNER JOIN a.books b")
    List<AuthorNameBookTitle> findAuthorsAndBooksJpql_INNERJOIN();

select
    b1_0.title,
    a1_0.name 
from
    author a1_0 
join
    book b1_0 
        on a1_0.id=b1_0.author_id

• 설명 : author와 book 테이블에서 매칭되는 레코드만 선택합니다.

• 결과 :

  name	title
  Joana Nimar	A History of Ancient Prague
  Joana Nimar	A Peoples History
  Olivia Goy	Carrie
  Joana Nimar	A History of Ancient Prague2

Book INNER JOIN

//file: `Book INNER JOIN REPOSITORY`
@Query(value = "SELECT b.title AS title, a.name AS name "
        + "FROM Book b INNER JOIN b.author a")
    List<AuthorNameBookTitle> findBooksAndAuthorsJpql_INNERJOIN();

select
    b1_0.title,
    a1_0.name 
from
    book b1_0 
join
    author a1_0 
        on a1_0.id=b1_0.author_id

• 설명 : book과 author 테이블에서 매칭되는 레코드만 선택합니다.

결과 :

title	name
A History of Ancient Prague	Joana Nimar
A Peoples History	Joana Nimar
Carrie	Olivia Goy
A History of Ancient Prague2	Joana Nimar

Author RIGHT JOIN

//file: `Author RIGHT JOIN REPOSITORY`
@Query(value = "SELECT b.title AS title, a.name AS name "
        + "FROM Author a RIGHT JOIN a.books b")
    List<AuthorNameBookTitle> findAuthorsAndBooksJpql_RIGHTJOIN();

select
    b1_0.title,
    a1_0.name 
from
    author a1_0 
right join
    book b1_0 
        on a1_0.id=b1_0.author_id

• 설명 : book 테이블의 모든 레코드와 해당 저자를 결합합니다. 책의 저자가 없는 경우에도 book의 레코드는 유지되며, 이때 author 필드는 null이 됩니다.

결과 :

name	title
Joana Nimar	A History of Ancient Prague
Joana Nimar	A Peoples History
Olivia Goy	Carrie
null	Lost book
Joana Nimar	A History of Ancient Prague2

Book RIGHT JOIN

//file: `Book RIGHT JOIN REPOSITORY`
@Query(value = "SELECT b.title AS title, a.name AS name "
        + "FROM Book b RIGHT JOIN b.author a")
    List<AuthorNameBookTitle> findBooksAndAuthorsJpql_RIGHTJOIN();

select
    b1_0.title,
    a1_0.name 
from
    book b1_0 
right join
    author a1_0 
        on a1_0.id=b1_0.author_id

• 설명 : author 테이블의 모든 레코드와 해당 저자가 작성한 book의 레코드를 결합합니다. 저자가 작성한 책이 없는 경우에도 author의 레코드는 유지되며, 이때 book 필드는 null이 됩니다.

결과 :

name	title
Mark Janel	null
Olivia Goy	Carrie
Quartis Young	null
Joana Nimar	A History of Ancient Prague
Joana Nimar	A Peoples History
Joana Nimar	A History of Ancient Prague2
Larisa Tomay	null

Author RIGHT JOIN Excluding

//file: `Author RIGHT JOIN EXCLUDING`
@Query(value = "SELECT b.title AS title, a.name AS name "
        + "FROM Author a RIGHT JOIN a.books b WHERE a.id IS NULL")
    List<AuthorNameBookTitle> findAuthorsAndBooksJpql_RIGHTJOIN_Excluding();

select
    b1_0.title,
    a1_0.name 
from
    author a1_0 
right join
    book b1_0 
        on a1_0.id=b1_0.author_id 
where
    a1_0.id is null

• 설명 : book 테이블에서 author 레코드와 매칭되지 않는 레코드만 선택합니다.

결과 :

title	name
Lost book	null

Book RIGHT JOIN Excluding

//file: `Book RIGHT JOIN EXCLUDING`
@Query(value = "SELECT b.title AS title, a.name AS name "
        + "FROM Book b RIGHT JOIN b.author a WHERE b.id IS NULL")
    List<AuthorNameBookTitle> findBooksAndAuthorsJpql_RIGHTJOIN_Excluding();

select
    b1_0.title,
    a1_0.name 
from
    book b1_0 
right join
    author a1_0 
        on a1_0.id=b1_0.author_id 
where
    b1_0.id is null

• 설명 : author 테이블에서 book 레코드와 매칭되지 않는 레코드만 선택합니다.

결과 :

name	title
Mark Janel	null
Quartis Young	null
Larisa Tomay	null

Author FULL JOIN

//file: `Author FULL JOIN`
@Query(value = "(SELECT b.title AS title, a.name AS name FROM author a "
        + "LEFT JOIN book b ON a.id = b.author_id) "
        + "UNION " //  will remove duplicates (use UNION ALL to keep duplicates)
        + "(SELECT b.title AS title, a.name AS name FROM author a "
        + "RIGHT JOIN book b ON a.id = b.author_id "
        + "WHERE a.id IS NULL)",
        nativeQuery = true)
    List<AuthorNameBookTitle> findAuthorsAndBooksSql_FULLJOIN();

(
    SELECT
        b.title AS title,
        a.name AS name 
    FROM
        author a 
    LEFT JOIN
        book b 
            ON a.id = b.author_id) 
UNION
(SELECT
    b.title AS title, a.name AS name 
FROM
    author a 
RIGHT JOIN
    book b 
        ON a.id = b.author_id 
WHERE
    a.id IS NULL)

• 설명 : author와 book 테이블의 모든 레코드를 결합합니다. 두 테이블 모두에서 매칭되지 않는 레코드도 포함됩니다.

결과 :

name	title
Larisa Tomay	null
Mark Janel	null
Quartis Young	null
Joana Nimar	A History of Ancient Prague
Joana Nimar	A Peoples History
Joana Nimar	A History of Ancient Prague2
Olivia Goy	Carrie
null	Lost book

Author FULL JOIN with UNION ALL

@Query(value = "(SELECT b.title AS title, a.name AS name FROM author a "
            + "LEFT JOIN book b ON a.id = b.author_id) "
            + "UNION ALL" //  use UNION ALL to keep duplicates
            + "(SELECT b.title AS title, a.name AS name FROM author a "
            + "RIGHT JOIN book b ON a.id = b.author_id "
            + "WHERE a.id IS NULL)",
            nativeQuery = true)
    List<AuthorNameBookTitle> findAuthorsAndBooksSql_FULLJOIN();

(
    SELECT
        b.title AS title,
        a.name AS name 
    FROM
        author a 
    LEFT JOIN
        book b 
            ON a.id = b.author_id) 
UNION
ALL(SELECT
    b.title AS title, a.name AS name 
FROM
    author a 
RIGHT JOIN
    book b 
        ON a.id = b.author_id 
WHERE
    a.id IS NULL)

• 설명 : author와 book 테이블의 모든 레코드를 결합합니다. UNION ALL은 중복된 레코드도 포함합니다.

  결과 :

  name	title
  Mark Janel	null
  Olivia Goy	Carrie
  Quartis Young	null
  Joana Nimar	A History of Ancient Prague
  Joana Nimar	A Peoples History
  Joana Nimar	A History of Ancient Prague2
  Larisa Tomay	null
  null	Lost book