[SQLD]2과목 - 3장. SQL 활용 / JOIN, 서브쿼리, 그룹함수

1절. 표준조인 (Standard join)

1-1. SQL에서의 연산

• 집합 연산 : UNION(합집합), INTERSECTION(교집합), DIFFERENCE(차집합), PRODUCT(곱집합)
• 순수 관계 연산
  SELECT WHRER절 (조건에 맞는 행)
  PROJECT SELECT절 (조건에 맞는 칼럼)
  JOIN (UPDATE 아님!!)
  DIVIDE (공통요소를 추출하고 분모 릴레이션의 속성을 삭제한 후 중복된 행 제거)

 

1-2. FROM절

JOINANSI/ISL에서 규정한 JOIN문법 : NATURAL JOIN, INNER JOIN, CROSS JOIN, OUTER JOIN

NATURAL JOIN	- 같은 이름을 가진 칼럼 전체에 대한 등가(=EQUI) 조인 - 앨리어스나 테이블명 사용 불가 ★ - USING 조건절이나 ON 조건절 사용 불가 - 같은 데이터 유형 칼럼만 조인 가능
	SQL>> SELECT 칼럼s FROM 테이블1 NATURAL JOIN 테이블2;
INNER JOIN	- 행에 동일한 값이 있는 칼럼 조인, JOIN의 디폴트 옵션, USING 조건절이나 ON 조건절 필수 ★ - CROSS JOIN, OUTER JOIN과 동시 사용 불가 - 두 테이블에 동일 이름 칼럼이 있을 경우 SELECT절에 앨리어스 필수 ★
	SQL>> SELECT 칼럼s FROM 테이블1 A, 테이블2 B WHERE A.칼럼=B.칼럼; SQL>> SELECT 칼럼s FROM 테이블1 A INNER JOIN 테이블2 B ON A.칼럼=B.칼럼; (ANSI/ISO 표준)
USING 조건절	- 같은 이름을 가진 칼럼 중 등가 조인 대상 칼럼 선택, SQL Server에서는 지원하지 않음 - 조건절에 앨리어스나 테이블명 사용 불가 ★
	SQL>> SELECT 칼럼s FROM 테이블1 JOIN 테이블2 USING (칼럼명);
ON 조건절	다른 이름을 가진 칼럼 간 조인 가능, 앨리어스나 테이블명 필수 ★, 괄호는 의무사항 아님
	SQL>> SELECT 칼럼s FROM 테이블1 A JOIN 테이블2 B ON (A.칼럼=B.칼럼);
CROSS JOIN	가능한 모든 조합으로 조인. 서로 연관된 칼럼이 없을 경우 수행하는 조인, Cartesian Product 만듬
	SQL>> SELECT 칼럼 FROM 테이블1, 테이블2; = SELECT 칼럼 FROM 테이블1 CROSS JOIN 테이블2; (조인 조건이 없을 때 발생 ↔ NATURAL JOIN은 명시해야 됨)
OUTER JOIN	조인 조건에서 행에 동일한 값이 없는 칼럼 조인, USING 조건절이나 ON 조건절 필수 ★
	LEFT OUTER JOIN	좌측 테이블 데이터 조회 후, 우측 테이블 조인 대상 데이터 조회
		SQL>> SELECT 칼럼s FROM 테이블1 A, 테이블2 B A.칼럼=B.칼럼(+); SQL>> SELECT 칼럼s FROM 테이블1 A LEFT OUTER JOIN 테이블2 B ON (A.칼럼 = B.칼럼);
	RIGHT OUTER JOIN	오른쪽 결과가 더 길다.
	FULL OUTER JOIN	LEFT와 RIGHT OUTER JOIN 포함
		SELECT 칼럼 FROM 테이블1 A FULL OUTER JOIN 테이블2 B ON(A.칼럼=B.칼럼);=SELECT 칼럼 FROM 테이블1 A LEFT OUTER JOIN 테이블2 B ON(A.칼럼=B.칼럼)UNION SELECT 칼럼 FROM 테이블1 A RIGHT OUTER JOIN 테이블2 B ON(A.칼럼=B.칼럼);
