[MySQL] INNER JOIN / OUTER JOIN / CROSS JOIN / SELF JOIN

 

---

 

 

1. 조인 Join

 

👾 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어내는 것

 ⚡️ 데이터베이스 테이블은 중복과 공간 낭비를 피하고 데이터 무결성을 위해 여러 개 테이블로 분리해 저장

 ⚡️ 분리된 테이블은 서로 관계 Relation 를 맺고 있는데, 가장 많이 사용하는 관계는 '1대 다' 관계

     ex. 학생 한 명이 여러 과목의 학점을 받는 것, 한 개의 아이디로 여러 상품을 주문하는 것

 

---

 

1) INNER JOIN (내부 조인)

 

  

    
    
    
    
  
 SELECT <열 목록>
 FROM <첫 번째 테이블>
      INNER JOIN <두 번째 테이블>
      ON <조인될 조건>
 [WHERE 검색조건]

 

 

· 첫 번째 테이블은 구매테이블(buytbl), 두 번째 테이블은 회원테이블(usertbl)

 

  

    
    
    
    
  
 SELECT * FROM buytbl
      INNER JOIN usertbl
         ON buytbl.userID = usertbl.userID
      WHERE buytbl.userID = 'JYP';

 

실행 결과

  📌  추출 과정

     1.  구매 테이블의 userID인 'JYP'를 추출한다.

     2.  'JYP'와 동일한 값을 회원 테이블의 userID열에서 검색한 후 찾으면 구매 테이블과 회원 테이블의 두 행을 결합

 

  📌 WHERE 절을 생략하면 buytbl 테이블의 모든 행에 대해서 위와 동일한 방식으로 추출

WHERE절을 생략한 결과

 

 

---

 

👾 필요한 열만 추출하는 방법

  

    
    
    
    
  
 SELECT userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
    FROM buytbl
      INNER JOIN usertbl
         ON buytbl.userID = usertbl.userID
      ORDER BY num;

 

  📌 위의 구문을 실행하면 다음과 같은 에러 코드가 뜬다.

  

    
    
    
    
  
Error Code: 1052. Column 'userID' in field list is ambiguous

 

  💡 userID의 경우 두 테이블 모두에 들어 있어 어느 테이블의 userID 추출할 지 명시해줘야 함

  

    
    
    
    
  
 SELECT buytbl.userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
    FROM buytbl
      INNER JOIN usertbl
         ON buytbl.userID = usertbl.userID
      ORDER BY num;

 

결과

 

 

👾 코드가 길어지면 복잡해 보이기 때문에 각 테이블에 '별칭 Alias' 을 부여하는 방법

  

    
    
    
    
  
 SELECT B.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
    FROM buytbl B
      INNER JOIN usertbl U
         ON B.userID = U.userID
      WHERE B.userID = 'JYP';

 

  📌 위와 같이 FROM 절에 나오는 테이블의 이름 뒤에 별칭을 붙여주면 된다.

결과

 

 

---

 

👾 세 개의 테이블을 조인하는 방법

· 첫 번째 stdTbl(학생테이블) / 두 번째 stdclubTbl / 세 번째 clubTbl

 

  

    
    
    
    
  
-- 학생 테이블, 동아리 테이블, 학생동아리 테이블을 이용해서 학생을 기준으로 학생 이름/지역/가입한 동아리/동아리방을 출력
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; 

 

결과

 

 

---

 

2) OUTER JOIN (외부 조인)

 

 

👾 주종관계에 있는 테이블에서 주테이블의 모든 레코드를 보여주고 조건을 만족하는 부테이블의 필드를 같이 출력

  

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

 

 

  📌 전체 회원의 구매기록 구하기 (단, 구매 기록 없는 회원도 출력)

  

    
    
    
    
  
 SELECT U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
    FROM usertbl U
      LEFT OUTER JOIN buytbl B     -- LEFT OUTER JOIN은 왼쪽 테이블(usertbl)의 것은 모두 출력되어야 한다는 뜻
         ON U.userID = B.userID
    ORDER BY U.userID;

 

결과

 

 

📌 RIGHT OUTER JOIN을 사용해서 동일한 결과 값을 얻으려면 단순히 왼쪽과 오른쪽 테이블 위치만 바꿔주면 됨

 

 

---

 

💡  FULL OUTER JOIN (전체 조인) : LEFT JOIN + RIGHT JOIN

      ➡️  왼쪽 테이블과 오른쪽 테이블의 모든 행을 반환. 조인 조건에 맞는 데이터가 없는 경우, 해당 테이블의 열에는 NULL 값이 채워짐

 

 

---

 

3) CROSS JOIN (상호 조인)

 

 

👾 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능

➡️ 결과 개수는 두 테이블의 개수를 곱한 개수

👾 카티션 곱 Cartesian Product이라고도 부른다.

👾 ON 구문 사용 x, 용도는 테스트로 사용할 많은 용량의 데이터를 생성할 때 주로 사용

 

 

  📌 회원 테이블과 구매 테이블의 CROSS JOIN 구문

  

    
    
    
    
  
 SELECT COUNT(*) AS '데이터 개수' FROM buytbl CROSS JOIN usertbl;

 

결과

 

 

---

 

4) SELF JOIN (자체 조인)

 

 

👾 자기 자신과 자기 자신이 조인한다는 의미

👾 대표적인 예가 조직도와 관련된 테이블

 

empTbl

 

  

    
    
    
    
  
 -- 우대리 상관의 연락처를 확인
 SELECT A.emp AS '부하직원' , B.emp AS '직속상관', B.empTel AS '직속상관연락처'
   FROM empTbl A
      INNER JOIN empTbl B
         ON A.manager = B.emp
   WHERE A.emp = '우대리';

 

결과

 

 

 

 

 

 

[ 내용 참고 : 책 '이것이 MySQL'이다 ]