[SQL] MYSQL JOIN (INNER, OUTER, SELF, CROSS)

✍️ Study Note

 

 

---

 

 

1. INNER JOIN

테이블 A, B의 교집합

SELECT U.USERID, U.NAME, B.PRODNAME, U.ADDR, CONCAT(U.MOBILE1, U.MOBILE2) AS '연락처'
FROM USERTBL U INNER JOIN BUYTBL B 
ON U.USERID = B.USERID 
ORDER BY U.USERID;

-- INNER JOIN vs EXISTS
-- 구매내역 1회 이상 고객 명단 조회
SELECT DISTINCT U.USERID, U.NAME, U.ADDR
FROM USERTBL U INNER JOIN BUYTBL B
ON U.USERID = B.USERID
ORDER BY U.USERID;

SELECT DISTINCT U.USERID, U.NAME, U.ADDR
FROM USERTBL U 
WHERE EXISTS (
	SELECT * FROM BUYTBL B
    WHERE U.USERID = B.USERID);

 

<예시>

-- 실습 준비
CREATE TABLE STDTBL
 (STDNAME VARCHAR(10) NOT NULL PRIMARY KEY,
  ADDR CHAR(4) NOT NULL);
CREATE TABLE CLUBTBL
  (CLUBNAME VARCHAR(10) NOT NULL PRIMARY KEY,
   ROOMNO VARCHAR(4) NOT NULL);
CREATE TABLE STDCLUBTBL
   (NUM INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    STDNAME  VARCHAR(10) NOT NULL,
	CLUBNAME VARCHAR(10) NOT NULL,
    FOREIGN KEY(STDNAME) REFERENCES STDTBL(STDNAME),
    FOREIGN KEY(CLUBNAME) REFERENCES CLUBTBL(CLUBNAME));
   
INSERT INTO STDTBL VALUES
    ('김범수', '경남'),
    ('성시경', '서울'),
    ('조용필', '경기'),
    ('은지원', '경북'),
    ('바비킴', '서울');
INSERT INTO CLUBTBL VALUES
    ('수영', '101호'),
    ('바둑', '102호'),
    ('축구', '103호'),
    ('봉사', '104호');
INSERT INTO STDCLUBTBL VALUES
    (NULL, '김범수', '바둑'),
    (NULL, '김범수', '축구'),
    (NULL, '조용필', '축구'),
    (NULL, '은지원', '축구'),
    (NULL, '은지원', '봉사'),
    (NULL, '바비킴', '봉사');
    
 
-- #1
-- 학생별 가입현황
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
-- 동호회별 가입현황
SELECT C.CLUBNAME, C.ROOMNO, S.STDNAME, S.ADDR
FROM STDTBL S 
INNER JOIN STDCLUBTBL SC
	ON S.STDNAME = SC.STDNAME
INNER JOIN CLUBTBL C
	ON SC.CLUBNAME = C.CLUBNAME;
        
-- #3
-- 동호회별 가입인원수
SELECT C.CLUBNAME, COUNT(SC.CLUBNAME)
FROM CLUBTBL C INNER JOIN STDCLUBTBL SC
	ON C.CLUBNAME = SC.CLUBNAME
GROUP BY SC.CLUBNAME, C.CLUBNAME;

 

 

2. OUTER JOIN 

1) LEFT & RIGHT JOING

테이블 A, B 중 한 쪽을 기준으로 테이블 결합

• LEFT JOIN : 명령어 'LEFT JOIN' 왼쪽에 있는 테이블 기준으로 결합
• RIGHT JOIN : 명령어 'RIGHT JOIN' 오른쪽에 있는 테이블 기준으로 결합

 

2) FULL JOIN

테이블 A, B의 합집합

 

-- #1
SELECT U.USERID, U.NAME, B.PRODNAME, U.ADDR, CONCAT(U.MOBILE1, U.MOBILE2) AS '연락처'
FROM USERTBL U LEFT JOIN BUYTBL B 
	ON U.USERID = B.USERID 
ORDER BY U.USERID;

-- #2
-- 구매내역 없는 고객명단
SELECT U.USERID, U.NAME, B.PRODNAME, U.ADDR
FROM USERTBL U LEFT JOIN BUYTBL B
	ON U.USERID = B.USERID
WHERE B.PRODNAME IS NULL
ORDER BY U.USERID;

-- #3
-- 동호회 미가입 학생
SELECT S.STDNAME, S.ADDR, SC.CLUBNAME
FROM STDTBL S LEFT JOIN STDCLUBTBL SC
	ON S.STDNAME = SC.STDNAME
WHERE SC.CLUBNAME IS NULL;

-- #4
-- 가입인원 없는 동호회
SELECT C.CLUBNAME, C.ROOMNO, SC.STDNAME
FROM CLUBTBL C LEFT JOIN STDCLUBTBL SC
	ON C.CLUBNAME = SC.CLUBNAME
WHERE SC.STDNAME IS NULL;

 

 

3. CROSS JOIN (Certesian Product)

• 테이블 A의 모든 행과 테이블 B의 모든 행 결합.
• 테이블 C (결합결과) 총 행수 = A의 총 행수 X B의 총 행수

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

 

 

4. SELF JOIN

1개의 테이블을 이용해 그 안의 열들을 조인하는 것

-- 실습 준비
CREATE TABLE EMPTBL 
(EMP CHAR(3), MANAGER CHAR(3), EMPTEL VARCHAR(8));

INSERT INTO EMPTBL VALUES 
('나사장', NULL, '0000'),
('김재무', '나사장', '2222'),
('김부장', '김재무', '2222-1'),
('이부장', '김재무', '2222-2'),
('우대리', '이부장', '2222-2-1'),
('지사원', '이부장', '2222-2-2'),
('이영업', '나사장', '1111'),
('한과장', '이영업', '1111-1'),
('최정보', '나사장', '3333'),
('윤차장', '최정보', '3333-1'),
('이주임', '윤차장', '3333-1-1');

-- #1
-- 부하직원과 매칭되는 상사 연락처 조회
SELECT A.EMP AS '부하직원', B.EMP AS '직속상관', B.EMPTEL AS '직속상관 연락처'
FROM EMPTBL A INNER JOIN EMPTBL B ON A.MANAGER = B.EMP;

 

5. 기타 (테이블 결합 및 조회)

1) UNION

• TABLE A UNION TABLE B
• 테이블 A, B의 열 개수 & 데이터 형식이 같아야 함
• UNION : 중복 제거하고 출력
• UNION ALL : 중복 포함하여 출력

SELECT STDNAME, ADDR FROM STDTBL 
UNION ALL
SELECT CLUBNAME, ROOMNO FROM CLUBTBL;

 

2) NOT IN

1번 쿼리 결과 중 2번 쿼리에 해당하는 것 제외

SELECT NAME, CONCAT(MOBILE1, MOBILE2) AS '전화번호' 
FROM USERTBL
WHERE NAME NOT IN (SELECT NAME FROM USERTBL WHERE MOBILE1 IS NULL);

-- 위 쿼리는 간단하기에 WHERE절 이용해서 같은 결과 만들 수 있으나,
-- 복잡한 조회의 경우 서브쿼리가 필요할 경우가 있으며 이때 NOT IN 사용
-- WHERE절을 이용한 동일결과 쿼리

SELECT NAME, CONCAT(MOBILE1, MOBILE2) AS '전화번호' 
FROM USERTBL
WHERE MOBILE1 IS NOT NULL;