SQLD SQL활용

1. 서브쿼리

서브쿼리란 하나의 쿼리안에 존재하는 또 다른 쿼리이다.
바깥에 있는 것이 메인쿼리 안에 있는 것이 서브쿼리이다.

서브쿼리의 위치는 다음 과 같이 나눌 수 있다.
SELECT절 - 스칼라 서브쿼리
FROM절 - 인라인뷰
WHERE절, HAVING절 - 중첩 서브쿼리

서브쿼리에 반드시 메인 쿼리의 컬럼이 포함될 필요는 없고 이런 것을 비연관 서브쿼리라고 한다.
ORDERBY절 INSER문의 VALUE절 등에도 사용할 수 있다.

1.1 스칼라 서브쿼리

주로 SELECT절에 위치하지만 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다.
컬럼 대신 사용되므로 반드시 하나의 값만을 반환해야하며 그렇지 않으면 에러가 발생한다.
컬럼 대신 사용한다!!

SELECT M.PROUDCT CODE,
(SELECT S.PRODUCT NAME
FROM PROUDCT S
WHERE S.PROUDCT_CODE = M.PRODUCT_CODE) AS PRODUCT_NAME,
M.MEMBER_ID
FROM PRODUCT_REVIEW M;

위 SQL을 보면 스칼라 서브쿼리를 이용해서 리뷰 테이블에 존재하지 않는 상품 이름데이터를 출력했다.
서브쿼리에서 조인을 통해 이름이라는 단하나의 값만 반환하고 있고 프로덕트네임이라는 컬럼명으로 출력했다.

1.2 인라인뷰

FORM절 등 테이블 명이 올 수 있는 위치에 사용가능하다.

SELECT M.PROUDCT CODE, S,PRODUCT_NAME, M.MEMBER_ID
FROM PRODUCT_REVIEW M,
(SELECT PROUDCT CODE, PRODUCT NAME
FROM PROUDCT S)
WHERE M.PRODCUT_CODE = S.PRODCUT_CODE;

위 SQL을 보면 상품 테이블에서 코드와 이름만 뽑은 테이블을 만들어 두 테이블을 조인하여 각각 출력하는 것을 볼 수 있다.

1.3 중첩 서브쿼리

WHERE절과 HAIVNG절에 사용할 수 있다. 중첩 서브쿼리는 메인 쿼리와의 관계에 따라 다음과 같이 나눌 수 있다.
비연관 서브쿼리 - 메인쿼리와 관계를 맺고 있지않음
연관 서브쿼리 - 메인쿼리와 관계를 맺고 있음

중첩 서브쿼리 반환하는 데이터 형태는 다음과 같이 나눌 수 있다.
1.단일행 서브쿼리 - 서브쿼리가 1건이하의 데이터를 반환, 단일 행 비교연산자와 함께 사용 예시) = < > 등
2.다중행서브쿼리 - 서브쿼리가 여러건의 데이터를 반환, 다중행 비교연산자와 함께 사용가능 예시) IN ALL ANY SOME EXISTS
3.다중컬럼 서브쿼리 - 서브쿼리가 여러컬럼의 데이터를 반환

2. 뷰

특정 SELECT문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트이다. SQL에서 테이블처럼 사용할 수 있으며 앞서 배운 인라인 뷰를 뷰로 정의한다고 가정해보면 쿼리 작성시 인랑니 뷰가 들어가는 위치에 뷰 이름만 기술하게 될 수 있다.
혼돈하지 말아야하는 점은 뷰는 가상 테이블이라는 것이다. 따라서 실제 데이터를 저장하지 않고 해당 제이터를 조회해오는 SELECT문만 가지고 있다.

뷰는 다음과 같은 특징을 가진다.
1.보안성 - 보안이 필요한 컬럼을 가진 테이블일 경우 해당 컬럼을 제외한 별도의 뷰를 생성하여 제공함으로써 보안을 유지할 수 있다.
2.독립성 - 테이블 스키마가 변경되었을 경우 어플리케이션은 변경하지 않고 관련 뷰만 수정한다.
3.편리성 - 복잡한 쿼리 구문을 뷰명으로 단축시킴으로써 가독성을 높이고 편리하게 사용할 수 있다.

위의 인라인 뷰에서 사용햇던 sql을 재사용해보자.

CREATE OR REPLACE VIEW PRODUCT_NAME_VIEW
AS
SELECT PROUDCT_CODE, PRODUCT_NAME FROM PROUDCT
위와 같이 뷰를 생성하면 이름만 사용하면 서브쿼리의 긴내용을 작성하지 않아도 된다.

SELECT M.PROUDCT CODE, S,PRODUCT_NAME, M.MEMBER_ID
FROM PRODUCT_REVIEW M,
PRODUCT_NAME_VIEW S
WHERE M.PRODCUT_CODE = S.PRODCUT_CODE;

3. 집합연산자(UNION)

집합연산자는 각 쿼리의 결과 집합을 가지고 연산하는 명령어이다.
조인이 컬럼을 합치는 것이었다면 유니온류는 레코드를 합치는 것이라고 이해하면된다.

