728x90
조인
- 서로 관련된 테이블의 데이터를 연결하여 추출하는 방법
- 기법
1) CROSS 조인(=Cartesian product)
2) NATURAL 조인
3) JOIN ~ ON
4) OUTER JOIN
1. cross join : 두 테이블의 데이터를 1:1로 모두 연결한다.
select mno, name from memb;
select mno, work, bank from stnt;
/* => mno가 어떤 테이블의 컬럼인지 지정하지 않으면 실행 오류!*/
select mno, name, mno, work, bank
from memb cross join stnt;
/* => select 컬럼이 두 테이블 모두 있을 경우,
컬럼명 앞에 테이블명을 명시하여 구분하라!*/
select memb.mno member_no, name, stnt.mno student_no, work, bank
from memb cross join stnt;
/* 예전 문법 */
select memb.mno member_no, name, stnt.mno student_no, work, bank
from memb, stnt;
/* => 컬럼명 앞에 테이블명을 붙이면 너무 길다.
테이블에 별명을 부여하고
그 별명을 사용하여 컬럼을 지정하라. */
select m.mno, name, s.mno, work, bank
from memb as m cross join stnt s;
/* 예전 문법 */
select m.mno, name, s.mno, work, bank
from memb m, stnt as s;
2. natural join: 같은 이름을 가진 컬럼 값을 기준으로 레코드를 연결한다.
select m.mno, name, s.mno, work, bank
from memb m natural join stnt s;
/* 예전 문법 */
select m.mno, name, s.mno, work, bank
from memb m, stnt s
where m.mno=s.mno;
/* natural join 의 문제점
* 1) 두 테이블의 조인 기준이 되는 컬럼 이름이 다를 때 연결되지 못한다.
2) 상관 없는 컬럼과 이름이 같을 때 잘못 연결된다.
3) 같은 이름의 컬럼이 여러 개 있을 경우 잘못 연결된다.
모든 컬럼의 값이 일치할 경우에만 연결되기 때문이다. */
/* 만약에 두 테이블에 같은 이름을 가진 컬럼이 여러 개 있다면,
join ~ using (기준컬럼) 을 사용하여
두 테이블의 데이터를 연결할 때 기준이 될 컬럼을 지정한다.*/
select m.mno, name, s.mno, work, bank
from memb m join stnt s using (mno);
/* natural join 의 문제점 2
=> 두 테이블에 같은 이름의 컬럼이 없을 경우
연결하지 못한다.*/
/* 만약 두 테이블에 같은 이름을 가진 컬럼이 없으면,
natural join을 수행하지 못한다.
또한 join using 으로도 해결할 수 없다.
이럴 경우 join ~ on 컬럼a=컬럼b 문법을 사용하여
각 테이블의 어떤 컬럼과 값을 비교할 것인지 지정하라!*/
select m.mno, name, s.mno, work, bank
from memb m inner join stnt s on m.mno=s.mno;
/* inner는 생략 가능하다 */
select m.mno, name, s.mno, work, bank
from memb m join stnt s on m.mno=s.mno;
/* 즉 inner join 은 기준 컬럼의 값이 일치할 때만 데이터를 연결한다. */
/* 예전의 조인 문법 = inner join */
select m.mno, name, s.mno, work, bank
from memb m, stnt s
where m.mno=s.mno;
3. [inner] join ~ on 의 문제점
- 반드시 on 에서 지정한 컬럼의 값이 같을 경우에만
두 테이블의 데이터가 연결된다.
- 같은 값을 갖는 데이터가 없다면 연결되지 않고, 결과로 출력되지 않는다.
/* 전체 강의 목록 */
select lno, titl, rno, mno from lect;
/* 전체 강의실 목록 */
select rno, loc, name from room;
/* 강의 테이블에서 강의명을 가져오고, 강의실 테이블에서 지점명과 강의실명을 가져오자. */
select
l.lno,
l.titl,
l.rno,
r.rno,
r.loc,
r.name
from lect l inner join room r on l.rno=r.rno;
/* inner join의 문제는 위의 경우처럼
강의실이 아직 지정되지 않은 강의의 경우 강의실 테이블의 데이터와 연결하지 못해
결과로 출력되지 않는 문제가 있다. */
/* inner join의 문제점 예2:
* 모든 강의장 이름을 출력하라.
* 단 강의장에 강의가 배정된 경우 그 강의 이름도 출력하라.
*/
select
r.rno,
r.name,
r.loc,
l.titl
from room r inner join lect l on r.rno = l.rno;
/* => 만약 기준 컬럼의 값과 일치하는 데이터가 없어서
다른 테이블의 데이터와 연결되지 않았다 하더라도
결과로 뽑아내고 싶다면 outer join을 사용하라!*/
/* 즉 아직 강의실이 배정되지 않은 강의 데이터도 출력하고 싶을 때
출력하고 싶은 테이블을 바깥쪽 테이블로 지정하라!
*/
select
l.lno,
l.titl,
r.rno,
r.loc,
r.name
from lect l left outer join room r on l.rno=r.rno;
/* 왼쪽 테이블인 lect를 기준으로 room 데이터를 연결한다.
* 만약 lect와 일치하는 데이터가 room에 없더라도
* lect 데이터를 출력한다!
*/
/* 요구사항:
모든 멤버의 번호와 이름을 출력하라!
단 학생의 경우 재직여부도 출력하라!*/
-- 1) 모든 멤버 데이터 출력하기
select mno, name
from memb;
-- 2) 학생 데이터를 가져와서 연결하기
select mno, name, work
from memb natural join stnt;
select mno, name, work
from memb join stnt using(mno);
select memb.mno, name, work
from memb, stnt
where memb.mno=stnt.mno;
select memb.mno, name, work
from memb inner join stnt on memb.mno=stnt.mno;
select memb.mno, name, work
from memb join stnt on memb.mno=stnt.mno;
select m.mno, name, work
from memb m join stnt s on m.mno=s.mno;
/* 안타깝게도 위의 SQL문은 학생 목록만 출력한다.
왜? memb테이블의 데이터와 stnt 테이블의 데이터를
연결할 때 mno가 같은 데이터만 연결하여 추출하기 때문이다.
해결책!
상대 테이블(stnt)에 연결할 대상(데이터)이 없더라도
select에서 추출하는 방법 */
select m.mno, name, work
from memb m left outer join stnt s on m.mno=s.mno;
/* 여러 테이블의 데이터를 연결하기
=> 다음의 결과가 출력될 수 있도록 수강 신청 데이터를 출력하시오!
수강신청번호, 강의명, 학생명, 재직여부, 수강신청일, 강의실명, 매니저명, 직위 */
/* 1단계: 수강신청 데이터를 출력 */
select la.lano, la.lno, la.mno, la.rdt
from lect_appl la;
/* 2단계: 수강신청한 학생의 번호 대신 이름을 출력 */
select la.lano, la.lno, m.name, la.rdt
from lect_appl la
inner join memb m on la.mno=m.mno;
/* 3단계: 수강 신청한 학생의 재직 여부 출력
* => inner join 에서 inner는 생략 가능
*/
select la.lano, la.lno, m.name, s.work, la.rdt
from lect_appl la
join memb m on la.mno=m.mno
join stnt s on la.mno=s.mno;
/* 4단계: 수상신청한 강의 번호 대신 강의명을 출력 */
select la.lano, l.titl, m.name, s.work, la.rdt, l.rno
from lect_appl la
join memb m on la.mno=m.mno
join stnt s on la.mno=s.mno
join lect l on la.lno=l.lno;
/* 5단계: 강의실 이름을 출력한다.
* => 강의실 번호는 lect 테이블 데이터에 있다.
* => 강의실 이름은 room 테이블 데이터에 있다.
*/
select la.lano, l.titl, m.name, s.work, la.rdt, r.name, l.mno
from lect_appl la
join memb m on la.mno=m.mno
join stnt s on la.mno=s.mno
join lect l on la.lno=l.lno
left outer join room r on l.rno=r.rno;
/* 6단계: 매니저 이름을 출력
* => 매니저 번호는 lect 테이블에 있다.
* => 매니저 이름은 memb 테이블에 있다.
*/
select
la.lano,
l.titl,
m.name member_name,
s.work,
la.rdt,
r.name room_name,
m2.name manager_name
from lect_appl la
join memb m on la.mno=m.mno
join stnt s on la.mno=s.mno
join lect l on la.lno=l.lno
left outer join room r on l.rno=r.rno
left outer join memb m2 on l.mno=m2.mno;
/* 7단계: 매니저의 직위 출력
* => 매니저 번호는 lect 테이블 있다.
* => 매니저 직위는 mgr 테이블에 있다.
*/
select
la.lano,
l.titl,
m.name snm,
s.work,
la.rdt,
r.name rnm,
m2.name mnm,
mr.posi
from lect_appl la
join memb m on la.mno=m.mno
join stnt s on la.mno=s.mno
join lect l on la.lno=l.lno
left outer join room r on l.rno=r.rno
left outer join memb m2 on l.mno=m2.mno
left outer join mgr mr on l.mno=mr.mno;
서브 쿼리
- 쿼리문 안에 쿼리문을 실행하는 기법
- 성능 문제를 생각하면서 사용해야 한다.
1. join이용하여 데이터를 추출한 방법
select la.lano, l.titl, m.name, s.work, la.rdt, r.name, m2.name, mr.posi
from lect_appl la
inner join memb m on la.mno=m.mno
inner join stnt s on la.mno=s.mno
inner join lect l on la.lno=l.lno
left outer join room r on l.rno=r.rno
left outer join memb m2 on l.mno=m2.mno
left outer join mgr mr on l.mno=mr.mno;
/* select 절에 서브쿼리 사용하기 */
/* 수강신청 데이터를 출력 */
select
la.lano,
la.lno,
la.mno,
la.rdt
from lect_appl la;
/* => 1단계: 수강신청 데이터를 출력 */
select
la.lano,
la.lno,
la.mno,
date_format(la.rdt, '%m/%d/%Y') reg_dt
from lect_appl la;
/* => 2단계 : 서브 쿼리를 이용하여 강의명을 가져오기
- 단, 컬럼 자리에 사용할 때는 결과 값이 한 개여야 한다.
- 결과 값이 여러 개가 리턴된다면 컬럼 값으로 사용할 수 없기 때문에 오류이다.
- 또한 컬럼 개수도 한 개여야 한다.
*/
select
la.lano,
(select titl from lect where lno=la.lno) as lect_title,
la.mno,
la.rdt
from lect_appl la;
/* => 3단계 : 서브 쿼리를 이용하여 학생명을 가져오기 */
select
la.lano,
(select titl from lect where lno=la.lno) as lect_title,
(select name from memb where mno=la.mno) as stud_name,
la.rdt
from lect_appl la;
2. from 절에 서브쿼리 사용하기
/* 0단계 : 강의 정보를 가져온다. */
select
l.lno,
l.titl,
l.rno,
l.mno
from lect l;
/* 1단계 : 강의 상세 정보를 가져오는 select를 준비한다.
=> 서브 쿼리를 이용하여 강의실 이름과 매니저 이름, 직위 정보를 가져오기 */
select
l.lno,
l.titl,
(select name from room where rno=l.rno) as room_name,
(select name from memb where mno=l.mno) as manager_name,
(select posi from mgr where mno=l.mno) as manager_posi
from lect l;
/* 2단계: 위에서 준비한 select 결과를 가상 테이블로 사용하여
기존의 lect_appl 테이블과 조인한다.*/
select
la.lano,
/*(select titl from lect where lno=la.lno) as lect_title,*/
(select name from memb where mno=la.mno) as stud_name,
lec.titl,
lec.room_name,
lec.manager_name,
lec.manager_posi
from lect_appl la
join (select
l.lno,
l.titl,
(select name from room where rno=l.rno) as room_name,
(select name from memb where mno=l.mno) as manager_name,
(select posi from mgr where mno=l.mno) as manager_posi
from lect l) as lec on la.lno=lec.lno;
/* lect_appl 테이블 대신에 서브 쿼리의 결과를 테이블로 사용할 수 있다. */
select
la2.lano,
la2.rdt,
la2.sname,
la2.work,
l2.titl,
l2.rname,
l2.mname,
l2.posi
from (
select
la.lano,
la.lno,
la.rdt,
m.name sname,
s.work
from lect_appl la
inner join memb m on la.mno=m.mno
inner join stnt s on la.mno=s.mno) la2
inner join (
select
l.lno,
l.titl,
r.name rname,
m.name mname,
mr.posi
from lect l
left outer join room r on l.rno=r.rno
left outer join memb m on l.mno=m.mno
left outer join mgr mr on l.mno=mr.mno
) l2 on la2.lno=l2.lno;
/* from 절에서 반복적으로 사용하는 서브 쿼리가 있다면,
* 차라리 가상 테이블인 view로 정의해놓고 사용하는 것이 편하다.
*/
create view lect2 as
select
l.lno,
l.titl,
(select name from room where rno=l.rno) as room_name,
l.mno as manager_no,
(select name from memb where mno=l.mno) as manager_name,
(select posi from mgr where mno=l.mno) as manager_posi
from lect l;
/* 위의 질의문을 view를 사용하여 다시 작성해보자! */
select
la.lano,
(select name from memb where mno=la.mno) as stud_name,
lec.titl,
lec.room_name,
lec.manager_name,
lec.manager_posi
from lect_appl la
join lect2 lec on la.lno=lec.lno;
3. where 절에 서브쿼리 사용하기
/* 과장 또는 대리 매니저가 담당하고 있는 수강 신청만 추출하기 */
select
la.lano,
/* (select titl from lect where lno=la.lno) as lect_title, */
(select name from memb where mno=la.mno) as stud_name,
lec.titl,
lec.room_name,
/* lec.manager_no, */
lec.manager_name,
lec.manager_posi
from lect_appl la
join lect2 as lec on la.lno=lec.lno
where
lec.manager_no in (select mno from mgr where posi in ('과장', '주임'));
데이터를 특정 컬럼을 기준으로 그룹으로 묶어 질의하기
1. group by ~ having ~
/* 각 지점별 강의실 수 구하기*/
-- 1단계: 강의실 목록 구하기
select
r.rno,
r.loc,
r.name
from
room r;
-- 2단계: 지점정보를 저장한 컬럼을 기준으로 그룹으로 묶는다.
select
r.rno, -- 그룹으로 묶인 경우 그 그룹의 첫 번째 데이터 값만 출력한다.
r.loc,
r.name -- 그룹으로 묶인 경우 그 그룹의 첫 번째 데이터 값만 출력한다.
from
room r
group by
r.loc;
-- 3단계: group by를 통해 데이터를 그룹으로 묶은 경우
-- 개별 항목의 값을 나타내는 컬럼의 값은 의미가 없기 때문에 제거한다.
select
r.loc
from
room r
group by
r.loc;
-- 4단계: 그룹으로 묶은 경우 그룹 관련 함수를 사용할 수 있다.
select
r.loc,
count(*) as cnt
from
room r
group by
r.loc;
-- 5단계: group by의 결과에서 최종 결과를 선택할 조건을 지정하고 싶다면
-- having절을 사용한다.
select
r.loc,
count(*) as cnt
from
room r
group by
r.loc
having
r.loc = '종로'; -- having의 조건은 그룹으로 묶을 때 사용한 컬럼이어야 한다.
-- 다음은 실행 오류!
select
r.loc,
count(*) as cnt
from
room r
group by
r.loc
having
r.name = '301'; -- having의 조건은 그룹으로 묶을 때 사용한 컬럼이어야 한다.
-- 다음은 실행 오류!
select
r.loc,
count(*) as cnt
from
room r
group by
r.loc
having
r.cnt > 3; -- having의 조건은 그룹으로 묶을 때 사용한 컬럼이어야 한다.
'DB' 카테고리의 다른 글
DBMS에 SQL문 보내기 : select,update,delete, FK 가 참조하는 데이터 지우는 방법, insert 한 후 auto increment PK 값 리턴 받기 (0) | 2021.10.20 |
---|---|
데이터 관리를 DBMS에게 맡기기 : JDBC API 사용 (0) | 2021.10.19 |
FK(Foreign Key) (0) | 2021.10.11 |
DQL(Data Query Language) (0) | 2021.10.11 |
DDL - 테이블 생성, 컬럼타입 (0) | 2021.10.10 |