JOIN

SELECT 문을 통해 조회할 수 있게 되었다.

이제는 두 개 이상의 테이블들에 저장된 정보를 연관시켜 조회할 수 있도록 해주는 조인 연산에 대해 배울 것이다.

 

1. 조인 연산 -> 두 개 이상 테이블에 저장된 정보를 연관시켜 조회

2. Inner Join, Natural Join, Outer Join, Cross Join

3. WHERE 절, ON 절, USING 절에서 사용되는 Join

4. 셀프 조인

5. 다양한 논리적 관계(테이블 구성 달라도 조인 가능) -> 테이블 연관시켜 조회

---

JOIN(조인)

 

무결성 때문에 데이터 중복을 막기 위해 테이블을 여러 개로 쪼개 놓는다.

-> 연관 있는 두 릴레이션(테이블)을 연결한 새로운 하나의 릴레이션 형태로 데이터 조회 가능

 

조인 조건

 

관계가 있어야 엮을 수 있기 때문에 조건을 충족시켜야 한다.

 

1. 참조관계(기본키 - 외래키 값의 연관)

: 기본키와 외래키로 연결된 두 속성 값이 같은 경우

 

2. 속성 간 논리적 연관 관계

: 암시적인 관계

 

=> 명시적인 참조관계가 아니더라도 논리적으로 연관이 있으면 조인이 가능하다.

 

 

조인 연산

 

: 조인할 테이블들은 FROM 절에 명시

 

** FROM 절에 여러 개의 테이블들이 조인 연산자로 연결되더라도,

조인 연산은 한 번에 단 두 개의 테이블에 대해서만 동작 가능하다.

=> A⋈B⋈C 이면 (A⋈B)⋈C

 

select ATTRIBUTE_1, ATTRIBUTE_2, ..., ATTRIBUTE_n
from JOINED_TABLES // from 절에 조인할 테이블들 써준다
where SELECT_PREDICATES
group by GROUPING_ATTRIBUTES
having GROUP_AGGREGATION_FUNCTION
order by ORDERING_ATTRIBUTES;

 

조인 연산의 종류

 

1. 암시적 조인

: 카티션 곱 기반 -> 결과 중 조건을 만족하는 투플만 필터링해 도축

: 가장 기본적인 형태의, 고전적 방식의 조인

: 컴마 / CROSS JOIN으로 연결된 릴레이션 + WHERE절

: 검색 조건과 조인 조건의 명시적 구분 x -> 검색 조건 파트와 조인 조건 파트를 모두 AND로 연결해 WHERE절에 기술

 

* 컴마/CROSS JOIN 사용할 거면 꼭 WHERE 절에 해줘야 함. ON/USING절에 쓰면 에러 !!!

select TAB_1.ATT_1, TAB_2.ATT_2, ..., TAB_n.ATT_n
from TAB_1, TAB_2, ..., TAB_n //컴마로 엮어줌
where JOIN_PREDICATES and SELECT_PREDICATES; // 검색 조건과 조인 조건 모두 where 절에 and로 연결해 써줌

select TAB_1.ATT_1, TAB_2.ATT_2, ..., TAB_n.ATT_n
from TAB_1 cross join TAB_2 cross join ... cross join TAB_n // cross join으로 엮어줌
where JOIN_PREDICATES and SELECT_PREDICATES; // 검색 조건과 조인 조건 모두 where 절에 and로 연결해 써줌

 

<예제>

배우 정보(이름/성별/현시점 출연료)와 그 배우가 출연한 영화 정보(그 당시 출연료)를 한꺼번에 조회하고 싶을 때

-> 조회하고 싶은 정보들이 두 테이블에 흩어져 있기 때문에 조인을 써줘야 한다.

왼쪽 테이블에 배우 정보가, 오른쪽 테이블에 출연한 영화 정보가 들어있기 때문에 조인 !!!

select ac.actor_no "actor_no(ac)", //별칭 ac, ap 주었기 때문에 계속 별칭 써줘야 한다.
	ac.name, ac.gender,
    ac.guarantee "guarantee_cur",
    ac.actor_no "actor_no(ap)",
    ap.movie,
    ap.guarantee "guarantee_then"
from actor ac cross join appearance ap // cross join 대신 ','도 가능
where ac.actor_no = ap.actor_no // actor_no가 일치하는 행만 추출
order by ac.actor_no;

actor_no가 일치하는 행만 추출

 

 

2. 명시적 조인

: 표준 조인

: 조인 연산자 사용 -> 다양한 방식으로 두 릴레이션 연결해 조회

: 조인 연산자 + ON절/USING절

: 검색 조건과 조인 조건의 명시적 구분 o

* 검색 조건: WHERE절에 기술

* 조인 조건: ON/USING절에 기술

