[MySQL] 테이블 합치기 join, left join

📝MySQL Workbench 테이블 합치기

테이블을 하나로 합칠때는 join 키워드 사용

※ 합친 테이블에서 특정 테이블의 전체컬럼 가져오기 = 테이블명.*

-- 2개의 테이블을 하나로 합치기
select *
from orders
join customers
on orders.customer_id = customers.id;

-- 줄여서 사용가능 (as생략가능)
select *
from orders as o
join customers as c
on o.customer_id = c.id;

-- 특정테이블의 전체컬럼 가져오기
select o*
from orders o
join customers c
on o.customer_id = c.id;

-- 3개의 테이블 하나로 합치기 (join-on 두번 사용)
select title, rating,
concat(first_name,' ', last_name) as reviewer
from reviews r 
join series s on r.series_id = s.id
join reviewers rv on r.reviewer_id = rv.id
order by title;

 

테이블을 하나로 합칠때 from 테이블의 데이터를 다 가져오고 싶을때 left join 키워드 사용

-- 모든 고객 데이터를 기준으로 주문정보를 붙이기
-- 어떤 고객은 주문을 안했는지 확인가능 (일치하지 않은 데이터는 null값으로 표시)
select *
from customers c
left join orders o
on c.id = o.customer_id;

-- 각 고객별로 주문수를 나타내기
select c.first_name, c.last_name, count(o.id) as 'order_count'
from customers c
left join orders o
on c.id = o.customer_id
group by c.id;

 

ex)

-- 두 테이블을 합쳐서 주문아이디, 주문날짜, 금액, 고객아이디, 고객이메일을 가져오기
select o.id, o.order_date, o.amount, c.id as c_id, c.email
from orders o
join customers c
on o.customer_id = c.id;

-- 주문데이터를 가져오되 주문시간을 최신순으로 해서 금액이 300달러 이상인 데이터만 가져오기
-- 데이터는 주문아이디, 주문날짜, 금액, 고객의 이메일주소가 나오도록
select o.id, o.order_date, o.amount, c.email
from orders o
join customers c
on o.customer_id = c.id
where amount >= 300
order by order_date desc;

-- 주문금액이 600달러보다 큰 사람들한테 프로모션 메일을 보내려한다
-- 600달러보다 큰 사람들의 이메일 가져오기
select distinct c.email
from orders o
join customers c
on o.customer_id = c.id
where amount >= 600;

-- 각 고객별로 주문금액 평균이 300달러 이상인 사람의 이름과 평균금액을 가져오기
select first_name, last_name, avg(amount) as amount
from orders o
join customers c
on o.customer_id = c.id
group by c.id having avg(o.amount) > 300;

-- 2019년 12월 20일부터 2020년 1월 10일 사이에 주문한 사람은 몇명인가
select count(distinct customer_id)
from orders
where order_date >= '2019-12-20' and order_date <= '2020-01-10';

-- 2019년 12월 20일부터 2020년 1월 10일 사이의 주문 데이터에서
-- 고객별 주문 금액이 평균 300달러 이상되는 사람의 이름과 평균주문금액을
-- 금액이 큰순으로 7명 표시하기
select c.first_name, c.last_name, avg(o.amount) as avg_amount
from orders o
join customers c
on o.customer_id = c.id
where order_date between '2019-12-20' and '2020-01-10'
group by c.id having avg_amount > 300
order by avg_amount desc
limit 7;