JOIN

테이블 A와 B를 엮을 때에는 관계성을 가지고 엮는다. (PK-FK)

주로 대표값으로.

 

• PK(Primary Key):
  • 유일하다. 중복노노
  • 대표값
  • 다른 데이터를 찾기위한 키 역할
  • 인덱스로 설정_(니가 아는 그 인덱스가 아님) = 색인
• UK (Unique Key)
  • 유일하다.
  • 단, 참조는 불가하다. FK 가 될 수 없음
• FK
  • 중복 가능

 

테이블 간 관계(ERD)

*집합레벨: 동시에 가질 수 있는 데이터의 개수

• 유저 테이블: 쇼핑몰 유저 정보. 집합레벨 1 (유저는 중복될 수 없으니까)
• 오더 테이블: 쇼핑몰 주문내역. 집합레벨 N (유저 여러명 있을 수 있응께)
  • 유저 - 오더 테이블 관계 = 1:N (유저 한 명당 주문 여러 건일 수 있다.)
• 프로덕트 테이블: 쇼핑몰에서 판매하는 상품 내역. 집합레벨 1(상품번호 하나씩만)
  • 프로덕트 - 오더 테이블 관계 = 1:N (상품 1개 당 주문 여러 개일 수 있다.)

• 조인할때 프롬절에 오는 애들은 무조건 집합레벨이 1 (유니크한 값만 가져야 한다) 
  • 1:N 관계에서 조인을 해야 조인 후 테이블이 N개가 된다.

https://silverkim.org/server-database/데이터베이스-조인-시-데이터-집합-레벨의-이해/

[데이터베이스] 조인 시 데이터 집합 레벨의 이해 - SILVERKIM.ORG

 

 

JOIN

테이블 생성

#  JOIN
CREATE DATABASE test;
use test;
CREATE TABLE user (
	user_id int(11) unsigned NOT NULL AUTO_INCREMENT, name varchar(30) DEFAULT NULL,
	PRIMARY KEY (user_id)
);

CREATE TABLE addr (
	id int(11) unsigned NOT NULL AUTO_INCREMENT, addr varchar(30) DEFAULT NULL,
	user_id int(11) DEFAULT NULL,
	PRIMARY KEY (id)
);

INSERT INTO user(name) VALUES ("jin"),
("po"),
("alice"),
("petter");
INSERT INTO addr(addr, user_id) VALUES ("seoul", 1),
("pusan", 2),
("deajeon", 3),
("deagu", 5), ("seoul", 6);

 

INNER JOIN

두 테이블의 공통 row만 출력

# INNER JOIN: 두 테이블 사이에 공통된 값이 없는 row는 출력 안함
SELECT user.user_id, user.name, addr.addr
FROM user
JOIN addr ON user.user_id = addr.user_id;

use world;

# INNER JOIN
# 도시 이름과 국가이름을 출력
# 도시: city, 국가이름: country 테이블

select * from city;
select * from country;

SELECT country.Name 'country_name' , city.Name 'city_name'
FROM city
JOIN country ON city.CountryCode = country.Code;

 

LEFT JOIN

두 테이블의 공통 row만 출력

FROM 절에 오는 테이블의 모든 정보 표시.

# LEFT JOIN
# FROM절에 오는 테이블의 모든 정보를 표시

use test;

SELECT user.user_id, user.name, addr.addr
FROM user
LEFT JOIN addr ON user.user_id = addr.user_id;

 

RIGHT JOIN

# RIGHT JOIN
SELECT addr.user_id, user.name, addr.addr
FROM user
RIGHT JOIN addr ON user.user_id = addr.user_id;

 

UNION

• SELECT 문의 결과 데이터를 하나로 합쳐서 출력.
• 주의해야 할 사항: 컬럼의 개수, 타입, 순서가 모두 일치해야 함.
• UNION은 자동으로 distinct가 적용된다. (중복 데이터는 제거됨)
• UNION ALL은 중복을 허용 

# UNION

SELECT name 
FROM user
UNION 
SELECT addr 
FROM addr; # 중복(서울) 제거된 후 출력

