조인

*목차

 1. 내부조인

 2. 외부조인

 3. 세타조인

 4. JPA 조인 - ON 절

   - 1.조인 대상 필터링

   - 2.연관관계 없는 조인

 

 

내부조인

 

SELECT m FROM Member m [INNER] JOIN m.team t

 

Member는 있고 Team이 없으면 포함안된다

 

List<Member> resultList = em.createQuery("select m from Member m INNER JOIN m.team")
.getResultList();

Member result = resultList.get(0);
System.out.println(result.getName());
System.out.println(result.getAge());

 

Hibernate: 
    /* select
        m 
    from
        Member m 
    INNER JOIN
        m.team */ select
            member0_.id as id1_3_,
            member0_.age as age2_3_,
            member0_.city as city3_3_,
            member0_.street as street4_3_,
            member0_.zipcode as zipcode5_3_,
            member0_.name as name6_3_,
            member0_.TEAM_ID as TEAM_ID9_3_,
            member0_.endDate as endDate7_3_,
            member0_.startDate as startDat8_3_ 
        from
            Member member0_ 
        inner join
            Team team1_ 
                on member0_.TEAM_ID=team1_.id

 

외부 조인

 

SELECT m FROM Member m LEFT [OUTER] JOIN m.team t

 

Member는 있고 Team이 없어도 Member는 나온다

 

List<Member> resultList = em.createQuery("select m from Member m LEFT JOIN m.team")
.getResultList();

Member result = resultList.get(0);
System.out.println(result.getName());
System.out.println(result.getAge());

 

Hibernate: 
    /* select
        m 
    from
        Member m 
    LEFT JOIN
        m.team */ select
            member0_.id as id1_3_,
            member0_.age as age2_3_,
            member0_.city as city3_3_,
            member0_.street as street4_3_,
            member0_.zipcode as zipcode5_3_,
            member0_.name as name6_3_,
            member0_.TEAM_ID as TEAM_ID9_3_,
            member0_.endDate as endDate7_3_,
            member0_.startDate as startDat8_3_ 
        from
            Member member0_ 
        left outer join
            Team team1_ 
                on member0_.TEAM_ID=team1_.id

 

 

세타 조인

 

SELECT COUNT(m) FROM Member m, Team t where m.username = t.name

 

카디시안 곱으로 모든 검색을 다하며, 연관관계가 없을 때 막 비교할 때 사용한다.

 

List<Member> resultList = em.createQuery("select m from Member m, Team t where m.name = t.name")
.getResultList();

Member result = resultList.get(0);
System.out.println(result.getName());
System.out.println(result.getAge());

 

Hibernate: 
    /* select
        m 
    from
        Member m,
        Team t 
    where
        m.name = t.name */ select
            member0_.id as id1_3_,
            member0_.age as age2_3_,
            member0_.city as city3_3_,
            member0_.street as street4_3_,
            member0_.zipcode as zipcode5_3_,
            member0_.name as name6_3_,
            member0_.TEAM_ID as TEAM_ID9_3_,
            member0_.endDate as endDate7_3_,
            member0_.startDate as startDat8_3_ 
        from
            Member member0_ cross 
        join
            Team team1_ 
        where
            member0_.name=team1_.name

 

특이한 점은 cross join이라는 용어인데, 모든 칼럼을 비교하는 카디시안 곱을 의미한다.

 

 

JPA 조인 - ON절

ON절을 활용한 조인이 JPA 2.1버전부터 지원된다

 

 

1. 조인 대상 필터링

 

예) 회원과 팀을 조인하면서, 팀이름이 A인 경우

 

SQL:

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

 

 

JPQL:

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

 

SQL과 달리 JPQL 특징은, on m.TEAM_ID = t.id가 없는데 자동으로 넣어주기 때문이다.

 

2. 연관관계 없는 엔티티 외부 조인(하이버네이트 5.1)

 

예) 회원의 이름과 팀의 이름이 같은 대상 외부 조인

 

SQL:

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

 

JPQL:

SELECT m, t FROM Member m LEFT JOIN TEAM t on m.name = t.name

 

1,2가 결국 on절 조건에서 조인을 한다는 것이 특징이다.

 

 

 특히 2번을 따로 분리한 이유는, LEFT JOIN m.team t가 아닌 LEFT JOIN Team t인데 아무 연관관계가 없는 테이블을 조회하기 때문이다. 연관관계가 있다면 무조건 LEFT JOIN m.team t on m.TEAM_ID = t.id로 서로 관련있는것을 id를 기준으로 조인해야하지만, 연관관계가 없기 때문에 전체 Team 테이블로 비교하면 된다.