[23일] Database (8) - 테이블 조인(inner join)

• 여러 테이블을 하나의 테이블처럼 사용하는 것
• 두 개 이상의 테이블을 결합하여 데이터를 추출하는 기법
• 두 테이블의 공통값을 이용하여 칼럼을 조합하는 수단

 

테이블 조인의 종류

1) inner join
   - 두 테이블에 공통으로 존재하는 칼럼을 이용하는 방식
   - inner 생략가능
   - 기본
   
2) left outer join
   - 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출
   - outer 생략가능
   
3) right outer join
   - 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출
   - outer 생략가능

 

형식

SELECT 칼럼명
FROM 테이블1 JOIN 테이블2
ON 조건절;                    --ANST(표준) SQL문
    
SELECT 칼럼명
FROM 테이블1, 테이블2
ON 조건절;                    --Oracle DB SQL문

SELECT T1.*, T2.*            --T1.모든칼럼, T2.모든칼럼
FROM T1 JOIN T2
ON T1.X=T2.X;                --테이블명.칼럼명

SELECT t1.*, T2.*, t3.*
FROM t1 JOIN T2
ON t1.X=T2.X JOIN t3            --3개 테이블 조인
ON t1.y=t3.y;
    
SELECT t1.*, T2.*, t3.*, t4.*   --4개 테이블 조인
FROM t1 JOIN T2
ON t1.X=T2.X JOIN t3
ON t1.y=t3.y JOIN t4
ON t1.z=t4.z;

 

물리적 테이블과 논리적 테이블

• 물리적 테이블 : 실제 create table 한 테이블
• 논리적 테이블 : SQL문에 의해 가공된 테이블

물리적 테이블과 논리적 테이블은 서로 동등한 관계이다.

 

 

조인

이전에 만들었던 수강 테이블과 학생 테이블을 학번을 기준으로 조인을 해보자

SELECT tb_sugang.*, tb_student.*
FROM tb_sugang JOIN tb_student
ON tb_sugang.hakno = tb_student.hakno;

 

tb_student 테이블

tb_sugang 테이블

join 테이블

위 조인을 inner join이라고 한다. inner join은 가장 기본이 되는 join이다.

-- 두 테이블 간의 교집합 조인. inner 생략 가능. 가장 기본
SELECT tb_sugang.*, tb_student.*
FROM tb_sugang INNER JOIN tb_student
ON tb_sugang.hakno = tb_student.hakno;

 

일부 칼럼만 조회를 해보자

-- 수강 신청한 학생들의 학번, 과목코드, 이름, 이메일 조회하시오
SELECT tb_sugang.*, tb_student.uname, tb_student.email
FROM tb_sugang INNER JOIN tb_student
ON tb_sugang.hakno = tb_student.hakno;

-- 과목 코드를 기준으로 수강 테이블과 과목 테이블 조인
SELECT tb_sugang.*, tb_gwamok.gname, tb_gwamok.ghakjum
FROM tb_sugang INNER JOIN tb_gwamok
ON tb_sugang.gcode=tb_gwamok.gcode;

 

여러 개의 테이블을 조인해보자

-- 3개 테이블 조인 : 수강 테이블 + 학생 테이블(이름) + 과목 테이블(과목명)
SELECT tb_sugang.*, tb_student.uname, tb_gwamok.gname
FROM tb_sugang JOIN tb_student
ON tb_sugang.hakno=tb_student.hakno JOIN tb_gwamok
ON tb_sugang.gcode=tb_gwamok.gcode;

 

테이블에 별칭도 부여할 수 있다.

SELECT su.*, ST.uname, ST.email
FROM tb_sugang SU JOIN tb_student ST
ON su.hakno=ST.hakno;

SELECT SU.*, GW.gname, GW.ghakjum
FROM tb_sugang SU JOIN tb_gwamok GW
ON SU.gcode=GW.gcode;

SELECT SU.*, ST.uname, ST.email, GW.gname, GW.ghakjum
FROM tb_sugang SU JOIN tb_student ST
ON SU.hakno=ST.hakno JOIN tb_gwamok GW
ON SU.gcode=GW.gcode;

 

조회 시 테이블 간에 중복되지 않은 칼럼명은 테이블 명을 생략할 수 있다.

tb_student

tb_sugang

tb_gwamok

위 세 테이블을 보면 uname, email, gname, ghakjum 칼럼은 중복되지 않는다. 이 경우 테이블 명을 생략할 수 있다.

SELECT SU.*, uname, email, gname, ghakjum
FROM tb_sugang SU JOIN tb_student ST
ON SU.hakno=ST.hakno JOIN tb_gwamok GW
ON SU.gcode=GW.gcode;

 

중복되는 칼럼명은 소속 테이블을 정확히 명시하지 않으면 오류가 발생한다.

-- 중복되는 칼럼명은 소속 테이블명을 정확히 명시해야 한다.
-- ORA-00918: 열의 정의가 애매합니다
SELECT hakno, gcode, uname, email, gname, ghakjum
FROM tb_sugang SU JOIN tb_student ST
ON SU.hakno=ST.hakno JOIN tb_gwamok GW
ON SU.gcode=GW.gcode;

 

