SQL join 명령어 요약

< 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' 교재를 공부하면서 주요 내용을 요약하고 있습니다.

혹시 잘못되거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.

---