웹개발 교육/Database
[23일] Database (8) - 테이블 조인(inner join)
ewok
2022. 8. 26. 17:38
- 여러 테이블을 하나의 테이블처럼 사용하는 것
- 두 개 이상의 테이블을 결합하여 데이터를 추출하는 기법
- 두 테이블의 공통값을 이용하여 칼럼을 조합하는 수단
테이블 조인의 종류
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;
위 조인을 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;
조회 시 테이블 간에 중복되지 않은 칼럼명은 테이블 명을 생략할 수 있다.
위 세 테이블을 보면 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;