Join 명령문

JOIN (조인) 연산자
두개 이상의 테이블을 연결하여 데이터를 검색할때 사용한다.
데이터베이스 시스템과 그 환경의 성능을 확인하는데도 사용할 수 있다. (느린 쿼리를 사용하여 비교)
조인에는 여러가지 종류가 있다.
- 내부조인(INNER JOIN) : 조건을 사용해서 두 테이블의 레코드를 결합한다.
- 외부조인(OUTER JOIN) : 내부조인과 비슷한데 일치하지 않는 열까지 반환하며 그 열은
NULL로 반환한다.
- 동등 조인(EQUI JOIN) : 내부조인이다. 두 테이블 사이의 같은 행들을 반환한다.
- 비동등 조인(NON-EQUI JOIN) : 내부조인이다. 두 테이블 사이의 같지 않은 행들을 반환한다.
- 자연 조인(NATURAL JOIN) : 'ON' 절이 없는 내부조인. 같은 열 이름을 가진 두 테이블을 조인할때만 작동
- 크로스 조인(CROSS JOIN) : 한 테이블의 모든 행과 다른 테이블의 모든 행이 연결되는 모든 경우를 반환.
- 카티젼 조인(CARTESIAN JOIN) : 크로스 조인의 한 종류. 조건이 없다. (크로스 프로덕트)
- 콤마 조인(COMMA JOIN) : 콤마가 CROSS JOIN 키워드 대신 사용된다는 점을 제외하면
크로스 조인과 같다.
- 셀프 조인(SELF JOIN) : 자기 자신을 조인한다.

CROSS JOIN
가장 간단하고 조인의 기본
조인된 테이블의 통합된 모든 행을 출력하고 중복되는 행은 제거된다.
모든 행을 다 가져오기 때문에 크로스 조인은 정규화된 데이터베이스에서는 거의 사용하지 않는다.
 - 한 테이블의 모든 행과 다른 테이블의 모든 행을 짝지워 반환한다.
- 카티전 조인, 카티전 프로덕트 등이 있다.

예)
Select t.toy, b.boy
From toys t, boys b;

예제) 학적테이블과 성적테이블을 croo join 하여 학번, 이름, 성적년도, 학기를 출력하라.
mysql> select s.stu_no, stu_name, sco_year, sco_term
> from student s, score sc;

내부 조인(INNER JOIN)
- 조건을 사용하여 두 테이블의 레코드를 결합한다.
- 동등 조인, 비동등 조인, 자연 조인 등이 있다.

1) 동등 조인(EQUI JOIN)
- 두 테이블 사이의 같은 행들을 반환한다.

예) 각 boy가 어떤 toy를 가지고 있는지 조사한다. (매핑 확인)

Select boys.boy, toys.toy
From boys, toys
where boys.toy_id = toys_toy.id;

2) 비동등 조인(NON-EQUI JOIN)
-두 테이블 사이의 같지 않은 모든 행들을 반환한다.

예)
Select boys.boy, toys.toy
From boys, toys
where boys.toy_id <> toys.toy_id // <> : 같지 않음
ORDER BY boys.boy; // 정렬

 

외부 조인(OUTER JOIN)
- 내부 조인과 유사하며 일치하는 것이 없을 경우 NULL로 표시한다.
- 왼쪽 테이블은 FROM 바로 다음에 나오는 테이블이고, JOIN 뒤에 나오는 테이블이 오른쪽 테이블이다.
- 왼쪽 외부 조인(LEFT OUTER JOIN)과 오른쪽 외부 조인(RIGHT OUTER JOIN)이 있다.
- 왼쪽 외부 조인을 사용할 경우 왼쪽 테이블을 오른쪽 테이블에 비교한다.
오른쪽 외부 조인도 그 반대로 동작한다. 일대다 관계에 유용하다.

유용한 예) girls가 어느 toys를 가지고 있는지 알아보도록 쿼리를 작성한다.

i) 왼쪽 테이블을 오른쪽 테이블에 비교하는 방법
Select g.girl, t.toy
From girls g
Left Outer Join toys t
ON g.toy_id = t.toy_id;

ii) 반대로 했을때
Select g.girl, t.toy
From toys t
Left Outer Join girls g
ON g.toy_id = t.toy_id;

예제)
학적테이블에 존재하는 학생들 중에서 등록한 학새으이 학번, 이름, 반 등록년도, 학기, 등록금 총액을 출력하라
mysql> select student.stu_no, stu_name,
> fee_year, fee_term, fee_pay
> from student, fee
> where student.stu_no = fee.stu_no
> union
> select stu_no, stu_name, '미납',0,0
> from student
> where stu_no not in
> (select stu_no from fee);

