[MySQL] left join 아무생각 없이 사용하고 있는 것은 아니지?

 

  

inner join 과 left join 개념 체크!

아래와 같은 테이블이 존재한다고 합시다!

 

member

id	name	team_id
1	세웅	1
2	임하	3
3	민영	2
4	민주	2
5	소영	1
6	영민	null
7	영소	null

 

team

id	name
1	서버 개발팀
2	클라이언트 개발팀
3	디자인팀

 

inner join

inner join 은 테이블의 교집합을 반환합니다.

즉, 테이블간에 모두 존재하는 결과만 반환하게 됩니다.

예를 들어 member 와 team 테이블이 존재할 때 inner join을 사용하게 되면, 현재 존재하는 team에 속해있는 member 만 반환하게 됩니다.

select m.id, m.name, m.team_id, t.name 
from member m
inner join team t on t.id = m.team_id;

 

id	name	team_id	t.name
1	세웅	1	서버 개발팀
2	임하	3	디자인팀
3	민영	2	클라이언트 개발팀
4	민주	2	클라이언트 개발팀
5	소영	1	서버 개발팀

  

left join

 

left join은 from 절의 테이블을 포함한 교집합을 결과로 반환합니다.

select m.id, m.name, m.team_id, t.name 
from member m
left join team t on t.id = m.team_id;

 

id	name	team_id	t.name
1	세웅	1	서버 개발팀
2	임하	3	디자인팀
3	민영	2	클라이언트 개발팀
4	민주	2	클라이언트 개발팀
5	소영	1	서버 개발팀
6	영민	null	null
7	영소	null	null

 

결과를 보면, team 테이블에 존재하지 않고 member 테이블에만 존재하는 결과도 함께 반환된 것을 확인할 수 있습니다!

물론, team 테이블에는 값이 존재하지 않기 때문에 null 로 반환됩니다!

 

left join 예시(on절 조건, where 절 조건)

아래와 같은 쿼리를 사용하게 되면 어떤 결과가 나올까요?!

select m.id, m.name, m.team_id, t.name 
from member m
left join team t on t.id = m.team_id and t.id = 2;

 

team 테이블의 id가 2인 것만 left join을 수행하기 때문에 아래와 같은 결과가 나오게 됩니다!

member 테이블의 team_id가 2가 아닌 레코드들은 null 로 표기되는 것이죠!

 

id	name	team_id	t.name
1	세웅	1	null
2	임하	3	null
3	민영	2	클라이언트 개발팀
4	민주	2	클라이언트 개발팀
5	소영	1	null
6	영민	null	null
7	영소	null	null

 

그렇다면 아래의 쿼리를 실행하게 되면 어떤 결과가 나올까요??? 🤔

select m.id, m.name, m.team_id, t.name 
from member m
left join team t on t.id = m.team_id
where t.id = 2;

 

이 쿼리는 member 테이블에서 team_id가 2인 멤버들만 선택하여 해당 레코드들과 team.id = 2인 팀을 결합한 결과만 반환합니다.

이로 인해 team_id가 2가 아닌 레코드들과 team_id가 null인 레코드들 결과에서 제외됩니다.

이 쿼리는 사실상 INNER JOIN과 같은 효과를 가지게 됩니다.!!!!

 

id	name	team_id	t.name
3	민영	2	클라이언트 개발팀
4	민주	2	클라이언트 개발팀

 

즉, 위 쿼리는 아래 쿼리와 동일한 결과를 반환하게 됩니다.!!

select m.id, m.name, m.team_id, t.name 
from member m
inner join team t on t.id = m.team_id and t.id = 2;

 

참고로 MySQL에서는 아래와 같은 쿼리를 실행하게 되면, 옵티마이저가 쿼리 최적화를 수행하면서 자동으로 inner join의 쿼리 형태로 변환하게 됩니다.

 

-- 아래 쿼리를 수행하게 되면
select m.id, m.name, m.team_id, t.name 
from member m
left join team t on t.id = m.team_id
where t.id = 2;

-- 옵티마이저가 아래 쿼리로 자동 변환한다.
select m.id, m.name, m.team_id, t.name 
from member m
inner join team t on t.id = m.team_id and t.id = 2;

 

  

join 실행 계획 살펴보기 👀

현재 사이드 프로젝트에서 사용하는 테이블을 예시로 보여드리겠습니다.

anonymous_member와 pick_vote는 1:N 관계 입니다.

 

anonymous_member

create table anonymous_member
(
    id                  bigint auto_increment
        primary key,
    anonymous_member_id varchar(30)                           null comment '익명 회원 아이디',
    created_at          timestamp default current_timestamp() not null comment '생성시간',
    last_modified_at    timestamp                             null comment '수정시간',
    constraint anonymous_member_unique
        unique (anonymous_member_id)
)
    comment '익명 회원';

