[MSSQL] 조인의 종류 (inner join,left(right) outer join, cross join, ....)

조인의 종류는 5가지로 나뉘어 집니다.
1. INNER JOIN
2. OUTER JOIN
3. CROSS JOIN
4. FULL OUTER JOIN
5. SELF JOIN
　

조인의 방식은 3가지로 나뉘어 집니다.
1. Nested loop Join
2. Hash Join
3. Merge Join
　

종류에 대한 이야기니 그렇구나 하시면 되구요.
주로 사용되는 조인은? 맨 처음의 INNER JOIN 이라는 녀석 입니다.
위의 ID-성별 처럼 두개의 테이블을 합칠때 사용되는 녀석 이지요
　

* Inner 조인

먼저 INNER JOIN에 대해 말을 드리지요.
1. INNER JOIN
두개의 관련된 키가 있는 테이블에서 Column의 값을 비교 후
Join 조건에 맞는 행만 검색합니다.
SQL서버의 기본 조인 방식 입니다.

　

SELECT * from authors SELECT * FROM publishers SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city = p.city

자 위의 조인문이 중요한 것은 아닙니다. 우선은 샘플만 보여 드린 거지요.
위의 두개 SELECT 구문은 데이터를 먼저 찬찬히 봐 보시라는 의미 이며.
세번째 쿼리가 JOIN 쿼리 입니다
　

이제 INNER JOIN의 좀더 다른 샘플을 보도록 하지요.
ID-이름 테이블
　

ID	이름
KONAN	김대우
BAEZZANG	이동혁
DEVIL	은정범

이런 테이블이 있구요.
아울러.
ID - 정보 테이블
　

ID	나이	성별
KONAN	18	남
BAEZZANG	45	남

의 식이라고 생각해 보세요 아시겠지요? 　

여기서!!! 가능한 모든 조합을 한번 뽑아 보도록 하지요
　

ID-이름의 이름	ID-이름의 ID	ID-정보의 ID	ID-정보의 나이	ID-정보의 성별
김대우	KONAN	KONAN	18	남
김대우	KONAN	BAEZZANG	45	남
김대우	KONAN	NULL	NULL	NULL
이동혁	BAEZZANG	KONAN	18	남
이동혁	BAEZZANG	BAEZZANG	45	남
이동혁	BAEZZANG	NULL	NULL	NULL
은정범	DEVIL	KONAN	18	남
은정범	DEVIL	BAEZZANG	45	남
은정범	DEVIL	NULL	NULL	NULL

이런 식이 될겁니다.그렇지요?
여기서 결과들중 바로 파란색으로 된 녀석들!
ID-이름 테이블의 ID컬럼과 ID-정보 테이블의 ID 컬럼이 같은 녀석들이 의미 있는
값이라는 것이지요!!!! 다른 값들은 바로 쓰레기 값이다!라는 의미 입니다.
나온 결과중 의미 있는 결과는!

　

ID-이름의 이름	ID-이름의 ID	ID-정보의 ID	ID-정보의 나이	ID-정보의 성별
김대우	KONAN	KONAN	18	남
이동혁	BAEZZANG	BAEZZANG	45	남

이런 식이 될겁니다.
이런식으로 생각해 보시면 조금 빨리 이해가 되시지요?
이렇게 여러 조합중 조건에 맞는 즉! 조건 컬럼이 같은 녀석을 뽑아 내는 것이 바로 JOIN 입니다.
따라서 이런 결과과 되지요.
이를 SQL문으로 생각해 본다면?

--실행 안됩니다. SELECT id-이름.이름, id-이름.ID, id-정보.id, id-정보.나이,id-정보.성별 FROM id-이름 INNER JOIN id-정보 ON id-이름.ID = id-정보.ID

이런 식이 되겠지요!! 조건은 바로 맨 아래줄의 ON 키워드 라는 것입니다.
자 상당히 많은 내용을 배우셨네요.
다음 내용은? INNER JOIN의 문법과 간단한 샘플들 입니다
　

