[천재교육] MySQL JOIN
📕 들어가며
이번 글에서는
관계형 데이터베이스의 꽃이라고 볼 수 있는
JOIN에 대하여 배워보고
관련 문제를 풀어보겠습니다.
📕 데이터 준비
CREATE DATABASE testdb;
DROP TABLE IF EXISTS `testdb`.`member`;
DROP TABLE IF EXISTS `testdb`.`product`;
DROP TABLE IF EXISTS `testdb`.`buy`;
CREATE TABLE `testdb`.`member`(
id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
member_id VARCHAR(30),
name VARCHAR(10),
address VARCHAR(10),
phone_number VARCHAR(20),
create_date datetime,
update_date datetime
);
CREATE TABLE `testdb`.`product`(
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
qty INT,
price INT,
create_date datetime,
update_date datetime
);
CREATE TABLE `testdb`.`buy` (
`id` bigint NOT NULL AUTO_INCREMENT,
`member_id` bigint DEFAULT NULL,
`product_id` bigint DEFAULT NULL,
`qty` int DEFAULT NULL,
`create_date` datetime DEFAULT CURRENT_TIMESTAMP,
`update_date` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
INSERT INTO `testdb`.`member` (member_id,name,address,phone_number,create_date,update_date) VALUES
('TWC','트와이스','Seoul','010-1111-1111','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('BLK','블랙핑크','Seoul','010-1111-2222','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('WMN','여자친구','Daegu','010-1111-3333','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('OMY','오마이걸','Daegu','010-1111-4444','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('GRL','소녀시대','Daegeon','010-1111-5555','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('ITZ','잇지','Daegeon','010-2222-1111','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('RED','레드밸벳','Daegeon','010-2222-1111','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('APN','에이핑크','Busan','010-2222-2222','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('SPC','우주소녀','Junnam','010-2222-2222','2023-02-06 14:04:08','2023-02-06 14:04:08');
INSERT INTO `testdb`.`product`
(`name`, `qty`, `price`, `create_date`, `update_date`)
VALUES
('carrot', 10, 1000, NOW(), NOW()),
('apple', 100, 500, NOW(), NOW()),
('pear', 30, 800, NOW(), NOW()),
('orange', 50, 800, NOW(), NOW()),
('honey', 10, 3000, NOW(), NOW()),
('cabage', 15, 3000, NOW(), NOW()),
('pine', 20, 5000, NOW(), NOW()),
('melon', 10, 10000, NOW(), NOW());
INSERT INTO `testdb`.`buy` (member_id,product_id,qty,create_date,update_date) VALUES
(1,1,10,'2023-02-07 08:24:33','2023-02-07 08:24:33'),
(1,2,30,'2023-02-07 08:29:48','2023-02-07 08:29:48'),
(2,1,10,'2023-02-07 08:29:58','2023-02-07 08:29:58'),
(5,2,10,'2023-02-07 08:30:07','2023-02-07 08:30:07'),
(6,8,5,'2023-02-07 08:30:30','2023-02-07 08:30:30'),
(3,3,4,'2023-02-07 08:30:39','2023-02-07 08:30:39'),
(3,5,10,'2023-02-07 08:30:50','2023-02-07 08:30:50'),
(4,4,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
(5,2,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
(4,1,20,'2023-02-07 08:38:40','2023-02-07 08:38:40');
INSERT INTO `testdb`.`buy` (member_id,product_id,qty,create_date,update_date) VALUES
(6,7,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
(9,4,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
(7,2,30,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
(1,7,20,'2023-02-07 08:38:40','2023-02-07 08:38:40');📕 기초지식
✏️ JOIN이 뭔데?!
조인이란 두 개의 테이블을 서로 묶어서
하나의 결과를 만들어 내는 것을 의미합니다. 요런 느낌?!
그럼 테이블이 두 개가 있으니
앞으로
왼쪽 테이블을 LEFT
오른쪽 테이블을 RIGHT 라고 하겠습니다.
✏️ JOIN의 종류
조인에는 3가지 종류가 있습니다.
INNER JOIN, LEFT JOIN, RIGHT JOIN
1. INNER JOIN
INNER JOIN은 교집합처럼
왼쪽 테이블과 오른쪽 테이블의 공통원소를 찾아서
두 테이블을 결합합니다.
2. LEFT JOIN
LEFT JOIN은
왼쪽 테이블을 기준으로
오른쪽 테이블을 결합합니다.
만약 왼쪽 테이블에는 있지만 오른쪽 테이블에는 없는 경우 Null로 표시 됩니다.
3. RIGHT JOIN
RIGHT JOIN은
오른쪽 테이블을 기준으로
왼쪽 테이블을 결합합니다.
만약 오른쪽 테이블에는 있지만 왼쪽 테이블에는 없는 경우 Null로 표시 됩니다.
✏️ MySQL에서 JOIN 사용법
# SQL에서는 다음과 같은 문법으로 JOIN을 수행할 수 있습니다.
SELECT <열목록>
FROM <왼쪽 테이블>
[ INNER | LEFT | RIGHT] JOIN <오른쪽 테이블>
ON <조인될 조건>예) member_id가 OMY인 member가 구매한 product_id를 가져 옵니다.
SELECT b.product_id
FROM buy b
INNER JOIN member m
ON b.member_id = m.id
WHERE m.member_id = 'OMY'; 예) product 테이블의 name이 carrot인 상품의 구매량(buy에 있는 qty)을 합산해서 가져옵니다.
SELECT SUM（b.qty）
FROM product p
INNER JOIN buy b
ON p.id = b.product_id
WHERE p.name='carrot';그럼 이제 부터 다양한 문제를 풀어보도록 하겠습니다.
익숙해질 때까지 연습하시라구 13문제나 가져왔습니다. ^^
📕 INNER JOIN 연습 문제
문제 1
구매이력이 있는 (buy 테이블에 데이터가 있는) 모든 member의 이름을 가져옵니다. (중복제거)
문제 2
구매이력이 있는 member 테이블의 meber_id, name,
구매한 product의 name, price를 가져옵니다.
문제 3
가장 많이 팔린 product의 name과 price를 가져 옵니다. (중복제거)
문제 4
memer테이블의 모든 사람이 (member_id)가 구매한 구매 양(qty)로 가져옵니다.
단 구매양이 없는 경우 NULL 로 처리합니다.
문제 5
product 테이블에 있는 모든 품목(name)이 팔린 갯수 (qty)를 가져옵니다.
단, 비어있는 경우 NULL로 처리합니다.
문제 6
어떤 멤버 (member_id)가 구매한 품목(product_name)과 구매수량(buy.qty)를 가져옵니다.
단 없는 경우 NULL로 처리합니다.
문제 7
member 테이블에 있는 member_id가 TWC인 사람이 구매한 물품 중에 가장 비싼 물품 (product의 price가 제일 높은) 의 name, price 가져오기
문제 8
product의 name이 melon 인 물품의 총 판매 수량(buy.qty) 가져오기
문제 9
단골 고객(count 구매 횟수가 제일 많은 member)의 member_id, 이름, 총 수량(SUM(buy.qty))을 가져오기
문제 10
product 테이블에서 가장 비싼 물건을 구매한 member의 member_id와 name을 가져옵니다.
문제 11
member 테이블에서 Daegue에서 사는 사람이 구매한 모든 produce의 name과 price를 가져옵니다.
문제 12
member_id가 'TWC'인 member가 구매한 물품의 양 (buy.qty)의 합계를 구합니다.
문제 13
member_id 'WMN'가 구매한 product의 모든 정보를 가격(price)이 비싼 순으로 정렬합니다.
문제1 정답
SELECT DISTINCT m.name
FROM member m
INNER JOIN buy b
ON m.id = b.member_id;문제2 정답
SELECT m.member_id, m.name, p.name, p.price
FROM buy b
INNER JOIN member m
ON b.member_id = m.id
INNER JOIN product p
ON b.product_id = p.id;문제3 정답
SELECT DISTINCT p.name , p.price
FROM buy b
INNER JOIN product p
ON b.product_id = p.id
WHERE p.id = (SELECT b.product_id
FROM buy b
GROUP BY b.product_id
ORDER BY SUM(b.qty) DESC
LIMIT 1
);문제4 정답
SELECT
m.member_id, SUM(b.qty)
FROM
member m
LEFT JOIN buy b ON m.id = b.member_id
GROUP BY m.member_id;문제5 정답
SELECT p.name, SUM(b.qty)
FROM
buy b
RIGHT OUTER JOIN product p ON b.product_id = p.id
GROUP BY p.name;문제6 정답
SELECT m.member_id, p.name, b.qty
FROM
member m
LEFT OUTER JOIN buy b ON m.id = b.member_id
LEFT OUTER JOIN product p ON b.product_id = p.id;
문제7 정답
SELECT p.name, p.price
FROM
member m
INNER JOIN buy b ON m.id = b.member_id
INNER JOIN product p ON b.product_id = p.id
WHERE 1=1
AND m.member_id = "TWC"
ORDER BY p.price DESC
LIMIT 1;문제8 정답
SELECT SUM(b.qty)
FROM
product p
INNER JOIN buy b ON p.id = b.product_id
WHERE
p.name = 'melon';문제9 정답
SELECT m.member_id, m.name, SUM(b.qty)
FROM buy b
INNER JOIN member m ON b.member_id = m.id
WHERE
m.id = (
SELECT
buy.member_id
FROM
buy
GROUP BY buy.member_id
ORDER BY COUNT(1) DESC
LIMIT 1
)
GROUP BY b.member_id;문제10 정답
SELECT m.member_id, m.name
FROM
buy b
INNER JOIN member m ON b.member_id = m.id
INNER JOIN product p ON b.product_id = p.id
WHERE
p.price = (
SELECT MAX(price)
FROM product
ORDER BY price
);문제11 정답
SELECT DISTINCT p.name, p.price
FROM
member m
INNER JOIN buy b ON m.id = b.member_id
INNER JOIN product p ON b.product_id = p.id
WHERE 1=1
AND m.address = 'Daegu' ;문제12 정답
SELECT SUM(b.qty)
FROM
buy b
INNER JOIN member m ON b.member_id = m.id
WHERE 1=1
AND m.member_id = 'TWC';문제13 정답
SELECT p.*
FROM
buy b
INNER JOIN member m ON b.member_id = m.id
INNER JOIN product p ON b.product_id = p.id
WHERE 1=1
AND m.member_id = 'WMN'
ORDER BY p.price DESC;