[엑셀보다 쉽고 빠른 SQL] 4주차 핵심정리

 

1. Subquery를 활용한 복잡한 연산

 

1.1 Subquery

다른 쿼리 내부에 포함되어 있는 SELETE 문을 의미한다.

서브쿼리를 포함하고 있는 쿼리를 외부쿼리(outer query)라고 부르며, 서브쿼리는 내부쿼리(inner query)라고도 부른다.

• 여러 번의 연산을 수행해야 할 때
• 조건문에 연산 결과를 사용해야 할 때
• 조건에 Query 결과를 사용하고 싶을 때

    → Query 결과를 Query에 다시 활용하는 것

 

 

1.2 기본 형식

: 서브쿼리는 반드시 괄호()로 감싸져 있어야 한다.

: SELECT 문의 FROM 절에서 사용되는 모든 테이블에는 이름이 필요하므로 FROM 절에서 사용되는 서브쿼리는 반드시 이름을 정의해야 한다.

 

-- 예시 구조1
SELECT column1, special_column
FROM
( 
SELECT column1, column2 AS special_column
FROM table1
) a

-- 예시 구조 2
SELECT column1, column2
FROM table1
WHERE column1 = (SELECT col1 FROM table2)

 

 

1.3 사용 예시

/*
EX)  음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
(수수료 구간 -  ~5000원 미만 0.05%, ~20000원 미만 1%, ~30000원 미만 2%, 30000원 초과 3%)
테이블: food_orders, 음식점이름: restaurant_name, 가격: price, 주문수량: quantity
*/

SELECT restaurant_name, 
	   price_per_plate*ratio_of_add "수수료"
FROM 
(
	SELECT restaurant_name,
		   CASE WHEN price_per_plate<5000 THEN 0.005
		 		WHEN price_per_plate between 5000 AND 19999 THEN 0.01
		 		WHEN price_per_plate between 20000 AND 29999 THEN 0.02
		 		ELSE 0.03
		   END ratio_of_add,
		   price_per_plate
	FROM 
	(
		SELECT restaurant_name, avg(price/quantity) price_per_plate
		FROM food_orders
		GROUP BY 1
	) a
) b

 

 

---

 

2. 여러 테이블의 데이터 한 번에 조회 (JOIN)

 

2.1 JOIN

두 개 이상의 테이블을 결합하여 사용하는 것. 여러 테이블의 데이터를 한 번에 조회할 수 있다.

 

• 기본 원리

주문 정보에서 고객의 이메일을 알기 위해서는 고객 정보에서 동일한 고객 ID의 이메일을 가져와야 한다.

각 주문 정보와 고객 정보가 테이블이라고 할 떄, 두 테이블의 공통컬럼은 고객 ID이다.

공통 컬럼을 기준으로 두 테이블을 합쳐 각 테이블에서 필요한 데이터를 조회할 수 있도록 하는 것이 JOIN이다.

 

 

2.2 JOIN 종류

테이블 조합 방식에 따라 3가지로 나뉜다.

• LEFT JOIN / RIGHT JOIN
  : 공통 컬럼을 기준으로, 하나의테이블에 값이 없더라도 모두 조회한다.
    이때 기준이 되는 테이블이 왼쪽에 있으면 LEFT JOIN, 오른쪽에 있으면 RIGHT JOIN이 된다.
    공통 컬럼 값이 일치하지 않는 경우는 NULL로 채운다.

LEFT JOIN 예시

• INNER JOIN
  : 공통 컬럼을 기준으로, 두 테이블 모두에 있는 값만 조회한다.
    즉, LEFT JOIN 예시에서 고객ID 5번의 경우, 고객 정보 테이블에 정보가 없으므로 INNER JOIN에서 제외된다.

INNER JOIN 예시

 

 

2.3 기본 형식

• 각 테이블 별로 별명을 지정 하여 컬럼명 작성 시 편리하게 사용할 수 있다.
• 공통컬럼은 묶어주기 위한 ‘공통 값’ 이기 때문에 두 테이블의 컬럼명은 달라도 괜찮다.
  예를 들어 주문정보에는 ‘고객ID’, 고객정보에는 ‘고객아이디’ 라고 컬럼명이 되어있다면, 테이블1.고객ID=테이블2.고객아이디 와 같이 묶어줄 수 있다.

-- LEFT JOIN
SELECT 조회 할 컬럼
FROM 테이블1 a LEFT JOIN 테이블2 b ON a.공통컬럼명=b.공통컬럼명

-- RIGHT JOIN
SELECT 조회 할 컬럼
FROM 테이블1 a RIGHT JOIN 테이블2 b ON a.공통컬럼명=b.공통컬럼명

-- INNER JOIN
SELECT 조회 할 컬럼
FROM 테이블1 a INNER JOIN 테이블2 b ON a.공통컬럼명=b.공통컬럼명

 

 

2.4 사용 예시

/*
EX) 고객의 주문 식당 조회하기 (이때 고객명으로 정렬, 중복 없도록 조회한다)
테이블: customers, 고객ID: customer_id, 고객이름: name, 연령: age, 성별: gender
테이블: food_orders, 고객ID: customer_id, 주문 식당: restaurant_name
*/

SELECT DISTINCT a.name,
	   a.age,
       a.gender,
       b.restaurant_name
FROM customers a INNER JOIN food_orders b ON a.customer_id=b.customer_id