조인의 구현
각 테이블에서 하나의 컬럼을 사용하여 두개의 테이블을 연결하는 것
- 고려사항
- 연결하려는 컬럼은 조인에 포함된 각 테이블에 있는 데이터를 쉽게 일치시키거나 비교할 수 있어야 한다.
- ANSI SQL 문법 또는 SQL 서버 문법을 사용할 수 있다.
- 하나의 SELECT 문에서 동시에 ANSI SQL문법과 SQL서버 문법을 사용 할 수 없다.
- 두 테이블 모두에 존재하는 컬럼 이름을 참조하는 경우에는 반드시 table_name.column_name 형식을 따라야 한다
　

조인의 구현 - ANSI SQL 문법
SELECT table_name.column_name [, table_name.column_name…]
FROM {table_name[join_type] JOIN table_name ON search_conditions}
WHERE [search_condition…]

- WHERE 절을 사용한 행의 선택에서 연결된 테이블을 구성한다
　

형식
SELECT table_name.column_name[, table_name.column_name…]
FROM {table_name, table_name}
WHERE table_name.column_name join_operator table_name.column_name

- 컬럼 들의 값을 한 행씩 비교하여 비교 결과가 참일때 그 행을 나열한다.
- FROM절에 조인에 관련되는 모든 테이블을 나열하고 WHERE절에 어떤 행동들이 결과에 포함되어야 하는지를 명시한다.

WHERE 절에 사용할 수 있는 연산자들
=, >, <, >=, <=, <>
　

두 테이블을 연결 조건에 맞는 행들만 포함하는 세번째 테이블로 연결한다.

내부 연결의 일반적 유형
- Equijoin
- 비교되는 컬럼의 값이 같을 경우에 연결이 이루어진다.
- 중복된 컬럼 정보를 만들게 된다.
- Natural join
- Equijoin 이 만들어 내는 결과 집합에서 중복된 컬럼의 데이터를 제거한다
　

SELECT pub_name, title FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id

　

/* ANSI 조인 */ USE pubs SELECT authors.au_lname, authors.state, publishers.* FROM publishers INNER JOIN authors ON publishers.city = authors.city

　

/*T-SQL 조인*/ USE pubs SELECT authors.au_lname, authors.state, publishers.* FROM publishers, authors WHERE publishers.city = authors.city

북스 온라인 상에서는 T-SQL조인을 사용하기 보다는 ANSI 조인의 사용을 권하고 있습니다.
T-SQL조인은 사실 약간 모호할 가능성이 있기 때문 입니다.
추후 스터디를 위해서라도 가능하심 ANSI - SQL로 배우시길 바랍니다
　

* OUTER JOIN

다음은 두번째의 OUTER JOIN 입니다.
LEFT 또는 RIGHT OUTER JOIN.
두 테이블에서 지정된 쪽인 LEFT 또는 RIGHT 쪽의
모든 결과를 보여준후 반대쪽에 대해는 매칭값이 없어도
보여주는 JOIN을 의미
　

조금 난해 하지요?
역시나 샘플을 보시면? 감이 빡빡 오실 겁니다.
위의 샘플과 마찬 가지로.

ID-이름 테이블
　

ID	이름
KONAN	김대우
BAEZZANG	이동혁
DEVIL	은정범

이런 테이블이 있구요.
아울러.

ID - 정보 테이블
　

ID	나이	성별
KONAN	18	남
BAEZZANG	45	남

다시 가능한 모든 조합입니다

ID-이름의 이름	ID-이름의 ID	ID-정보의 ID	ID-정보의 나이	ID-정보의 성별
김대우	KONAN	KONAN	18	남
김대우	KONAN	BAEZZANG	45	남
김대우	KONAN	NULL	NULL	NULL
이동혁	BAEZZANG	KONAN	18	남
이동혁	BAEZZANG	BAEZZANG	45	남
이동혁	BAEZZANG	NULL	NULL	NULL
은정범	DEVIL	KONAN	18	남
은정범	DEVIL	BAEZZANG	45	남
은정범	DEVIL	NULL	NULL	NULL

