[SQLD] 4.SQL 활용

UNION / UNION ALL

• UNION : 각 쿼리의 결과 합을 반환하는 합집합 (중복제거) / 여러개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다.
• UNION ALL : 각 쿼리의 모든 결과를 포함한 합집합 (중복제거 안함) / 여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시된다. 즉, 단순히 결과만 합쳐놓은 것이다. 일반적으로 여러 질의 결과가 상호 배타적일때 많이 사용한다. 개별 SQL문의 결과가 서로 중복되지 않는 경우, UNION과 결과가 동일하다 (결과의 정렬 순서에는 차이가 있을 수 있음)

EXISTS / NOT EXISTS

• EXISTS : 서브쿼리 결과가 한 건이라도 존재하면 정상적으로 조회 수행, 없으면 아무값도 출력되지 않는다
• NOT EXISTS : 서브쿼리 결과가 없을 경우 조회를 수행하고, 조회 결과가 있으면 아무 값도 출력되지 않는다

EXCEPT

• 오른쪽 쿼리 결과에 없는 왼쪽 쿼리에서만 있는 고유한 행을 리턴
• 앞의 SQL 문의 결과에서 두의 SQL 문의 결과에 대한 차집합이다. 중복된 행은 하나의 행으로 만든다.
• 차집합에 대한 연산이므로 NOT IN 또는 NOT EXISTS로 대체하여 처리 가능하다

MINUS

• 차집합

INTERSECT

• 여러 개의 SQL문의 결과에 대한 교집합. 중복된 행은 하나의 행으로 만든다

ORDER BY

• ORDER BY 1, 2 : 1, 2 번째 컬럼순으로 오름차순 정렬
• ORDER BY 1 DESC : 1번째 컬럼 기분으로 내림차순 정렬

CROSS JOIN (PRODUCT 연산)

• 상호 조인이라고도 불리며, 한 쪽 테이블의 모든 행들과 다른 테이블의 모든 행을 조인시키는 기능을 한다
• 결과개수는 두 테이블의 행의 개수를 곱한 개수가 된다

계층형 쿼리

• START WITH 절에 시작 조건을 찾는다
• CONNECT BY 절에 연결조건을 찾는다

계층적 쿼리

• START WITH : 계층 구조 전개의 시작 위치를 지정하는 구문. 루트 데이터를 지정한다
• ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다
• 순방향전개란 부모 노드로부터 자식 노드방향으로 전개하는 것을 말한다
• 루트 노드의 LEVEL 값은 1이다

계층형 질의(Hierarchical Query)

• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 사용
• 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다
• EX 사원 테이블에서 사원들 사이에 상위 사원과 하위 사원 관계가 존재한다
• 오라클 계층형 질의문에서 WHERE 절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는데 활용된다
• PRIOR 키워드는 SELECT, WHERE, CONNECT BY 절에 사용할 수 있다
• SQL Server 에서의 계층형 질의문은 CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개한다
• SQL Server 에서의 계층형 질의문은 앵커멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다

SELF JOIN

• Range Join (Equi Join X)
• 동일 테이블 사이의 조인
• 한 테이블 내에서 두 칼럼이 연관 관계가 있다

서브쿼리

• SELECT, FROM, HAVING, ORDER BY 절 등에서 사용 가능하다.
• Single Row 서브쿼리 (단일 행 서브쿼리) : 서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리를 의미한다. 단일 행 서브쿼리는 단일 행 비교 연산자와 함께 사용된다. 단일 행 비교 연산자에는 =, <, <=, >, >=, <> 이 있다
• Multi Row 서브쿼리 (다중 행 서브쿼리) : 서브쿼리의 실행 결과가 여러 건인 서브쿼리를 의미한다. 다중행 서브쿼리는 다중 행 비교 연산자와 함께 사용된다. 다중 행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS가 있다.
• Multi Column 서브쿼리 (다중 칼럼 서브쿼리) : 서브쿼리의 실행 결과로 여러 칼럼을 반환한다. 메인쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼레이서 비교하고자 하는 컬럼 개수와 컬럼의 위치가 동일해야 한다. (SQL Server 에서는 현재 지원하지 않는 기능이다)
• 서브쿼리를 괄호로 감싸서 사용한다
• 서브쿼리에서 ORDER BY를 사용하지 못한다.
• 단일 행 서브쿼리의 비교연산자는 다중 행 서브쿼리의 비교연산자로 사용할 수 없다

RANK / DENSE_RANK

• RANK : 중복 순위 개수만큼 다음 순위 값을 증가시킴
• DENSE_RANK : 중복 순위가 존재해도 순차적으로 다음 순위 값을 표시함

저장모듈 (Stored Module)

• SQL문장을 DB서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램
• 독립적으로 실해오디거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램
• Oracle의 저장 모듈에는 Procedure, User Defined Function, Trigger 가 있다

PROCEDURE

• CREATE Procedure 문법사용
• EXECUTE 명령어로 실행
• COMMIT, ROLLBACK 실행가능

TRIGGER

• CREATE Trigger 문법사용
• 생성 후 자동으로 실행
• COMMIT, ROLLBACK 실행 안됨
• DB에 로그인하는 작업에도 정의할 수 있다

EXECUTE IMMEDIATE

• 오라클 Procedure 내에서 문자열 형태로 된 쿼리문을 실행시키기 위해 사용

Role

• DBMS 사용자를 생성하면 기본적으로 많은 권한을 부여해야 한다. 많은 DBMS 에서는 DBMS 관리자가 사용자별로 권한을 관리해야하는 부담과 복잡함을 줄이기 위하여 다양한 권한을 그룹으로 묶어 관리할 수 있도록 사용자와 권한 사이에서 중개 역할을 수행한다

PL/SQL 특징

• Block 구조로 되어있어 각 기능별로 모듈화가 가능하다
• 변수, 상수, 등을 선언하여 sql 문장 간 값을 교환한다.
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다
• 오라클에 내장되어 있으므로 오라클과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다
• 응용 프로그램의 성능을 향상시킨다
• 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다

PARTITION BY

• 분석 함수를 사용할 때 PARTITION BY를 사용하여 그룹으로 묶어서 연산할 수 있다
• GROUP BY 절을 사용하지 않고, 조회된 각 행에 그룹으로 집계된 값을 표시할 때 OVER 절과 함께 PARTITION BY 절을 사용하면 된다

LAG / LEAD

• LAG : 이전 행의 값을 리턴
• LEAD : 다음 행의 값을 리턴
• OVER PARTITION_BY 그룹컬럼명 ORDER_BY 정렬컬럼명

WINDOW FUNCTION

• 분석함수 중 윈도우절을 사용하는 함수
• 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER
• 집계 함수 : SUM, MAX, AVG, COUNT
• 행 순서 관련 함수 : FIRST
• Partition과 Group by 구문은 의미적으로 유사하다
• Partition 구문이 없으면 전체 집합을 하나의 Partition 으로 정의한 것과 동일하다
• 윈도우 함수 적용 범위는 Partition을 넘을 수 없다
• 윈도우 함수는 결과에 대한 함수처리이기 때문에 결과 건수는 동일하다

Grouping Sets 110