3.1 UNION ALL / UNION

UNION ALL은 각 쿼리 결과 집합의 합집합이다. 중복된 행도 그대로 출력된다.
UNION은 각 쿼리 결과 집합의 합집합이다. 중복된 행은 한줄로 출력된다.

SELECT * FROM A
UNION
SELECT * FROM B

컬럼개수가 같아야하고 위와 같은 SQL을 사용하면 A테이블과 B테이블을 겹치고 중복 레코드를 제외하고 출력하게 된다.
UNION을 사용하면 데이터베이스 내부적으로 중복된 행을 제거하는 과정을 거치기때문에 성능상 불리하다는 점을 알아두자.

3.2 INTERSECT

INTERSECT는 각 쿼리 결과의 교집합이다. 중복된 행은 한줄로 출력한다.
쿼리1과 쿼리2의 결과에서 공통된 부분만 중복을 제거하여 출력한다.

SELECT * FROM A
UNION
SELECT * FROM B

위 SQL을 사용하게 되면 A테이블과 B테이블에서 겹치는 레코드들만 남게 된다.
헤더값은 첫번째 쿼리를 따라간다.

3.3 MINUS / EXCEPT

앞에있는 쿼리 결과집합에서 뒤에있는 쿼리의 결과 집합을 뺀 차집합이다. 중복된 행은 한줄로 출력된다.
쿼리1의 결과에서 쿼리2의 결과를 제거하고 출력한다.

SELECT * FROM A
UNION
SELECT * FROM B

위 SQL을 사용하면 A테이블에서 B테이블과 겹치는 내용을 제외하고 출력되게 된다.

4. 그룹함수

데이터를 GROUP BY하여 나타낼 수 있는 데이터를 구하는 함수이다. 역할에 따란 구분하면 집계함수와 소계(총계)함수로 나눌 수 있다.
집계함수 - COUNT SUM AVG MAX MIN 등
소계함수 - ROLLUP CUBE GROUPING SETS등
집계함수는 이미 알아보았고 소계함수에 대해 알아보자

4.1 ROLLUP

소그룹간의 소계 및 총계를 계산하는 함수이다.

1.ROLLUP(A) - A로 그루핑 총합계
2.ROLLUP(A,B) - A와 B로 그루핑/ A로 그루핑/ 총합계
3.ROLLUP(A,B,C) - A와 B와 C로 그루핑/ A와 B로 그루핑/ A로 그루핑/ 총합계

책185P부터 예시를 봐야 이해가능하다.
총합계는 모두 출력되고 모든인자 하나의 인자뺀것 순서대로 출력된다.
중간에(A,B), C를 하게되면 A,B 를 하나의 A로 보면된다.

4.2 CUBE

소그룹간의 소계 및 총계를 다차원적으로 계산하는 함수이다.
GROUP BY가 일방향으로 그루핑을하며 소계를 구했다면 CUBE는 조합할 수 있는 모든 그룹에 대한 소계를 집계한다.

1.CUBE(A) A로 그루핑 / 총합계
2.CUBE(A,B) A,B로 그루핑 / A로 그루핑 / B로 그루핑 / 총합계
3.CUBE(A,B,C) A,B,C로 그루핑 / A,B로 그루핑 / A,C로 그루핑 / B,C로 그루핑 / A로그루핑/ B로그루핑 / C로 그루핑 / 총합계

ROLLUP에서 더 다양하게 총계를 낼 수 있는 느낌이다.

4.3 GROUPING SETS

특정 항목에 대한 소계를 계산하는 함수이다.
인자값으로 ROLLUP이나 CUBE를 사용할 수도 있다.

1.GROUPING SETS (A,B) - A로 그루핑, B로 그루핑
2.GROUPING SETS ((A,B),()) - A로 그루핑/ B로 그루핑 / 총합계
3.GROUPING SETS(A, ROLLUP(B)) - A로 그루핑 / B로 그루핑 / 총합계
4.GROUPING SETS(A, ROLLUP(B,C)) - A로 그루핑 / B,C로 그루핑 / B로 그루핑/총합계

4.4 GROUPING

GROUPING함수는 ROLLUP, CUBE, GROUPING SETS등과 함께 쓰이며 소계를 나타내는 ROW를 구분할 수 있게 해준다.
앞서 보여준 예제는 소계를 나타내는 ROW에서 그루핑의 기준이 되는 커럶을 제외하고는 모두 NULL값으로 표현되었지만
GROUPING함수는 원하는 위치에 원하는 텍스트를 출력할 수 있다.

SELECT절에서 사용하며 컬럼명을 지정하고 CASE WHEN THEN을 사용해서 값을 변환할 수도 있다.

SELECT CASE GROUPING(ORDER_DT)
WHEN 1 THEN 'TOTAL' ELSE ORDER_DT
END AS ORDER_DT
COUNT(*)
FROM STARBUCKS_ORDER
GROUPING BY ROLLUP(ORDER_DT)