이때 INNER JOIN과 다른점은?
다시 가능한 모든 조합입니다.

　

ID-이름의 이름	ID-이름의 ID	ID-정보의 ID	ID-정보의 나이	ID-정보의 성별
김대우	KONAN	KONAN	18	남
이동혁	BAEZZANG	KONAN	18	남
이동혁	BAEZZANG	BAEZZANG	45	남
은정범	DEVIL	NULL	NULL	NULL

따라서 이런 결과과 됩니다.

즉!
FROM id-이름 LEFT OUTER JOIN id-정보
ON id-이름.ID = id-정보.ID
이런 LEFT OUTER JOIN 키워드가 있을 경우 JOIN의 왼쪽에 표시된 테이블인
id-이름 테이블은 오른쪽에 매칭 되는 결과가 없어도!!! 왼쪽 테이블을 우선 보여 준다는
의미 입니다.
그래서 ID-이름 테이블의 은정범 : DEVIL 은 매칭되는 ID-정보 테이블의 로우가 없어도
우선은 보여 준다는 의미 이지요.
　

이를 SQL문으로 생각해 본다면?

　

SELECT id-이름.이름, id-이름.ID, id-정보.id, id-정보.나이,id-정보.성별 FROM id-이름 LEFT OUTER JOIN id-정보 ON id-이름.ID = id-정보.ID

의 식이 된다. 이것이 LEFT OUTER JOIN 이다.
RIGHT OUTER JOIN 역시 같습니다. 오른쪽 테이블은 무조건 보이고 왼쪽은 NULL로
표기 한다는 의미 입니다. LEFT 와 같지요?
코난이의 경우 대부분 LEFT를 많은 사람이 사용하는 경우를 보았지
RIGHT는 쓰시는 분이 거의 없더군요
　

그렇다면! 이 LEFT OUTER JOIN은 주로 언제 사용하는가!
코난이의 경우 몇번 전자 상거래 구축시 사용한 경험이 있습니다.
바로 제품 - 판매량을 볼때 이지요.
특정 제품들의 리스트를 보기위해 아래의 표와 같은 데이터를 보려 합니다.
이런 테이블을 INNER JOIN해 볼때는? C 제품의 경우
즉! 하나도 팔리지 않은 제품도 분명히 있을 겁니다.

　

제품	판매량
A	10
B	20
C	NULL

이때
C제품도 우선은 보이게 하고 판매량은 NULL로 보이더라도 우선은 보이고 싶을때
입니다. 그래서 LEFT OUTER JOIN을 사용하게 되는 것이지요.
비슷한 케이스가 상당히 많으니.. 이 LEFT OUTER JOIN도 알아두심 많은 도움 되실 겁니다
　

이러한 OUTER JOIN의 구문 정보와 샘플 입니다.

Outer 조인
한 테이블에 있는 행에는 제한 조건을 가하지 않는
반면에 다른 테이블에 대해서는 행에 제한을 한다.

고려사항
- 관계된 테이블에서 일치하지 않는 outer 테이블의 모든 행을 보여준다.
- 두 테이블간에만 이루어질 수 있다.
- 기본키와 참조키가 동기화 되지 않았는지 등을 알아보는데 유용하게 사용된다
　

SELECT title, stor_id, ord_num, qty, ord_date FROM titles LEFT OUTER JOIN sales ON titles.title_id = sales.title_id

* CROSS JOIN

다음은 CORSS JOIN 입니다.
CROSS JOIN 연관된 두개의 테이블에서 가능한 모든 조합을 찾는다
　

SELECT au_fname, au_lname, pub_name FROM authors CROSS JOIN publishers

자 역시 찬찬히 풀어서 말을 드릴 차례 이지요
위의 샘플과 마찬 가지로

