SQLD 요약 3 SQL 활용

순수 관계 연산자

- SELECT(where)  , PROJECT (select )  ,JOIN, DIVIDE

 

CROSS JOIN (카타시안 곱 = Cartesian Product)

- 테이블간 조인 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다.

- NxM건의 데이터 조합이 발생한다.

 

UNION vs UNION ALL

UNION : 여러개 SQL 문의 결과에 대한 합집합으로 결과에서 중복된 모든 행은 하나의 행으로 만든다. (중복 제거)

+ 앞의 쿼리에 UNION ALL이 있더라도 나중에 UNION이 나오면 중복 제거 됨.

UNION ALL : 여러개 SQL문의 결과에 대한 합집합으로 결과에서 중복된 행은 그대로 중복되게 결과에 포함한다.

 

INTERSECT vs EXCETPT 

INTERSECT : SQL 결과에 대한 교집합, 중복된 행은 하나의 행으로 만든다. (중복 제거)

EXCEPT : SQL 결과에 대한 차집합, 중복된 행은 하나의 행으로 만든다 (중복 제거)

 

일반 집합 연산자를 SQL과 비교

UNION -- UNION

Intersection - INTERSECT

DIFFERENCE(차집합) -- EXCEPT(MINUS)

PRODUCT -- CROSS JOIN

 

계층형 질의

- 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용

- 계층형 데이터 예시 : 하위 사원 /상위 관리자

- START WITH 절 : 계층형 구조의 최상위 계층의 열을 식별하는 조건

- CONNECT BY 절

:계층형 구조가 어떤식으로 연결되어 있는지 표현

: 그 다음 자식 레벨 지정 (CONNECT BY 조건이 만족해야 다음에 붙음)

- PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정줌

   * PRIOR 자식 = 부모 ~> 부모 -> 자식 순으로 순방향 전개

   * PRIOR 부모 = 자식 ~> 자식 -> 부모 순으로 역방향 전개

   * 순방향 전개와 역방향 전개는 전개방향이 반대일 뿐 동작원리는 동일하다.

- ORDER BY SIBLING BY : 형제 노드 (동일 LEVEL)사이에서 정렬을 수행한다.

 

+ SQL SERVER에서의 계층형 질의문은 CTE(COMMON TABLE EXPRESSION)을 재귀호출 함으로써 계층구조를 전개한다.

+ SQL SERVER에서의 계층형 질의문은 앵커멤버를 실행해 기본 결과 집합을 만들고 이후 재귀멤버를 지속적으로 실행한다.

+ 오라클형 계층형 질의문에서 WHERE 절은 모든 전개를 진행한 이후 필터조건으로서 조건을 만족하는 데이터만을 추출하는데 활용된다.

+ 오라클형 계층형 질의문에서 PRIOR키워드는 CONNECT BY절에서도, SELECT ,WHERE절에서도 사용이 가능하다.

 

계층형 쿼리 동작 원리

1. START WITH절을 수행해 루트 노드 생성

2. CONNECT BY절의 결과가 없을 때까지 부모노드의 결과로 CONNECT BY절 반복 수행

 

분기(BRANCH) 제거

중간노드에 해당하는 행(ROW)이 제외되면, 그 하위노드들도 모두 결과에서 제외된다.

노드(NODE) 제거

- 조건을 만족하지 않는 특정노드만 결과에서 제외된다

- 전체 노드를 모두 전개한 후 WHERE절은 실행된다/

서브쿼리의 특징

- 서브쿼리는 단일 행 또는 복수행 비교 연산자와 함께 사용할 수 있다.

- 서브쿼리는 SELECT, FROM , WHERE, HAVING, ORDER BY, INSERT문의 VALUES, UPDATE문의 SET절에서 사용이 가능하다.

- 서브쿼리의 결과가 복수행 결과를 반환하는 경우에는 IN, ANY, ALL등의 복수형 비교 연산자와 사용해야 한다.

- 연관 서브쿼리(Correlated 서브쿼리) : 서브쿼리가 메인 쿼리 테이블의 칼럼을 포함하고 있는 형태, 메인 쿼리가 먼저 실행이 되어 읽은 데이터를 서브쿼리에서 조건이 맞는지 확인하려 할 때 사용

- 비연관 서브쿼리(Un-Correlated 서브쿼리) : 서브쿼리가 메인쿼리 테이블의 칼럼을 갖고 있지 않은 형태, 메인 쿼리에 값을 제공하기 위한 목적으로 주로 사용

