MYSQL join&inner join & outer join 예제&문제 11

CREATE table stdTbl

(stdName varchar(10) not null primary key,

addr char(4) not null

);

create table clubTbl(

clubName VARCHAR(10) not null PRIMARY key,

roomNo char(4) not null

);

create TABLE stdclubTbl(

num int AUTO_INCREMENT not null PRIMARY key,

stdName VARCHAR(10) not null,

clubName VARCHAR(10) not null,

FOREIGN KEY(stdName) REFERENCES stdTbl(stdName),

FOREIGN KEY(clubName) REFERENCES clubTbl(clubName)

);

insert into stdTbl VALUES('bumsoo','kn'),('sungsiky','su'),('joyong','kgi'),('unjiwon','kb'),('babikim','su');

insert into clubTbl VALUES('swim','101'),('badok','102'),('scor','103'),('bongsa','104');

insert into stdclubTbl VALUES (null,'bumsoo' ,'badok') ,(null,'bumsoo' ,'scor'),(null,'joyong' ,'scor'),(null,'unjiwon' ,'scor'),

(null,'unjiwon' ,'bongsa'),(null,'babikim' ,'bongsa');

commit;

-- 학생테이블, 동아리 테이블, 학생 - 동아리 테이블을 이용해서 학생을 기준으로 학생 이름, /지역/가입한 동아리 / 동아리 이름을 출력하자 

select s.stdName, s.addr, c.clubName, c.roomNo

from stdTbl s

  inner join stdclubTbl sc

    on s.stdName= sc.stdName

  inner join clubTbl c

    on sc.clubName =c.clubName

order by s.stdName;

-- 동아리를 기준으로 가입한 학생의 목록을 출력하자 

select c.clubName, c.roomNo,s.stdName, s.addr

from stdTbl s

  inner join stdclubTbl sc

    on sc.stdName=s.stdName

  inner join clubTbl c

    on sc.clubName =c.clubName

order by c.clubName;

-- 동아리에 가입하지 않은 학생 성시경은 출력이 안됐다.

-- 아웃터 조인으로 동아리에 가입하지 않은 학생도 출력되도록 수정해보자

select s.stdName, s.addr, c.clubName, c.roomNo

from stdTbl s

  left join stdclubTbl sc

    on s.stdName= sc.stdName

  left join clubTbl c

    on sc.clubName =c.clubName

order by s.stdName;

-- 이제는 동아리를 기준으로 가입한 학생을 출력하되, 가입학생이 하나도 없는 동아리도 출력되게 해보자

-- 클럽을 기준으로 조인을 해야 하므로 두 번재 조인은 라이트 아우터 조인으로 처리 해서 clubTbl이 조인의 기준이 되도록 설정하면 된다. 

select c.clubName, c.roomNo,s.stdName, s.addr

from stdTbl s

  left join stdclubTbl sc

    on s.stdName= sc.stdName

  right join clubTbl c

    on sc.clubName =c.clubName

order by c.clubName;

-- 동아리에 가입하지 않은 학생도 출력되고 학생이 한명도 없는 동아리도 출력되게 하자 

select s.stdName, s.addr, c.clubName, c.roomNo

from stdTbl s

  left join stdclubTbl sc

    on s.stdName= sc.stdName

  left join clubTbl c

    on sc.clubName =c.clubName

union

select s.stdName, s.addr,c.clubName, c.roomNo

from stdTbl s

  left join stdclubTbl sc

    on s.stdName= sc.stdName

  right join clubTbl c

    on sc.clubName =c.clubName

 
CREATE table stdTbl
(stdName varchar(10) not null primary key,
addr char(4) not null
);
 
create table clubTbl(
 
clubName VARCHAR(10) not null PRIMARY key,
roomNo char(4) not null
 
);
 
 
create TABLE stdclubTbl(
 
num int AUTO_INCREMENT not null PRIMARY key,
stdName VARCHAR(10) not null,
clubName VARCHAR(10) not null,
FOREIGN KEY(stdName) REFERENCES stdTbl(stdName),
FOREIGN KEY(clubName) REFERENCES clubTbl(clubName)
);
 
 
insert into stdTbl VALUES('bumsoo','kn'),('sungsiky','su'),('joyong','kgi'),('unjiwon','kb'),('babikim','su');
 
insert into clubTbl VALUES('swim','101'),('badok','102'),('scor','103'),('bongsa','104');
 
insert into stdclubTbl VALUES (null,'bumsoo' ,'badok') ,(null,'bumsoo' ,'scor'),(null,'joyong' ,'scor'),(null,'unjiwon' ,'scor'),
(null,'unjiwon' ,'bongsa'),(null,'babikim' ,'bongsa');
 
commit;
 
 
-- 학생테이블, 동아리 테이블, 학생 - 동아리 테이블을 이용해서 학생을 기준으로 학생 이름, /지역/가입한 동아리 / 동아리 이름을 출력하자 
 
 
select s.stdName, s.addr, c.clubName, c.roomNo
from stdTbl s
  inner join stdclubTbl sc
    on s.stdName= sc.stdName
  inner join clubTbl c
    on sc.clubName =c.clubName
order by s.stdName;
 
-- 동아리를 기준으로 가입한 학생의 목록을 출력하자 
 
select c.clubName, c.roomNo,s.stdName, s.addr
from stdTbl s
  inner join stdclubTbl sc
    on sc.stdName=s.stdName
  inner join clubTbl c
    on sc.clubName =c.clubName
order by c.clubName;
 
-- 동아리에 가입하지 않은 학생 성시경은 출력이 안됐다.
-- 아웃터 조인으로 동아리에 가입하지 않은 학생도 출력되도록 수정해보자
 
 
select s.stdName, s.addr, c.clubName, c.roomNo
from stdTbl s
  left join stdclubTbl sc
    on s.stdName= sc.stdName
  left join clubTbl c
    on sc.clubName =c.clubName
order by s.stdName;
 
 
 
-- 이제는 동아리를 기준으로 가입한 학생을 출력하되, 가입학생이 하나도 없는 동아리도 출력되게 해보자
-- 클럽을 기준으로 조인을 해야 하므로 두 번재 조인은 라이트 아우터 조인으로 처리 해서 clubTbl이 조인의 기준이 되도록 설정하면 된다. 
 
 
select c.clubName, c.roomNo,s.stdName, s.addr
from stdTbl s
  left join stdclubTbl sc
    on s.stdName= sc.stdName
  right join clubTbl c
    on sc.clubName =c.clubName
order by c.clubName;
 
 
-- 동아리에 가입하지 않은 학생도 출력되고 학생이 한명도 없는 동아리도 출력되게 하자 
 
 
select s.stdName, s.addr, c.clubName, c.roomNo
from stdTbl s
  left join stdclubTbl sc
    on s.stdName= sc.stdName
  left join clubTbl c
    on sc.clubName =c.clubName
 
union
 
select s.stdName, s.addr,c.clubName, c.roomNo
from stdTbl s
  left join stdclubTbl sc
    on s.stdName= sc.stdName
  right join clubTbl c
    on sc.clubName =c.clubName