SELF JOIN	자기 자신의 테이블을 참조하는 조인으로 OUTER든 INTTER든 자신의 테이블과 조인하면 SELF JOIN 임.
	같은 테이블 이름이 2번 나와서 반드시 앨리어스(AS) 필수 ★ 계층형 쿼리를 작성할 때 사용한다 ★ 한테이블 내에서 두칼럼이 연관관계가 있을 때 사용
	SELECT * FROM table t1 JOIN table t2 ON t1.column1 = t2.column2

 

---

2절. 집합 연산자

• 집합 연산자: 조인 없이 여러 테이블의 관련 데이터를 조회하는 연산자

UNION	합집합, 칼럼수와 데이터 타입이 모두 동일한 테이블 간 연산만 가능. 정렬을 유발함
	SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 UNION SELECT 테이블명 WHERE 조건절;
UNION ALL	중복된 행도 전부 출력하는 합집합, 정렬 안함, 집합 연산자에 속함
	SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 UNION ALL SELECT 테이블명 WHERE 조건절;
INTERSECT	교집합
	SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 MS INTERSECT SELECT 테이블명 WHERE 조건절;
MINUS, EXCEPT	차집합
	SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 MINUS SELECT 테이블명 WHERE 조건절;  = SELECT 칼럼명 FROM 테이블1 A WHERE NOT EXIST    (SELECT 칼럼 FROM 테이블2 B WHERE A.칼럼1=B.칼럼1 AND A.칼럼2=B.칼럼2);

 

---

3절. 계층형 쿼리

3-1. 계층형 쿼리(Hierarchical Query)

계층형 데이터를 조회하기 위해 사용함, Oracle에서 지원함

• 계층형 데이터 : 엔터티를 순환관계 데이터 모델로 설계할 때 발생함
• 트리 형태의 구조로 쿼리 수행 (루트 노드부터 하위 노드의 쿼리를 실행함)
  ex) 상사 이름과 사람 이름을 조인하여 상사 밑에 넣기

 

3-2.

START WITH : 시작 조건 지정

CONNECT BY PRIOR : 조인 조건 지정 (PRIOR키워드는 SELECT,WHERE절에서도 사용가능)

• LEVEL : 검색 항목의 깊이, 최상위 계층의 레벨은 1
• CONNECT_BY_ROOT : 최상위 계층 값 표시
• CONNECT_BY_ISLEAF : 최하위 계층 값 표시
• SYS_CONNECT_BY_PATH : 계층 구조의 전개 경로 표시
• CONNECT BY절의 루프 알고리즘 키워드
  • NOCYCLE : 순환구조의 발생지점까지만 전개
  • CONNECT_BY_ISCYCLE : 순환구조 발생지점 표시 (부모노드와 자식노드가 같을때 1 아니면 0 출력)
    
• LPAD : 계층형 조회 결과를 명확히 하기 위해 사용 (LEVEL 값을 이용하여 결과 데이터 정렬)

 

 

3-3. SQL Server 계층형 질의

: CTE(Common Table Expression)로 재귀 호출

 

ex1) 

시작 : C2가 null인 1열 '1 null A'

CONNECT BY PRIOR C1=C2 → C2는 이전 C1값이다.

이전 C1값이 1인 C2는 ‘2 1 B'와 ’3 1 C‘ 이다.

이때, SIBLINGS BY C3 DESC 라고 했으니까,

C3기준 내림차순으로 정렬한다. → '1 null A'

’3 1 C‘

‘2 1 B'

3번째 열 ‘2 1 B’의 C1은 2니까 ‘4 2 D’가 다음으로 온다.

 

 

ex2)

 

시작 : 매니저사원번호가 NULL인 ‘001 홍길동 NULL'과 ‘005 이병헌 NULL’