** USING 쓸 경우 괄호 필수, 컬럼명만 써주면 알아서 비교해줌 !!!(자주 쓰이는 방식이기 때문에 단순화한 것)

select TAB_1.ATT_1, TAB_2.ATT_2, ..., TAB_n.ATT_n
from TAB_1 join_op TAB_2 //우선 TAB_1과 TAB_2 조인
on JOIN_PREDICATE_1
join_op TAB_3 // 그 결과에 TAB_3 조인
on JOIN_PREDICATE_2
...
join_op TAB_n
on JOIN_PREDICATE_n-1 //두 테이블의 조인 대상 컬럼명이 같아야 함
where SELECT_PREDICATES;

select TAB_1.ATT_1, TAB_2.ATT_2, ..., TAB_n.ATT_n
from TAB_1 join_op TAB_2 //우선 TAB_1과 TAB_2 조인
on JOIN_PREDICATE_1
join_op TAB_3 // 그 결과에 TAB_3 조인
on JOIN_PREDICATE_2
...
join_op TAB_n
using JOIN_PREDICATE_n-1 //컬럼명만 써주기 !(단 두 테이블의 조인 대상이 되는 컬럼명이 같아야 함)
where SELECT_PREDICATES;

 

 

(1) INNER JOIN(내부 조인)

: 조인 조건을 만족하는 투플들만 조인 결과에 포함

: JOIN만 써주면 디폴트로 INNER JOIN이다.

: 다음과 같이 조인 조건 있는 조인 연산에 대응

select ac.actor_no "actor_no(ac)", // 해당 쿼리 내에서는 무조건 별칭 써줘야 함
	ac.name, ac.gender,
    ac.guarantee "guarantee_cur",
    ap.actor_no "actor_no(ap)",
    ap.movie,
    ap.guarantee "guarantee_then"
from actor ac join appearance ap
on ac.actor_no = ap.actor_no; // on 절에 조인 조건 써줌 !!!

 

select ac.actor_no "actor_no(ac)", // 해당 쿼리 내에서는 무조건 별칭 써줘야 함
	ac.name, ac.gender,
    ac.guarantee "guarantee_cur",
    ap.actor_no "actor_no(ap)",
    ap.movie,
    ap.guarantee "guarantee_then"
from actor ac join appearance ap
using(actor_no); //using절 쓰면 조인 조건에 해당하는 컬럼명만 적어주면 된다.(모든 표준조인 연산 공통)

 

=> (중요)이때 두 테이블 모두 actor_no와 guarantee를 가지고 있기 때문에 조인할 때는 이름을 서로 다르게 주어서 구별해줘야 한다 !!!

- actor_no(ac) & actor_no(ap)

- guarantee_cur & guarantee_then

 

=> 구분해주지 않으면 다음과 같이 에러가 난다.

 

<on절 사용했을 때>

select actor_no //에러
	ac.name, ac.gender,
    guarantee, //에러
    ap.movie
from actor ac inner join appearance ap
on ac.actor_no = ap.actor_no;

=> 오류 이유: actor_no & guarantee 컬럼 이름은 모두 중복되는데, on절에 actor_no & guarantee 써놓고 select 절에서는 구분해주지 않았기 때문

 

<having절 사용했을 때>

select actor_no, //using절이므로 ok
	ac.name, ac.gender,
	guarantee, //에러
    ap.movie
from actor ac inner join appearance ap
using (actor_no);

=> 오류 이유: actor_no & guarantee 컬럼 이름은 모두 중복되는데, having절에 actor_no만 있고 gender는 없기 때문

=> 해결: 3번째줄 "guarantee" -> "ap.guarantee" 또는 "ac.guarantee"

 

======>   이름이 중복되는데 컬럼 구분해주지 않으려면 using절 쓰자.

on절 쓰면 무조건 컬럼 구분해줘야 한다.

 

출력 결과도 같은 이름의 컬럼 구분이 원칙 !!

select *
from actor ac inner join appearance ap
using (actor_no); // actor_no는 하나만 출력, guarantee는 using에 안 썼으므로 두 테이블꺼 모두 출력

MySQL에서는 using절을 썼을 때 위와 같이 같은 이름의 컬럼을 구분하지 않는 것도 허용하지만,

원칙은 다음과 같이 테이블명을 넣어 구분해줘야 한다.

select actor_no,
	ac.name, ac.gender,
    ac.guarantee "guarantee", //이런 식으로 이름을 주는 게 원칙
    ap.movie,
    ap.guarantee "guarantee", //이런 식으로 이름을 주는 게 원칙
from actor ac inner join appearance ap
using(actor_no); //actor_no는 using 써줬으므로 하나만 출력해줌, 하지만 guarantee는 아님

 

둘 중 어느 쪽이든 MySQL에서는 다음과 같은 출력 결과를 가져온다.

 

