MySQL Workbench : join과 left join 하는 방법과 예시

< join과 left join 하는 방법과 예시 >

- 학습을 위해서 DB와 테이블은 이전에 만든 형식을 가져와서 사용

- 참고 : https://sorktjrrb.tistory.com/123

MySQL Workbench : 데이터 정규화를 위한 foreign key 설정 방법

 

# customers 테이블과 orders 테이블에 각각 데이터를 넣어준다.

customers.sql

0.01MB

 

 

orders.sql

0.06MB

 

# 이제 두개의 테이블을 합쳐서 가져오는 방법 join

-- 데이터 정규화를 위해 테이블을 두개로 나누어서 fk 설정 및 데이터까지 입력하였다.
-- 두개 테이블을 하나로 합쳐서 가져오시오.

-- 주로 보여줄 테이블을 먼저 적어주고, 그뒤에 불러올 테이블을 join 으로 불러온다.
-- on 연결시킬 컬럼을 적어주면 되는데, fk로 설정한 컬럼을 붙여주면 된다. pandas에 merge와 비슷한 개념

select *
from orders
join customers
	on orders.customer_id = customers.id;

-- 실무에서는 on 작성이 길기 때문에 테이블 이름을 줄여서 사용한다.
-- 컬럼명을 바꿀때와 동일하게 as 사용 (당연히 동일하게 as를 빼도됨)

select *
from orders as o
join customers as c
	on o.customer_id = c.id;
    
select *
from orders o
join customers c
	on o.customer_id = c.id;

ㄴ 출력 결과는 상단의 표와 동일하게 나옴

 

# 누락되는 데이터 없이 값이 없을경우 null로라도 표시해서 보여줘라 left join, right join

-- 위의 조인은!!!
-- 두개 테이블에서 공통으로 들어있는 데이터만 합쳐서 가져온 것이다.
-- 즉, o.customer_id 와 c.id 컬럼 둘 다 존재하는 데이터만 함친것.

-- 예를 들어, 회원가입은 했는데, 주문을 한번도 안한 사람들도 있다. => 그럼 위에 방식으로는 해당 데이터들은 그냥 누락되어 버림.
-- 따라서, 모든 고객 데이터를 가져오되, 주문 안한 사람들도 정보를 볼수 있도록 하는 방법!
-- left join , right join
select *
from customers c
left join orders o
	on c.id = o.customer_id;
    
select *
from customers c
right join orders o
	on c.id = o.customer_id;

ㄴ 보통 참조하는 데이터를 먼저 쓰기 때문에, 보편적으로는 left join을 사용함

 

# join을 활용한 실습문 (left join과 조건문 사용)

-- 주문금액이 600달려보다 큰 데이터를 가져오시오.
select *
from orders o
left join customers c
	on o.customer_id = c.id
where amount > 600;

 

-- 위에서, 이 사람들의 이메일과 주문금액, 주문날짜를 가져오시오.
select c.email, o.amount, o.order_date
from orders o
left join customers c
	on o.customer_id = c.id
where amount > 600;

 

-- 위의 결과를, 주문날짜 내림차순으로 가져오시오.
select c.email, o.amount, o.order_date
from orders o
left join customers c
	on o.customer_id = c.id
where amount > 600
order by o.order_date desc;

 

-- 고객 아이디가 36인 사람의 주문내역(모든정보)을 가져오시오.
select *
from customers c
left join orders o
	on c.id = o.customer_id
where c.id = 36;

 

-- first_name 이 'Cobby' 인 사람의 주문내역을 가져오시오.
select *
from customers c
left join orders o
	on c.id = o.customer_id
where c.first_name = 'Cobby';

 

-- first_name 에 ty 가 들어있는 사람의, 주문내역을 가져오시오.
select *
from customers c
left join orders o
	on c.id = o.customer_id
where c.first_name like '%ty%';

 

-- 주문금액이 300이상이고 500이하인 주문내역을 가져오되,
-- 주문한 사람의 이메일도 함께 나오도록 가져오시오.
select o.*, c.email
from orders o
left join customers c
	on o.customer_id = c.id
where amount between 300 and 500;

 

-- 각 고객별로, 주문수를 나타내시오.
-- 고객의 이름과 이메일이 함께 나와야 합니다.
select c.first_name, c.last_name, c.email, count(o.id) as order_cnt
from customers c
left join orders o
	on c.id = o.customer_id
group by email;

 

-- 각 고객별로, 주문 금액 평균이 300달러 이상인 데이터를 가져오시오.
-- ( 사람의 이메일과, 이름과, 평균금액을 보여주세요)
-- 주문을 한사람만 대상으로 하기 때문에 구지 left join까지 할필요없음.

select c.email, c.first_name, c.last_name, avg( o.amount ) as avg_amount
from customers c
join orders o
	on c.id = o.customer_id
group by email having avg_amount >= 300
order by avg_amount;

 

-- 각 고객별로, 주문 금액의 최대값을 구하고,
-- 이 값이 600달러 이상인 데이터만 가져와서
-- 내림차순으로 정렬하세요. (이메일, 이름, 주문최대금액)

select c.email, c.first_name, c.last_name, max(o.amount) as max_amount
from customers c
join orders o
	on c.id = o.customer_id
group by email having max_amount >= 600
order by max_amount desc;

 

# 날짜 데이터와 함수까지 활용한 실습문

-- order 테이블의, 주문날짜의 최소값과 최대값을 구하세요.
select min(order_date) min_date, max(order_date) max_date
from orders;

 

-- 2019년 12월 20일부터 2020년 1월 10일 사이에 주문한 사람은 몇명인가?
-- iso 형식으로 날짜를 작성해주면 된다.
select *
from orders
where order_date between '2019-12-20' and '2020-01-10';

select count(distinct customer_id )
from orders
where order_date between '2019-12-20' and '2020-01-10';

ㄴ 중복을 제거하고 카운트하기 위해 distinct 활용

 

-- 2019년 12월 20일 부터 2020년 1월 10일 사이의 주문데이터에서
-- 고객별 주문금액 평균이 300달러 이상인 사람의
-- 이름과 평귬금액을 가져오세요.

select c.first_name, c.last_name , avg(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;

 

다음 게시글로 계속~!