순수 관계 연산자와 SQL 문장 비교
- SELECT 연산은 WHERE 절로 구현
- PROJECT 연산은 SELECT 절로 구현
- (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현
- DIVIDE 연산은 현재 사용되지 않음
ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태
- INNER JOIN
- NATURAL JOIN
- USING 조건절
- ON 조건절
- CROSS JOIN
- OUTER JOIN(LEFT, RIGHT, FULL)
INNER 조인
: OUTER JOIN과 대비하여 내부 JOIN이라고 하며 JOIN 조건에서 동일한 값이 있는 행만 반환된다.
CROSS 조인
- 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다.
- 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생한다.
LEFT OUTER JOIN
- 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다.
- 즉, TABLE A와 B가 있을 때 (TABLE A가 기준이 됨) A와 B를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B의 JOIN 칼럼에서 같은 값이 없는 경우에는 B테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.
FULL OUTER JOIN
- 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다.
- 즉, TABLE A와 B가 있을 때(TABLE A, B 모두 기준이 됨), RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일하다.
OUTER JOIN 문장 예시
LEFT OUTER JOIN
SELECT X.KEY1, Y.KEY2
FROM TAB1 X LEFT OUTER JOIN TAB2 Y
ON (X.KEY1=Y.KEY2);RIGHT OUTER JOIN
SELECT X.KEY1, Y.KEY2
FROM TAB1 X RIGHT OUTER JOIN TAB2 Y
ON (X.KEY1=Y.KEY2);FULL OUTER JOIN
SELECT X.KEY1, Y.KEY2
FROM TAB1 X FULL OUTER JOIN TAB2 Y
ON (X.KEY1=Y.KEY2);NATURAL JOIN
- Oracle SQL에서만 지원
- 두 테이블 간의 동일한 이름을 갖는 모든 컬럼들에 대해 EQUI JOIN을 수행한다.
- NATURAL JOIN이 명시되면, USING 절, ON 절, WHERE 절에서 JOIN을 정의할 수 없다.
USING 조건절
- Oracle SQL에서만 지원
- NATURAL JOIN에서 일치하는 모든 칼럼들에 대해 JOIN이 이루어지지만, FROM 절에 USING 조건절을 이용하면 같은 이름을 가진 칼럼 중 원하는 칼럼에 대해서만 선택적으로 EQUL JOIN을 할 수 있다.
ON 조건절
: JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있다.
일반 집합 연산자를 SQL과 비교
- UNION 연산은 UNION 기능으로
- INTERSECTION 연산은 INTERSECTION 기능으로
- DIFFERENCE 연산은 EXCEPT(Oracle : MINUS) 기능으로
- PRODUCT 연산은 CROSS JOIN 기능으로 구현되었다.
CONNECT BY
SELECT 칼럼, LEVEL AS 계층의 깊이
FROM 테이블
WHERE 조건
START WITH 시작위치 조건
CONNECT BY PRIOR 하드코드=상위코드;CONNECT_BY 키워드
- CONNECT_BY_ISLEAF : 전개 과정에서 해당 데이터가 리프 데이터면 1, 아니면 0
- CONNECT_BY_ISCYCLE : 전개 과정에서 지식을 갖는데, 해당 데이터가 조상으로 존재하면 1, 그렇지 않으면 0 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 테이블을 말함
- SYS_CONNECT_BY_PATH : 하위 레벨의 칼럼까지 모두 표시해줌 (구분자 지정 가능)
- CONNECT_BY_ROOT : Root 노드의 정보를 표시
PRIOR
- CONNECT BY절에 사용되며, 현재 읽은 칼럼이 지정한다.
- PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 부모 데이터를 자식 데이터 (부모 → 자식) 방향으로 전개하는 순방향 전개를 한다.
- PRIOR 부모 = 자식 형태를 사용하면 반대로 자식 데이터에서 부모 데이터 (자식 → 부모) 방향으로 전개하는 역방향 전개를 한다.
- CONNECT BY PRIOR : 조인 조건
START WITH절
- 계층 구조 전개의 시작 위치 지정하는 구문이다. 즉, 루트 데이터를 지정한다. (엑세스)
- 시작 조건을 의미한다.
ORDER SIBLINGS BY
: 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.
계층형 질의
- 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의를 사용한다.
- 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.
- 예를 들어, 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재한다.
SELF JOIN
- 동일 테이블 사이의 조인을 말한다.
- 따라서 FROM 절에 동일 테이블 두 번 이상 나타난다.
- 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭을 사용해야 한다.
SELF JOIN 문장
SELECT ALIAS명1.칼럼명, ALIAS명2.칼럼명, ...
FROM 테이블 ALIAS명1, 테이블 ALIAS명2
WHERE ALIAS명1.칼럼명2, ALIAS명2.칼럼명1;
서브쿼리
- 서브쿼리는 단일 행 또는 복수행 비교 연산자와 함께 사용할 수 있다.
- 서브쿼리는 SELECT 절. FROM 절. HAVING 절. ORDER BY 절 등에서 사용이 가능하다.
- 서브쿼리의 결과가 복수 행 결과를 반환하는 경우에는 IN, ANY, ALL 등 복수행 비교연산자와 사용하여야한다.
- 연관 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브쿼리이다.
- 다중 컬럼 서브쿼리는 서브쿼리의 결과로 여러 개의 컬럼이 반환되어 메인 쿼리의 조건과 비교되는데, SQL Server에서는 현재 지원하지 않는 기능이다.
서브쿼리를 사용시 주의사항
1. 서브쿼리를 괄호로 감싸서 사용한다.
2. 서브쿼리는 단일행 또는 복수행 비교연산자와 함께 사용 가능한다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관없다.
3. 서브쿼리에서는 ORDER BY를 사용하지 못한다. ORDER BY절은 SELECT절에서 오직 한 개만 올 수 있기 때문에 ORDER BY절은 메인쿼리의 마지막 문장에 위치해야 한다.
INLINE VIEW
- FROM 절에서 사용되는 서브쿼리를 인라인 뷰라고 한다.
- 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다.
- 인라인뷰는 SQL문이 실행될때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다.
- 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다.
- 인라인뷰는 SQL문이 실행될때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다.
뷰 사용의 장점
- 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
- 편리성 : 복잡한 질의를 뷰로 생성함으로써. 관련 질의를 단순하게 작성할 수 있다. 쪼한, 해당 형태의 SQL문을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다.
- 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.
그룹함수
Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직하나, ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다.
ROLLUP
- GROUP BY의 칼럼에 대해서 Subtotal을 만들어 준다.
- ROLLUP을 할 때 GROUP BY구에 칼럼이 두 개 이상 오면 순서에 따라서 결과가 달라진다.
CUBE
- 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다.
- 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다.
GROUPING SETS
- 다양한 소계 집합을 만들 수 있는데, GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있으며, 이 때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다.
- 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다.
RANK
- ORDER BY를 포함한 쿼리문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이며 동일한 값에 대해서는 동일한 순위를 부여하게 된다.
- 동일 순위를 부여하고 중간 순위는 비워둔다.
DENSE_RANK
- RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 다른 점이다.
- 동일 순위를 부여하되 중간 순위를 비우지 않는다.
ROW_NUMBER
- RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여한다.
- 동일 값에 대해서도 유일한 순위를 부여한다.
LAG 함수
- 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.
- 이후 몇 번째 행의 값을 가져오는 것은 LEAD함수이며, SQL Server에서느 지원하지 않는 함수이다.
PL/SQL의 특징
- PL/SQL은 block구조로 되어있어 각 기능별로 모듈화가 가능하다.
- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.
- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
- PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
- PL/SQL은 응용 프로그램의 성능을 향상시킨다.
- PL/SQL은 여러 SQl 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.
저장 모듈(Stored Module)
- SQL 문장을 데이터 베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행 될 수 있는 완전한 프로그램이다.
- Oracle의 저장 모듈에는 Procedure, User Defined Function, Trigger가 있다.
'Certificate > SQLD' 카테고리의 다른 글
| SQL 최적화 기본 원리 (0) | 2021.01.04 |
|---|---|
| SQL 기본 (0) | 2021.01.04 |
| 데이터 모델과 성능 (0) | 2021.01.04 |
| 데이터 모델링의 이해 (0) | 2021.01.04 |
| SQLD 공부 방법 및 합격 후기 | 요점정리 PDF 공유 (538) | 2021.01.04 |