Mysql - join

✔ join이란?

---

두 개 이상의 테이블을 묶어서 하나의 결과 집합으로 만들어 내는 것이다. 즉, 서로 다른 테이블에서 데이터를 가져올 때 사용하는 것이 조인이다.

✔ inner join ( 내부 조인 )

---

inner join은 조인 중 가장 많이 사용된다. 따라서 보통 join을 얘기 할 때는 inner join을 말한다.(join=inner join)
예를 들어, 쇼핑몰 사이트에서 사용자가 물건을 구매하기 위해서는 구매 테이블에 물품,수량 등을 입력하고,외래키인 ID와 함께 삽입될것이다. 물품을 구매하고 배송을 할 때는 그 구매 테이블에 있는 정보 뿐만 아니라 사용자 테이블에 있는 배송 주소, 전화번호 등을 함께 알아야 한다. 이때 사용하는 것이 inner join이다.

SELECT <열 목록>
FROM <기준 테이블>
    INNER JOIN<참조할 테이블>
    ON <조인 조건>
[WHERE 검색조건]

위 형식에서 inner join 대신에 그냥 join을 써도 인식한다.

USE shopDB
SELECT *
FROM buyTBL
    INNER JOIN userTBL
    ON buyTBL.userID = userTBL.userID
WHERE buyTBL.userID = 'LEE'; (조건)

여기서 내부 조인을 했기 때문에 buyTBL과 userTBL이 서로 내부 조인이 되어서 두테이블을 조회하면서 where문에 맞는 이름이 'Lee'인 userId의 행들'만' 조회하게 된다. 모든 열을 검색할 필요는 없기 때문에 select문에서 * 이 아니고 buyTBL.userID, prodID, amount, name 등등 필요한 열만 검색 하면된다.

JOIN은 두 개 이상의 테이블을 결합하기 때문에 결합하는 테이블들이 동일한 열을 가지고 있다면 '테이블이름.열이름'형식으로 테이블명을 명시해줘야 에러가 발생하지 않는다. 가장 안전한 방법은 모든 열이름에 테이블명을 붙혀주는 것입니다.

SELECT B.userID, B.prodID, B.amount, U.name, U.addr, U.phoneNumber
FROM buyTBL B
INNER JOIN userTBL U
ON B.userID = U.userID

간결하게 별칭을 이용해서 작성 가능하다.

SELECT *
FROM buyTBL, userTBL
WHERE buyTBL.userID = userTBL.userID;

이런식으로 from문에서 두개의 테이블을 작성해서 조인하는 방법은 권장되지 않는방식이다.

✔ 2.outer join ( 외부 조인 )

---

inner join은 양쪽 테이블에 모두 내용이 있는 경우에만 결과가 검색되고, Outer join은 한쪽 테이블에만 내용이 있어도 결과가 검색된다.
자주 사용되지는 않지만 유용하게 사용되는 방식이다.

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT)>
    <LEFT | RIGHT | FULL> [OUTER] JOIN <두 번째 테이블(RIGHT)>
    ON <조인 조건>
[WHERE 검색조건];

outer join은 기준 테이블 내용의 누락 없이 검색하면서도, 대상 테이블의 내용을 가져올 수 있다. 두 가지 테이블의 내용을 한 번에 가져올 수도 있다.
외부조인 종류에는 left,light,full이 있다. outer는 생략 가능하다.

1. left : outer join 앞에 left를 쓰면 첫 번째 테이블의 내용은 두 번째 테이블과 연계되는 내용이 없더라도 모두 검색되어야한다는 뜻이다.
2. light : light는 두 번째 테이블의 내용은 모두 검색되어야 한다는 뜻이다.
3. full : 모든 테이블의 내용이 모두 검색되어야 한다는 뜻이다.

SELECT U.userID, U.addr, U.phoneNumber, B.prodID
FROM userTBL U
    LEFT OUTER JOIN buyTBL B
    ON U.userID = B.userID
WHERE B.prodID IS NULL
ORDER BY U.userID;

OUTER JOIN을 이용하면 구매내역이 없는 유저만을 검색할 수 있다. 위는 예시이다.

✔ 3.cross join ( 상호 조인 )

---

cross join은 한쪽 테이블의 행 하나당 다른 쪽 테이블의 모든 행을 하나씩 모든 행들을 각각 조인합니다.

즉, A 테이블의 1번 행을 B 테이블의 1번 행에 조인 시키고, 다음은 A 테이블의 1번 행을 B 테이블의 2번 행에 조인시키고 ... 모든 A 테이블의 행에 각 모든 B 테이블의 행들에 조인한다. 행의 개수는 [ A 테이블 행의 개수 X B 테이블의 행의 개수 ] 가 된다. cross join은 '카티션 곱'이라고도 부른다

SELECT * FROM ATable
CROSS JOIN BTable;

기본 적인 cross join 구문이다. inner join과 outer join과 달리 on구문은 사용하지 않는다. select * from ATable,BTable; 형식으로 작성할 수도 있는데 호환성 등의 이유로 권장되지 않는다.

✔ 4.self join ( 자체 조인 )

---

self join은 자기자신에게 조인하는 것이다. 같은 테이블에 두 번 참조해야 하는 경우도 있다.

곤충	천적	수명
거미	참새	1년
메뚜기	거미	6개월

이렇게 곤충 도감 테이블이 있다고 가정하자. 이 테이블에서 '메뚜기'의 천적 곤충의 이름,천적,수명을 검색하려면 어떻게 해야 할까?

SELECT 이름, 천적, 수명
FROM 곤충테이블
WHERE 이름 = ( SELECT 천적
               FROM 곤충테이블
               WHERE 곤충 = '메뚜기');

이런 식으로 서브 쿼리문을 사용할 수 있다.

SELECT B.곤충, B.천적, B.수명 
FROM 곤충도감 A
    INNER JOIN 곤충도감 B
    ON A.천적 = B.곤충
WHERE A.곤충 = '메뚜기';

self join 시켜서 정보를 확인할 수도 있다. 두 가지 다 결과는 [거미,참새,1년]이 나올 것이다. self join을 사용할 때는 반드시 별칭을 이용해서 논리적으로 두 개의 테이블을 분리시켜야 한다.