[SQLD 이론정리] Ⅱ. SQL 기본 및 활용 2

제 2장. SQL 활용

일반 집합 연산자

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

• UNION 연산은 UNION 기능으로,
• INTERSECTION 연산은 INTERSECT 기능으로,
• DIFFERENCE 연산은 EXCEPT(Oracle 은 MINUS) 기능으로,
• PRODUCT 연산은 CROSS JOIN 기능으로 구현되었다.

 

순수 관계 연산자

순수 관계 연산자는 관계형 데이터베이스를 구현하기 위해 새롭게 만들어진 연산자로, SELECT, PROJECT, JOIN, DIVIDE가 있다.

 

순수 관계 연산자와 SQL 문장 비교

• SELECT 연산은 WHERE 절로 구현되었다.
• PROJECT 연산은 SELECT 절로 구현되었다.
• (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현되었다.
• DIVIDE 연산은 현재 사용되지 않는다.

 

FROM 절 JOIN 형태

ANSI/ISO SQL 에서 표시하는 FROM 절의 JOIN 형태

• INNER JOIN
• NATURAL JOIN
• USING 조건절
• ON 조건절
• CROSS JOIN
• OUTER JOIN(LEFT, RIGHT, FULL)

 

INNER JOIN

INNER JOIN 은 OUTER(외부) JOIN 과 대비하여 내부 JOIN 이라고 하며 JOIN 조건에서 동일한 값이 있는 행만 반환한다.

 

NATURAL JOIN

NATURAL JOIN은 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN 을 수행한다. NATURAL JOIN 이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다. 그리고, SQL Server 에서는 지원하지 않는 기능이다. JOIN 에 사용된 칼럼들은 같은 데이터 유형이어야 하며, ALIAS나 테이블 명과 같은 접두사를 붙일 수 없다.

 

USING 조건절

USING 조건절을 이용한 EQUI JOIN 에서도 NATURAL JOIN 과 마찬가지로 JOIN 칼럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.

 

ON 조건절

JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있다.

 

CROSS JOIN

E.F.CODD 박사가 언급한 일반 집합 연산자의 PRODUCT의 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생한다.

NATURAL JOIN의 경우 WHERE 절에서 JOIN 조건을 추가할 수 없지만, CROSS JOIN의 경우 WHERE 절에 JOIN 조건을 추가할 수 있다. 그러나, 이 경우는 CROSS JOIN이 아니라 INNER JOIN과 같은 결과를 얻기 때문에 CROSS JOIN을 사용하는 의미가 없어지므로 권고하지 않는다.

 

OUTER JOIN

INNER(내부) JOIN 과 대비하여 OUTER(외부) JOIN 이라고 불리며, JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다.

ORACLE에서는 OUTER JOIN 구문을 (+) 기호를 사용하여 처리할 수도 있으며, 이를 ANSI 문장으로 변경하기 위해서는 Inner쪽 테이블에 조건절을 ON절과 함께 위치시켜야 정상적인 OUTER JOIN을 수행할 수 있다.

LEFT OUTER JOIN

조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다. 즉, TableA 와 B 가 있을 때(Table 'A'가 기준이 됨), A와 B를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B의 JOIN 칼럼에서 같은 값이 없는 경우에는 B 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.

 

RIGHT OUTER JOIN

조인 수행시 LEFT JOIN 과 반대로 우측 테이블이 기준이 되어 결과를 생성한다. 즉, TABLE A와 B가 있을 때(TABLE 'B'가 기준이 됨), A와 B 를 비교해서 A의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, A의 JOIN 칼럼에서 같은 값이 없는 경우에는 A 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.

 

FULL OUTER JOIN

조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다. 즉, TABLE A와 B가 있을 때(TABLE 'A', 'B' 모두 기준이 됨), RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일하다.

 

집합 연산자(SET OPERATOR)

집합 연산자의 종류

• EXCEPT는 차집합에 대한 연산이므로 NOT IN 또는 NOT EXISTS로 대체하여 처리가 가능하다.

 

집합 연산자를 사용하여 만들어지는 SQL 문의 형태

SELECT 칼럼명 1, 칼럼명 2, ...
FROM 테이블명 1
[WHERE 조건식 ]
[[GROUP BY 칼럼(Column)이나 표현식
[HAVING 그룹조건식 ] ]
집합 연산자
SELECT 칼럼명 1, 칼럼명 2, ... 
FROM 테이블명 2
[WHERE 조건식 ]
[[GROUP BY 칼럼(Column)이나 표현식
[HAVING 그룹조건식 ] ]
[ORDER BY 1, 2 [ASC 또는 DESC ];
→ 집합 연산에서 ORDER BY는 집합 연산을 적용한 최종 결과에 대한 정렬 처리이므로 가장 마지막 줄에 한번만 기술한다.

 

계층형 질의

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다. 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다. 예를 들어, 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재한다.

 

Oracle 계층형 질의

SELECT ∙∙∙
FROM 테이블
WHERE condition AND condition ∙∙∙
START WITH condition
CONNECT BY [NOCYCLE] condition AND condition ∙∙∙
[ORDER SIBLINGS BY column, column, ∙∙∙]

• START WITH 절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정한다.(액세스)
• CONNECT BY 절은 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야 한다.(조인)
• PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다. PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 순방향 전개를 한다. 그리고 PRIOR 부모 = 자식 형태를 사용하면 반대로 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 역방향 전개를 한다. SELECT, WHERE 절에서도 사용할 수 있다.
• NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임오류가 발생한다. 그렇지만 NOCYCLE 를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
• ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.
• WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

SYS_CONNECT_BY_PATH : 하위 레벨의 칼럼까지 모두 표시해줌 (구분자 지정 가능)

CONNECT_BY_ROOT : Root 노드의 정보를 표시

 

SQL Server 계층형 질의

• CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개한다.
• 앵커 멤버(Anchor Member)를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다.

 

셀프 조인

셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다. 따라서 FROM 절에 동일 테이블이 두 번 이상 나타난다. 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)를 사용해야 한다.

 

서브 쿼리

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

서브쿼리 사용 시 주의사항

1. 서브쿼리를 괄호로 감싸서 사용한다.
2. 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1 건 이하이어야 하고 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.
3. 서브쿼리에서는 ORDER BY 를 사용하지 못한다. ORDER BY 절은 SELECT 절에서 오직 한 개만 올 수 있기 때문에 ORDER BY 절은 메인쿼리의 마지막 문장에 위치해야 한다.

 

반환되는 데이터의 형태에 따른 서브쿼리 분류

• 다중 칼럼 서브쿼리는 SQL Server에서는 현지 지원하지 않는 기능이다.
• 다중 행 서브쿼리 비교 연산자는 단일 행 서브쿼리 비교 연산자로도 사용할 수 있다.

 

 

연관 서브쿼리

연관 서브쿼리(Correlated Subquery)는 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리이다. 메인 쿼리의 결과가 서브쿼리로 제공될 수도 있고, 서브쿼리의 결과가 메인쿼리로 제공될 수도 있다.

EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용된다. 또한 EXISTS 서브쿼리의 특징은 아무리 조건을 만족하는 건이 여러 건이더라도 조건을 만족하는 1 건만 찾으면 추가적인 검색을 진행하지 않는다.

참고) EXISTS, NOT EXISTS 문법

 

비 연관 서브쿼리

서브쿼리가 메인쿼리 컬럼을 가지고 있지 않은 형태로, 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용된다.

 

스칼라 서브쿼리(Scalar Subquery)
스칼라 서브쿼리는 한 행, 한 칼럼(1 Row 1 Column)만을 반환하는 서브쿼리를 말한다. 스칼라 서브쿼리는 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다.

 

인라인 뷰(Inline View)

FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)라고 한다. 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다. 인라인 뷰는 SQL 문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다. 그래서 일반적인 뷰를 정적 뷰(Static View)라고 하고 인라인 뷰를 동적 뷰(Dynamic View)라고도 한다. 인라인 뷰는 테이블명이 올 수 있는 곳에서 사용할 수 있다.

인라인 뷰의 칼럼은 메인 쿼리에서도 사용이 가능하다.

 

뷰(View)

테이블은 실제로 데이터를 가지고 있는 반면, 뷰(View)는 실제 데이터를 가지고 있지 않다. 뷰는 단지 뷰 정의(View Definition)만을 가지고 있다. 질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성(Rewrite)하여 질의를 수행한다. 뷰는 실제 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 수행하기 때문에 가상 테이블(Virtual Table)이라고도 한다.

 

뷰 사용의 장점

• 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
• 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 SQL문을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다.
• 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.

 

그룹 함수

ROLLUP 함수

ROLLUP에 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용되어지며, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다. 중요한 것은, ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 인수의 순서에도 주의해야 한다.

ROLLUP(A, B)를 수행하면 (A, B)별 집계, A별 집계와 전체 집계를 출력할 수 있다.

 

CUBE 함수

ROLLUP에서는 단지 가능한 Subtotal만을 생성하였지만, CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다. Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직하나, ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다.

CUBE도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다.

CUBE(A, B)를 수행하면 (A, B)별 집계, A별 집계, B별 집계와 전체 집계를 출력할 수 있다.

 

GROUPING SETS 함수

GROUPING SETS는 다양한 소계 집합을 만들 수 있는데, GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다. 그리고 GROUPING SETS 함수도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다.

GROUPING SETS(A, B)를 수행하면 (A, B)별 집계를 출력할 수 있다.

 

GROUPING 함수

ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수가 추가되었다.
ROLLUP 이나 CUBE 에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1 이 표시되고, 그 외의 결과에는 GROUPING(EXPR) = 0 이 표시된다.

 

CUBE, ROLLUP, GROUPING SETS 함수들에 의해 집계된 레코드에서 집계 대상 컬럼 이외의 GROUP 대상 컬럼의 값은 NULL을 반환한다.

 

 

WINDOW FUNCTION

PL/SQL, SQL/PL, T-SQL, PRO*C 같은 절차형 프로그램을 작성하거나, INLINE VIEW를 이용해 복잡한 SQL 문을 작성해야 하던 것을 부분적이나마 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 바로 WINDOW FUNCTION이다.

 

WINDOW FUNCTION 종류

• 그룹 내 순위(RANK) 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER
• 그룹 내 집계(AGGREGATE) 관련 함수: SUM, MAX, MIN, AVG, COUNT
• 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
• 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
• 선형 분석을 포함한 통계 분석 관련 함수

 

그룹 내 순위 함수

RANK 함수

RANK 함수는 ORDER BY를 포함한 QUERY문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이다. 이때 특정 범위(PARTITION) 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있다. 또한 동일한 값에 대해서는 동일한 순위를 부여하고 중간 순위는 비워 둔다.

 

DENSE_RANK 함수

DENSE_RANK 함수는 동일한 값에 대해 동일한 순위를 부여하지만, RANK 함수와 다르게 중간 순위를 비워두지 않는다.

 

ROW_NUMBER 함수

RANK 나 DENSE_RANK 함수와 다르게 동일한 값이라도 고유한 순위를 부여한다.

순위 함수

TOP (Expression) [PERCENT] [WITH TIES]

- WITH TIES : ORDER BY 조건 기준으로 TOP N의 마지막 행으로 표시되는 추가 행의 데이터가 같을 경우 N+동일 정렬 순서 데이터를 추가 반환하도록 지정하는 옵션 (마지막 기준 공통일 경우 모두 출력)

 

일반 집계 함수

집계함수(대상칼럼) OVER(PARTITION BY 칼럼 [ORDER BY 칼럼])

RANGE BETWEEN start_point AND end_point

- start_point는 end_point와 같거나 작은 값이 들어감
- Default값은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- UNBOUNDED PRECEDING : start_point만 들어갈 수 있으며, 파티션의 first row
- UNBOUNDED FOLLOWING : end_point만 들어갈 수 있으며, 파티션의 last row
- CURRENT ROW : start, end_point 둘다 가능. 윈도우는 CUREENT ROW에서 start하거나 end 함

 

그룹 내 행 순서 함수

LAG 함수

LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. SQL Server에서는 지원하지 않는 함수이다.

LAG(대상 컬럼명 [,값을 가져올 행의 위치 기본값은 1] [,값이 없을 경우 기본값])

 

LEAD 함수

LEAD 함수를 이용해 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다. SQL Server에서는 지원하지 않는 함수이다.

LAG(대상 컬럼명 [,값을 가져올 행의 위치 기본값은 1] [,값이 없을 경우 기본값])

 

 

DCL

ROLE

데이터베이스 관리자는 유저가 생성될 때마다 각각의 권한들을 유저에게 부여하는 작업을 수행해야 하며 간혹 권한을 빠뜨릴 수도 있으므로 각 유저별로 어떤 권한이 부여되었는지를 관리해야 한다. 하지만 관리해야 할 유저가 점점 늘어나고 자주 변경되는 상황에서는 매우 번거로운 작업이 될 것이다. 이와 같은 문제를 줄이기 위하여 많은 데이터베이스에서 유저들과 권한들 사이에서 중개 역할을 하는 ROLE 을 제공한다.

 

 

절차형 SQL

PL/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server) 등의 절차향 SQL을 이용하면 저장 모듈을 생성할 수 있다.

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

 

PL/SQL 특징

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

PL/SQL 에서는 동적 SQL 또는 DDL 문장을 실행할 때 EXECUTE IMMEDIATE를 사용해야 함

 

Procedure

일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

 

User Defined Function

Procedure처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미한다. Procedure와
다른 점은 RETURN을 사용해서 하나의 값을 반드시 되돌려 줘야 한다는 것이다.

 

Trigger

특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML 문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다. 즉 사용자가 직접 호출하여 사용하는 것이 아니고 데이터베이스에서 자동적으로 수행하게 된다.

Trigger는 테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 작업에 대해 발생되는 Trigger와 각 행에 대해서 발생되는 Trigger가 있다.

 

프로시저와 트리거의 차이점

프로시저	트리거
CREATE Procedure 문법사용	CREATE Trigger 문법사용
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 실행 불가능