ID-이름 테이블
　

ID	이름
KONAN	김대우
BAEZZANG	이동혁
DEVIL	은정범

이런 테이블이 있구요.
아울러.

ID - 정보 테이블

　

ID	나이	성별
KONAN	18	남
BAEZZANG	45	남

다시 가능한 모든 조합입니다

ID-이름의 이름	ID-이름의 ID	ID-정보의 ID	ID-정보의 나이	ID-정보의 성별
김대우	KONAN	KONAN	18	남
김대우	KONAN	BAEZZANG	45	남
김대우	KONAN	NULL	NULL	NULL
이동혁	BAEZZANG	KONAN	18	남
이동혁	BAEZZANG	BAEZZANG	45	남
이동혁	BAEZZANG	NULL	NULL	NULL
은정범	DEVIL	KONAN	18	남
은정범	DEVIL	BAEZZANG	45	남
은정범	DEVIL	NULL	NULL	NULL

지금 보고 계신 이 모든 가능한 조합이? 바로 CORSS JOIN 의 결과 입니다.
자 여기엔 쓰레기 값이 상당히 많습니다.
LRFT OUTER JOIN에서 사용되는 맨 아래 줄을 제외 하더라도.
6개의 노란 셀로 표시된 로우는 모두 쓰레기 값이라는 것이지요.
그래도 CORSS JOIN에서는? 모두 보여 줍니다.
코난이의 경우 실무에서는 한번도 사용해 본적 없지만
이런게 있다는 것은 알아 두시길 바랍니다
　

* FULL OUTER JOIN

다음은 FULL OUTER JOIN 입니다.
FULL OUTER JOIN
LEFT OUTER JOIN의 결과와 RIGHT OUTER JOIN의
결과를 표시한후 한번 중복되는 값(INNER JOIN의 값)의 중복을 제거한 값을 표시한다
　

SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a FULL OUTER JOIN publishers p ON a.city = p.city

이는? LEFT OUTER JOIN과 RIGHT OUTER JOIN을 실행 해 보심 아실 수 있습니다.
두 OUTER JOIN을 실행한후
겹치는 부분인 INNER JOIN의 결과 부분이 두번 나오겠지요?
이 INNER JOIN의 결과를 한번 제외한 결과 라고 생각 하심 빠릅니다.
LEFT , RIGHT 조인을 실행 한다고 생각하심 빠르다는 의미 입니다.
코나니는 실무에서 사용해본 경험이 없습니다. - 거의 사용 안한다는 말이지요
　

* SELF JOIN 

다음은 SELF JOIN 입니다

SELF JOIN

사용하는 경우 :
1. 계층적인 구조를 테이블화 할 경우.
2. 한 테이블에서 일치하는 값을 찾고자 하는 경우.
아울러 반드시 테이블 Alias 명을 사용해 질의해야 한다
　

판매 라는 테이블이 있다고 생각해 보세요.
우선 판매 라는 테이블을 별명으로 판매 a 라고 잡겠습니다.
판매a

　

구매자 ID	물품
KONAN	디아블로2
KONAN	스타크래프트
BAEZZANG	니드포스피드
DEVIL	프리셀
DEVIL	디아블로2

아울러 판매 b 라는 테이블이 또 있다고 생각 하지요. 바로!

　

구매자 ID	물품
KONAN	디아블로2
KONAN	스타크래프트
BAEZZANG	니드포스피드
DEVIL	프리셀
DEVIL	디아블로2

이렇게 같은 테이블 입니다
　

구매자 ID	물품	구매자 ID
KONAN	디아블로2	DEVIL

이런 식으로 찾기를 원한다고 생각해 보세요.
바로! 같은 물품을 구매한 다른 사람을 찾고 싶을때 입니다
　

SELECT au_fname, au_lname, zip, city FROM authors WHERE city = 'Oakland'