이와 같은 형식의 조인을 outer join이라 하며, fee(등록테이블)에 있는 모든 행을 출력하고 student(학생신상테이블)에서 미동록자이므로(즉, 등록테이블에 존재하지 않음)는 위의 예제의 결과가와 같이 등록년도에 모납이라고 출력한다.

FULL OUTER JOIN
LEFT OUTER JOIN과 RIGHT OUTER JOIN의 합집합이다.

셀프 조인(SELF JOIN)
같은 테이블 내에서 다른 행과 공통되는 값을 가진 행을 찾고 싶을 때 사용하고 한 테이블 내에서 일치하는 데이터를 찾는데 유용하다. self-join은 테이블의 자료를 검증하는 방법으로도 사용된다.
self-join은 관리자가 하나의 테이블에 관련된 여러개의 데이터를 공통으로 관리하기 위해 사용되며, 동조 조인과 유사하고 다른 점은 하나의 테이블에서 조인이 일어나므로 자신의 테이블을 마치 2개의 테이블이 존재하는 거솨 같이 가정하여 작업하고, 가명을 사용해야한다.
- 자기 자신을 조인한다.
- 자기 자신을 하나씩 비교하기 위해 사용한다.
- 하나의 테이블로 같은 정보를 가진 테이블이 두 개 있는 것처럼 쿼리를 보낼 수 있다.

예) 각 사람의 이름(name)과 그 사람을 담당하는 보스(boss)의 이름을 가져온다.

Select c1.name, c2.name AS boss // c22.name은 boss로 출력
From clown_info c1
Inner Join clown_info c2
ON c1.boss_id = c2.id;

예제)
컴퓨터 워크스테이션을 만드는데 사용되는 하드웨어의 다양한 부품에 관한 정보를 가진 매우 큰 데이타베이스를 관리해야 한다고 가정하자.
워크스테이션은 데스크, PC, 모니터, 키보드, 마우스등으로 이루어져 있다. 게다가, 데스크는 워크스테이션의 모두 다른 부분의 '부모'라고 생각될 수 있다. 우리는 각 워크스테이션의 레코드가 정확한 자료로 유지되기를 원할 것이며, 유일한 ID번호를 부여함으로써 워크스테이션의 모든 부분을 구체적으로 관련시킬 것이다. 사실, 각 부분은 항목을 분명하게 해주는 유일한 ID번호와 그것의 부모(데스크) ID번호를 확인하기 위한, 두개의 ID 번호를 포함 것이다.

테이블이 아래와 같다고 가정하자.

mysql> select * from ws;
+---------+-----------+-----------+
| uniq_id | name      | parent_id |
+---------+-----------+-----------+
| d001    | desktop   | NULL      |
| m4gg    | monitor   | d001      |
| k235    | keyboar   | d001      |
| pc345   | 200mhz pc | d001      |
| d002    | desktop   | NULL      |
| m156    | monitor   | d002      |
| k9334   | keyboar   | d002      |
| pa556   | 350mhz pc | d002      |
+---------+-----------+-----------+


desktop은 그와 관련된 모든 부분들의 부모와 같으므로 parent_id를 가지고 있지 않음을 주목하자. 지금부터 유용한 정보를 위 질의를 시작할 것이다. self-join의 사용법을 쉽게 설명하기 위해 테이블을 간단하게 만들었다.

-------------------------------------------------------------------
mysql> select t1.*, t2.* from ws as t1, ws as t2;
-------------------------------------------------------------------

어떻게 출력되는가? 이전처럼, 첫번째 테이블의 각 열들이 두번째 테이블에 있는 모든 열들에 매치되어 연결되 출력될 것이다. 우리에게 매우 유용하지는 않지만 다시 한번 시도해보고 확인해 보기바란다. 좀더 재미있는 예를 들어보겠다.

-------------------------------------------------------------------
mysql> select parent.uniq_id, parent.name, child.uniq_id, child.name
    -> from ws as parent, ws as child
    -> where child.parent_id = parent.uniq_id and parent.uniq_id = "d001";
-------------------------------------------------------------------

흥미로운 결과가 출력될 것이다.
+---------+---------+---------+-----------+
| uniq_id | name    | uniq_id | name      |
+---------+---------+---------+-----------+
| d001    | desktop | m4gg    | monitor   |
| d001    | desktop | k235    | keyboar   |
| d001    | desktop | pc345   | 200mhz pc |
+---------+---------+---------+-----------+

self-join은 테이블의 자료를 검증하는 방법으로도 사용된다. 테이블내에 있는 uniq_id컬럼은 테이블에서 유일해야 하며, 만일 데이타의 엔트리가 깊어 뜻하지 않게 같은 uniq_id를 가진 두개의 항목이 데이타베이스에 입력된다면 좋지 않은 결과가 생길것이다. 이럴 경우 정기적으로 self-join을 사용해 체크할 수 있다.