매니저사원번호가 이전 행의 사원번호니까
매니저번호가 001과 005인 행 → ‘002 강감찬 001’ ‘003 이순신 001’ 
                                                     ‘004 이민정 001’ 인데

강감찬은 입사일자 범위밖이니까 제외하고,

→ ‘006 안성기 005’ ‘007 이수근 005’ ‘008 김병만 005’인데

안성기, 이수근, 김병만 모두 입사일자 범위 밖이라서 제외한다.

결과 : ‘001 홍길동 NULL'

‘003 이순신 001’

‘004 이민정 001’

‘005 이병헌 NULL’ 이 답이다.

 

---

4절. 서브쿼리 ★

4-1. 서브쿼리

: 하나의 SQL문 안의 SQL문. SELECT절, FROM절, HAVING절, ORDER BY절 에서 사용가능.
(반대로 서브쿼리안에서 ORDER BY는 안됨 주의!)
서브쿼리에서는 메인쿼리의 칼럼 사용가능, 반대로 메인쿼리에서는 서브쿼리 컬림 사용 불가능.

 

4-2. 종류

동작 방식에 따른 분류

• 비연관 서브쿼리: 메인쿼리 칼럼을 갖고 있지 않는 서브쿼리, 메인쿼리에 값을 제공하기 위해 주로 사용함
  • Access Subquery : 제공자 역할
  • Filter Subquery : 확인자 역할
  • Early Filter Subquery : 데이터 필터링 역할
    
    
• 연관 서브쿼리(Associative Subquery) : 메인쿼리의 결과를 조건이 맞는지 확인하기 위한 목적으로 주로 사용

반환 데이터 형태에 따른 분류

• 단일 행 서브쿼리 : 실행 결과가 1건 이하인 서브쿼리, 단일 행 비교 연산자와 함께 사용. COUNT(*)일 때 단일행
• 다중(복수) 행 서브쿼리 : 실행 결과가 여러 건인 서브쿼리, 다중 행 비교 연산자와 함께 사용
  다중 행 비교 연산자 (단일행 비교 연산자로도 사용할 수 있다.)
  • IN : 서브쿼리의 결과 중 하나의 값이라도 동일하다는 조건
  • ANY : 서브쿼리의 결과 중 하나의 값이라도 만족한다는 조건
  • ALL : 서브쿼리의 모든 결과값을 만족한다는 조건
  • EXISTS : 서브쿼리 결과를 만족하는값이 존재하는지 여부 확인(TRUE,FALSE 반환) (항상 연관 서브쿼리로 사용)
    WHERE EXISTS (SELECT ~) 또는 WHERE NOT EXISTS
    (NOT EXISTS는 null을 제외하고 조건을 처리한다.)
    
    
• 다중 칼럼 서브쿼리: 실행 결과로 여러 칼럼 반환, 주로 메인쿼리의 조건과 비교하기 위해 사용 (Oracle에서만)
  (비교하고자하는 칼럼의 개수와 위치가 동일해야 함)

 

 

4-3. 스칼라 서브쿼리

: 값 하나를 반환하는 서브쿼리, SELECT절에 사용하는 서브쿼리

 

뷰 : 가상의 테이블
FROM절에 사용하는 뷰는 인라인 뷰(Inline View)라고 함,
수행속도, 성능향상, 보안관리단지 정의만 갖고있고, 실행 시점에 질의를 재작성하여 수행. 뷰를 생성하는 기능을 지원하는 DBMS도 있다.

• 장점 (독 편 보)
  • 독립성 : 테이블 구조 변경 자동 반영
  • 편리성 : 쿼리를 단순하게 작성할 수 있음, 자주 사용하는 SQL문의 형태를 뷰로 생성하여 사용할 수 있음
  • 보안성 : 뷰를 생성할 때 칼럼을 제외할 수 있음
    
    

