7월25일 - SQL 활용 5 : JOIN

JOIN

1. JOIN?

- 두개이상의 테이블을 결합하여 데이터를 추출하는 기법
- 두테이블의 공통값을 이용하여 컬럼을 조합하는 수단

2. 테이블 조인

1) select T1.*, T2.*
from T1 join T2
on T1.x=T2.x

2) select T1.*, T2.*, T3.*
from T1 join T2
on T1.x=T2.x join T3
on T1.y=T3.y;

3) select T1.x, T2.y, T3.z, T4.*
from T1 join T2
on T1.x=T2.x join T3
on T1.y=T3.y join T4
on T1.z=T4.z;

전날 만들었던, 부서테이블과 직원테이블에 조인을 접목해 실행시켜보려 한다.

문제) 아래의 조건과 같이 커미션을 구하시오
사원: 급여*0.03 / 주임: 급여*0.02 / 과장: 급여*0.01

update emp set comm=sal*0.03 where job='사원';
update emp set comm=sal*0.02 where job='주임';
update emp set comm=sal*0.01 where job='과장';

문제) 직원테이블에 부서명을 출력하시오

1) 전체 조회

select *
from emp join dept
on emp.deptno=dept.deptno;

2) 2개 테이블에 중복되지 않은 컬럼명은 테이블명 생략가능

select empno, ename, dname, loc
from emp join dept
on emp.deptno=dept.deptno;

3) 테이블 별칭 alias

- 각 테이블에 별칭을 따로 지어줄 수 있다.

select EM.*, DE.*
from emp EM, dept DE
where EM.deptno = DE.deptno;

select EM.*, DE.*
from emp EM join dept DE
on EM.deptno=DE.deptno;

select empno, ename, EM.deptno, dname, loc
from emp EM join dept DE
on EM.deptno=DE.deptno;

4) 논리적 테이블을 AA 별칭을 통해 새로운 테이블로 가공할 수 있다

- from에 테이블을 넣고 새로운 AA테이블로 가공해서 쓸 수 있다.

select AA.*
from (
     select empno, ename, EM.deptno, dname, loc
     from emp EM join dept DE
     on EM.deptno=DE.deptno
     ) AA;

select AA.empno, AA.ename, AA.deptno, AA.dname, AA.loc
from (
     select empno, ename, EM.deptno, dname, loc
     from emp EM join dept DE
     on EM.deptno=DE.deptno
     ) AA;

JOIN의 종류

조인 유형		내용
내부조인 INNER JOIN		두 테이블에 공통으로 존재하는 칼럼을 이용하는 방식 (공통 컬럼 기반)
	동등 조인 EQUI JOIN	공통 존재 칼럼의 값이 같은 경우를 추출
	자연 조인 NATURAL JOIN	두 테이블의 모든 컬럼을 비교하여 같은 컬럼명을 가진 모든 컬럼 값이 같은 경우를 추출
	교차 조인 CROSS JOIN	조인 조건이 없는 모든 데이터의 조합을 추출
외부 조인 OUTER JOIN		특정 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출
	왼쪽 외부 조인 LEFT OUTER JOIN	왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출
	오른쪽 외부 조인 RIGHT OUTER JOIN	오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출
	완전 외부 조인 FULL OUTER JOIN	양쪽의 모든 데이터를 추출

1. INNER JOIN 내부 조인

select SU.hakno, STU.uname, SU.gcode, GW.gname, GW.ghakjum
from tb_sugang SU inner join tb_student STU
on SU.hakno=STU.hakno inner join tb_gwamok GW
on SU.gcode=GW.gcode
order by SU.hakno;

-> inner join 의 inner는 생략가능하다 .

문제) 학번별 수강신청과목의 총 학점을 조회

select SU.hakno, STU.uname, sum(GW.ghakjum)
from tb_sugang SU inner join tb_student STU
on SU.hakno=STU.hakno inner join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno, STU.uname
order by SU.hakno;

문제) 디자인교과목 대상으로 학번별 수강신청과목의 총 학점을 조회

select AA.hakno, STU.uname, AA.총학점
from(
     select SU.hakno, sum(GW.ghakjum) as 총학점
     from tb_sugang SU join tb_gwamok GW
     on SU.gcode=GW.gcode
     where GW.gcode like 'd%'
     group by SU.hakno
     ) AA join tb_student STU
on AA.hakno=STU.hakno
order by AA.hakno;

-> 강사님 풀이

select CC.hakno, STU.uname, CC.총학점
from(
    select BB.hakno, sum(BB.ghakjum) 총학점
    from(
        select AA.hakno, AA.gcode, GW.ghakjum
        from(
             select hakno, gcode
             from tb_sugang
             where gcode like 'd%'
             ) AA join tb_gwamok GW
        on AA.gcode=GW.gcode
        ) BB
    group by BB.hakno
    ) CC join tb_student STU
