JPQL JOIN

1. Join

---

• 객체 중심.
• 내부 조인 : INNER JOIN,
  • inner는 생략 가능.
  • 멤버는 있는데 팀이 없으면 결과 안나옴. 
  • SELECT m FROM Member m [INNER] JPOIN m.team t
• 외부 조인 : LEFT OUTER or LEFT
  • outer는 생략 가능.
  • 팀이 없어도 팀 데이터 NULL로 넣고 멤버는 조회됨.
  • SELECT m FROM Member m LEFT [OUTER] JOIN m.team t
• 세타 조인
  • 연관관계가 없는 두 엔티티를 막 비교해보고 싶을 때 사용.
  • select count(m) from Member m, Team t where m.username = t.name

 

 

 

2. 내부 조인

---

//member 와 team을 inner join함. 이제 team을 쓸 수 있음.
String query = "select m from Member m inner join m.team t";

List<Member> resultList = em.createQuery(query, Member.class)
							.getResultList();

System.out.println("resultList.size() = " + resultList.size());
for (Member member1 : resultList) {
  System.out.println("member1.toString() = " + member1.toString());
}

Hibernate: 
    /* select
        m 
    from
        Member m 
    inner join
        m.team t */ select
            member0_.id as id1_0_,
            member0_.age as age2_0_,
            member0_.TEAM_ID as team_id4_0_,
            member0_.username as username3_0_ 
        from
            Member member0_ 
        inner join
            Team team1_ 
                on member0_.TEAM_ID=team1_.TEAM_ID

 

 

 

 

3. 외부 조인

---

//member 와 team을 inner join함. 이제 team을 쓸 수 있음.
String query = "select m from Member m left outer join m.team t";

List<Member> resultList = em.createQuery(query, Member.class)
								.getResultList();

System.out.println("resultList.size() = " + resultList.size());
for (Member member1 : resultList) {
	System.out.println("member1.toString() = " + member1.toString());
}

Hibernate: 
    /* select
        m 
    from
        Member m 
    left outer join
        m.team t */ select
            member0_.id as id1_0_,
            member0_.age as age2_0_,
            member0_.TEAM_ID as team_id4_0_,
            member0_.username as username3_0_ 
        from
            Member member0_ 
        left outer join
            Team team1_ 
                on member0_.TEAM_ID=team1_.TEAM_ID

 

 

3. 세타 조인

---

String query = "select m from Member m, Team t where m.username = t.name";

List<Member> resultList = em.createQuery(query, Member.class)
							.getResultList();

System.out.println("resultList.size() = " + resultList.size());
for (Member member1 : resultList) {
	System.out.println("member1.toString() = " + member1.toString());
}

Hibernate: 
    /* select
        m 
    from
        Member m,
        Team t 
    where
        m.username = t.name */ select
            member0_.id as id1_0_,
            member0_.age as age2_0_,
            member0_.TEAM_ID as team_id4_0_,
            member0_.username as username3_0_ 
        from
            Member member0_ cross 
        join
            Team team1_ 
        where
            member0_.username=team1_.name

 

 

4. join - ON 절

---

• ON절을 활용한 조인
  • 조인 대상을 미리 필터링
  • 연관관계 없는 엔티티 외부 조인
• 조인 대상 필터링
  • 예를들어 회원과 팀을 조인하면서 팀이름이 A인 팀만 조인
  • JPQL
    member m에서 team을 조인하는데 team 이름이 'A'인 경우만.
    

    SELECT m, t FROM Member m LEFT JOIN m.team t on t.name = 'A'​

  • SQL
    

    SELECT m.*, t.* FROM Member m LEFT JOIN Team t on m.TEAM_ID=t.id and t.name = 'A'​

  • 예시
    

     String query = "select m from Member m left join m.team t on t.name = 'team'";​

  • 조인 조건문 안에 필터링 조건 추가됨.
    

    Hibernate: 
        /* select
            m 
        from
            Member m 
        left join
            m.team t 
                on t.name = 'team' */ select
                    member0_.id as id1_0_,
                    member0_.age as age2_0_,
                    member0_.TEAM_ID as team_id4_0_,
                    member0_.username as username3_0_ 
            from
                Member member0_ 
            left outer join
                Team team1_ 
                    on member0_.TEAM_ID=team1_.TEAM_ID 
                    and (
                        team1_.name='team'
                    )

• 연관관계 없는 엔티티 외부 조인
  • 회원의 이름과 팀의 이름이 같은 대상을 외부 조인
  • JPQL
    

    SELECT m, t FROM Member m LEFT JOIN Team t on m.username = t.name;

  • SQL
    

    SELECT m.*, t.* FROM
    Member m LEFT JOIN Team t ON m.username = t.name​

  • 예시
    

    String query = "select m from Member m left outer join Team t on m.username = t.name";​

  • Hibernate: 
        /* select
            m 
        from
            Member m 
        left outer join
            Team t 
                on m.username = t.name */ select
                    member0_.id as id1_0_,
                    member0_.age as age2_0_,
                    member0_.TEAM_ID as team_id4_0_,
                    member0_.username as username3_0_ 
            from
                Member member0_ 
            left outer join
                Team team1_ 
                    on (
                        member0_.username=team1_.name
                    )

 

5. GitHub : 210816 JOIN

---

GitHub - bsh6463/JPQL_STUDY