- 다중 칼럼 서브쿼리는 서브쿼리의 결과로 여러 컬럼이 반환되어 메인 쿼리의 조건과 비교되는데, 현재 SQL SERVER에서는 지원하지 않는다.

 

단일행 비교 연산자 vs 다중행 비교 연산자

단일행 비교 연산자 : =, <, > , <=, >=

다중행 비교 연산자 : ANY, ALL, IN , SOME, EXISTS

* 단일행 서브쿼리는 단일행, 다중행 비교 연산자 모두 사용이가능하지만, 

   다중행 서브쿼리는 다중행 비교 연산자만 사용 가능

 

뷰

- 단지 정의만을 가지고 있으며, 실행 시점에 질의를 재작성해 수행한다.

- 실제로 데이터를 갖지 않음( But 실제 데이터를 저장하고 있는 뷰를 생성하는 DBMS도 있다.)

- 가상 테이블, VIEW, STORED QUERY 다 같은말

 

뷰 장점

- 독립성 : 테이블의 구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경되지 않아도 된다

- 편리성 : 복잡한 질의를 뷰를 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 SQL문을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다.

- 보안성 : 상위 관리자가 뷰를 생성할 때 조회할 수 있는 테이블과 칼럼을 지정할 수 있으므로 이용자가 접근 가능한 정보를 제한할 수 있다. (보안 강화를 하기 위한 목적으로 사용 가능하다.)

 

GROUP 함수 

- GROUP BY 절의 확장 기능을 제공한다

1. Grouping Sets (A,B..) 

= 괄호안에 명시된 집계칼럼들 별로 여러개의 집계 그룹을 생성

= 전체 합계 X

2. ROLLUP (A,B..)

= 괄호안의 집계컬럼들을 우에서 좌로 하나씩 줄여나가며, 점진적으로 상위 집계 그룹을 생성

3. CUBE(A,B..)

= 괄호 안의 집계컬럼들로 만들어지는 모든 경우의 수를 집계 기준으로 해 집계 그룹 생성

 

윈도우 함수(WINDOW FUNCTION)

= 분석 함수 (Analytic Function)

= 개별 행은 유지한 채 집계값을 계산할 수 있는 함수

+ 분석함수 실행 시 대상이 되는 행의 범위를 윈도우(window)라 하며, analytic_clause에 의해 각행 별로 윈도우가 정의된다.

+ Over의 유무에 따라 분석함수/ 집계함수로 구분할 수 있다.

 

분석함수의 활용 (누적집계)

- 파티션 내에서 ORDER BY절에 기술한 순서대로 현재행까지의 값을 누적집계로 구할 수 있다.

쿼리 파티션

- Partition BY 절을 사용해 GROUP BY 절과 유사하게 논리적인 행의 그룹을 생성할 수 있다.

 

WINDOW 절

- 각 행마다 분석함수의 실행 대상이 되는 행의 범위(윈도우)를 세밀하게 지정한다.

- 물리적인 행의 범위를 지정하거나 논리적인 값의 범위를 지정할 수 있다.

EX) ROW/RANGE BETWEEN start_points AND end_points

 

start & end points	ROWS	RANGE
UNBOUNED PRECEDING	파티션의 첫번재 행	첫번째 행의 값
N PRECEDING	현재행을 기준으로 이전 N번째 행	현재행의값 - N
CURRENT ROW	현재 행	현재행의 값
N FOLLOWING	현재행을 기준으로 이후 N 번째 행	현재행의 값 + N
UNBOUNDED FOLLOWING	파티션의 마지막 행	마지막 행의 값

 

RANK

1. RANK : 동일 값에 대해서는 동일 순위를 부여하고 중간 순위는 비워둠

2. DENSE_RANK : 동일 순위를 부여하되 중간 순위를 비워두지 않는다.

3. ROW_NUMBER : 동일 값에 대해서도 유일한 순위를 부여한다.

 

LAG vs LEAD

- LAG : 현재 읽은 데이터의 이전 행을 가져오는 것 (EX. LAG(컬럼명1) OVER(ORDER BY 컬럼2) )

- LEAD : 현재 읽은 행의 이후 행을 가져오는 것 (EX. LEAD(컬럼명1) OVER(ORDER BY 컬럼2))

* 이전행이 없는 경우 NULL입력됨.

 

DISTINCT(A||B)

: 문자열 A,B를 합친 것