연습

문제 1

문1)수강신청을 한 학생들 중에서 '제주'에 사는 학생들만 학번, 이름, 주소를 조회하시오

-- 수강 신청 한 학생들 목록 보기
SELECT * FROM tb_sugang;

-- 수강 테이블 + 학생 테이블
SELECT su.*, uname, address
FROM tb_sugang su JOIN tb_student ST
ON su.hakno=ST.hakno;

-- '제주'만 조회하기
SELECT su.*, uname, address
FROM tb_sugang su JOIN tb_student ST
ON su.hakno=ST.hakno
WHERE address='제주';
-----------------------------------------------------
SELECT su.hakno, su.gcode, uname, address
FROM tb_sugang su JOIN tb_student ST
ON su.hakno=ST.hakno;

-- 가공된 논리적 테이블의 이름을 AA라고 지정하고, 재가공 할 수 있다
select AA.hakno, AA.gcode, AA.uname, AA.address
from (
        SELECT su.hakno, su.gcode, uname, address
        FROM tb_sugang su JOIN tb_student ST
        ON su.hakno=ST.hakno
     ) AA
where aa.address='제주';

-- ORA-00904: "AA"."EMAIL": 부적합한 식별자
-- ST 테이블에서 eamil 칼럼을 가져오지 않았음
select AA.hakno, AA.gcode, AA.uname, AA.address, AA.email
from (
        SELECT su.hakno, su.gcode, uname, address
        FROM tb_sugang su JOIN tb_student ST
        ON su.hakno=ST.hakno
     ) AA
where aa.address='제주';

-- AA 별칭 생략 가능
select hakno, gcode, uname, address
from (
        SELECT su.hakno, su.gcode, uname, address
        FROM tb_sugang su JOIN tb_student ST
        ON su.hakno=ST.hakno
     )
where address='제주';

select *                                             -- * 사용 괜찮음
from (
        SELECT su.hakno, su.gcode, uname, address    -- 칼럼명이 노출된 상태에서는
        FROM tb_sugang su JOIN tb_student ST
        ON su.hakno=ST.hakno
     )
where address='제주';

 

문제 2

문2) 지역별로 수강신청 인원수, 지역을 조회하시오
     서울 2명
     제주 2명
-- 1) 수강 테이블 조회
select * from tb_sugang;

-- 2) 수강 신청한 학생들의 명단(학번)
select * from tb_sugang order by hakno;
select distinct(hakno) from tb_sugang order by hakno;
select hakno from tb_sugang group by hakno;  -- group by 절은 내부적으로 정렬을 가지고 있음

-- 3) 수강 신청한 학번(AA)들의 주소를 학생 테이블에서 가져오기
select AA.hakno, st.address
from (
        select hakno from tb_sugang group by hakno
     ) AA join tb_student st
on AA.hakno=st.hakno;

-- 4) 3)의 결과를 BB테이블로 만든 후 주소별 그룹 후 행 갯수 구하기
select BB.address, count(*) || '명' as cnt
from (
        select AA.hakno, st.address
        from (
                select hakno from tb_sugang group by hakno
             ) AA join tb_student st
        on AA.hakno=st.hakno
      ) BB
group by BB.address;
---------------------------------------------------------     
    select *
    from tb_student st join tb_sugang su
    on st.hakno = su.hakno;
    
    select distinct(st.hakno), address
    from tb_student st join tb_sugang su
    on st.hakno = su.hakno;
    
    select address, count(*)
    from (
        select distinct(st.hakno), address
        from tb_student st join tb_sugang su
        on st.hakno = su.hakno
    )
    group by address;

 

문제 3

문3) 과목별 수강 신청 인원수, 과목코드, 과목명를 조회하시오 
     d001 HTML   2명 
     d002 포토샵   1명
     d003 일러스트 1명
     p001 OOP    2명
    
    -- 1) 수강테이블에서 과목코드 정렬해서 조회하기
    select * from tb_sugang order by gcode;
    
    -- 2) 수강테이블에서 과목코드가 동일한 행을 그룹
    select gcode, count(*)
    from tb_sugang
    group by gcode;
    
    -- 3) 2)의 결과를 AA테이블로 생성하고
	select aa.gcode, concat(aa.cnt, '명'), gw.gname
	from (
            select gcode, count(*) as cnt
            from tb_sugang
            group by gcode
          ) AA join tb_gwamok gw
    on aa.gcode=gw.gcode
    order by aa.gcode;
    ----------------------------------------------------------
    -- 내가 푼 방법
    select aa.gcode, gw.gname, cnt
    from (
            select su.gcode, count(*) as cnt
            from tb_sugang su join tb_gwamok gw
            on su.gcode=gw.gcode
            group by su.gcode
            order by gcode
        ) aa join tb_gwamok gw
    on aa.gcode=gw.gcode;

 

문제 4

