[MY-SQL] join

SQL에서 조인(JOIN)은 여러 테이블을 어떠한 조건에 의해 하나의 테이블 정보처럼 불러들일 때 사용됩니다.

그리고 조인(JOIN)은 각 테이블의 연결 고리같은 일치하는 필드값이 있어야만 사용이 가능합니다.
아래의 예를 들어 설명하겠습니다.

예)

학적 테이블(Select * from 학적;)





학번	이름	학과	학년
071048	홍길동	101	3
061000	권율	102	1
051001	강감찬	103	2

학과 테이블(Select * from 학과;)

학과코드	학과	담당교수
101	컴퓨터 공학	고길동
102	전자통신	안철수
103	컴퓨터프로그래밍	김제동

학적 테이블과 학과 테이블이 있습니다.
2개의 테이블을 조인(JOIN)을 이용하여, 학적 테이블의 학번, 이름, 학과 학년과 학과 테이블의 학과, 담당교수를
두 테이블의 연결고리인 학과코드의 정보를 가지고, 하나의 테이블로 불러들여서 쉽게 정보를 확인 할 수 있도록 해줍니다.

학적 + 학과 테이블을 조인(JOIN) 후

학번	이름	학과	담당교수	학년
071048	홍길동	101	고길동	3
061000	권율	102	안철수	1
051001	강감찬	102	김제동	2

조인(JOIN)의 종류

1. INNER JOIN
두개의 테이블을 비교해서 같은 데이터만 불러들이는 교집합입니다.

예를 들어
A테이블에 1, 2, 3, 4, 5의 정보가 있고
B테이블은 1, 2, 5 정보가 있습니다.

두개의 테이블을 INNER JOIN을 하면 1, 2, 5의 정보만 불러들여집니다.

2. OUTER JOIN
OUTER JOIN은 비교 기준에 따라 LEFT JOIN과 RIGHT JOIN으로 나뉩니다.

LEFT OUTER JOIN방법은 왼쪽 테이블을 기준으로 두개의 테이블을 비교하여,
왼쪽의 데이터가 오른쪽 테이블에 없으면 NULL로 표시됩니다.

예)
A테이블 [a] [b]
             1     a
             2     b
             3     c

B테이블 [a] [c]
             2     b
             3     e

LEFT OUTER JOIN

a	b	c
1	a	null
2	b	d
3	c	e

위의 그림을 보면 LEFT OUTER JOIN은 왼쪽 테이블을 우선으로 하기 때문에, 왼쪽 테이블인 A 테이블을 기준으로
A테이블에 없는 정보는 B테이블을 이용해 채우고, 값이 다른 정보는 왼쪽 테이블인 A테이블의 값을 우선으로 하게 된다.
만약, A 테이블에도 없고, B 테이블에도 없다면 null 값으로 남기게 된다.

반대로 RIGHT OUTER JOIN방법은 오른쪽 테이블을 기준으로 두개의 테이블을 비교하여, 테이블을 불러온다.


예)
A테이블 [a] [b]
             1     a
             2     b
             3     c

B테이블 [a] [c]
             2     b
             3     e

RIGHT OUTER JOIN

a	b	c
2	b	b
3	c	e

RIGHT OUTER JOIN은 오른쪽 테이블을 참조하기 때문에
위와 같은 정보가 나타나게 됩니다.

JOIN의 사용방법

우선 조인(JOIN)에서 사용할 테이블 생성부터 하겠습니다.

mysql> create table a(a char(2), b char(2) );
mysql> desc a;

a테이블을 생성하고 desc a;로 필드 형식을 확인합니다.

mysql> create table b(a char(2), c char(2) );
mysql> desc b;

b테이블을 생성하고 desc b;로 필드 형식을 확인합니다.

두개의 테이블에 내용도 넣어보겠습니다.

mysql> insert into a values(1, 'a');
mysql> insert into a values(2, 'b');
mysql> insert into a values(3, 'c');

myslq> select * from a;

insert문을 입력해 내용을 저장한 후 select문을 이용 확인합니다.

b테이블도 역시 내용을 넣어보겠습니다.

mysql> insert into b values(2, 'b');
mysql> insert into b values(3, 'e');

myslq> select * from b;

insert문을 입력해 내용을 저장한 후 select문을 이용 확인합니다.

1. INNER JOIN
INNER JOIN의 형식은 다음과 같습니다.

select [테이블명].[필드명], [테이블명].[필드명], ... from [테이블명] inner join [테이블명]where[조건절];

