💡 서브쿼리 (Subquery)
서브쿼리 (Subquery)란 ❓
하나의 SQL문안에 포함되어있는 또다른 SQL문을 말한다.
▪ 주의 사항
-
서브 쿼리는 메인 쿼리의 칼럼을 모두 사용할 수 있지만, 메인 쿼리는 서브 쿼리의 칼럼을 사용할 수 없다.
-
서브쿼리를 괄호로 감싸서 사용한다. 단일행(Single Row) 또는 복수행(Multiple Row) 비교연산자와 함께 사용가능하다. ORDER BY를 사용하지 못한다.
-
단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고, 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.
◽ 서브쿼리가 SQL문에서 사용이 가능한 곳
-
SELECT 절
-
FROM 절
-
WHERE 절
-
HAVING 절
-
ORDER BY 절
-
INSERT문의 VALUES 절
-
UPDATE문의 SET 절
◽ 서브쿼리 분류
▪ 단일행 서브 쿼리
- 서브쿼리가 단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용할 때는 서브쿼리의 결과 건수가 반드시 1건 이하 이어야 한다.
- 서브쿼리의 결과 건수가 2건 이상을 반환하면 SQL문은 실행시간(Run Time) 오류 가 발생한다.
정남일과 같은 팀코드를 가진 선수명, 포지션, 백넘버를 출력하시오. (서브쿼리 사용)
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER
WHERE TEAM_ID = (
SELECT TEAM_ID
FROM PLAYER
WHERE PLAYER_NAME = '정남일')
ORDER BY PLAYER_NAME;🟡 결과
브쿼리가 먼저 실행되어서 '정남일'이 소속되어 있는 팀코드를 반환하고 이 반환값과 같은 팀코드를 가진 선수들의 정보를 출력한다.
(만약 정남일 선수 중에 동명이인이 있을 경우 2건이 되어 오류가 날 수 있다)
◽ 다중행 서브쿼리
서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용해야 한다.그렇지 않으면 SQL문은 오류를 반환한다.
다중 행 비교 연산자는 다음과 같다.
- SQL>>
SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 FROM TEAM
WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정현수')
ORDER BY TEAM_NAME; 선수들 중에서 ‘정현수’라는 선수가 소속되어 있는 팀 정보를 출력
◽ 동작 방식에 따른 분류
| 종류 | 설명 |
|---|---|
| Un-Correlated(비연관) 서브 쿼리 | - 서브쿼리가 메인쿼리 칼럼을 갖고 있지 않은 형태 - 메인쿼리에 값을 제공하기 위한 목적으로 주로 사용한다. |
| Correlated(연관) 서브쿼리 | - 서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태 - 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용한다. |
▪ 비연관 서브쿼리
- Access Subquery: 제공자 역할
- Filter Subquery: 확인자 역할
- Early Filter Subquery: 데이터 필터링 역할
▪ 스칼라 서브쿼리
값 하나를 반환하는 서브쿼리, SELECT절에 사용하는 서브쿼리 즉 한 행, 한 칼럼만을 반환하는 서브쿼리
▪ 뷰 | View
이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다. 가상 테이블
CREATE VIEW V_PLAYER_TEAM AS
DROP VIEW V_PLAYER_TEAM;
| 장점 | 설명 |
|---|---|
| 독립성 | 테이블 구조가 변경되어도 뷰를 사용하는 응용 플그램은 변경하지 않아도 된다. 즉 테이블 구조 변경 자동 반영한다. |
| 편리성 | 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성 가능하다. 즉 자주 사용하는 SQL문의 형태를 뷰로 생성하여 사용할 수 있다. |
| 보안성 | 직원의 급여정보와 같이 숨기고 싶은 정보가 존재할 때 사용한다. 즉 뷰를 생성할 때 칼럼을 제외할 수 있다. |
▪ 인라인 뷰 (Inline View)
- FROM 절에서 사용한다.
- SQL 문이 실행될 때만 임시로 생성되는 동적인 뷰이기 때문에 Dynamic View 라고도 한다.
- 인라인뷰에서는 ORDER BY절을 사용할 수 있다. 인라인뷰에 먼저 정렬을 수행하고 정렬된 결과 중에서 일부데이터를 추출하는 것을 TOP-N 쿼리라고 한다
💡 그룹 함수(GROUP FUNCTION)
◽ 데이터분석 개요
- ANSI/ISO SQL 표준은 데이터 분석을 위해서 다음 세 가지 함수를 정의하고 있다.
- 집계함수(AGGREGATE FUNCTION), 그룹 함수(GROUP FUNCTION), 윈도우 함수(WINDOW FUNCTION)
▪ 집계함수(AGGREGATE FUNCTION)
여러 행으로부터 하나의 결괏값을 반환하는 함수이다. SELECT 구문에서만 사용되며, 이전에 다룬 기본 함수들이 행(row)끼리 연산을 수행했다면, 집계 함수는 열(column)끼리 연산을 수행 한다.
GROUP AGGREGATE FUNCTION 이라고도 부르며, GROUP FUNCTION의 한 부분으로 분류할 수 있다.
COUNT, SUN, AVG, MAX 와 같은 함수들이 집계함수에 속한다.
▪ 그룹 함수(GROUP FUNCTION)
테이블 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수이며,
소계 및 총계 등을 구하기 위해서 그룹 함수를 사용한다면, 단일 DML만으로도 원하는 작업을 할 수 있다.
합계 계산 함수, Null을 빼고 집계, 결과값 없는 행 출력하지 않는다.
| ROLLUP | GROUPING | GROUPING SETS | CUBE |
| - GROUP BY절과 함께 사용한다. - 집합의 소계/총계 계산한다. - 칼럼 순서에 따라 결과 다르다. |
- 소계/총계 계산 식별한다. - 합계 계산되면 1 - 합계가 아니면 0 |
- GROUP BY절과 함께 사용한다. - 칼럼 순서와 관계없다. - 칼럼 순서 달라도 결과는 같다. |
- GROUP BY절과 함께 사용한다. - 다차원 집계 - 가능한 모든 결합 계산한다. |
-
ROLLUP 함수
-
GROUP BY절과 함께 사용 → 그룹화된 집합에 대해 더 상세한 정보를 생성한다.
-
기존에 SELECT된 데이터의 소계, 총계를 구할 수 있다.
-
ROLLUP에 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용되어지며,
Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다.
-
계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀜. 가능한 Subtotal만 생성
-
GROUP BY의 확장된 형태이다.
-
SQL >>
-
SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs'
ELSE JOB END AS JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY CUBE (DNAME, JOB) ;부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장을 수행
-
GROUPING SETS 함수| 칼럼 순서와 관계없이, 다양한 소계(Subtotal) 생성
- 원하는 부분의 소계 만 손쉽게 추출할 수 있다.
- 칼럼간 서로 관계가 없으므로, 순서가 바뀌어도 결과는 같다.
CUBE 함수 | 다차원 집계/ 가능한 모든 집계 계산
다차원 집계: 제시한 칼럼에 대해서, 조합할 수 있는 모든 경우의 수 / 결합 가능한 모든 집계를 계산한다.
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB);전체합계, 직업별합계, 부서별합계, 부서별-직업별합계 모두 계산 및 조회
✔ 윈도우 함수(WINDOW FUNCTION) 는 다음 포스팅에서!