문4) 학번별 수강신청과목의 총학점을 학번별순으로 조회하시오
     g1001  홍길동  9학점
     g1002  홍길동  6학점
     g1005  진달래  9학점
    
    -- 1) 수강 테이블에서 학번 별로 조회
    select hakno from tb_sugang order by hakno;
    
    -- 2) 수강 테이블에 과목코드가 일치하는 학점을 과목 테이블에서 가져와서 붙이기
    select su.hakno, su.gcode, gw.ghakjum
    from tb_sugang su join tb_gwamok gw
    on su.gcode=gw.gcode;
    
    -- 3) 2)의 결과를 AA테이블로 만들고, 학번 별로 그룹화한 후, 학점의 합계를 구하기
    select aa.hakno, sum(aa.ghakjum) as hap
    from (
            select su.hakno, su.gcode, gw.ghakjum
            from tb_sugang su join tb_gwamok gw
            on su.gcode=gw.gcode
          ) AA
    group by aa.hakno;
    
    -- 4) 3)의 결과를 DB테이블로 만들고, 학번을 기준으로 학생 테이블에서 이름 가져와서 붙이기
    select bb.hakno, concat(bb.hap, '학점'), st.uname
    from (
            select aa.hakno, sum(aa.ghakjum) as hap
            from (
                    select su.hakno, su.gcode, gw.ghakjum
                    from tb_sugang su join tb_gwamok gw
                    on su.gcode=gw.gcode
                  ) AA
            group by aa.hakno
          ) BB join tb_student st
    on bb.hakno=st.hakno;
    ----------------------------------------------
    -- 또 다른 방법
    -- 1) 수강테이블 + 학생테이블 + 과목테이블 한번에 조인
    select su.hakno, su.gcode, st.uname, gw.ghakjum
    from tb_sugang su join tb_student st
    on su.hakno=st.hakno join tb_gwamok gw
    on su.gcode=gw.gcode
    order by su.hakno;
    
    -- 2) 1차 그룹(학번), 2차 그룹(이름)으로 그룹화하고, 총 학점 구하기
    select su.hakno, st.uname, sum(gw.ghakjum) || '학점' as hap
    from tb_sugang su join tb_student st
    on su.hakno=st.hakno join tb_gwamok gw
    on su.gcode=gw.gcode
    group by su.hakno, st.uname
    order by su.hakno;
    -----------------------------------------------
    -- 내가 푼 방법
    -- 학번별 수강신청과목 총 학점 
    select su.hakno, sum(ghakjum) as 총학점
    from tb_gwamok gw join tb_sugang su
    on gw.gcode=su.gcode
    group by su.hakno
    order by su.hakno;
    
    -- 위 결과와 이름 연결
    select aa.hakno, uname, grade || '학점' as 총학점
    from (
        select su.hakno, sum(ghakjum) as grade
        from tb_gwamok gw join tb_sugang su
        on gw.gcode=su.gcode
        group by su.hakno
        order by su.hakno
    ) aa join tb_student bb
    on aa.hakno=bb.hakno;

 

문제 5

문5) 학번 g1001이 수강신청한 과목을 과목코드별로 조회하시오
     g1001  p001  OOP
     g1001  p003  JSP  
     g1001  d001  HTML
    
     -- 1) 수강 테이블 + 과목 테이블 조인
     select su.hakno, su.gcode, gw.gname
     from tb_sugang su join tb_gwamok gw
     on su.gcode = gw.gcode;
     
     -- 2) 학번 g1001이 신청한 정보 조회하기
     select su.hakno, su.gcode, gw.gname
     from tb_sugang su join tb_gwamok gw
     on su.gcode = gw.gcode
     where su.hakno='g1001'
     order by su.gcode;
     -----------------------------------------
     -- 내가 푼 방법
     select su.hakno, gw.gcode, gname
     from tb_gwamok gw join tb_sugang su
     on gw.gcode = su.gcode
     where su.hakno='g1001';

 

문제 6

문6)수강신청을 한 학생들의 학번, 이름 조회
    -- 1) 수강신청을 한 학생들의 학번 조회
    select hakno from tb_sugang;
    select distinct(hakno) from tb_sugang;
    select hakno from tb_sugang group by hakno;
    
    -- 2)
    select hakno, uname
    from tb_student
    where hakno='g1001' or hakno='g1002' or hakno='g1005' or hakno='g1006';
    
    select hakno, uname
    from tb_student
    where hakno in ('g1001', 'g1002', 'g1005', 'g1006');
    
    -- 3)
    select hakno, uname
    from tb_student
    where hakno in (select hakno from tb_sugang group by hakno);
    ---------------------------------
    -- 내가 푼 방법
    select distinct(st.hakno), uname
    from tb_student st join tb_sugang su
    on st.hakno = su.hakno;

 

문제 7

문7)수강신청을 하지 않은 학생들의 학번, 이름 조회
    select hakno, uname
    from tb_student
    where hakno not in (select hakno from tb_sugang group by hakno);
    --------------------------------------
    -- 내가 푼 방법
    select hakno, uname
    from tb_student
    minus
    select distinct(st.hakno), uname
    from tb_student st join tb_sugang su
    on st.hakno = su.hakno;
    
    select st.hakno, uname
    from tb_student st left join tb_sugang su
    on st.hakno = su.hakno
    where su.hakno is null;