[MYSQL] SQL 문법(JOIN,CASE WHEN,RANK,SUBQUERY)

데이터는 한 가지 테이블에 적재되지 않는다. 고객 테이블, 매출 테이블, 상품 테이블과 같이 여러가지 테이블로 분리되어 관리된다. 한 가지 테이블에 모든 정보를 다 담기도 힘들고, 관리 측면에서도 좋은 방법은 아닐 것이다.

 

1. JOIN

SQL JOIN

 

1)LEFT JOIN(LEFT OUTER JOIN)

SELECT *
FROM order
LEFT
JOIN customers
ON order.고객 ID = CUSTOMER.고객 ID

 

ex) classicmodels.customers, classicmodels.orders 테이블을 결합하고 ORDERNUMBER 와 COUNTRY를 출력하세요.

SELECT A.ORDERNUMBER,
B.COUNTRY
FROM CLASSICMODELS.CUSTOMERS A
LEFT
JOIN
CLASSICMODELS.CUSTOMERS B
ON A.CUSTOMERNUMBER = B.CUSTOMERNUMBER

 

ex) classicmodels.customers, classicmodels.orders 테이블을 이용해 USA 거주자의 주문 번호(OrderNumber), 국가(Country)를 출력하세요.

SELECT A.ORDERNUMBER,
B.COUNTRY
FROM CLASSICMODELS.ORDERS A
LEFT
JOIN CLASSICMODELS.CUSTOMERS B
ON A.CUSTOMERNUMBER = B.CUSTOMERNUMBER
WHERE B.COUNTRY = 'USA'

 

2)INNER JOIN

 

ex) classicmoderls.customer, classicmodels.orders 테이블을 이용해 USA 거주자의 주문 번호(CustomerNumber), 국가(Counry)를 출력하세요.

SELECT A.ORDERNUMBER,
B.COUNTRY
FROM CLASSICMODELS.ORDERS A
INNER
JOIN CLASSICMODELS.CUSTOMERS B
ON A.CUSTOMERNUMBER = B.CUSTOMERNUMBER
WHERE B.COUNTRY = 'USA'

 

3) FULL JOIN

SELECT *
FROM TABLE_A
FULL JOIN TABLE_B
ON TABLE_A.Column 1 =  TABLE_B.Column 2

 

2. CASE WHEN

CASE WHEN 구문은 조건에 따른 값을 다르게 출력하고 싶은 경우 사용된다.

SELECT CASE WHEN 조건 1 THEN 결과 1
WHEN 조건 2 THEN 결과 2 ELSE 결과 3 END
FROM 데이터베이스.테이블 명;

 

조건 1을 만족하는 경우 결과 1을 출력하게 되고, 조건 2를 만족하는 경우 결과 2를 출력하고, 조건 1, 조건 2를 모두 만족하지 않는 경우 결과3을 출력하게 된다.

 

Classicmodels.customers의 country 칼럼을 이용해 북미(Canada, USA), 비북미를 출력하는 칼럼을 생성하세요.

 

ex) Classicmodels.customers의 country 칼럼을 이용해 북미(Canada, USA), 비북미를 출력하는 칼럼을 생성하세요.

SELECT COUNTRY,
CASE WHEN COUNTRY IN('USA','Canada') then 'North America' ELSE
'OTHERS' END AS region
FROM CLASSICMODELS.CUSTOMERS

 

ex) Classicmodels.customers의 country칼럼을 이용해 북미(Canada, USA), 비북미를 출력하는 칼럼을 생성하고, 북미, 비북미 거주 고객의 수를 계산하세요.

SELECT CASE WHEN COUNTRY IN ('USA','Canada') then 'NORTH AMERICA'
ELSE 'OTHERS' END AS region
FROM CLASSICMODELS.CUSTOMERS
GROUP
BY CASE THEN COUNTRY IN ('USA','Canada') then 'NORTH AMERICA'
ELSE 'OTHERS' END

