[번역]MySQL Table Joins

-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
원본사이트: http://www.devshed.com/Server_Side/MySQL/Join/
* MySQL Table Joins
*(o)        By W.J. Gilmore
*(o)|      July 06, 1999
  (o)| |    번역 : 원주희(lolol at shinan.hongik.ac.kr)
=======================================================================

[영어실력이 너무 부족하다보니 잘못된 부분이 있을 수도 있습니다. 고쳐야할 부분이 있으면 꼭 메일을 보내주세요.]

◆◆ 소개하기

SQL(Structured Query Language)의 가장 큰 특징 중의 하나는 여러개의 테이블을 연결시켜 데이터를 검색하거나 조작할 수 있는 기능이다. 이것은 데이터를 쉽고 빠르게 검색하고 불필요한 데이터를 줄여주는 장점이 있다. 다른 SQL 언어와 마찬가지로 MySQL도 join명령어로 이 연산을 수행한다.

간단히 말하면 join은 두개 이상의 테이블로부터 필요한 데이터를 연결해 하나의 포괄적인 구조로 결합시키는 연산이다.

예를 들어, 한 컴퓨터 제조업자가 자신의 데이터를 능률적으로 관리하기 위한 데이터베이스가 필요하다고 하자. 이 데이터들은 주문, 고객, 생산과 같은 어떤 일관성있는 개념과 관련된 각각의 데이터들이 모여 다양하고 간결한 테이블들을 이룰 것이다. 테이블을 생성한 후에는, 다양한 예를 들어 데이터베이스에서 가장 많이 사용되는 join의 조작법에 대해 설명하겠다.