이 쿼리를 먼저 실행해서 결과를 확인한후 Self-Join 의 쿼리를 실행해 보지요.
결과를 찬찬히 봐 보세요.. ZIP 코드가 같은 작가들이 있습니다.
이때 이를 찾을때 어떻게 찾으면 될까요?
　

SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname, au1.zip FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip WHERE au1.city = 'Oakland' and au1.au_id <> au2.au_id ORDER BY au1.au_fname ASC, au1.au_lname ASC

이 쿼리는 오클랜드 지역에 사는 작가들중 zip 코드가 같은 사람이 있는가 하는 쿼리 이다.
즉, 오클랜드 지역에 같은 zip코드에 거주하는 사람이 있는가 하는 쿼리 이다.
코나니가 책회사 사장 이라면? 같은 지역에 거주하는 작가들 끼리 서로의 정보를
공유해 더 좋은 책을 쓰게 함 좋겠죠? 이럴 경우 사용 가능한 질의 입니다

　

이제 SELF JOIN의 구문정보 입니다.

Self 조인
테이블의 행을 같은 테이블 안에 있는 다른 행과 연관시킨다.
- 비교되는 컬럼은 같은 자료형이어야 하고 여러 방법에 대해 비교 가능해야 한다.
- 같은 테이블을 조인하기 위해서는 하나의 테이블을 두개의 다른 논리적인 테이블로
참조 할 수 있도록 별명을 할당해야 한다.

　

ANSI SQL 문법

SELECT column_name, column_name [, column_name…]

FROM table_name alias [join_type] JOIN table_name alias

ON search_conditions
　

T - SQL 서버 문법

SELECT column_name, column_name [, column_name…]

FROM table_name alias, table_name alias [, table_name…]

WHERE alias.column_name join_operator alias.column_name
　

코나니의 경우 SELF 조인을 가끔 사용한 경험이 있습니다.
아울러 최근 많은 이슈가 되고 있는 CRM(Customer Relationship Management)
에서 종종 사용될 경우가 있으니.. 주의해 보심 많은 도움 되실 겁니다
　

다음은 둘 이상의 테이블을 조인할 경우 입니다.

둘 이상의 테이블 조인
ASNI SQL 문법

SELECT table_name.column_name [, table_name.column_name…]

FROM table_name[join_type]

JOIN table_name ON search_conditions…[join_type]JOIN table_name ON search_conditions

WHERE [search_condition…]
　

T - SQL문법

SELECT table_name.column_name[, table_name.column_name…]

FROM table_name, table_name[, table_name…]

WHERE table_name.column_name join_operator

table_name.column_name

[AND table_name.column_name join_operator

table_name.column_name…]
　

ANSI 표준을 가능하면 보시고.. 간단히 ON 키워드에 추가추가 하심 됩니다

　

/*ASNI 조인*/ SELECT stor_name, qty, title FROM titles INNER JOIN sales ON titles.title_id = sales.title_id INNER JOIN stores ON stores.stor_id = sales.stor_id /*T - SQL 조인*/ SELECT stor_name, qty, title FROM titles, sales, stores WHERE titles.title_id = sales.title_id AND stores.stor_id = sales.stor_id

끝으로 조인의 방식에 대한 이야기 입니다.

조인의 방식은 3가지로 나뉘어 집니다.
1. Nested loop Join
2. Hash Join
3. Merge Join

의 식입니다.
이는 조인이 내부적으로 사용하는 알고리즘에 대한 이야기 입니다.
이에 대한 내용은 알고리즘 이야기 인데
코난이가 화일 시스템 시간에 배웠던 HASH의 이야기완 조금 틀리고
MERGE 조인의 경우 제가 알고있는 MERGE에 대한 내용과 역시나 틀리 더군요.
아울러 Nested Loop는 루프를 돌며 조건 찾기로 아마 이해가 쉬우실 겁니다.
이에 대한 자료를 sqler의 Tip 게시판에 올려 두었으니 참고 하시길 바랍니다