• WITH : 서브쿼리를 이용하여 뷰로 사용할 수 있는 구문
  >> WITH 뷰명 AS (SELECT ~ FROM ~)

---

5절. 그룹 함수 ★

1. ANSI/ISO 표준 데이터 분석 함수: 집계 함수, 그룹 함수, 윈도우 함수
2. 그룹 함수(Group Function): 합계 계산 함수, NULL을 빼고 집계함 (~ 집계 함수), 결과값 없는 행은 출력 안함
   • ROLLUP : GROUP BY로 묶인 칼럼의 소계 계산, 계층 구조로 GROUP BY의 칼럼 순서가 바뀌면 결과 값 바뀜
   • CUBE : 조합 가능한 모든 값에 대해 다차원 집계
   • GROUPING SETS : 특정 항목에 대한 소계 계산, GROUP BY의 칼럼 순서와 무관하게 개별적으로 처리함
3. GROUPING
   그룹 함수에서 생성되는 합계를 구분해주는 함수
   소계나 합계가 계산되면 1 아니면 0 반환

GROUP BY ROLLUP (E1,E2)	E1과 E2별 소계 / E1별 소계 / 총합계
GROUP BY CUBE (E1,E2)	E1과 E2별 소계 / E1별 소계 / E2별 소계 / 총합계
GROUP BY GROUPING SETS (E1,E2)	E1별 소계 / E2별 소계

• ‘GROUP BY CUBE (E1,E2)’와 ‘GROUP BY GROUPING SETS (E1,E2,(E1,E2),())’는 동일한 결과 출력

---

6절. 윈도우 함수

6-1. 윈도우 함수(Window Function)

: 여러 행 간의 관계 정의 함수, 중첩 불가, 윈도우 함수로 인한 결과 건수는 그대로임.

 

순위 함수 ★★★

• RANK : 중복 순위 포함 1,1,3,4,5..
• DENSE_RANK : 중복 순위 무시 (중간 순위를 비우지 않음) 1,1,2,3,4...
• ROW_NUMBER : 값에 무관하게 고유한 순위 부여 1,2,3,4,5... (반드시 ORDRER BY 필요!)

 

6-2. 일반집계 함수

: SUM, MAX, MIN, AVG, COUNT

 

6-3. 행 순서 함수

• FIRST_VALUE ↔ LAST_VALUE : 첫 값, 끝 값
• LAG(컬럼,몇개전) OVER (ORDER BY): 이전 행
• LEAD(컬럼,몇개후) OVER (ORDER BY) : 이후 행 (Oracle) 랙릿

※ ‘LEAD(E,A)’는 E에서 A번째 행의 값을 호출하는 형태로도 쓰임 (A의 기본값은 1)

 

 

6-4. 비율 관련 함수

• PERCENT_RANK() : 백분율 순서
• CUME_DIST() : 현재 행 이하 값을 포함한 누적 백분율
• NTILE(A) : 전체 데이터 A등분
• RATIO_TO_REPORT : 총합계에 대한 값의 백분율

 

6-5. 윈도우 함수 문법

SELECT 윈도우함수(A) OVER (PARTITION BY 칼럼 ORDER BY 칼럼 윈도잉절)
FROM 테이블명;

PARTITION BY

• 그룹핑 기준 (Group By구문과 의미적으로 유사함). 윈도우 함수 적용범위는 PARTITION 못넘음
• PARTITJION구문이 없으면 전체 집합을 하나의 파티션으로 정의한 것과 동일함

ORDER BY : 순위 지정 기준

 

윈도잉절 : 함수의 대상이 되는 행 범위 지정

• BETWEEN A AND B : 구간 지정
• N PRECEDING, N FOLLOWING : N번째 앞 행, N번째 뒤 행
• UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING : 첫 행, 끝 행
• CURRENT ROW : 현재 행

 

ROWS, RANGE

