[MySQL] JOIN 정리 (INNER JOIN, LEFT/RIGHT JOIN, OUTER JOIN)

 

 

 

 

 

 

INNER JOIN

(1) INNER JOIN (교집합)

SELECT A.컬럼명
FROM 테이블명_1 A JOIN 테이블명_2 B ON A.ID = B.ID;

두 테이블의 공통된 내용을 뽑아내고 싶을 경우에 사용한다. 하나의 테이블을 두 테이블로 인식하여 INNER JOIN을 수행할 수도 있다. 해당 개념은 아래 예시를 통해 자세히 이해하고자 한다. 

 

 

 

예제 -1) 홍초와 우유를 동시에 구입한 카트가 있다면 그 카드의 아이디를 조회하는 SQL문을 작성하시오.

CART_ID	CART_PRODUCT
18	우유
18	홍초
20	계란
23	두부
23	우유

 

예제 -1 답) 

SELECT DISTINCT A.CART_ID 
FROM CART_PRODUCT A INNER JOIN CART_PRODUCT B ON A.CART_ID = B.CART_ID
WHERE (A.NAME = '홍초' AND B.NAME = '우유') OR (A.NAME = '우유' AND B.NAME = '홍초');

예제 -1 해설) 

A.CART_ID	A.CART_PRODUCT	B.CART_ID	B.CART_PRODUCT
18	우유	18	우유
18	우유	18	홍초
18	홍초	18	홍초
18	홍초	18	우유
20	계란	20	계란
23	두부	23	두부
23	두부	23	우유
23	우유	23	우유
23	우유	23	두부

INNER JOIN 시 다음과 같은 테이블이 생성된다. CART_ID가 동일할 경우 JOIN되는데 이중에서 우유와 홍초를 동시에 포함하는 것은 CART_ID 18번만 해당된다. 두번씩 카운팅 되므로 중복을 제거하기 위해 DISTINCT를 사용한다.

A.CART_ID

18

A.CART_ID

A.CART_ID

예제 -2) 있었는데요 없었습니다

https://programmers.co.kr/learn/courses/30/lessons/59043

SELECT O.ANIMAL_ID, O.NAME FROM ANIMAL_INS I
INNER JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.DATETIME < I.DATETIME
ORDER BY I.DATETIME ASC;

* 똑같은 컬럼명 DATETIME이나 ANIMAL_INS에서는 보호 시작일, ANIMAL_OUTS에서는 입양일임을 주의

* 즉, ORDER BY O.DATETIME 을 사용하는 경우 다른 결과 조회됨

 

예제 -3) 보호소에서 중성화한 동물

https://programmers.co.kr/learn/courses/30/lessons/59045

# full name 사용할 경우
SELECT O.ANIMAL_ID, O.ANIMAL_TYPE, O.NAME
FROM ANIMAL_OUTS O INNER JOIN ANIMAL_INS I 
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE LIKE 'Intact%' AND O.SEX_UPON_OUTCOME IN ('Spayed Female','Neutered Male')
ORDER BY O.ANIMAL_ID;

# % 사용하여 특정 문자열만 가져올 경우
SELECT O.ANIMAL_ID, O.ANIMAL_TYPE, O.NAME
FROM ANIMAL_OUTS O INNER JOIN ANIMAL_INS I 
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE LIKE "Intact%"
      AND (O.SEX_UPON_OUTCOME LIKE "Spayed%" OR O.SEX_UPON_OUTCOME LIKE "Neutered%")
ORDER BY O.ANIMAL_ID;

* LIKE '특정문자열' 처리시 문자열 조금만 오타나도 결과 출력이 안되니까 주의 

* ''(Single quotation) / ""(Double quotation) 차이 없음 

* IN 사용할 경우 % 사용 불가. % 사용시 반드시 LIKE와 함께 처리  IN ('Spayed%', 'Neutered%') > error

 

예제 -4) 오랜 기간 보호한 동물(2)

https://programmers.co.kr/learn/courses/30/lessons/59411

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS O INNER JOIN ANIMAL_INS I ON I.ANIMAL_ID = O.ANIMAL_ID
ORDER BY O.DATETIME - I.DATETIME DESC
LIMIT 2;

* 보호 '시작일'과 보호 '기간'의 차이를 파악해야 하는 문제 

* ORDER BY I.DATETIME DESC 로 처리하는 경우, 보호 '시작일'이 긴 순으로 처리 되므로 다른 결과 조회

 

 

 

 

 

 

 

LEFT JOIN (왼쪽 테이블 전부)

(2) LEFT JOIN (왼쪽 테이블 전부)

ID 값이 중첩될 경우 왼쪽 테이블인 테이블명_1의 ID 값을 출력한다. 

SELECT A.컬럼명
FROM 테이블명_1 A LEFT JOIN 테이블명_1 B ON A.ID = B.ID;

 

 

예제 -1) 입출금 통장(SAVING ACCOUNT)을 개설한 고객의 ID와 GRADE를 조회하는 SQL문을 작성하시오.

 

[PERSON 테이블]

 

 

ID	FIRST NAME	GRADE
1001	CHOI	5
1002	PARK	1
1003	HAN	1
1004	LEE	2
1005	JEONG	3
1006	KIM	1
1007	KIM	2
1010	SHIN	4