(2) NATURAL JOIN(자연 조인)

: INNER JOIN의 간소화된 버전

: 동일한 이름의 속성들끼리 같은 값을 갖는 투플들을 데이터베이스가 자동으로 연결

(두 테이블을 비교하며, 이름이 동일한 컬럼들을 자동으로 찾아줌)

=> 모든 중복되는 컬럼의 값이 모두 같아야 자연 조인 가능

: on/using절 사용 불가

: 조인 조건 없는 조인 연산에 대응

select *
from actor natural join appearance;

 

따라서 두 테이블에서 빨간색 컬럼들만 조인이 가능한 것이다.

 

즉, 다음과 같은 INNER JOIN 쿼리와 같은 결과를 가져온다.

select ac.actor_no "actor_no",
	ac.guarantee "guarantee",
    ac.name, ac.gender,
    ap.movie
from actor ac appearance ap
on ac.actor_no = ap.actor_no // on절 사용
and ac.guarantee = ap.guarantee;

select ac.actor_no "actor_no"
	ac.guarantee "guarantee"
    ac.name, ac.gender,
    ap.movie
from actor ac inner join appearance ap
using(actor_no, guarantee); //using절 사용

 

하지만 자연조인보다는 다음과 같이 컬럼 개수가 늘어나는 한이 있더라도 내부조인을 써서 의도에 맞는 결과를 내는 것이 좋다.

select ac.actor_no "actor_no",
	ac.name, ac.gender,
    ac.guarantee "guarantee_cur",
    ac.movie
    ap.guarantee "guarantee_then"
from actor ac inner join appearance ap
on ac.actor_no = ap.actor_no;

조인할 테이블

결과

(3) OUTER JOIN(외부 조인)

: outer join이라고만 쓰면 안 되고, 꼭 어떤 outer join을 쓸건지 명시해줘야 한다.(left/right/full)

=> 이렇게 외부 조인은 조인조건을 만족하지 않은 것들도 출력해준다.

왜?) 예컨대 사원들의 매니저를 출력하고 싶을 때, 사장님도 누군가의 매니저가 될 수 있기 때문에, 조인 조건 만족시키지 않는 사장님도 포함해야 함.

이외 곳의 정보는 모두 NULL로 출력

 

- LEFT OUTER JOIN

select ac.actor_no "actor_no(ac)",
	ac.name, ac.gender,
    ac.guarantee "guarantee_cur",
    ap.actor_no "actor_no(ap)",
    ap.movie,
    ap.guarantee "guarantee_then"
from actor ac left outer join appearance ap
on ac.actor_no = ap.actor_no;

 

=> 조인조건을 원래 만족하지 않는 "actor_no=3333"인 행은 appearance에 관한 정보는 없기 때문에 NULL로 채워서 출력된다.

 

- RIGHT OUTER JOIN

select ac.actor_no "actor_no(ac)",
	ac.name, ac.gender,
    ac.guarantee "guarantee_cur",
    ap.actor_no "actor_no(ap)",
    ap.movie,
    ap.guarantee "guarantee_then"
from actor ac right outer join appearance ap
on ac.actor_no = ap.actor_no;

 

- FULL OUTER JOIN

: MySQL에서는 지원하지 않음

: left outer join과 right outer join의 합집합

select ac.actor_no "actor_no(ac)",
	ac.name, ac.gender,
    ac.guarantee "guarantee_cur",
    ap.actor_no "actor_no(ap)",
    ap.movie,
    ap.guarantee "guarantee_then"
from actor ac full outer join appearance ap
on ac.actor_no = ap.actor_no;

 

다양한 조인 연산

 

"두 속성의 값이 같다"가 조인 조건인 경우가 대부분이지만,

다른 종류의 조인 조건도 있다. 이번엔 이걸 살펴볼 것이다.

 

3개 이상의 테이블 조인

 

Q) 2018년 봄 학기에 컴퓨터공학과에서 개설된 강의를 수강한 학생들에 대하여, 각 수강생들의 이름과, 각 수강 과목명, 분반, 담담 교수명, 개설학과, 담당 교수의 소속 학과를 검색하라.

<조회할 컬럼>

수강생 이름: student 테이블의 name

수강 과목명: course 테이블의 title

분반: section 테이블의 sec_id

담당 교수명: instructor 테이블의 name

개설 학과: course 테이블의 dept_name

담당 교수의 소속학과: instructor 테이블의 dept_name

 

 

<JOIN할 테이블>

: student, course, section, instructor, teaches, takes

select student.name as student,
	instructor.name as instructor
    title as course
    section.sec_id as section,
    course.dept_name as course_from,
    instructor.dept_name as instructor_from
