[SQL 공부] 메타코드 강의를 통한 SQL 스킬 학습(JOIN)

HJ·2024년 1월 8일

학습

저번 SQL 메타코드 포스팅에서 서론을 모두 설명했으니 이번 포스팅부터는 학습한 내용 위주로 작성해 보려고 한다. 이번 포스팅에서는 강의 3장에서 다루는 데이터 전처리에서 핵심이라고 할 수있는 데이터를 합치는 방법에 대해 집중적으로 포스팅 하려고 한다.

JOIN , UNION

Join 연산자는 두 테이블 간의 관계를 나타내기 위해 사용하는 연산자로 간단하게 말하면 두 테이블을 결합하는 연산자 이다.

# 문법 
select * from table a join b on 조건 ~

기본적인 문법은 위와 같고, JOIN의 종류는 3가지가 있다.

만약 위 구조로 이루어진 ex3,ex4 두개의 테이블을 join한다고 생각해보자.

INNER JOIN

INNER JOIN 인 경우에는 join기준인 id를 기준으로 같은 값이 있는것들만 join되기 때문에 모두 조회해도 id가 1인 값만 나오게 된다.

LEFT(RIGHT) JOIN

LEFT JOIN 으로 했을시 왼쪽 테이블이 기준이 된다. ex3테이블이 기준이 되어 ex4를 join하게 되면 ex3에 있는 id 3가지(1,2,3) + 공통(1) 이므로 ex3 id 3개 row는 무조건 나오게 된다.

그런데 where 조건이 걸려 있으므로 ex4가 null값인 즉 ex4 id값이 비어있는 2,3에대해서만 결과가 나오게 된다. RIGHT JOIN 또한 기준만 바뀌는거지 JOIN되는 형식을 일치한다.

UNION

UNION은 두 테이블의 데이트를 세로로 쭉 나열하는 역할을 한다. 즉 아래방향으로 합친다는 느낌이다. UNION은 COLUMN의 수가 같아야 하며 , 중복은 제거된다 . 또한 다른 같은 수의 COLUMN을 가지는 테이블 두개를 합치지만 두 COLUMN명이 다르다면 결과는 앞 COLUMN으로 이름이 정해지고 as를 통해 이름을 바꿔 줄 수 도 있다.

UNION ALL

UNION ALL은 UNION과 다르게 COLUMN수가 동일하지않아도 합쳐진다. 또한 UNION ALL은 중복을 그대로 표시하는 특성이 있다.

OUTER JOIN

MYSQL 에서는 FULL OUTER JOIN을 한번에 하는 방법이 없다 . 따라서 LEFT JOIN과 RIGHT JOIN을 한 후 UNION을 수행해주면 된다.

문제

이제 적용할 수 있는 다양한 문제를 풀어보겠다.

문제) 'Leslie'이라는 이름을 가진 직원이 담당하는 모든 고객의 이름을 조회하세요.

select c.customername
from employees as e
join customers as c on e.employeeNumber = c.salesRepEmployeeNumber
where e.firstName like 'Leslie';

employee 테이블과 customers 테이블을 employeeNumber 기준으로 join시키도 이름이 Lesile테이블만 볼 수 있도록 조건을 주면 쉽게 해결된다.

문제) 주문 가격이상품의 구매 가격보다2.5배 높은 상품의 이름, 코드, 판매가격, 주문가격, 주문수량을 조회하세요.

SELECT p.productcode, p.productName, od.priceEach, p.buyPrice, od.quantityOrdered
FROM orderdetails od
JOIN products p ON od.productCode = p.productCode
WHERE od.priceEach > 2.5 * p.buyPrice;

문제) 각 직원별로 담당한 고객의 수를 조회하세요.

select CONCAT(e.firstName, ' ', e.lastName) as employname , count(c.customernumber) as NumberofCustomers
from employees as e
join customers as c on e.employeeNumber = c.salesRepEmployeeNumber
group by e.employeeNumber;

employname

문제) 각 고객별 총 주문 금액을 조회하세요.

select  c.customerName, SUM(od.priceEach * od.quantityOrdered) AS TotalOrderValue
from customers as c
join orders as o on c.customerNumber = o.customerNumber
left join orderdetails as od on o.ordernumber = od.ordernumber
group by c.customername;

우선 고객과 주문정보를 join시켜 주문한 고객테이블을 만들고 그 테이블에 주문 상세 내역을 join시킨다. 그러면 주문한 고객의 리스트와 그 상세 주문내역까지 보이게 된다. 거기서 customername 그룹별 가격(priceEach)*주문양(quantityOrdered)의 총합을 구하면 된다.

문제) 2004년에 가장 많이 판매된 상위5개 상품 이름과 해당 상품의 총 판매 수량을 조회하세요

select p.productName , sum(od.quantityOrdered) as TotalQuantity
from orders o 
join orderdetails od on o.ordernumber = od.ordernumber
join products p on p.productCode = od.productCode
WHERE YEAR(o.orderDate) = 2004
group by p.productName
order by TotalQuantity desc
LIMIT 5;

위 방법과 마찬가지로 총 3개의 테이블을 join시키고 porductname별로 주문 총합sum(quantityOrdered)을 구하고 필터조건 2004년을 적용시키면 된다. 그 후 주문총합을 내림차순으로 정렬시키고 , 5개를 뽑으면 상위 5개가 뽑히게된다.