집합 연산자
UNION
합집합 (중복 행은 1개로 처리), 자동으로 정렬
- 컬럼 수와 데이터 타입이 완전 일치해야함
UNION ALL
합집합 (중복 행도 표시)
- UNION ALL이 UNION보다 성능 우수
- 정렬 자동적으로 발생하지 않는다.
- 컬럼 수와 데이터 타입이 완전 일치해야 한다.
INTERSECT
- 교집합 (INTERSCTION)
EXCEPT(MY-SQL) / MINUS(ORACLE)
- 차집합 (DIFFERENCE)
CROSS JOIN
- 곱집합(PRODUCT)
계층형 질의
STATRT WITH
- 계층 구조 전개의 시작 위치 지정
CONNECT BY
- 다음에 전개될 자식 데이터 지정
- 부모 계층형 쿼리에서 부모노드와 자식 노드 사이의 특정한 관계를 나타내는데 사용
PRIOR
- CONNNECT BY 절에 사용되며, 현재 읽은 컬럼을 지정한다.
- PRIOR 자식 = 부모 형태 :
계층구조에서 부모 데이터에서 자식 데이터(부모 -> 자식) 방향으로 전개하는 순방향 전개,
하위의 모든 노드 추출
PRIOR 부모 = 자식 형태 :
(자식 -> 부모) 방향, 역방향 전개로, 상위의 모든 노드 추출
NOCYCLE
- 동일한 데이터가 전개되지 않는다. 순환구조 발생 지점까지만 전개
ORDER SIBLINGS BY
- 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다. (필터링)
LEVEL
- 루트 데이터이면 1, 그 하위 데이터면2, 리프 데이터까지 1씩 증가
CONNECT_BY_ISLEAF
- 해당 데이터가 리프 데이터면1, 그렇지 않으면 0
CONNECT_BY_ISCYCLE
- 해당 데이터가 조상이면1, 아니면 0 (CYCLE 옵션을 사용했을 시에만 사용가능)
SYS_CONNECT_BY_PATH
- 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시
CONNECT_BY_ROOT
- 현재 전개할 데이터의 루트 데이터(최상위)를 표시. 단항 연산자.
START WITH절에서 필터링된 시작 데이터는 결과목록에 포함!!
이후 데이터는 CONNECT BY절에 의해 필터링
[SQL SERVER] 계층형 질의문은
- CRE(common table expression)를 재귀 호출함으로싸 계층 구조 전개
- 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행
[Oracle] 계층형 질의문은
- WHERE절은 모든 전개를 진행한 이후 필터 조건. 조건을 만족하는 데이터만 추출하는데 활용.
- PRIOR 키워드는 SELEC,WHERE 절에서도 사용 가능하다.
SELECT (스칼라 서브쿼리)
FROM (인라인뷰)
WHERE (서브쿼리)
서브쿼리 :
하나의 SQL문 안에 포함되어 있는 또 다른 SQL문, 알려지지 않은 기준을 이용한 검색에 사용
- 서브쿼리를 괄호로 감싸서 사용
- 서브쿼리는 단일 행 또는 복수행 비교 연산자와 함게 사용가능
- 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고
복수 행 비교 연산자는 결과 건수와 상관 없다.
- 서브쿼리에서는 ORDER BY를 사용하지 못한다.
- SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용가능 WHERE 절에 있는 서브쿼리
TOP-N 서브쿼리
-
INLINE VIEW 의 정렬된 데이터를 ROWNUM을 이용해 결과 행수를 제한 하거나 TOP(N)조건을 사용한다.
-
SELECT *
FROM (SELECT ROWNUM TOP, A FROM B) a
WHERE a.TOP =2 ;
단일 행 비교 연산자
- =, <, >, <=, >= 등, 항상 1건 이하인 서브쿼리
다중 행 비교 연산자
- IN,ALL, ANY, SOME 등 하나 이상의 데이터가 RETURN
- ALL : 서브쿼리 값 모두가 조건에 만족하면 TRUE
(WHERE 컬럼명 < ALL(A,B) : A보다 작으면서, B보다도 작아야함)
-ANY: 서브쿼리 값 중 어느 하나의 값이라도 만족하면 결과 값을 리턴
다중 컬럼 서브쿼리
- 여러 컬럼을 반환
- 메일쿼리의 조건절에 여러 컬럼을 동시 비교 가능
- 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼 개수와 컬럼 위치가 동일해야한다.
- SQL SERVER에서는 현재 지원이 되지 않는다.
상호 연관 서브쿼리 (Correlated SubQuery)
- 메인 쿼리 값을 서브쿼리가 사용하고 서브쿼리의 값을 받아서 메인 쿼리가 계산되는 쿼리
- 서브쿼리가 메인쿼리의 행 수 만큼 실행되는 쿼리
- 실행 속도가 상대적으로 떨어짐
- 복잡한 일반 배치 프로그램을 대체할 수 있어 조건에 맞는다면 적극적 검토 필요
스칼라 서브쿼리
- 한 행, 한 컬럼만을 반환하는 서브쿼리
- 메인쿼리의 컬럼 사용 가능. 메인쿼리는 스칼라 서브쿼리의 컬럼 사용 불가능
- ORDER BY구 사용할 수 없다.
SQL 개선 측면 서브쿼리 종류
- Access Subquery : 쿼리의 변형불가, 제공자 역할
- Filter Subquery : 쿼리의 변형불가, 확인자 역할
- Early Subquery : 쿼리의 변형불가, 서브쿼리가 먼저 실행하여 데이터를 걸러낸다.
인라인 뷰 (INLINE VIEW)
- 테이블 명이 올 수 있는 FROM절에서 정의
- ORDER BY 사용 가능
- SQL 문장 내 절차성을 주는 효과를 볼 수 있다.
- 실질적인 오브젝트는 아니지만, SQL 문장에서 마치 VIEW나 테이블처럼 사용
- 메인쿼리에서 컬럼 사용 가능
뷰
- 테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다. 가상테이블이라고도 한다.
- 단지 정의만을 가지고 있으며, 실행 시점에 질의를 재작성하여 수행한다.
[뷰 사용 장점]
- 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그림은 변경하지 않아도 된다.
- 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
- 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재할 때 사용
CREATE VIEW 뷰명 AS
DROP VIRE 뷰명;
그룹함수
ROLLUP
Subtotal을 생성하기 위해 사용, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다. 인수 순서에 주의
- ROLLUP(A,B) : A별 합계, A/B별 합계, 전체 합계
- ROLLUP(COL1,COL2) = GROUPING SETS((COL1,COL2),(COL1),())
GROUPING
-Subtotal의 total을 생성
CUBE
- 결합 가능한 모든 값에 대하여 다차원 집계를 생성, ROLLUP에 비해 시스템에 부하가 심함
- GROUPING SETS(A,B(A,B),())=CUBE(A,B)
GROUPING SETS
- 인수들에 대한 개별 집계를 구할 수 있다, 다양한 소계 집합 생성 가능