JOIN에서는 필드명 앞에 위의 형식과 같이 테이블명으로 별칭을 붙이게 됩니다. 이유는 여러개의 테이블을 이용하기
때문에, 필드명이 같은 부분은 테이블명으로 별칭 구분하기 위해서입니다.
조건절 역시 테이블명으로 별칭을 사용합니다.

a테이블과 b테이블을 이용하여, inner join을 해 보겠습니다.

mysql> select a.a, a.b, b.c from a inner join b where a.a=b.a;

앞장에서 말 했듯이 INNER JOIN은 테이블간의 교집합입니다. 즉, 두 테이블간에 같은 값만을 불러와 출력합니다.

예) A 테이블 [a][b]      B 테이블[a][c]
                    1   a                     2    b
                    2   b                     3    e
                    3   c

조건을 a테이블의 a값과 b테이블의 a값이 같은 정보를 출력하려고 합니다.
그럼으로, a테이블의 a값과 b테이블의 a값이 같은 정보는 필드명 a에서 2, 3이 됨으로,
아래의 표와 같은 출력값이 나타나게 됩니다.

a	b	c
2	b	b
3	c	e

2. LEFT OUTER JOIN

LEFT OUTER JOIN의 형식은 다음과 같습니다.

select [테이블명].[필드명], [테이블명].[필드명], ... from [테이블명] LEFT OUTER JOIN [테이블명] on [조건절];

mysql> select a.a, a.b, b.c from a left outer join b on a.a=b.a;

예) A 테이블 [a][b]      B 테이블[a][c]
                    1   a                   nul l null      <== 실제로는 없는 공간입니다.
                    2   b                     2     b               이해를 돕기 위해 임의로 null값으로 표현한 겂입니다.
                    3   c                     3     e

A테이블과 B테이블의 정보를 하나의 정보로 불러오기 위해
a, b, c의 필드명을 입력했고, 조건을 A테이블과 B테이블의 연결고리인 A테이블의 a와
B 테이블의 a로 조건을 걸었습니다.

앞장에서 말 했듯이 LEFT OUTER JOIN은 왼쪽 테이블을 우선으로 불러들이고,
그 다음 b 테이블과 비교해서 같은 값은 그대로 적용하고, 서로 다른 값은 A테이블의
정보를 적용합니다. 양쪽 다 정보가 없을 땐 NULL로 표시하게 됩니다.
즉 위의 예를 보면 A테이블의 c에 대한 정보값이 없음으로, null로 표현이 됩니다.

a	b	c
1	a	NULL
2	b	b
3	c	e

3. RIGHT OUTER JOIN

RIGHT OUTER JOIN의 형식은 다음과 같습니다.

select [테이블명].[필드명], [테이블명].[필드명], ... from [테이블명] RIGHT OUTER JOIN [테이블명] on [조건절];

mysql> select a.a, a.b, b.c from a right outer join b on a.a=b.a;

예) A 테이블 [a][b]      B 테이블[a][c]
                    1   a                     2     b  
                    2   b                     3     e    
                    3   c                    
RIGHT OUTER JOIN은 오른쪽 테이블을 우선으로 불러들이고, A테이블과 비교하여, 같은 값은 그대로 넘어오고,
다른 값은 오른쪽 테이블을 우선으로 불러들이게 됩니다. A테이블의 1, a를 불러들이지 않는 이유는
B 테이블에서는 1, a의 정보가 없어서 서로 비교를 하지 않기 때문입니다.

a	b	c
2	b	b
3	c	e

OUTER JOIN은 정확한 정보가 있는 테이블을 우선으로해서 비교 출력하기 때문에, LEFT와 RIGHT같은
방향성이 있는 것입니다.

조인(JOIN) 활용

member 테이블 정보

id	passwd	sex	age	hg	address
abc1	1	m	20	101	경북 경산시 백천동
abc2	2	m	22	101	대구광역시 동구
abc3	3	w	24	102	부산광역시 북구
abc4	4	w	30	104	경북 영천시 시림동

테이블 생성

create table member ( id varchar(20) not null, passwd varchar(16) not null, sex char(1), age varchar(3), hg char(3) not null, address varchar(100) );

정보 입력

insert into memver values( 'abc1', 1, 'm', 20, 101, '경북 경산시 백천동' );
insert into memver values( 'abc2', 2, 'm', 22, 101, '대구광역시 동구' );
insert into memver values( 'abc3', 3, 'w', 24, 102, '부산광역시 북구' );
insert into memver values( 'abc4', 4, 'w', 30, 104, '경북 영천시 시림동' );