SELECT CASE WHEN COUNTRY IN ('USA', 'Canada') then 'NORTH AMERICA'
ELSE 'OTHERS' END AS region,
COUNT(CUSTOMERNUMBER) N_CUSTOMERS
FROM CLASSICMODELS.CUSTOMERS
GROUP
BY 1

 

두 개의 쿼리는 동일한 결과를 출력한다. GROUP BY에서 칼럼 명을 숫자로 대체 가능하다.

1은 SELECT의 첫 번째 칼럼을 의미하고, 2는 SELECT의 두 번째 칼럼을 의미한다. GROUP BY 1은 SELECT의 첫 번째 칼럼으로 그룹핑 하겠다는 의미다.

 

3. RANK, DENSE_RANK, ROW_NUMBER

DENSE_RANK, RANK는 동점인 경우 같은 등수로 계산한다. 두 함수의 차이는 그 다음 등수를 어떤 값으로 매기는지에 따라 다르다.

DENSE_RANK는 동점의 등수 바로 다음 수로 순위를 매기고, RANK는 동점인 경우의 데이터 세트를 고려해 다음 등수를 매긴다.

SELECT ROW_NUMBER() OVER(ORDER BY column) FROM ...
SELECT RANK() OVER(ORDER BY column) FROM ...
SELECT DENSE_RANK() OVER(ORDER BY column) FROM ...

 

다음과 같이 고객번호, 구매 금액 테이블이 존재할 때 구매 금액으로 순위를 매기고 싶다면 다음과 같이 순위를 매길 수 있다.

 

SELECT *,
ROW_NUMBER() OVER(ORDER BY 구매 금액) ROWNUMBER,
RANK() OVER(ORDER BY 구매 금액) RANK,
DENSE_RANK() OVER(ORDER BY 구매 금액) DENSE_RANK
FROM TABLE;

 

ORDER BY의 기본값은 오름차순으로 순위를 매기게 된다. 내림차순으로 순위를 매기려면, ORDER BY 구매 금액 DESC와 같은 형태로 가능하다.

 

만약 고객 번호 내에서 순위를 메기고 싶다면, 다음과 같은 방법으로 가능하다.

SELECT *,
ROW_NUMBER() OVER(PARTITION BY 고객 번호 ORDER BY 구매 금액)
ROWNUMBER,
RANK() OVER(PARTITION BY 고객 번호 ORDER BY 구매 금액) RANK,
DENSE_RANK() OVER(PARTITION BY 고객 번호 ORDER BY 구매 금액)
DENSE_RANK
FROM TABLE;

 

ex) classicmodels.products 테이블의 productline별로 순위를 매겨 주세요. 

(buyprice칼럼 기준, 오름차순) (row_number, rank, dense rank 사용)

 

SELECT BUYPRICE,
ROW_NUMBER() OVER(PARTITION BY PRODUCTLINE ORDER BY BUYPRICE)
ROWNUMBER,
RANK() OVER(PARTITION BY PRODUCTLINE ORDER BY BUYPRICE) RNK,
DENSE_RANK() OVER( PARTITION BY PRODUCTLINE ORDER BY BUYPRICE)
DENSERANK
FROM CLASSICMODELS.PRODUCTS;

 

4. SUBQUERY

WHERE 절의 IN 연산자 내부에 NYC 거주 고객의 고객 번호를 출력하는 쿼리를 입력하면, 해당 고객 번호의 주문 번호를 모두 출력할 수 있다.

select ordernumber
from classicmodels.orders
where customerNumber in (select customernumber
from classicmodels.customers
where city = 'NYC')
;

 

from join 에 subquery를 사용하는 경우

select customernumber
from (select customernumber
from classicmodels.customers
where city = 'NYC') A

 

ex) calssicmodels.custommers와 classicmodels.orders를 이용해 USA 거주자의 주문 번호를 출력하세요.

SELECT ORDERNUMBER
FROM CLASSICMODELS.ORDERS
WHERE CUSTOMERNUMBER IN (SELECT CUSTOMERNUMBER
FROM CLASSICMODELS.CUSTOMERS
WHERE COUNTRY = 'USA');