[32]Join

작성일 | In

Join(조인)

  • 두 개 이상의 테이블을 결합하여 데이터를 추출하는 기법
  • 두 테이블의 공통값을 이용하여 칼럼을 조합하는 수단
  • 표준(ANSI) SQL문 -> 공통 -> left 테이블이 기준(테이블1)
1
2
3
SELECT 칼럼명
FROM 테이블1 JOIN 테이블2
ON 조건절;
  • Oracle SQL문 (각 DB만의 SQL문이 있다.)
1
2
3
SELECT 칼럼명
FROM 테이블1 , 테이블2
WHERE 조건절;
  • 테이블명.칼럼명
1
2
3
SELECT *
FROM test1 JOIN test2
ON test1.deptno=test2.deptno; --공통된 칼럼

테이블 조인

1) 테이블 2개 조인

1
2
3
SELECT t1.*, t2.*
FROM t1 JOIN t2
ON t1.x=t2.x;

2) 테이블 3개 조인

1
2
3
4
SELECT t1.*, t2.*, t3.*
FROM t1 JOIN t2
ON t1.x=t2.x JOIN t3
ON t1.y=t3.y;

3) 테이블 4개 조인

1
2
3
4
5
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;

부서 테이블

1
SELECT * FROM dept;

직원 테이블

1
SELECT * FROM emp;
  • 2개 테이블에 중복되는 칼럼명의 테이블명 명시해야함
1
2
3
4
5
6
7
SELECT empno
            ,ename
            ,dept.deptno --이렇게!
            ,dname
            ,loc
FROM emp JOIN dept
ON emp.deptno = dept.deptno;
  • 테이블 별칭(ALIAS)을 해줘서 간편하게 한다.
1
2
3
SELECT em.* , de.*
FROM emp em, dept de
WHERE em.deptno=de.deptno;
  • 논리적 테이블을 별칭을 통해 새로운 테이블로 가공할 수 있다.

ALIAS가 여기까지 허용

1
2
3
4
5
6
7
8
9
10
SELECT aa.*
FROM (
    SELECT empno
                ,ename
                ,em.deptno
                ,dname
                ,loc
    FROM emp em JOIN dept de
    ON em.deptno=de.deptno
) aa;

이렇게도 작성 가능

물론 aa 생략 가능하다

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;

1) 각 부서의 인원수를 인원수순으로 아래와 같이 조회

예시)

연구팀 5명, 총무팀 4명, 경리팀 2명

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT dname,COUNT(*) || '명' cnt
FROM (
    SELECT empno
                ,ename
                ,em.deptno
                ,dname
                ,loc
    FROM emp em JOIN dept de
    ON em.deptno=de.deptno
) aa
GROUP BY dname
ORDER BY COUNT(*) DESC;

2) 각 부서별 급여, 커미션의 합계를 구한 후 급여합계순으로 조회

예시)

총무팀 1655 413

연구팀 1535 307

경리팀 425 212

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT *
FROM (
    SELECT ename
                ,sal
                ,comm
                ,em.deptno
                ,dname
    FROM emp em JOIN dept de
    ON em.deptno=de.deptno
) aa;

SELECT dname
            ,SUM(sal)
            ,SUM(NVL(comm,0))
FROM (
    SELECT ename
                ,sal
                ,comm
                ,em.deptno
                ,dname
    FROM emp em JOIN dept de
    ON em.deptno=de.deptno
) aa
GROUP BY dname
ORDER BY SUM(sal) DESC;

선생님 방식 1

조인 후 합계

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT EM.sal
            ,EM.comm
            ,EM.deptno
            ,DE.dname
FROM emp EM JOIN dept DE
ON EM.deptno=DE.deptno;

SELECT AA.dname 
            ,SUM(sal)   급여합계
            ,SUM(NVL(comm,0)) 커미션합계
FROM(
    SELECT EM.sal
                ,EM.comm
                ,EM.deptno
                ,DE.dname
    FROM emp EM JOIN dept DE
    ON EM.deptno=DE.deptno
) AA
GROUP BY AA.dname
ORDER BY SUM(sal) DESC;

선생님 방식 2

합계 후 조인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT deptno
            ,SUM(sal)
            ,SUM(NVL(comm,0))
FROM emp
GROUP BY deptno;

SELECT AA.*
            ,DE.dname
FROM(
    SELECT deptno
            ,SUM(sal) hap1
            ,SUM(NVL(comm,0)) hap2
    FROM emp
    GROUP BY deptno
) AA JOIN dept DE
ON AA.deptno=DE.deptno
ORDER BY hap1 DESC;

3) 각 지역별 커미션 합계를 구한 후 그 커미션 합계가 20이하인 지역만 지역순으로 조회

예시)

대구 15

대전 13

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT *
FROM (
    SELECT ename
                ,sal
                ,comm
                ,em.deptno
                ,dname
                ,loc
    FROM emp em JOIN dept de
    ON em.deptno=de.deptno
) aa;