subinfo 테이블 정보

subnum	subname	profname
101	컴퓨터공학과	우용태
102	디자인학과	문현정
103	전자공학과	김영지
104	간호사학과	이슬기

테이블 생성

create table subinfo ( subnum char(3), subname varchar(20), profname varchar(8) );

정보 입력

insert into subinfo values( 101, '컴퓨터공학과', '우용태' );
insert into subinfo values( 102, '디자인학과', '문현정' );
insert into subinfo values( 103, '전자공학과', '김영지' );
insert into subinfo values( 104, '간호사학과', '이슬기' );


문제 1) member 테이블과 subinfo 테이블을 이용하여 id, passwd, sex, age, address, subname, profname 정보를
출력하시오.

member 테이블과 subinfo 테이블을 LEFT OUTER JOIN을 이용한 출력정보

id	passwd	sex	age	address	subname
abc1	1	m	20	경북...	컴퓨터...	우용태
abc2	2	m	22	대구...	컴퓨터...	우용태
abc3	3	w	24	부산...	디지인학과	문현정
abc4	4	w	30	경북...	간호사학과	이슬기

조인(JOIN) 명령문

select member.id, member.passwd, member.sex, member.age, member.address, subinfo.subname, subinfo.profname
from member left outer join subinfo on member.hg = subinfo.subnum;

문제 2) member테이블과 subinfo 테이블을 이용하여, 컴퓨터 공학과 학생의 id, 성별, 나이, 학과이름, 담당교수정보를
출력하시오.

id	sex	age	subname	profname
abc1	m	20	컴퓨터공학과	우용태
abc2	m	22	컴퓨터공학과	우용태

조인(JOIN) 명령문

select member.id, member.sex, member.age, subinfo.subname, subinfo.profname from member inner join subinfo on
member.hg = subinfo.subnum where subinfo.subname = '컴퓨터공학과';

출처 : http://kch1183.blog.me

내부조인(INNER JOIN)과 외부조인(OUTER JOIN)

JOIN이란? 둘 이상의 테이블을 특정한 조건으로 연결하여 원하는 데이터를 출력하기 위한 방법이라고 할 수 있습니다.(SELF JOIN이라고 해서 하나의 테이블에 별칭을 주어 각각 구분하여 JOIN하는 방법도 있습니다.)

JOIN은 크게 INNER JOIN과 OUTER JOIN으로 나뉩니다.

1. 내부조인(INNER JOIN)

1)설명

일반적으로 가장 많이 사용하는 JOIN으로

둘 이상의 테이블을 연결하여 각각의 키값이 일치하는 데이터만 출력합니다.

간단하게 교집합의 의미로 생각하면 쉽습니다.

2)사용방법

SELECT 컬럼명

FROM 테이블1 INNER JOIN 테이블2 (INNER을 생략하면 Default로 INNER JOIN이 됩니다.)
ON 테이블1.키값 = 테이블2.키값 (두 테이블이 연결될 조건을 적어줍니다.)

2. 외부조인(OUTER JOIN)

1)설명

특별한 경우에 쓰이는 JOIN으로

둘 이상의 테이블을 연결하여 각각의 키값이 일치하는 데이터 뿐만 아니라, 기준이 되는 테이블의 모든 값까지 출력합니다.

그러므로 기준이 되는 테이블을 정해 주어야 합니다. (기준이 되는 테이블을 정해 준다는 것은 키값이 일치하는 데이터가 존재하지 않더라도, 기본적으로 보여질 테이블을 정해 준다는 것을 의미합니다.)

간단하게 기준테이블 집합의 의미로 생각하면 쉽습니다.

종류는 LEFT,RIGHT,FULL 이렇게 세가지 조건이 있습니다. (LEFT는 왼쪽에 있는 테이블, RIGHT는 오른쪽에 있는 테이블이 기준테이블되고 FULL은 합집합으로 양쪽의 테이블 모두 보여줍니다.)

2)사용방법

SELECT 컬럼명
FROM 테이블1 LEFT OUTER JOIN 테이블2 (LEFT,RIGHT,FULL이 있고 OUTER는 생략가능)
ON 테이블1.키값 = 테이블2.키값 (두 테이블이 연결될 조건을 적어줍니다.)

출처 : http://www.cyworld.com/springlog