[mysql] Join

JOIN : 테이블 여러 개를 합쳐서 그 안에서 정보를 빼내는 것
실습>
a. 테이블 만들기 (test DB사용 : mysql>use test)
mysql>create table pcpc (
->pid int, //컴퓨터 id
->spec char(3), //586인지 486인지를 나타냄
->os char(10), //운영체제
->ram int, //램 크기
->hd char(4), //하드 디스크의 크기
->);
mysql>create table clients (
->name char(25), //고객 이름
->cid char(8), //고객 id
->email char(25), //고객 email주소
->tel char(10), //고객 전화번호
->);
mysql>create table orders (
->order_date date, //컴퓨터 주문 날짜
->pid int, //컴퓨터 id
->cid char(8), //고객 id
->);
*insert_join.sql 이라는 파일명으로 /usr/local/mysql 에 생성
--pcpc 테이블
insert into pcpc values (1,'386','linux',64,'3.1');
insert into pcpc values (2,'386','linux',128,'4.2');
insert into pcpc values (3,'486','WinNT',64,'3.1');
insert into pcpc values (4,'586','linux',128,'4.2');
insert into pcpc values (5,'586','Win98',128,'6.4');
--clients 테이블 고객 정보 입력
insert into clients values ('acme','a042','ac@tood.net','7890');
insert into clients values ('widgets','w043','tood.net','3434');
insert into clients values ('italimp','i042','tood.net','3212');
insert into clients values ('fedey','f043','fed@ey.com','8899');
--orders 테이블 주문정보 입력
insert into orders values ('2001-12-05',2,'a042');
insert into orders values ('2001-12-04',3,'w043');
insert into orders values ('2001-12-04',1,'a042');
insert into orders values ('2001-12-05',2,'a042');
insert into orders values ('2001-12-12',5,'f043');
insert into orders values ('2001-12-05',5,'i042');
shell>mysql -u root -p test < insert_join.sql
mysql>select * from pcpc;
mysql>select * from clients;
mysql>select * from orders;
1. 크로스 조인 : 조인된 테이블의 모든 통합된 행을 보여준다.
첫번째 테이블의 행수 * 두번째 테이블의 행수 만큼의 행을 반환한다.
mysql>select * from orders,clients; (6*4=24개의 행을 반환)
mysql>select c.name,o.cid from orders o,clients c where o.cid='a042';
(테이블 alias 사용)
2. INNER join : 2개의 테이블이나 여러테이블에서 공통적인 칼럼을 연결해서 데이터를 추출하는 것
*a, b, c 세개의 테이블로부터 join을 이용하여 데이터를 검색할 경우,
a, b 테이블의 관계있는 칼럼을 비교하고 b, c테이블의 관계있는 칼럼을 비교하고,
마지막으로 a, b, c 중 하나의 테이블에서 특정 조건을 만족하는 조건을 만들어 사용.
ex1) order, pcpc 테이블과 clients 테이블로부터 pcpc테이블의 pid 값과 orders 테이블의
pid 값이 같고, orders 테이블의 cid 값과 clients 테이블의 cid 값이 같고 orders 테이블의
pid 값이 1인 레코드 중 pcpc 테이블의 os 칼럼과 clients 테이블의 name 을 검색하여라.
mysql>select p.os,c.name from orders o,pcpc p,clients c
->where p.pid=o.pid and o.pid=1 and o.cid=c.cid;
ex2) 이름이 acme 인 사람이 구입해간 제품의 spec 과 날짜를 검색하여라.
mysql>select p.spec,o.order_date from pcpc p,orders o,clients c
->where c.name='acme' and c.cid=o.cid and o.pid=p.pid;
ex3) 2001-12-04일 판매된 제품의 spec 과 os 와 해당 제품 구입자명, 전화번호, 이메일 검색
mysql>select p.spec,p.os,c.name,c.tel,c.email from pcpc p,orders o,clients c
->where p.pid=o.pid and o.cid=c.cid and o.order_date='20011204';
ex4) os 가 linux 인 컴퓨터를 구입해간 사람의 이름, 이메일, spec, 구입날짜를 검색
mysql>select c.name,c.email,p.spec,o.order_date from pcpc p,orders o,clients c
->where p.pid=o.pid and o.cid=c.cid and p.os='linux';
3.OUTER 조인
pcpc테이블에 insert into pcpc values (6,'686','win2003',512,'40'); 값으로 데이터를 하나 추가해보자
orders 테이블에는 6번째 설정사항과 관련된 데이터가 설정되어 있지 않습니다. 이럴 경우 pcpc 테이블의
6번째 레코드와 맞는 orders 테이블의 값이 없으므로 6번째 데이터는 출력되지 않습니다.
그렇다면 6번째 레코드를 출력하려면 어떻게 할까요? 이럴경우 outer 조인을 사용합니다.
mysql>select * from pcpc p,orders o where p.pid=o.pid; (pcpc 테이블의 6번째 자료가 출력되지않음)
mysql>select * from pcpc p left outer join orders o on p.pid=o.pid; (pcp 테이블의 6번재 자료가 출력)
*outer 조인을 사용하니, 조건에 맞는 레코드가 없어도 6번재 레코드가 출력됩니다.
outer 조인에는 left outer join 과 right outer join 두가지가 있습니다.
left outer join은 왼쪽에 있는 테이블의 칼럼이 null이라도 출력하는 조인이고,
right outer join 은 오른쪽에 있는 테이블의 칼럼이 null이라도 출력하는 조인입니다.
이번엔 세 개의 테이블에 outer join 과 inner 조인을 섞어서 사용해봅시다.
mysql>select * from pcpc p
->left outer join orders o
->on p.pid=o.pid
->left outer join clients c
->on o.cid=c.cid;
Union : 두개 이상의 테이블을 결합하여, 한개의 결과를 나타내주는 쿼리. 중복된 것은 제거.
테이블의 칼럼 데이터형이 반드시 일치해야 한다.
mysql>create table one (id int,str text);
mysql>create table two (id int,str text);
mysql>insert into one values (1,'table-one 1'),
->(2,'table-one 2'),
->(3,'wow');
mysql>insert into two values (3,'wow'),
->(4,'table-two 4'),
->(5,'table-two 5');
mysql>select * from one;
mysql>select * from two;
mysql>select * from one
->union
->select * from two;
mysql>select * from one
->union all
->select * from two; (중복된 값포함 모든결과 출력)
mysql>select * from one union select * from two order by id desc;
(order by 절 사용해 재정렬가능)
*2개 이상의 테이블이 가능하므로, three란 테이블을 만들어보자.
mysql>create table three (id int,str text);
mysql>insert into three values (6,'table-one 1'),
->(7,'table-one 2'),
->(3,'wow');
mysql>select * from three
mysql>select * from one
->union
->select * from two
->union
->select * from three;