: 행, 값 범위 지정 (ORDER BY 칼럼 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

 

---

7절. DCL

7-1. DCL

: 유저를 생성하거나 권한을 제어하는 명령어, 보안을 위해 필요함

• GRANT: 권한 부여 SQL
  >> GRANT 권한 ON 오브젝트 TO 유저명;
• REVOKE: 권한 제거 SQL
  >> REVOKE 권한 ON 오브젝트 FROM 유저명;

 

7-2. 권한(Privileges)

• SELECT, INSERT, UPDATE, DELETE, ALTER, ALL : DML 관련 권한
• REFERENCES : 지정된 테이블을 참조하는 제약조건을 생성하는 권한
• INDEX : 지정된 테이블에서 인덱스를 생성하는 권한

 

7-3. Oracle의 유저

• SCOTT: 테스트용 샘플 유저
• SYS: DBA 권한이 부여된 최상위 유저
• SYSTEM: DB의 모든 시스템 권한이 부여된 DBA

 

7-4. ROLE

권한의 집합, 사용자와 권한 사이에서 중개 역할. 권한을 일일이 부여하지 않고 여러 권한을 부여할 수 있음.

Oracle의 ROLE	권한
CONNECT	CREATE SESSION
RESOURCE	CREATE CLUSTER CREATE PROCEDURE CREATE TYPE CREATE SEQUENCE	CREATE TRIGGER CREATE OPERATOR CREATE TABLE CREATE INDEXTYPE

---

8절. 절차형 SQL

8-1. 절차형 SQL

: 일반적인 개발언어처럼 절차지향적인 프로그램을 작성할 수 있도록 제공하는 기능

• SQL문의 연속적인 실행 및 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈 생성 가능
• PL/SQL (Oracle) : SQL을 확장시켜 절차적 프로그래밍을 가능하게 함

• PL/SQL에서는 DDL문장을 실행할 때 EXECUTE IMMEDIATE를 사용해야한다.
• 블록 구조로 되어있어서 1)기능별로 모듈화 가능 2)블록 내에 DML, 쿼리, IF나 LOOP 등을 사용할 수 있음
• 변수, 상수 등을 선언하여 SQL 문장간 값을 교환한다. 변수, 상수를 사용하여 WHERE절의 조건으로 대입 가능.
• Procedure, User Defined Function, Trigger 객체를 PL/SQL로 작성할 수 있다.
• Procedure 내부에 작성된 절차적 코드는 PL/SQL 엔진이 처리하고 일반적인 SQL 문장은 SQL실행기가 처리한다.
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
• PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
• PL/SQL은 응용 프로그램의 성능을 향상시킨다.
• 여러 SQL문장을 Block으로 한번에 묶고 한번에 Block전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

Declare(선언부) : 블록에서 사용할 변수나 인수에 대한 정의
Begin(실행부) : 처리할 SQL문 정의
Exception(예외 처리부) : 블록에서 발생한 에러 처리 로직 정의, 유일한 선택 항목
T-SQL (SQL Server)

 

8-2. 프로시저(Procedure)

: 내부에 작성된 절차적 코드는 SQL실행기가 처리

일반적으로 SQL문항은 SQL실행기가 처리저장형 프로시저는 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미한다.

 

 

8-3. 사용자 정의 함수

: 절차형 SQL을 로직과 함께 DB 내에 저장해 놓은 명령문 집합RETURN을 통해 반드시 하나의 값 반환 (↔ 프로시저)

저장형 함수는 단독으로 실행되기보다는 다른 SQL문을 통해 호출되고 결과를 리턴하는 보조적인 역할을 한다.

 

 

8-4. 트리거(Trigger)

: DML문이 수행되었을때 자동으로 동작하는 프로그램 (↔ 프로시저는 EXECUTE로 실행함)

 

데이터 무결성과 일관성을 지키기 위한 방법 중 하나.

DCL와 TCL 실행 불가 (↔ 프로시저는 사용 가능함)

데이터베이스에 로그인하는 작업에도 정의할 수 있다.