SQL join 명령어 요약

728x90
< join : 하나 이상의 테이블(여러개)에서 원하는 데이터 추출 >
- cross join // 거의 안씀
select sawon_name, buser_name from sawon, buser;
select sawon_name,buser_name from sawon cross join buser; -- ANSI SQL 표준

<  equi join >
- inner join 양쪽 테이블 모두에 자료가 있는 경우에만 출력
select * from buser;
insert into buser values(50,'축구부', '파주','031-234-5678'); - 축구부 삽입
select * from sawon;
alter table sawon modify buser_num number(4) null; -- 부서 번호 4를 null로 변경
update sawon set buser_num=null where sawon_no=5;
commit;

select sawon_name, buser_name from sawon, buser where buser_num=buser_no; -- 오라클 표준
select sawon_name, buser_name from sawon inner join buser on buser_num=buser_no; -- ANSI 표준
select sawon_name, buser_name from sawon inner join buser on sawon.buser_num=buser.buser_no;

< non equi join // 잘 안씀 >
create table paygrade(grade number(1) primary key, lowpay number, highpay number);
insert into paygrade values(1,0,1999);
insert into paygrade values(2,2000,2999);
insert into paygrade values(3,3000,3999);
insert into paygrade values(4,4000,4999);
insert into paygrade values(5,5000,9999);
select * from paygrade;

select sawon_no, sawon_name, sawon_pay, grade from sawon, paygrade where sawon_pay >= lowpay and sawon_pay <= highpay; -- 오라클 표준
select sawon_no, sawon_name, sawon_pay, grade from sawon inner join paygrade on sawon_pay >= lowpay and sawon_pay <= highpay; -- ansi 표준

< SELF Join >
select a.sawon_name,b.sawon_jik from sawon a, sawon b where a.sawon_no=b.sawon_no;

- sawon + gogek
select sawon_no, sawon_name, sawon_jik, gogek_name, gogek_jumin from sawon inner join gogek on sawon_no=gogek_damsano;

- 부서내에 근무자 출력(부서없는 근무자)
select buser_name, sawon_name,sawon_jik,buser_tel from sawon inner join buser on sawon.buser_num = buser.buser_no;

- 부서별 급여합, 급여평균
select buser_num as 부서, sum(sawon_pay) as 급여합 from sawon group by buser_num;

- self join // 잘 안씀
- 보통 inner join을 많이 씀 - 그냥 순서대로 정렬
- Left outer join : 왼쪽 칼럼은 전부 나옴 / 오른쪽은 대응될땐 표시, 안되면 null 표기
select sawon_name, buser_name from sawon, buser where buser_num=buser_no(+); -- oracle 표준
select sawon_name, buser_name from sawon left outer join buser on buser_num=buser_no; -- ANSI 표준

< full join : 양쪽 테이블 자료 모두 출력 >
select sawon_name,buser_name from sawon full join buser on buser_num=buser_no;

- right outer join : 오른쪽 칼럼은 전부 나옴 / 왼쪽쪽은 대응될땐 표시, 안되면 null 표기


< 문1) 직급이 사원인 직원이 관리하는 고객 출력 >
--출력 ==>  사번   사원명   직급    고객명    고객전화   고객성별
--           3    한국인   사원    우주인    123-4567     남
select sa.sawon_no, sa.sawon_name, sa.sawon_jik,
go.gogek_name, go.gogek_tel,
case substr(gogek_jumin,8,1)
when '1' then '남' else '여' end as 고객성별
from sawon sa inner join gogek go
on sa.sawon_no = go.gogek_damsano
where sa.sawon_jik = '사원';

< 문2) 직원별 고객 확보 수  -- GROUP BY 사용 >
--   - 모든 직원 참여
select sawon_name 직원이름,count(gogek_name) 고객확보수
from sawon left outer join gogek
on sawon_no = gogek_damsano
group by sawon_name;  --동명이인 처리X

