(SQL) JOIN, LEFT JOIN, RIGHT JOIN 정리 및 연습문제

데이터 구조 예시

1) JOIN : 한 번에 둘 이상의 테이블에서 데이터를 가져올 수 있도록 함

• JOIN 문을 추가하면 ON 문도 추가됨

SELECT orders.*, accounts.*
#테이블 이름은 항상 마침표 앞/ 열은 항상 마침표 뒤
FROM orders
JOIN accounts
ON orders.account_id = accounts_id;

SELECT orders.standard_qty, orders.gloss_qty, orders.poster_qty, accounts.website, accounts.primary_poc
FROM orders
JOIN accounts
ON orders.account_id = accounts.id;

SELECT *
FROM web_events
JOIN accounts
ON web_events.account_id = accounts.id
JOIN orders
ON accounts.id = orders.account_id

•  
• Alias 사용하기

SELECT o.*, a.*
FROM orders o
JOIN accounts a
ON o.account_id = a.id;

SELECT r.name region, s.name rep, a.name account #열이름 같으면 하나만 보여줌 ⇒ alias 에 alias 또 붙여주자
FROM sales_reps s
JOIN region r
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
ORDER BY a.name;

•  

2) Left (Outer) Join / Right (Outer) Join / Full Outer Join(잘 안씀)

• Left Join

(형식) SELECT
FROM left table
LEFT JOIN right table

SELECT a.id, a.name, o.total
FROM orders o
LEFT JOIN accounts a
ON o.account_id = a.id

• Right Join (Left Join 순서 바꾼 것과 결과 동일, 잘 안씀)

SELECT a.id, a.name, o.total
FROM orders o
RIGHT JOIN accounts a
ON o.account_id = a.id

•  
• 규칙 : ON 절의 모든 항목과 JOIN 을 먼저 실행한 후 그 결과 세트는 WHERE 절을 사용하여 필터링 됨

SELECT orders.*, accounts.*
FROM orders
LEFT JOIN accounts
ON orders.account_id = accounts.id
WHERE accounts.sales_rep_id = 321500

⇒ but, id가 321500인 영업 사원이 계약한 모든 주문을 남겨두면서 다른 모든 주문들도 결과에 포함시키는 방법?

SELECT orders.*, accounts.*
FROM orders
LEFT JOIN accounts
ON orders.account_id = accounts.id
AND accounts.sales_rep_id = 321500

 

⇒ JOIN이 실행되기 전에 오른쪽 테이블을 미리 필터링하여 sales_rep_id가 321500인 행만 포함되게 한 후 LEFT JOIN

 

3) 연습문제

• 모든 order에 대한 각 지역의 name과 주문에 대해 지불한 계정 name 및 unit price(total_amt_usd/total)를 제공하십시오. 단, standard order quantity가 '100'을 초과하는 경우에만 결과를 제공해야 합니다. 최종 테이블에는 region name, account name 및 unit price의 3개 열이 있어야 합니다.

SELECT r.name region, a.name account, o.total_amt_usd/(o.total + 0.01) unit_price
FROM region r
JOIN sales_reps s
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
WHERE o.standard_qty > 100;

• 모든 order에 대한 각 지역의 name과 주문에 대해 지불한 계정 name 및 unit price(total_amt_usd/total)를 제공하십시오. 단, standard order quantity이 '100'을 초과하고 poster order quantity이 '50'을 초과하는 경우에만 결과를 제공해야 합니다. 최종 테이블에는 region name, account name 및 unit price의 3개 열이 있어야 합니다. 가장 큰 unit price를 먼저 정렬하십시오.

ELECT r.name region, a.name account, o.total_amt_usd/(o.total + 0.01) unit_price
FROM region r
JOIN sales_reps s
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
WHERE o.standard_qty > 100 AND o.poster_qty > 50
ORDER BY unit_price DESC;

• account id 1001이 사용하는 다른 channel은 무엇입니까? 최종 테이블에는 account name과 다른 channel의 2개 열만 있어야 합니다. SELECT DISTINCT를 시도하여 고유 값으로만 결과를 좁힐 수 있습니다.

SELECT DISTINCT a.name, w.channel
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
WHERE a.id = '1001';

• '2015년'에 발생한 모든 주문을 찾으십시오. 최종 테이블에는 occurred_at, account name, order total 및 order total_amt_usd의 4개 열이 있어야 합니다.

SELECT o.occurred_at, a.name, o.total, o.total_amt_usd
FROM accounts a
JOIN orders o
ON o.account_id = a.id
WHERE o.occurred_at BETWEEN '01-01-2015' AND '01-01-2016'
ORDER BY o.occurred_at DESC;