SELECT loc
            ,TRUNC(SUM(comm)) 
FROM (
    SELECT ename
                ,sal
                ,comm
                ,em.deptno
                ,dname
                ,loc
    FROM emp em JOIN dept de
    ON em.deptno=de.deptno
) aa
GROUP BY loc
HAVING TRUNC(SUM(comm))<=20
ORDER BY SUM(AA.comm) DESC;

선생님 방식

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT dept.loc
            ,emp.comm
FROM emp JOIN dept
ON emp.deptno=dept.deptno;

SELECT AA.loc
            ,ROUND(SUM(NVL(AA.comm,0)),0)
FROM(
    SELECT dept.loc
                ,emp.comm
    FROM emp JOIN dept
    ON emp.deptno=dept.deptno
) AA
GROUP BY AA.loc
HAVING SUM(AA.comm)<=20
ORDER BY AA.loc;

조인 연습하기(학사관리 프로그램)

학생테이블

1
2
3
4
5
6
7
8
CREATE TABLE tb_student (
    hakno CHAR(5) PRIMARY KEY
    ,uname VARCHAR(20) NOT NULL
    ,email VARCHAR(50) UNIQUE
    ,address VARCHAR(20) NOT NULL
    ,phone VARCHAR(20)
    ,regdt DATE DEFAULT SYSDATE
);

행 추가

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO tb_student (hakno , uname, email, address, phone)
VALUES ('g1001','홍길동','11@naver.com','서울','111-2222');
INSERT INTO tb_student (hakno , uname, email, address, phone)
VALUES ('g1002','홍길동','22@daum.net','제주','331-2223');
INSERT INTO tb_student (hakno , uname, email, address, phone)
VALUES ('g1003','개나리','33@naver.com','서울','111-2224');
INSERT INTO tb_student (hakno , uname, email, address, phone)
VALUES ('g1004','홍길동','44@soldesks.com','부산','222-2225');
INSERT INTO tb_student (hakno , uname, email, address, phone)
VALUES ('g1005','진달래','55@naver.com','서울','445-2226');
INSERT INTO tb_student (hakno , uname, email, address, phone)
VALUES ('g1006','개나리','66@soldsks.com','제주','331-2227');
  • 주소별 인원수가 3명 미만 행을 조회
1
2
3
4
SELECT address, COUNT(*)
FROM tb_student
GROUP BY address
HAVING COUNT(*) <3;

01

과목테이블

1
2
3
4
5
6
CREATE TABLE tb_gwamok(
    gcode CHAR(5) PRIMARY KEY       --과목코드
    ,gname VARCHAR(20) NOT NULL     --과목명
    ,ghakjum NUMBER(2) DEFAULT 1    --학점
    ,regdt DATE DEFAULT SYSDATE     --등록일 / 역추적하기위해서 넣음
);

행 추가

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO tb_gwamok (gcode, gname, ghakjum)
VALUES ('p001', 'OOP', 3);
INSERT INTO tb_gwamok (gcode, gname, ghakjum)
VALUES ('p002', 'Oracle', 2);
INSERT INTO tb_gwamok (gcode, gname, ghakjum)
VALUES ('p003', 'JSP', 3);
INSERT INTO tb_gwamok (gcode, gname, ghakjum)
VALUES ('d001', '웹표준', 1);
INSERT INTO tb_gwamok (gcode, gname, ghakjum)
VALUES ('d002', '포토샵', 3);
INSERT INTO tb_gwamok (gcode, gname, ghakjum)
VALUES ('d003', 'HTML', 1);
INSERT INTO tb_gwamok (gcode, gname, ghakjum)
VALUES ('c001', '실내건축', 3);
INSERT INTO tb_gwamok (gcode, gname, ghakjum)
VALUES ('p004', 'JavaScript', 2);
  • 디자인 교과목 중 3학점만 조회
1
2
3
SELECT *
FROM tb_gwamok
WHERE gcode LIKE 'd%' AND ghakjum=3;
  • 프로그램 교과목의 학점 평균보다 낮은 프로그램 교과목을 조회
1
2
3
4
5
6
SELECT *
FROM tb_gwamok
WHERE ghakjum<(SELECT AVG(ghakjum)
               FROM tb_gwamok
               WHERE gcode LIKE 'p%') 
AND  gcode LIKE 'p%';

02

수강 테이블 생성

1
2
3
4
5
6
7
8
CREATE TABLE tb_sugang(
    sno NUMBER PRIMARY KEY  --일련번호
     ,hakno CHAR(5) --학번 , 부모테이블과 동일하게! (학생테이블의 학번)
    ,gcode CHAR(5) --과목코드 , 부모테이블과 동일하게 (과목테이블의 과목코드)
    --제약조건을 이렇게 따로 작성할 수도 있음
    ,FOREIGN KEY(hakno) REFERENCES tb_student(hakno) 
    ,FOREIGN KEY(gcode) REFERENCES tb_gwamok(gcode)
);