[ACCOUNT 테이블]

ID	NUMBER	DETAIL
1001	211117-03-405250	ISA
1001	211117-01-313000	SAVING
1003	211117-01-313001	SAVING
1005	211117-01-313002	SAVING
1005	211117-04-407725	FIXED
1005	211117-03-405251	ISA
1010	NULL	NULL

 

예제 -1 답)

SELECT P.FIRST_NAME, P.GRADE
FROM ACCOUNT A LEFT JOIN PERSON P ON P.ID = A.ID

예제 -1 해설)

 

LEFT JOIN이기에 PERSON 테이블의 ID 값이 ACCOUNT ID에 존재하지 않더라도 테이블에 조인된다. 여기서 입출금 계좌를 보유한 고객의 ID는 1001, 1003, 1005 GRADE는 ID 순서대로 5,1,3이 된다.  

ID	NUMBER	DETAIL	ID	FIRST_NAME	GRADE
1001	211117-03-405250	ISA	1001	CHOI	5
1001	211117-01-313000	SAVING	1001	CHOI	5
1003	211117-01-313001	SAVING	1003	HAN	1
1005	211117-01-313002	SAVING	1005	JEONG	3
1005	211117-04-407725	FIXED	1005	JEONG	3
1005	211117-03-405251	ISA	1005	JEONG	3
1010	211117-04-407726	FIXED	NULL	NULL	NULL

ID	GRADE
1001	5
1003	1
1005	3

ID

 

 

 

 

 

 

 

LEFT JOIN (순수 왼쪽 테이블)

(2) -1 LEFT JOIN (순수 왼쪽 테이블만 > 왼쪽테이블 - 오른쪽테이블)

SELECT A.컬럼명
FROM 테이블명_1 A LEFT JOIN 테이블명_1 B ON A.ID = B.ID
WHERE B.ID IS NULL;

 

 

예제 -1) 없어진 기록 찾기

https://programmers.co.kr/learn/courses/30/lessons/59042

# USING 사용
SELECT O.ANIMAL_ID, O.NAME FROM ANIMAL_OUTS O
LEFT JOIN ANIMAL_INS I USING (ANIMAL_ID) 
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID;

# USING 사용 X > ON 사용
SELECT O.ANIMAL_ID, O.NAME FROM ANIMAL_OUTS O
LEFT JOIN ANIMAL_INS I ON O.ANIMAL_ID = I.ANIMAL_ID  
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID;

* KEY 값으로 사용하는 컬럼명이 같은 경우 USING 사용 가능 (컬럼명이 다르다면 ON 사용)

* USING 사용시 ANIMAL_ID 값에 별칭(Alias) 붙이는 경우 에러 발생  USING(O.ANIMAL_ID) > error

 

 

 

예제 -2) 오랜 기간 보호한 동물 (1)

https://programmers.co.kr/learn/courses/30/lessons/59044

# LEFT JOIN 사용한 경우
SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O 
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME ASC
LIMIT 3;

# RIGJT JOIN 사용한 경우
SELECT I.NAME, I.DATETIME
FROM ANIMAL_OUTS O RIGHT JOIN ANIMAL_INS I  
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME ASC
LIMIT 3;

* 즉, 테이블 위치를 어디에 두느냐에 따라 RIGHT JOIN을 사용할 수도, LEFT JOIN을 사용할 수도 있다.

 

 

 

 

 

 

RIGHT JOIN (오른쪽 테이블 전부)

(3) RIGHT JOIN (오른쪽 테이블 전부)

ID 값이 중첩될 경우 오른쪽 테이블인 테이블명_2의 ID 값을 출력  

SELECT B.컬럼명
FROM 테이블명_1 A RIGHT JOIN 테이블명_1 B ON A.ID = B.ID;

 

 

 

RIGHT JOIN (순수 오른쪽 테이블)

(3) -1 RIGHT JOIN (순수 오른쪽 테이블만 오른쪽 테이블 - 왼쪽 테이블)

SELECT B.컬럼명
FROM 테이블명_1 A LEFT JOIN 테이블명_1 B ON A.ID = B.ID
WHERE A.ID IS NULL;

 

 

 

 

 

 

OUTER JOIN

(4) OUTER JOIN

SELECT *
FROM 테이블명_1 A LEFT JOIN 테이블명_1 B ON A.ID = B.ID
UNION
SELECT *
FROM 테이블명_1 A RIGHT JOIN 테이블명_1 B ON A.ID = B.ID;

예제 -1)

SELECT * FROM CITY C1
LEFT JOIN COUNTRY C2
ON C1.COUNTRY.ID = C2.COUNTRY.ID
UNION
SELECT * FROM CITY C1
RIGHT JOIN COUNTRY C2
ON C1.COUNTRY.ID = C2.COUNTRY.ID;

 

 

(5) CROSS JOIN (왼쪽 테이블 ROW 개수 X 오른쪽 테이블 ROW 개수)

SELECT * FROM 테이블명_1 CROSS JOIN 테이블명_2;

예제 -1) 각 사원이 부서에 배치될 경우의 수를 모두 구해 테이블로 완성하시오 

SELECT * 
FROM N_AME CROSS JOIN D_EPARTMENT;