11. [ DB ] MySQL 문법 : JOIN의 활용 예시

지난 시간에 알아본 join의 정의와 종류에 이어 이번시간에는 활용하는 문법을 알아보겠습니다. 

 

 

 

DBeaver의 컬럼을 생성 후 join 할 Foreign Keys시트에서 Create New Foreign Key를 클릭합니다. 

 

 

 

각 join 할 컬럼을 선택 후 join 진행을 해줍니다. 

 

 

 

join 완료하게 되면 위와 같이 ER Diagram에 연동된 내역이 표시됩니다. 

 

 

 

1. 활용 예시 

 

1) 예시

 

조건 1. 두개 테이블을 하나로 합쳐서 가져오시오.

 

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

 

 

 

 

2) 예시

 

조건 1. 회원가입은 했는데, 주문은 한 번도 안 한 사람도 있다.
조건 2. 고객 테이블의 데이터를 모두 살리고, 오른쪽에 오더 테이블을 붙이고 싶다.
조건 3.  join 문에서, 왼쪽 테이블이 고객 테이블이다!
조건 4. 즉, join의 왼쪽 테이블의 데이터는 모두 살리고, 오른쪽에 다른 테이블의 데이터를 합치는
조건 5. 조인을 left join 이라고 한다.

 

SELECT * from customers c left join orders o on c.id = o.customer_id ; SELECT * from orders o right join customers c on o.customer_id = c.id;

 

 

 

 

3) 예시

 

조건 1. 주문 금액이 600달러보다 큰 데이터를 가져오세요.
조건 2. 주문 정보는 모두 보여주고, 주문한 사람의 이메일주소, 이름도 가져오세요.

 

SELECT o.*, c.email, c.first_name, c.last_name from orders o join customers c on o.customer_id = c.id WHERE o.amount > 600;

 

 

조건 3. 위의 결과를, 주문날짜 내림차순으로 가져오세요. 

SELECT o.*, c.email, c.first_name, c.last_name from orders o join customers c on o.customer_id = c.id WHERE o.amount > 600 order by o.order_date desc;

 

 

 

 

 

 

4) 예시 

 

조건 1. 퍼스트 네임이 'Cobby' 인 사람의 주문내역을 가져오세요.

SELECT * from customers c join orders o on c.id = o.customer_id where first_name = 'Cobby';

 

 

 

조건 2. 퍼스트 네임에 'ty'가 들어있는 사람의 주문내역을 가져오세요.

SELECT * from customers c join orders o on c.id = o.customer_id where first_name like '%ty%';

 

 

 

 

 

5) 예시 

 

조건 1. 주문 금액이 300달러 이상이고, 500달러 이하인 주문내역을 가져오세요.
조건 2.  단, 주문한 사람의 이메일도 같이 나와야 합니다.

 

 

SELECT o.* , c.email from orders o join customers c on o.customer_id = c.id where amount BETWEEN 300 AND 500 order by amount desc;

 

 

 

 

 

 

6) 예시 

 

조건 1.  각 고객별로 주문 금액 최댓값을 구하고,
조건 2. 이 값이 600 달러 이상인 데이터만 가져와서, 내림차순으로 정렬하세요.
조건 3. 이메일 주소도 나와야 합니다.

 

SELECT c.id, c.email, max( o.amount ) as max_amount from customers c join orders o on c.id = o.customer_id GROUP BY c.id HAVING max_amount >= 600 order by max_amount desc;

 

 

 

 

7) 예시

 

조건 1. 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';

 

 

 

 

 

 

 

8) 예시 

 

조건 1.  2019년 12월 20일부터 2020년 1월 10일 사이의 주문 데이터에서,
조건 2. 고객별 주문 금액 평균이 300달러 이상인 사람의
조건 3. 평균 금액을 가져오세요.  이메일도 나오게 해 주세요.

 

SELECT customer_id , avg(amount) as avg_amount, c.email 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 customer_id HAVING avg_amount >= 300 order by avg_amount desc;