옵션!

ON UPDATE CASCADE–부모테이블이 업데이트를 하면 같이 업데이트

ON DELETE NO ACTION –부모테이블이 삭제되면 부모테이블만 삭제 /CASCADE는 같이 삭제

수강테이블 시퀀스 생성

1
CREATE SEQUENCE sugang_seq;

행 추가

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO tb_sugang(sno, hakno, gcode)
VALUES(sugang_seq.NEXTVAL ,'g1002','p001');
INSERT INTO tb_sugang(sno, hakno, gcode)
VALUES(sugang_seq.NEXTVAL ,'g1004','p001');
INSERT INTO tb_sugang(sno, hakno, gcode)
VALUES(sugang_seq.NEXTVAL ,'g1003','d002');
INSERT INTO tb_sugang(sno, hakno, gcode)
VALUES(sugang_seq.NEXTVAL ,'g1001','p004');
INSERT INTO tb_sugang(sno, hakno, gcode)
VALUES(sugang_seq.NEXTVAL ,'g1001','c001');
INSERT INTO tb_sugang(sno, hakno, gcode)
VALUES(sugang_seq.NEXTVAL ,'g1006','d002');
INSERT INTO tb_sugang(sno, hakno, gcode)
VALUES(sugang_seq.NEXTVAL ,'g1002','p004');

03

수강테이블과 학생테이블 조인

1
2
3
4
5
6
SELECT SU.hakno
            ,SU.gcode
            ,STU.uname
FROM tb_sugang SU JOIN tb_student STU
ON SU.hakno=STU.hakno;

04

수강테이블과 과목테이블 조인

1
2
3
4
5
6
SELECT SU.hakno
            ,SU.gcode
            ,GW.gname
            ,GW.ghakjum
FROM tb_sugang SU JOIN tb_gwamok GW
ON SU.gcode=GW.gcode;

05

수강테이블을 기준으로 학생테이블, 과목테이블 조인

1
2
3
4
5
6
7
8
SELECT SU.hakno
            ,SU.gcode
            ,STU.uname
            ,GW.gname
            ,GW.ghakjum
FROM tb_sugang SU JOIN tb_student STU
ON SU.hakno=STU.hakno JOIN tb_gwamok GW
ON SU.gcode=GW.gcode;

06

문1) 지역별로 수강신청 인원수 지역 조회

예시)

서울 5

제주 3

부산 1

1
2
3
4
5
6
7
SELECT address, hakno, COUNT(*)
FROM(
     SELECT su.hakno, stu.uname, stu.address
     FROM tb_sugang su join tb_student stu
     ON su.hakno=stu.hakno
    )aa
GROUP BY aa.address, aa.hakno;

문2) 과목별 수강 신청 인원수, 과목코드, 과목명 조회

예시)

p001 2 OOP

p002 1 Oracle

…

1
2
3
4
5
6
7
8
9
10
11
SELECT AA.gcode
            ,AA.cnt
            ,gname
FROM(
    SELECT gcode
                ,COUNT(gcode) AS cnt
    FROM tb_sugang
    GROUP BY gcode
) AA JOIN tb_gwamok GW
ON AA.gcode=GW.gcode
ORDER BY AA.gcode;

문3) 지역별로 수강신청 인원수, 지역 조회

예시)

부산 1

제주 2

서울 2

1
2
3
4
5
6
7
8
SELECT aa.address, COUNT(aa.address)
FROM (
    SELECT stu.address, su.hakno
    FROM tb_sugang su JOIN tb_student stu
    ON su.hakno=stu.hakno
    GROUP BY stu.address, su.hakno
) aa
GROUP BY aa.address;

조인 종류

Visual_SQL_JOINS_V2

  • INNER JOIN (내부조인)
    • 두 테이블에 공통으로 존재하는 칼럼을 이용하는 방식. INNER 생략가능
1
2
3
4
5
6
SELECT stu.hakno
       ,stu.uname
       ,stu.phone
       ,su.gcode
FROM tb_student stu INNER JOIN tb_sugang su
ON stu.hakno=su.hakno;
  • LEFT OUTER JOIN
    • 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출
    • 왼쪽에 있는건 일치하지 않더라도 데이터를 가져오는거.
1
2
3
4
5
6
SELECT stu.hakno
       ,stu.uname
       ,stu.phone
       ,su.gcode
FROM tb_student stu LEFT OUTER JOIN tb_sugang SU --OUTER 생략가능
ON stu.hakno=su.hakno;
  • RIGHT OUTER JOIN
    • 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출
1
2
3
4
5
6
SELECT stu.hakno
            ,stu.uname
            ,stu.phone
            ,su.gcode
FROM tb_student stu RIGHT OUTER JOIN tb_sugang SU --OUTER 생략가능
ON stu.hakno=su.hakno;