[SQLD] 2과목. SQL 기본 및 활용 - 2

02. SQL 활용

표준 조인

순수 관계 연산자

• SELECT 연산은 WHERE 절로 구현
• PROJECT 연산은 SELECT절로 구현
• (NATURE) JOIN 연산은 다양한 JOIN 기능으로 구현
• DIVIDE 연산은 현재 사용 X

 

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

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

 

JOIN

• 적절한 JOIN 조건이 없으면 카티시안 곱(Cartesian Product)이 발생함

 

JOIN 종류 

• INNER JOIN : JOIN 조건에서 동일한 값이 있는 행만 반환
• LEFT OUTER JOIN : 조인 수행시 먼저 표기된 좌측 테이블 데이터 먼저 읽고, 우측 테이블에서 JOIN 대상을 읽어옴
• FULL OUTER JOIN : 조인 수행시 좌, 우측 테이블의 모든 데이터를 JOIN 하여 결과 반환
• CROSS JOIN : 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합 M*N건의 데이터 조합 발생

 

USING 조건절

• JOIN 컬럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사 붙일 수 없음

 

집합 연산자

집합 연산자 종류

UNION	여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만듬
UNION ALL	여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 표시
INTERSECT	여러 개의 SQL문의 결과에 대한 교집합으로 중복된 행은 하나의 행으로 만듬
EXCEPT(=MINUS)	앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합으로 중복된 행은 하나의 행으로 만듬

 

계층형 질의와 셀프 조인

계층형 질의

• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용
• ORACLE 계층형 질의의 루트 노드 LEVEL은 1
• SQL SERVER에서는 CTE를 재귀 호출 함으로써 계층 구조를 전개
• SQL SERVER에서는 앵커 멤버를 실행해 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행
• ORACLE 계층형 질의문에서 WHERE절은 모든 전개를 진행한 이후 필터 조건으로서 조건 만족하는 데이터만을 추출
• ORACLE에서 PRIOR키워드는 CONNECT BY절뿐만 아니라 SELECT, WHERE 절에서도 사용 가능
• START WITH : 계층 구조 전개의 시작 위치 지정 
• CONNECT BY : 다음에 전개될 자식 데이터 지정
  • PRIOR : CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정
    • PRIOR 자식 = 부모 형태는 계층구조에서 부모데이터에서 자식 방향으로 순방향 전개
    • PRIOR 부모 = 자식 형태는 자식데이터에서 부모 방향으로 역방향 전개
• NOCYCLE : 동일한 데이터가 전개되지 않음
• ORDER SIBLINGS BY : 형제 노드간의 정렬 수행

 

SELF JOIN

• 동일 테이블 사이의 조인으로 FROM절에 동일 테이블이 두 번 이상 나타남 반드시 ALIAS 사용
• 한 테이블 내에서 두 칼럼이 연관관계가 있을 때 사용

 

서브쿼리

서브 쿼리

• 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능
• SELECT 절, FROM절, HAVING절, ORDER BY절에서 사용 가능
• 연관 서브 쿼리는 서브 쿼리가 메인 쿼리 컬럼을 포함하고 있는 형태의 서브쿼리
• 비연관 서브 쿼리는 주로 메인 쿼리에 값을 제공하기 위한 목적으로 사용
• 메인 쿼리의 결과가 서브 쿼리로 제공될 수도 있고, 반대의 상황도 있으므로 실행순서는 상황에 따라 다름

 

서브쿼리 사용 시 주의사항

• 서브 쿼리를 괄호로 감싸서 사용
• 서브 쿼리에서는 ORDER BY를 사용하지 못한다. ORDER BY 절은 메인 쿼리의 마지막 문장에 위치해야 함

 

서브 쿼리 종류

Single Row 서브쿼리 (단일 행 서브쿼리)	서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리를 의미 단일 행 서브쿼리는 단일행 비교 연산자(=,<,>,<>,<=,>=)와 함께 사용
Multi Row 서브쿼리 (다중 행 서브쿼리)	서브쿼리 실행결과가 여러 건인 서브쿼리를 의미 다중 행 서브쿼리는 다중 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS)와 함께 사용
Multi Column 서브쿼리 (다중 열 서브쿼리)	서브쿼리 실행결과로 여러 칼럼 반환하고, 메인 쿼리의 조건절에 여러 칼럼 동시 비교 가능 서브쿼리와 베인쿼리에서 비교할 컬럼 개수, 위치가 동일해야함

 

 

인라인 뷰(Inline View)

• FROM절에서 사용되는 서브쿼리
• 서브 쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용 가능
• SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 DB에 해당 정보가 저장 X

 

뷰

• 테이블은 실제로 데이터를 가지고 있지만, 뷰는 실제 데이터를 가지지 않음
• 뷰는 보안을 강화하기 위한 목적으로도 활용 가능
• 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있음

 

