5. join

: 두 개 잉상의 테이블을 연결하는 것

 

- relation DB의 가장 큰 장점

- PK, FK 이용

- join할 때 순서는 내부 DBMS 옵티마이저가 결정

 

- 종류?

 

 

 

create table member (
    member_type     varchar(10) not null,
    name            varchar(20),
    user_id         varchar(100) not null ,
    pw              varchar(50),
    primary key (member_type, user_id)
);

member

create table member_detail (
    member_type         varchar(10) not null,
    user_id             varchar(50) not null,
    phone               varchar(12),
    marketAgree         bit,
    register_date       datetime default current_timestamp(),
    primary key (member_type, user_id),
    constraint fk_member_detail foreign key (member_type, user_id) references member (member_type, user_id)
);

member_detail

 

1. inner join

select * from member join member_detail on member.member_type = member_detail.member_type and member.user_id = member_detail.user_id;

==

select * from member as m join member_detail as md on m.member_type = md.member_type and m.user_id = md.user_id;

join

 

2. left join

select * from member as m left join member_detail as md on m.member_type = md.member_type and m.user_id = md.user_id;

left join

 

3. right join

select * from member as m right join member_detail as md on m.member_type = md.member_type and m.user_id = md.user_id;

right join

 

4. full join

select * from member as m join member_detail as md;

full join