create index idx__anonymous_member_id
    on anonymous_member (anonymous_member_id);

 

pick_vote

create table pick_vote
(
    id                  bigint auto_increment
        primary key,
    member_id           bigint                                null,
    pick_id             bigint                                not null,
    pick_option_id      bigint                                not null,
    anonymous_member_id bigint                                null comment '익명 회원 외래키',
    created_at          timestamp default current_timestamp() not null comment '생성시간',
    last_modified_at    timestamp                             null comment '수정시간',
    constraint fk_pick_vote_01
        foreign key (pick_id) references pick (id),
    constraint fk_pick_vote_02
        foreign key (member_id) references member (id),
    constraint fk_pick_vote_03
        foreign key (pick_option_id) references pick_option (id),
    constraint fk_pick_vote_04
        foreign key (anonymous_member_id) references anonymous_member (id)
)
    comment '픽픽픽 선택지 투표';

 

left join 실행 계획

explain select *
from anonymous_member am
left join pick_vote pv on pv.anonymous_member_id = am.id and pv.pick_id = 23;

 

 

실행 계획을 보면, left join 왼쪽에 있는 anonymous_member 테이블을 항상 먼저 읽는 것을 확인할 수 있습니다!

where 절에 특별한 조건이 없기 때문에 anonymous_member 의 전체 데이터를 스캔하면서, pick_vote 테이블과 left join을 수행합니다.

 

inner join 실행 계획

explain select *
from anonymous_member am
inner join pick_vote pv on pv.anonymous_member_id = am.id and pv.pick_id = 23;

 

 

inner join의 실행 계획을 보면, pick_vote 테이블을 먼저 읽는 것을 확인할 수 있습니다.

특정 pick_vote 데이터에 대해서만 결과를 조회하는 것이기 때문에 anonymous_member 테이블에 먼저 접근하여 전체 데이터를 읽기 보다는 효율적인 처리를 위해 pick_vote 의 인덱스(fk_pick_vote_01)를 사용하여 pick_id = 23 인 데이터만 먼저 읽고 anonymous_member 테이블과 join 하는 형태로 처리되는 것을 확인 할 수 있습니다! 😉

left join은 실제 기준이 되는 왼쪽에 위치한 driving table 을 항상 먼저 읽는 반면에, inner join 은 join에 참여하는 table들의 교집합 데이터를 결과로 반환하는 것이기 때문에 left join 처럼 쿼리에 명시된 테이블들에 대해 처리 순서가 항상 고정되는 것은 아닙니다.

옵티마이저에 의해 최적화 되면서, 효율적으로 처리 될 수 있도록 테이블 접근 순서가 변경 될 수 있습니다! 👍

 

참고

• left join 왼쪽에 있는 테이블을 일반적으로 outer table 또는 driving table 이라고 말합니다.
• left join 오른쪽에 있는 테이블을 inner table 또는 driven table 이라고 말합니다.

 

count(*) with left join

페이징 처리 등을 위해 조회 대상 데이터의 전체 데이터를 확인하기 위해서 select 절만 count 함수로 변경해서 사용하는 경우가 많았을 것 입니다.

그런데, 이러한 경우에서 실제로 left join이 항상 필요할까요?! 🤔

아래 2개의 쿼리를 비교해 봅시다!

 

left join이 있는 count 쿼리

select count(*)
from anonymous_member am
left join pick_vote pv on pv.anonymous_member_id = am.id and pv.pick_id = 23;

 

 

left join이 없는 count 쿼리

select count(*)
from anonymous_member am;

 

 

이처럼 쿼리 결과가 동일할 때는 불필요한 left join은 제거해서 사용 하는 것이 좋습니다. 🙌

그런데 아래의 경우에는 반드시 left join이 필요한 경우 입니다.

select count(*)
from anonymous_member am
left join pick_vote pv on pv.anonymous_member_id = am.id
where pv.anonymous_member_id is null;

 

 

정리

• left join을 사용하고자 한다면 driven table(inner table) 컬럼의 조건(join 조건)은 반드시 on 절에 명시하여 사용(is null 조건은 예외)
• left join과 inner join은 결과 데이터 및 쿼리 처리 방식 등이 매우 다르기 때문에, 필요에 따라서 올바르게 사용하는 것이 중요
• left join 쿼리에서 count 를 사용하는 경우 left join이 굳이 필요하지 않는다면 join은 제거

 

참고

https://www.inflearn.com/course/real-mysql-part-1/dashboard

Real MySQL 시즌 1 - Part 1 강의 | 이성욱 - 인프런