뷰 사용의 장점

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

 

그룹 함수

• CUBE, GROUPING SETS, ROLLUP 모두 일반 그룹함수로 동일한 결과 추출 가능
• ROLLUP은 함수 인자로 주어진 컬럼 순서에 따라 다른 결과가 추출되며, 나열된 컬럼에 대해 계층 구조로 집계 출력
• CUBE, ROLLUP, GROUPING SETS에 의해 집계된 레코드에서 집계 대상 컬럼 외의 GROUP대상 컬럼값은 NULL 반환
• CUBE는 인자로 주어진 컬럼의 결합 가능한 모든 조합에 대해 집계 수행
• 모든 경우에 대해 subtotal을 생성하는 경우엔 CUBE 사용이 맞으나, ROLLUP에 비해 시스템 부담이 큼
• GROUPING SETS에 표시된 인수들에 대한 개별 집계 구할 수 있고, 이 때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수가 바껴도 결과는 같음
• GROUPING SETS 함수도 결과에 대한 정렬이 필요한 경우 ORDER BY 절에 명시적으로 정렬 칼럼 표시되어야함

 

윈도우 함수

• 윈도우 함수는 결과에 대한 함수처리이기 때문에 결과 건수는 줄지 않음
• Partition과 Group By구문은 의미적으로 유사
• Partition 구문이 없으면 전체 집합을 하나의 Partition으로 정의하는 것과 동일
• 윈도우 함수 적용 범위는 Partition을 넘을 수 없음
• GROUP BY절의 집합을 원본으로 하는 데이터는 윈도우 함수와 함께 사용해도 오류 발생 X

 

RANK()

ORDER BY를 포함한 QUERY문에서 특정 항목에 대한 순위를 구하는 함수이며 동일한 값에 대해서는 동일한 순위 부여

 

DENSE_RANK()

RANK함수와 흡사하나 동일한 순위를 하나의 건수로 취급

 

ROW_NUMBER()

RANK나 DENSE_RANK함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위 부여

 

LAG()

파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있음.

이후 몇 번째 행의 값을 가져오는 것은 LEAD()함수이며 SQL SERVER에서는 지원 X

 

DCL

GRANT

DBMS에 생성된 USER와 다양한 권한들 사이에서 중개 역할을 하도록 ROLE을 제공하는데, 이 ROLE을 USER에게 부여하는 명령어

REVOKE

DBMS에 생성된 USER와 다양한 권한들 사이에서 중개 역할을 하도록 ROLE을 제공하는데, 이 ROLE을 다시 회수하기 위한 명령어

 

PL/SQL

• Block 구조로 되어있어 각 기능별로 모듈화가 가능
• 변수, 상수 등을 선언해 SQL문장 간 값을 교환
• 변수와 상수 등을 사용해 일반 SQL 문장 실행할 때 WHERE절의 조건 등으로 대입 가능
• IF, LOOP 등의 절차형 언어를 사용해 절차적인 프로그램이 가능하도록 함
• Procedure, User Defined Funcion, Trigger 객체를 PL/SQL로 작성 가능
• PL/SQL로 작성된 Procedure, User Defined Funcion은 작성자 기준으로 트랜잭션 분할 가능
• 또한 프로시저 내에서 다른 프로시저 호출할 경우 호출 프로시저의 트랜잭션과는 별도로 선언해 자율 트랜잭션 처리 가능
• Procedure내부에 작성된 절차적 코드는 PL/SQL엔진이 처리하고 일반적인 SQL문장은 SQL실행기가 처리
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있음
• PL/SQL은 Oracle에 내장되어 있어 Oracle과 PL/SQL 지원하는 어떤 서버로도 프로그램 옮길 수 있음
• PL/SQL은 응용 프로그램의 성능 향상시킴
• PL/SQL은 여러 SQL문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량 줄일 수 있음

 

절차형 SQL

• 저장형 프로시져 : SQL을 로직과 함께 DB내에 저장해 놓은 명령문의 집합
• 저장형 함수(사용자 정의 함수) : 단독적으로 실행되기 보다는 다른 SQL문 통해 호출되고 그 결과를 리턴하는 SQL보조 역할
• 트리거 : 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때 DB에서 자동으로 동작하도록 작성된 프로그램
  
  • 데이터의 무결성과 일관성을 위해 사용자 정의 함수를 사용하는것

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

 

트리거

• DB에 의해 자동으로 호출되고 수행됨
• 특정 테이블에 대해 INSERT, UPDATE, DELETE문이 수행되었을 떄 호출되도록 정의 가능
• TCL 사용 불가
• DB에 로그인하는 작어에도 정의 가능

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

 

 

---

References

• SQL 자격검정 실전문제, 한국데이터산업진흥원