from student inner join takes //여기서부터
on student.ID = takes.ID
inner join section
on takes.course_id = section.course_id
and takes.sec_id = section.sec_id
and takes.semester = section.semester
and takes.year = section.year
inner join course
on section.course_id = course.course_id
inner join teaches
on section.course_id = teaches.course_id
and section.sec_id = teaches.sec_id
and section.semester = teaches.semester
and section.year = teaches.year
inner join instructor
on teaches.ID = instructor.ID //여기까지 모두 join 조건
where section.year = 2018
and section.semester = 'Spring'
and course.dept_name = 'Comp. Sci.';

 

 

속성 간의 다양한 논리적 관계에 의한 조인

 

Q1) 2018년 봄 학기에 개설된 모든 강의의 각 분반에 대해서,

각 강의 분반과 동일한 건물에서 개설된 다른 강의들을 분반 단위로 조회하라.

단, 조회 기준이 되는 강의 분반과 동일한 강의 분반은 출력 결과에서 제외한다.

 

A) "셀프 조인" => 두 개의 section: S & T

 

출력의 기준이 되는 데이터와 출력해야 하는 데이터들이 같은 테이블을 기반으로 나온다면, 셀프 조인해야 함

-> 하나의 테이블을 마치 두 개처럼 사용 가능

 

T.year = S.year = '2018'

T.semester = S.semester = 'Spring'

 

같은 건물에서 열리는: S.building = T.building

조회 기준 강의분반과 동일한 강의 분반은 출력에서 제외: where (S.sec_id != T.sec_id) or (S.course_id != T.course_id)

 

select S.course_id, S.sec_id, S.building,
	T.course_id as other_course,
    T.sec_id as other_sec
    T.building as same_building
from section S inner join section T
on S.building = T.building
where S.year = '2018'
and S.semester = 'Spring'
and T.year = 2018
and T.semester = 'Spring'
and S. (S.course_id != T.course_id
	or S.sec_id != T.sec_id) // 조회 기준이 되는 강의 분반과 동일한 강의 분반은 출력 결과에서 제외
order by S.course_id, S.sec_id, T.course_id; //차례대로 오름차순

 

11번째 줄은 다음과 같은 코드이다. 헷갈리면 다음과 같이 써줘도 된다.

즉, course_id 와 sec_id가 모두 같으면 동일한 강의 분반이므로 제외해달라는 뜻 !!

and not (S.course_id = T.course_id and S.sec_id = T.sec_id)

 

 

 

Q2) 컴퓨터공학과 소속 교수들 중 가장 낮은 급여를 받는 교수보다 급여를 많이 받는 타학과 소속 교수들을 중복 없이 조회하라.

 

select: T.name, T.dept_name, T.salary

on(조인조건): S.salary = T.salary & S.dept_name='Comp. Sci.' -> 컴퓨터공학과 교수랑 급여 비교해야 하기 때문 !!

where(검색조건): 타학과 교수들(T.dept_name != 'Comp. Sci.')

select distinct T.name as other_prof // 중복 없이 조회
	T.dept_name as others_dept
    T.dalary as others_sal
from instructor T inner join instructor S //셀프조인
on (T.salary > S.salary) and (S.dept_name = 'Comp. Sci.') // 급여를 더 많이 받아야 하고, 컴퓨터공학과 소속이어야 함
where T.dept_name !='Comp. Sci.'; // 같은 컴퓨터공학과인 교수들은 출력 행에서 제거

 

왼쪽은 distinct 이전, 오른쪽은 distinct 한 후(중복 제거)

 

Q3) 2018년 봄 학기에 개설된 모든 강의의 각 분반에 대해서

각 강의 분반의 capacity만을 기준으로 대여 가능한 다른 강의실들을 모두 조회하라.

단, 조회 기준이 되는 분반의 강의실과 동일한 강의실은 출력 결과에서 제외한다.

 

<조인 대상 테이블들>

- section 테이블

- classroom 테이블(capacity 비교 대상) = S

- 조회할 classroom 테이블 = T

=> 셀프조인 필요

 

"select"

course_id, sec_id, S.building, 등

 

"on(조인조건)"

(section&S)- 기본키 같다는 조건

(T&S)- T.capacity >= S.capacity

 

 

"where(검색조건)"

section.year = '2018'

section.semester = 'Spring'

not(S.sec_id = T.sec_id and S.course_id = T.course_id)

 

select course_id, sec_id,
	S.building, S.room_number, S.capacity
    T.building as capable_bld,
    T.room_number as capable_roomnum
    T.capacity as capable_capacity
from section innerjoin classroom S
on section.building = S.building
and section.room_number = S.room_number
inner join classroom T
on T.capacity >= S.capacity
where section.year = 2018
and section.semester = 'Spring'
and (S.building != T.building
	or S.room_number != T.room_number)
//and not (S.building = T.building and S.room_number = T.room_number)
order by course_id, sec_id, T.capacity;