Transact-SQL ( INNER JOIN )

--============================================================--
-- INNER JOIN
--============================================================--
use sqlDB
go

-- inner join 사용
-- 구매 테이블 중에 LCS 라는 아이디를 가진 사람이 구매한 물건을
-- 발송하기 위해서 이름/주소/연락처 등을 조인해서
-- 검색하려면 아래와 같이 사용
select *
from buyTbl
 inner join userTbl
  on buyTbl.userid = userTbl.userid
where buyTbl.userid='LCS'
/* 결과
num         userid   prodName ... birthYear   addr mobile1 mobile2  height
----------- -------- -------- --------- ----------- ------ -------- ---------- ----------- ---- ------- -------- ------
3           LCS      모니터   ...  1983        인천   011     4444444  179
*/

-- 아이디/이름/구매물품/주소/연락처만 추출
select buyTbl.userid, name, prodName, addr, mobile1 + mobile2 as [연락처]
from buyTbl
 inner join userTbl
  on buyTbl.userid = userTbl.userid
-- select userid << 이렇게 하면 buyTbl, userTbl에 userid가 있기 때문에
-- 오류가 납니다. 정확하게 명시를 해야 합니다.
-- >> buyTbl.userid 로 적어야 합니다.
/* 결과
userid   name       prodName addr 연락처
-------- ---------- -------- ---- -----------
PJY      박주영        운동화      경기   0112222222
PJY      박주영        노트북      경기   0112222222
LCS      이천수        모니터      인천   0114444444
CJC      최진철        모니터      제주   0190000000
PJY      박주영        청바지      경기   0112222222
CJC      최진철        메모리      제주   0190000000
*/

-- WHERE로 JOIN을 처리 할 수 있다.
select buyTbl.userid, name, prodName, addr, mobile1 + mobile2 as [연락처]
from buyTbl, userTbl
where buyTbl.userid = userTbl.userid
/* 결과
userid   name       prodName addr 연락처
-------- ---------- -------- ---- -----------
PJY      박주영        운동화      경기   0112222222
PJY      박주영        노트북      경기   0112222222
LCS      이천수        모니터      인천   0114444444
CJC      최진철        모니터      제주   0190000000
PJY      박주영        청바지      경기   0112222222
CJC      최진철        메모리      제주   0190000000
*/

-- 테이블에 별칭을 붙인다.
select B.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 as [연락처]
from buyTbl B, userTbl U
where B.userid = U.userid
/* 결과
userid   name       prodName addr 연락처
-------- ---------- -------- ---- -----------
PJY      박주영        운동화      경기   0112222222
PJY      박주영        노트북      경기   0112222222
LCS      이천수        모니터      인천   0114444444
CJC      최진철        모니터      제주   0190000000
PJY      박주영        청바지      경기   0112222222
CJC      최진철        메모리      제주   0190000000
*/

-- LCS 아이디의 사용자가 구매한 정보를 보여주는 쿼리
select B.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 as [연락처]
from buyTbl B
 inner join userTbl U
  on B.userid = U.userid
where B.userid = 'LCS'
/* 결과
userid   name       prodName addr 연락처
-------- ---------- -------- ---- -----------
LCS      이천수        모니터      인천   0114444444
*/

-- 전체 회원들이 구매한 목록을 모두 출력하자.
-- 그리고 결과를 정렬해보자.
select U.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 As [연락처]
from userTbl U
 inner join buyTbl B
  on U.userid = B.userid
order by U.userid
/* 결과
userid   name       prodName addr 연락처
-------- ---------- -------- ---- -----------
AJH      안정환        책        강원   NULL
CJC      최진철        메모리      제주   0190000000
CJC      최진철        모니터      제주   0190000000
CJC      최진철        운동화      제주   0190000000
CJC      최진철        운동화      제주   0190000000
LCS      이천수        모니터      인천   0114444444
PJY      박주영        청바지      경기   0112222222
PJY      박주영        운동화      경기   0112222222
PJY      박주영        노트북      경기   0112222222
SKH      설기현        책        서울   0118888888
SKH      설기현        청바지      서울   0118888888
SKH      설기현        책        서울   0118888888
*/

-- 위의 결과에는 구매 테이블에 대한 목록이다.
-- 하지만 구매를 하지 않은 나머지 회원은 나타나지 않는다.

-- 쇼핑몰에서 한번이라도 구매한 기록이 있는 우수회원들에게
-- 감사의 안내문을 발송하도록 하자. 라는 경우에
-- DISTINCT 를 사용한다.
select distinct U.userid, U.name, U.addr
from userTbl U
 inner join buyTbl B
  on U.userid = B.userid
order by U.userid
/* 결과
userid   name       addr
-------- ---------- ----
AJH      안정환        강원 
CJC      최진철        제주 
LCS      이천수        인천 
PJY      박주영        경기 
SKH      설기현        서울 
*/

-- 이 내용은 바로위의 내용에서 중복되는 목록을
-- DISTINCE로 해서 하나로 묶어 주는 역활을 한다.

-- 위의 결과를 EXISTS 문을 사용해서도 동일한 결과를 낼 수 있다.
-- 내부적으로 JOIN보다 복잡하여 성능이 떨어지므로 권장은 하지 않는다.
select U.userid, U.name, U.addr
from userTbl U
 where exists(
  select *
  from buyTbl B
  where U.userid = B.userid
 )
/* 결과
userid   name       addr
-------- ---------- ----
AJH      안정환        강원 
CJC      최진철        제주 
LCS      이천수        인천 
PJY      박주영        경기 
SKH      설기현        서울 
*/

-- ★★"학생 동아리"생성 후에 해야함..

-- 학생 테이블, 동아리 테이블, 학생동아리을 이용해서 학생을 기준으로
-- 학생 이름/지역/가입한 동아리/동아리명으로 출력해 보도록 하자.
select s.stdName, S.addr, C.clubName, C.roomNo
 from stdTbl S
 inner join stdclubTbl SC
  on S.stdName = SC.stdName
 inner join clubTbl C
  on SC.clubName = C.clubName
order by S.stdName
/* 결과
stdName    addr clubName   roomNo
---------- ---- ---------- ------
박지성        서울   바둑         102호
박지성        서울   축구         103호
안정환        강원   봉사         104호
이천수        인천   축구         103호
이천수        인천   봉사         104호
조재진        충북   축구         103호
*/

-- 동아리를 기준으로 가입한 학생의 목록을 출력
select C.clubName, C.roomNo, S.stdName, S.addr
from stdTbl S
 inner join stdclubTbl SC
  on SC.stdName = S.stdName
 inner join clubTbl C
  on SC.clubName = C.clubName
order by C.clubName
/* 결과
clubName   roomNo stdName    addr
---------- ------ ---------- ----
바둑         102호   박지성        서울 
봉사         104호   이천수        인천 
봉사         104호   안정환        강원 
축구         103호   박지성        서울 
축구         103호   조재진        충북 
축구         103호   이천수        인천 
*/

-- 외부 조인은 조인의 조건에 만족되지 않는 행까지도 포함시키는 것
--