첫번째 테이블은 제조업자가 분류한 다양한 타입의 PC들의 데이터로 구성될 것이다.
---------------------------------------------- mysql> create table pcs ( -> pid INT, // product id -> spec char(3), -> os char(10), -> ram INT, -> hd char(4) -> ); -----------------------------------------------

두번째 테이블은 제조업자의 다양한 고객들에 관한 데이터로 이루어질 것이다.
----------------------------------------------- mysql> create table clients ( -> name char(25), -> cid char(8), // client id -> email char(25), -> tel char(10) -> ); -----------------------------------------------

세번째 테이블은 주문 정보에 관한 데이타를 포함할 것이다.
----------------------------------------------- mysql> create table orders ( -> order_date date, -> pid INT, -> cid char(8) -> ); -----------------------------------------------

◆◆ 자료(Data) 삽입하기

각각의 테이블에 아래와 같이 자료를 삽입해 보자.

☆ pcs (테이블1)
+------+------+-------+------+------+ | pid | spec | os | ram | hd | +------+------+-------+------+------+ | 1 | 386 | Linux | 64 | 3.1 | | 2 | 386 | Linux | 128 | 4.2 | | 3 | 486 | WinNT | 64 | 3.1 | | 4 | 586 | Linux | 128 | 4.2 | | 5 | 586 | Win98 | 128 | 6.4 | +------+------+-------+------+------+

[삽입방법]
----------------------------------------------------------- mysql> INSERT INTO pcs (pid, spec, os, ram, hd) -> VALUES (1, '386', 'Linux', 64, '3.1'); -----------------------------------------------------------

☆ clients (테이블2)
+--------+---------+---------------------------+------------+ | name | cid | email | tel | +--------+---------+---------------------------+------------+ | 원주희 | wjh-042 | haremoon at won.hongik.ac.kr | 123-456-78 | | 홍길동 | hgd-043 | honggd at won.hongik.ac.kr | 421-555-34 | | 이쁘니 | pty-042 | pretty at won.hongik.ac.kr | 459-555-32 | | 못난이 | ugy-043 | ugly at won.hongik.ac.kr | 439-555-88 | +--------+---------+---------------------------+------------+

[삽입방법]
----------------------------------------------------------- mysql> INSERT INTO clients (name, cid, email, tel) -> VALUES ('원주희', 'wjh-042', -> 'haremoon at won.hongik.ac.kr', '123-456-7890'); -----------------------------------------------------------

☆ orders (테이블3)
+------------+------+---------+ | order_date | pid | cid | +------------+------+---------+ | 1999-12-05 | 2 | wjh-042 | | 1999-12-04 | 3 | hgd-043 | | 1999-12-04 | 1 | wjh-042 | | 1999-12-05 | 2 | wjh-042 | | 1999-12-12 | 5 | ugy-043 | | 1999-12-05 | 5 | pty-042 | +------------+------+---------+

[삽입방법]
----------------------------------------------------------- mysql> INSERT INTO orders (order_date, pid, cid) -> VALUES ('1999-12-05', 2, 'wjh-042'); -----------------------------------------------------------

자, 이제부터 만들어진 3개의 테이블로부터 필요한 데이터만을 추출해 결합하는 방법에 대해 알아보자. 만일 당신의 상사가 특정한 날에 특정한 PC를 주문한 모든 고객의 이메일 목록을 원한다고 하자! 또는 특정한 고객에 의해 작성된 주문서에 있는 RAM의 양을 보고받기를 원한다고 하자. 이러한 모든 일들은 다양한 join문에 의해 쉽게 수행될 수 있다. 만들어진 테이블을 사용해 첫번째 join문을 생성해보고 작성해보자.

◆◆ The Cross Join

Cross Join은 가장 기본적인 join의 타입으로 한 테이블에 있는 각각의 열이 다른 테이블의 모든 열에 간단하게 매치되어 출력된다. 능률적이지는 않지만, 모든 join의 공통된 특징을 나타내준다.   

Cross Join의 간단한 예 :
---------------------------------------------- mysql> SELECT * FROM pcs, clients; ----------------------------------------------

매우 많은 열들이 출력될 것이다. 테이블1(pcs)에 있는 각각의 열이 테이블2(clients)의 모든 열에 매치된다는 것을 기억하자. 따라서, 3 열을 가진 테이블1(pcs)과 4열을 가진 테이블2(clients)를 포함한 join문은 총 12 열의 테이블을 만들것이다. 

즉 cross-join은 테이블1에 있는 각각의 열들이 테이블2에 있는 모든열들을 한 번씩 교차해 출력한다고 기억하는 것이 쉬울 것같다.
 
첫번째 join을 성공적으로 수행했다면 다음의 예도 어렵지 않을 것이다.

아래의 예를 따라해 보고 어떤 결과가 출력될지 예상해보자.

----------------------------------------------------------------------------- mysql> select c.name, o.cid from orders o, clients c where o.cid = "wjh-042"; ----------------------------------------------------------------------------- +--------+---------+ | name | cid | +--------+---------+ | 원주희 | wjh-042 | | 원주희 | wjh-042 | | 원주희 | wjh-042 | | 홍길동 | wjh-042 | | 홍길동 | wjh-042 | | 홍길동 | wjh-042 | | 이쁘니 | wjh-042 | | 이쁘니 | wjh-042 | | 이쁘니 | wjh-042 | | 못난이 | wjh-042 | | 못난이 | wjh-042 | | 못난이 | wjh-042 | +--------+---------+

예상한 대로 결과가 출력되었나? clients 테이블에 있는 각각의 name이 orders테이블의 "wjh-042"를 포함한 열마다 매치되어 출력되었다. 지금까지의 설명이 Cross Join을 설명하는데 충분하지 않으므로 다른 질의를 사용해가며 JOIN을 활용해보기 바란다.

NOTE : 왜 테이블의 이름에 별명을 주어 사용할까? Aliases(별명)은 질의를 입력할때 반복적인 키의 입력을 줄여주는 방법으로 사용되어진다. 따라서 열을 지정해 줄때 반복적으로 'clients'를 한자 한자 입력하는 대신에, 질의내에 'from clients c'를 지정해주고 'c'를 사용할 수 있다.

Cross Join이 테이블들을 연결해 주기는 하지만, 능률적이지는 못하다. 따라서 각각의 테이블들로 부터 우리가 원하는 데이타를 어떻게 하면 쉽게 선택할 수 있는지 계속 다음 장을 읽어보기 바란다.

◆◆ The Equi-join   

Equi-join은 한 테이블에 있는 어떠한 값이 두번째(또는 다수의) 테이블내에 포함된 값에 일치 할 때 수행된다.

product id 가 1인 PC를 주문한 고객의 목록을 원한다고 가정해 보자.

------------------------------------------------------------------- mysql> select p.os, c.name from orders o, pcs p, clients c -> where p.pid=o.pid and o.pid = 1 and o.cid=c.cid; ------------------------------------------------------------------- +-------+--------+ | os | name | +-------+--------+ | Linux | 원주희 | +-------+--------+

◆ Non-Equi-join   

Equi-join은 다수의 테이블들 사이에서 일치하는 자료들만을 추출해낸다. 그러나 만일 일치하지 않은 자료들만을 추출해야 한다면...? 예를 들어, 당신의 상사가 주문한 pid가 제품의 pid보다 더 큰 order id의 모든 운영체제(OS)의 목록을 필요로 한다면 어떻게 할 것인가? 적당히 이름을 non-equi join라고 하겠다.

------------------------------------------------------------------- mysql> SELECT p.os, o.pid from orders o, pcs p where o.pid > p.pid; ------------------------------------------------------------------- +-------+------+ | os | pid | +-------+------+ | Linux | 2 | | Linux | 3 | | Linux | 2 | | Linux | 5 | | Linux | 5 | | Linux | 3 | | Linux | 5 | | Linux | 5 | | WinNT | 5 | | WinNT | 5 | | Linux | 5 | | Linux | 5 | +-------+------+

orders 테이블의 pid가 pcs테이블의 pid보다 더 큰 모든 열들이 매치될 것이다. 주의깊게 살펴보면, 여러가지 제한을 준 간단한 cross-join 임을 파악할 수 있을 것이다. 상사에게는 특별하게 유용하지 않을 지도 모르지만, 매우 유용한 기능인 left join을 위한 준비 과정으로 생각하자. 자, 이제 다음장으로 가서 left join을 사용할 때 유용할 수있는 옵션들에 대해 집중적으로 알아보자.

◆◆ The Left Join

Left Join은 사용자가 어떠한 제한을 기반으로 관심있는 모든 종류의 자료를 추출하게한다. 테이블 join중 가장 막강한 옵션으로, 테이블을 매우 쉽게 조작할 수 있게 한다.

만일 상사가 좀더 자세히, 자세히, 자세하게, 자세하게!를 외친다고 가정해보자. left join이 우리의 문제를 해결해 줄 것이다.

------------------------------------------------------------------- mysql> select * from orders left join pcs on orders.pid = pcs.pid; ------------------------------------------------------------------- +------------+------+---------+------+------+-------+------+------+ | order_date | pid | cid | pid | spec | os | ram | hd | +------------+------+---------+------+------+-------+------+------+ | 1999-12-05 | 2 | wjh-042 | 2 | 386 | Linux | 128 | 4.2 | | 1999-12-04 | 3 | hgd-043 | 3 | 486 | WinNT | 64 | 3.1 | | 1999-12-04 | 1 | wjh-042 | 1 | 386 | Linux | 64 | 3.1 | | 1999-12-05 | 2 | wjh-042 | 2 | 386 | Linux | 128 | 4.2 | | 1999-12-12 | 5 | ugy-043 | 5 | 586 | Win98 | 128 | 6.4 | | 1999-12-05 | 5 | pty-042 | 5 | 586 | Win98 | 128 | 6.4 | +------------+------+---------+------+------+-------+------+------+

고객이 주문한 모든 PC들의 목록을 추출해 낸다. 예를 들어, PHP3 또는 Perl스크립트를 사용해 영수증을 출력하는데 사용할 수도 있다. 고객들에게 우리회사로부터 구입한 모든 제품의 목록을 가끔씩 메일로 보내야 할 때에도 clients 테이블과 연결해 사용할 수 있을 것이다.

아래의 예에서 우리는 제품의 id(pid)가 3인 PC의 정보만을 볼 수 있다.

------------------------------------------------------------------- mysql> select * from orders left join pcs on pcs.pid=3 and pcs.pid=orders.pid; ------------------------------------------------------------------- +------------+------+---------+------+------+-------+------+------+ | order_date | pid | cid | pid | spec | os | ram | hd | +------------+------+---------+------+------+-------+------+------+ | 1999-12-05 | 2 | wjh-042 | NULL | NULL | NULL | NULL | NULL | | 1999-12-04 | 3 | hgd-043 | 3 | 486 | WinNT | 64 | 3.1 | | 1999-12-04 | 1 | wjh-042 | NULL | NULL | NULL | NULL | NULL | | 1999-12-05 | 2 | wjh-042 | NULL | NULL | NULL | NULL | NULL | | 1999-12-12 | 5 | ugy-043 | NULL | NULL | NULL | NULL | NULL | | 1999-12-05 | 5 | pty-042 | NULL | NULL | NULL | NULL | NULL | +------------+------+---------+------+------+-------+------+------+

◆ The Using Clause

left join에 약간의 옵션을 주어 둘 이상의 테이블에 있는 동일한 컬럼을 조금 더깊게 연관지을수도 있다. on과 using옵션이 사용되며, 아래의 예제를 참조하자.


-------------------------------------------------------------------- ## 원본의 예제는 아래와 같지만, 에러가 발생해 필자가 MySQL매뉴얼을 참조해 나름대로 수정을 했다.## mysql> SELECT * from clients join on orders where clients.cid = orders.cid; mysql> SELECT * from clients join on orders using (cid); -------------------------------------------------------------------

==> 수정한 예제

------------------------------------------------------------------- mysql> SELECT * from clients left join orders on clients.cid = orders.cid; -------------------------------------------------------------------

또는 아래와 같이 나타낼 수도 있다.

------------------------------------------------------------------- mysql> SELECT * from clients left join orders using (cid); -------------------------------------------------------------------

두 예제 모두 똑같은 결과가 출력될 것이다.

+--------+---------+---------------------------+------------+------------+------+---------+ | name | cid | email | tel | order_date | pid | cid | +--------+---------+---------------------------+------------+------------+------+---------+ | 원주희 | wjh-042 | haremoon at won.hongik.ac.kr | 123-456-78 | 1999-12-05 | 2 | wjh-042 | | 원주희 | wjh-042 | haremoon at won.hongik.ac.kr | 123-456-78 | 1999-12-04 | 1 | wjh-042 | | 원주희 | wjh-042 | haremoon at won.hongik.ac.kr | 123-456-78 | 1999-12-05 | 2 | wjh-042 | | 홍길동 | hgd-043 | honggd at won.hongik.ac.kr | 421-555-34 | 1999-12-04 | 3 | hgd-043 | | 이쁘니 | pty-042 | pretty at won.hongik.ac.kr | 459-555-32 | 1999-12-05 | 5 | pty-042 | | 못난이 | ugy-043 | ugly at won.hongik.ac.kr | 439-555-88 | 1999-12-12 | 5 | ugy-043 | +--------+---------+---------------------------+------------+------------+------+---------+
 

다른 구문의 예를 적용해 가며 left join에 대해 이해하기 바란다. 공부를 하다보면 left join이 여러분의 개발활동에 매우 중요한 역할을 한 다는 것을 느낄 것이다.
테이블 join에 관한 정보를 좀더 깊게 교환하고 싶다면 http://www.mysql.com에 있는 다양한 토론 그룹을 체크해 보기 바란다.

◆◆ Self-joins
 
Self-join은 관리자가 하나의 테이블에 관련된 데이타를 집중시키는 막강한 방법을 제공해 준다. 사실, self-join은 그 자신의 테이블에 결합하는 것에 의해 수행된다.
개념의 이해를 위해 예를 들어 설명하겠다.

컴퓨터 워크스테이션을 만드는데 사용되는 하드웨어의 다양한 부품에 관한 정보를 가진 매우 큰 데이타베이스를 관리해야 한다고 가정하자. 워크스테이션은 데스크, 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을 사용해 체크할 수 있다. 우리는 350mhz pc의 uniq_id가 'm156'(이 값은 워크스테이션 'd002'에 속한 모니터의 uniq_id 값이다.)이 되도록 변경했다고 가정하자.

테이블의 내용은 다음과 같이 변경한다.
------------------------------------------------------------------- mysql> update ws set uniq_id = 'm156' where name = '350mhz pc'; -------------------------------------------------------------------

아래의 예를 참고해 ws테이블에 self-join을 적용해보자.

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

아래와 같이 출력될 것이다.
+---------+-----------+---------+-----------+ | uniq_id | name | uniq_id | name | +---------+-----------+---------+-----------+ | m156 | 350mhz pc | m156 | monitor | | m156 | monitor | m156 | 350mhz pc | +---------+-----------+---------+-----------+

Table join은 데이터베이의 관리를 쉽게 도와줄것이다. 구문의 이해를 정확하게 하기위해서 예제에서 배운 명령을 다양하게 변화시켜 적용해보기 바란다.