예시 1) 회원 중 팀이 있는 회원만 조인
JPQL :
SELECT m FROM Member m JOIN m.team t;
SQL :
SELECT m.* FROM
MEMBER m JOIN TEAM t ON m.TEAM_ID = t.TEAM_ID;
Team team = new Team();
team.setName("A");
em.persist(team);
Member member = new Member();
member.setUsername("member1");
member.setAge(20);
member.setTeam(team);
em.persist(member);
Member member2 = new Member();
member2.setUsername("member2");
member2.setAge(20);
em.persist(member2);
em.flush();
em.clear();
String query = "select m from Member m join m.team t";
List<Member> resultList = em.createQuery(query, Member.class)
.getResultList();
for (Member findMember : resultList) {
System.out.println("username = " + findMember.getUsername());
}
// username = member1
예시 2) 회원과 팀을 조인하면서, 팀 이름이 A인 팀만 조인
JPQL :
SELECT m FROM Member m JOIN m.team t ON t.name = 'A';
SQL :
SELECT m.* FROM
MEMBER m JOIN TEAM t ON m.TEAM_ID = t.TEAM_ID AND t.NAME = 'A';
Team team = new Team();
team.setName("A");
em.persist(team);
Team team2 = new Team();
team2.setName("B");
em.persist(team2);
Member member = new Member();
member.setUsername("member1");
member.setAge(20);
member.setTeam(team);
em.persist(member);
Member member2 = new Member();
member2.setUsername("member2");
member2.setAge(20);
member2.setTeam(team2);
em.persist(member2);
-------------------------------------------------------------------------
String query = "select m from Member m join m.team t on t.name = 'A'";
List<Member> resultList = em.createQuery(query, Member.class)
.getResultList();
for (Member findMember : resultList) {
System.out.println("username = " + findMember.getUsername());
}
// username = member1
-------------------------------------------------------------------------
String query = "select m from Member m left join m.team t on t.name = 'A'";
List<Member> resultList = em.createQuery(query, Member.class)
.getResultList();
for (Member findMember : resultList) {
System.out.println("username = " + findMember.getUsername());
}
// username = member1
// username = member2
-------------------------------------------------------------------------
LEFT JOIN의 경우에는 ON절 이후의 조건에 해당하지 않더라도 모두 반환한다는 것을 염두에 둬야 한다.
예시) 회원의 이름과 팀의 이름이 같은 대상 외부 조인
JPQL :
SELECT m FROM Member m JOIN TEAM t ON m.username = t.name;
SQL :
SELECT m.*, t.* FROM
MEMBER m JOIN TEAM t ON m.username = t.name;
Team team = new Team();
team.setName("A");
em.persist(team);
Member member = new Member();
member.setUsername("A");
member.setAge(20);
member.setTeam(team);
em.persist(member);
Member member2 = new Member();
member2.setUsername("B");
member2.setAge(20);
em.persist(member2);
em.flush();
em.clear();
String query = "select m from Member m join Team t on m.username = t.name";
List<Member> resultList = em.createQuery(query, Member.class)
.getResultList();
for (Member findMember : resultList) {
System.out.println("username = " + findMember.getUsername());
}
// username = A