select sawon_no 직원이름,count(gogek_name) 고객확보수
from sawon left outer join gogek
on sawon_no = gogek_damsano
group by sawon_no;  --동명이인 처리

<  문3) 고객이 담당직원의 자료를 보고 싶을 때 즉, 고객명을 입력하면,  >
--     담당직원 자료 출력  
--       :    ~ WHERE GOGEK_NAME='강나루'
--출력 ==>  직원명       직급
--          한국인       사원
select sawon_name, sawon_jik
from sawon inner join gogek
on sawon_no = gogek_damsano where gogek_name='이나라';

< 문4) 직원명을 입력하면 관리고객 자료 출력 >
--      : ~ WHERE SAWON_NAME='한국인'
--출력 ==>고객명   고객전화         주민번호       나이
--        강나루   123-4567    700512-1234567     38

-- 2000년생 이전 고객
select go.gogek_name 고객명, go.gogek_tel 고객전화,
go.gogek_jumin 주민번호,
to_char(sysdate, 'YYYY')-(substr(go.gogek_jumin,1,2)+1900) 나이
from sawon sa inner join gogek go
on sa.sawon_no = go.gogek_damsano where sa.sawon_name='한국남';

--2000년생 이후 고객 존재 시
select go.gogek_name 고객명, go.gogek_tel 고객전화,
go.gogek_jumin 주민번호 ,
case
when substr(go.gogek_jumin, 8,1) > 3
then to_char(sysdate, 'YYYY')-(substr(go.gogek_jumin, 1,2)+2000)
else to_char(sysdate, 'YYYY')-(substr(go.gogek_jumin, 1,2)+1900) end as 나이
from sawon sa inner join gogek go
on sa.sawon_no = go.gogek_damsano where sa.sawon_name='한국남';

< 문1-1) 총무부에서 관리하는 고객수 출력 (고객 30살 이상만 작업에 참여) >

select buser_name 부서이름, count(buser_name) 고객수
from sawon inner join buser on buser_num = buser_no
inner join gogek on gogek_damsano = sawon_no
where (to_char(sysdate, 'YYYY')- (substr(gogek_jumin, 1,2)+1900)) >=30 and  buser_name = '총무부'
group by buser_name;


< 문2-1) 부서명별 고객 인원수 (부서가 없으면 "무소속") >
select nvl(buser_name,'무소속') 부서명, count(nvl(buser_name,'무소속')) 고객인원수
from sawon inner join gogek on gogek_damsano = sawon_no
left outer join buser on  buser_no = buser_num
group by  nvl(buser_name,'무소속');


< 문3-1) 고객이 담당직원의 자료를 보고 싶을 때 즉, 고객명을 입력하면     담당직원 자료 출력  >
--        :    ~ WHERE GOGEK_NAME='강나루'
--출력 ==>  직원명    직급   부서명  부서전화    성별

select sawon_name 직원명, nvl(sawon_jik,'프리랜서') 직급, nvl(buser_name,'프리랜서') 부서명, nvl(buser_tel,'프리랜서') 부서전화, sawon_gen 성별
from  sawon left outer join buser on buser_num = buser_no
inner join gogek on gogek_damsano = sawon_no where gogek_name = '강나루';

< 문4-1) 부서와 직원명을 입력하면 관리고객 자료 출력 >
--       ~ WHERE BUSER_NAME='영업부' AND SAWON_NAME='이순신'
--출력 ==>  고객명    고객전화      성별
--            강나루   123-4567       남
select gogek_name 고객명, gogek_tel 고객전화, case substr(gogek_jumin,8,1) when '1' then '남' else '여' end as 성별
from  sawon inner join buser on buser_num = buser_no
inner join gogek on gogek_damsano = sawon_no where buser_name='영업부'  and sawon_name = '이순신';




본 포스팅은 IT 교육기관인 KIC 캠퍼스의 지원을 받은 리포트입니다.
본문은 '남궁성, JAVA의 정석 3rd Edition' 교재를 공부하면서 주요 내용을 요약하고 있습니다.
혹시 잘못되거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.


728x90