on CC.hakno=STU.hakno;

문제) 과목코드 p001을 신청한 학생들의 명단을 조회

select SU.gcode, GW.gname, SU.hakno, STU.uname
from tb_sugang SU join tb_student STU
on SU.hakno=STU.hakno join tb_gwamok GW
on SU.gcode=GW.gcode
where SU.gcode='p001';

문제) 프로그램 교과목 중에서 학점이 제일 많은 과목을 신청한 학생들 명단 조회

select SU.gcode, STU.uname, SU.hakno, GW.gname
from tb_sugang SU join tb_student STU
on SU.hakno=STU.hakno join tb_gwamok GW
on SU.gcode=GW.gcode
where GW.ghakjum=3;

-> 선생님 풀이

--프로그램 교과목조회
select * from tb_gwamok where gcode like 'p%';

--프로그램 교과목 중에서 가장 많은 학점 조회
select max(ghakjum)
from tb_gwamok
where gcode like 'p%';

--프로그램 교과목 중에서 가장 많은 학점을 가진 교과목들 조회
select gcode, ghakjum
from tb_gwamok
where ghakjum = (select max(ghakjum)
                 from tb_gwamok
                 where gcode like 'p%')
and gcode like 'p%';

문제) 수강신청을 하지 않은 학생들의 명단을 조회하시오

--수강신청을 한 학생들
select hakno
from tb_sugang SU
group by hakno

--수강신청을 하지 않은 학생들
select hakno, uname
from tb_student
where hakno not in (
                select hakno
                from tb_sugang SU
                group by hakno);

2. OUTER JOIN 외부조인

1) left outer join

: 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터 추출

select STU.hakno, STU.uname, STU.phone, SU.gcode
from tb_student STU left outer join tb_sugang SU
on STU.hakno=SU.hakno; --outer 생략가능

select STU.hakno, STU.uname, SU.gcode
from tb_student STU left join tb_sugang SU
on STU.hakno=SU.hakno;

3)
select STU.hakno, STU.uname, SU.gcode
from tb_student STU, tb_sugang SU
where STU.hakno=SU.hakno(+);

2) right outer join

: 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출

select STU.hakno, STU.uname, STU.phone, SU.gcode
from tb_sugang SU right outer join tb_student STU
on STU.hakno=SU.hakno; --outer 생략가능

select STU.hakno, STU.uname, SU.gcode
from tb_sugang SU left join tb_student STU
on SU.hakno=STU.hakno;
3)
select STU.hakno, STU.uname, SU.gcode
from tb_sugang SU,tb_student STU
where SU.hakno=STU.hakno(+);

문제) 수강신청을 하지 않은 학생들의 명단을 조회하시오

- 방법 1

--수강신청한 과목
select gcode from tb_sugang group by gcode
--수강신청하지 않은 과목
select gcode
from tb_gwamok
where gcode not in (
                    select gcode
                    from tb_sugang
                    group by gcode
                    );

- 방법 2 : left join

-> left join 명령문을 적은 후 where조건절로 gcode에 null값이 들어가 있는 것을 찾는다.

select GW.gcode, GW.gname, GW.ghakjum, SU.gcode
from tb_gwamok GW left join tb_sugang SU
on GW.gcode=SU.gcode
where SU.gcode is null;

- 방법 3 : right join

-> right join 명령문을 적은 후 where조건절로 gcode에 null값이 들어가 있는 것을 찾는다.

select SU.gcode, GW.gcode, GW.gname, GW.ghakjum
from tb_sugang SU right join tb_gwamok GW
on SU.gcode=GW.gcode
where SU.gcode is null;

저작자표시

'공부 > SQL 활용' 카테고리의 다른 글

7월29일 - SQL활용 7 : JOIN의 종류 (0)	2019.07.30
7월26일 - SQL활용 6 : 학사관리프로그램 (0)	2019.07.26
7월24일 - SQL활용 4 : View & Index (0)	2019.07.24
7월22일 - SQL활용 3 : 함수 (0)	2019.07.22
7월22일 - SQL 활용연습문제 (0)	2019.07.22

3XL

7월25일 - SQL 활용 5 : JOIN

JOIN

JOIN의 종류

'공부 > SQL 활용' 카테고리의 다른 글

티스토리툴바

7월25일 - SQL 활용 5 : JOIN

JOIN

JOIN의 종류

'공부 > SQL 활용' 카테고리의 다른 글

'공부/SQL 활용' Related Articles

티스토리툴바