SELECT name 
FROM user
UNION ALL 
SELECT addr 
FROM addr;


# UNION 으로 FULL OUTER JOIN 구현
SELECT id, user.name, addr.addr
FROM user
LEFT JOIN addr ON user.user_id = addr.user_id
UNION
SELECT id, user.name, addr.addr
FROM user
RIGHT JOIN addr ON user.user_id = addr.user_id;

 

JOIN 실습

실습 자료는 sakila에서 가져옴.

아래 사이트에서 sakila database 다운받고, schema 먼저 만든 후 data 넣으면 됨.

코드를 그대로 복붙해서 실행하면 되기 때문에 굉장히 간단.

https://dev.mysql.com/doc/index-other.html

MySQL :: Other MySQL Documentation

 

0. 테이블 정보

SELECT * FROM film; #film_ie
SELECT * FROM film_category; # film_id, category_id
SELECT * FROM category; # category_id
SELECT * FROM rental;
SELECT * FROM customer; # user information
SELECT * FROM inventory; # no need to use.. maybe

 

 

1. 영화 대여 이력이 가장 많은 3명의 고객에 대해, 고객 이름, 이메일, 대여 횟수를 출력하세요.

✅ 고객 정보 테이블과 대여 관련 테이블을 합쳐야 한다. (join)

✅ 고객 별로 대여 수를 세야 한다. (group by)

✅ 대여 이력이 많은 순서로 정렬해야 한다. (order by)

✅ 3명만 봐야한다. (limit)

 

SELECT rental.customer_id, 
        COUNT(rental.customer_id) as rental_cnt, 
        concat(customer.first_name,' ', customer.last_name) as full_name, 
        customer.email
FROM rental
LEFT JOIN customer ON rental.customer_id = customer.customer_id
GROUP BY customer_id
ORDER BY rental_cnt DESC
LIMIT 3;

• concat : 열과 열의 내용을 합쳐주는 명령어.
• 구조를 크게 보면 SELECT  --- FROM rental LEFT JOIN customer ON rental.customer_id = customer.customer_id 로 볼 수 있다
• rental과 customer 테이블을 조인한다. 조인 방식은 rental 테이블의 전체를 기준으로 하며 customer 테이블에만 있는 row들은 모두 drop한다. 그리고 두 테이블에서 동일하게 가지고 있는 열인 customer_id를 기준으로 join한다.

 

 2. "Comedy" 장르에 속한 영화의 평점 평균이 4점 이상이고, 2005년 이후에 발행된 영화 중에서 평점이 가장 높은 5편의 영화 제목과, 평점을 출력하세요.

✅ 필요한 테이블: film(영화 제목) + film_category(영화 카테고리 설명) + category (카테고리 명 설명)

✅ 조건들이 많음 : where ~ and 사용

✅ 평점이 높은 5편 영화 : order by + limit 

SELECT *
FROM film
JOIN film_category ON film.film_id = film_category.film_id
JOIN category ON category.category_id = film_category.category_id
WHERE film.release_year >= 2005
AND film.rental_rate >= 4
AND category.name = 'Comedy'
ORDER BY film.rental_rate DESC
LIMIT 5;

 

3. 2005년 이후에 출시된 영화 중에서, 대여한 횟수가 가장 많은 영화의 제목과 대여 횟수를 출력하세요. (inventory 활용)

✅ 테이블: film(영화 출시일) + rental(대여 횟수) + inventory (film-rental 테이블의 징검다리)

✅ 영화별 대여 횟수 : group by, count

SELECT title, count(rental.rental_id) as cnt
FROM rental
JOIN inventory ON rental.inventory_id = inventory.inventory_id
JOIN film ON inventory.film_id= film.film_id
WHERE film.release_year > 2005
GROUP BY film.film_id
ORDER BY cnt DESC
LIMIT 1
;

2005년 이후에 release_year 영화 중에서, 대여한 횟수가 가장 많은 영화의 제목과 대여 횟수를 출력하세요. (inventory 활용)
select