🍀 SQL 활용

---

☘️ 표준 조인

1. NATURAL JOIN

같은 이름을 가진 컬럼 전체에 대한 등가 조인, USING 조건절이나 ON 조건절 사용 불가, 같은 데이터 유형 컬럼만 조인 가능, 앨리어스나 테이블명 사용 불가

SELECT 컬럼, ... FROM 테이블1 NATURAL JOIN 테이블2

2. INNER JOIN

행에 동일한 값이 있는 컬럼 조인, JOIN의 디폴트 옵션, USING 조건절이나 ON 조건절 필수, CROSS JOIN이나 OUTER JOIN과 동시 사용 불가, 두 테이블에 동일 이름 컬럼이 있을 경우 SELECT절에 앨리어스 필수

SELECT 컬럼, ... FROM 테이블1 A, 테이블2 B WHERE A.컬럼 = B.컬럼

SELECT 컬럼, ... FROM 테이블1 A INNER JOIN 테이블2 B ON A.컬럼 = B.컬럼 

• UISING 조건절 : 같은 이름을 가진 컬럼 중 등가 조인 대상 컬럼 선택, SQL Server에서는 지원 X, 조건절에 앨리어스나 테이블명 불가

SELECT 컬럼, ... FROM 테이블1 A, 테이블2 B USING (컬럼명) 

• ON 조건절 : 다른 이름을 가진 컬럼 간 조인 가능(앨리어스나 테이블명 필수)

SELECT 컬럼, ... FROM 테이블1 A INNER JOIN 테이블2 B ON A.컬럼 = B.컬럼  

3. CROSS JOIN

가능한 모든 조합으로 JOIN

SELECT 컬럼, ... FROM 테이블1, 테이블2

SELECT 컬럼, ... FROM 테이블1 CROSS JOIN 테이블2

4. OUTER JOIN

조인 조건에서 행에 동일한 값이 없는 컬럼 조인, USING 조건절이나 ON 조건절 필수

• LEFT OUTER JOIN : 좌측 테이블 데이터 조회 후 우측 테이블 조인 대상 데이터 조회

SELECT 컬럼, ... FROM 테이블1 A, 테이블2 B A.컬럼 = B.컬럼

SELECT 컬럼, ... FROM 테이블1 A LEFT OUTER JOIN 테이블2 B ON A.컬럼 = B.컬럼

• RIGHT OUTER JOIN : LEFT OUTER JOIN의 반대 개념

• FULL OUTER JOIN : LEFT와 RIGHT OUTER JOIN 포함

SELECT 컬럼, ... FROM 테이블1 A FULL OUTER JOIN 테이블2 B ON A.컬럼 = B.컬럼  

---

☘️ 집합 연산자

1. 집합 연산자

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

2. UNION (합집합)

컬럼 수와 데이터 타입이 모두 동일한 테이블 간 연산만 가능
UNION 은 정렬 O

SELECT 컬럼, ... FROM 테이블 A WHERE 조건절 UNION SELECT 테이블 B WHERE 조건절

• UNION ALL : 중복된 행도 전부 출력하는 합집합, 정렬 X

SELECT 컬럼, ... FROM 테이블 A WHERE 조건절 UNION ALL SELECT 테이블 B WHERE 조건절

3. INTERSECT (교집합)

SELECT 컬럼, ... FROM 테이블 A WHERE 조건절 INTERSECT SELECT 테이블 B WHERE 조건절

4. MINUS, EXCEPT (차집합)

SELECT 컬럼, ... FROM 테이블 A WHERE 조건절 MINUS SELECT 테이블 B WHERE 조건절

---

☘️ 계층형 질의 (Hierarchical Query)

1. 계층형 질의

계층형 데이터를 조회하기 위해 사용

• 계층형 데이터 : 엔터티를 순환관계 데이터 모델로 설계할 때 발생

• CONNECT BY : 트리 형태의 구조로 쿼리 수행 (루트 노드부터 하위 노드의 쿼리를 실행)

  ° START WITH : 시작 조건 지정
  ° CONNECT BY PRIOR : 조인 조건 지정 LEVEL : 검색 항목의 깊이, 최상위 계층의 레벨은 1
  ° CONNECT_BY_ROOT : 최상위 계층 값 표시
  ° CONNECT_BY_ISLEAF : 최하위 계층 값 표시
  ° SYS_CONNECT_BY_PATH : 계층 구조의 전개 경로 표시
  

• CONNECT BY절의 루프 알고리즘 키워드

  ° NOCYCLE : 순환구조의 발생지점까지만 전개
  ° CONNECT_BY_ISCYCLE : 순환구조 발생지점 표시(부모 노드와 자식 노드가 같을 때 1 아니면 0 출력)
  

• LPAD
  계층형 조회 결과를 명확히 하기 위해 사용(LEVEL 값을 이용하여 결과 데이터 정렬)
  

2. SQL Server 계층형 질의

CTE(Common Table Expression)로 재귀 호출

---

☘️ 서브쿼리

하나의 SQL문 안의 SQL문

1. 종류

- 동작 방식에 따른 분류

° 비연관 서브쿼리

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

Access Subquery : 제공자 역할
Filter Subquery : 확인자 역할
Early filter Subquery : 데이터 필터링 역할

° 연관 서브 쿼리

메인쿼리의 결과를 조건이 맞는지 확인하기 위한 목적으로 주로 사용함

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

° 단일 행 서브쿼리 : 실행 결과가 1건 이하인 서브쿼리, 단일 행 비교 연산자와 함께 사용

° 다중 행 서브쿼리 : 실행 결과가 여러 건인 서브쿼리, 다중 행 비교 연산자와 함께 사용

※ 다중행 비교 연산자

1. IN : 서브쿼리의 결과 중 하나의 값이라도 동일하다는 조건
2. ANY : 서브쿼리의 결과 중 하나의 값이라도 만족한다는 조건
3. ALL : 서브쿼리의 모든 결과 값을 만족한다는 조건
4. EXISTS : 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건

° 다중 컬럼 서브쿼리 : 실행 결과로 여러 컬럼 반환, 주로 메인쿼리의 조건과 비교하기 위해 사용(비교하고자 하는 컬럼의 개수와 위치가 동일해야 함)

2. 스칼라 서브쿼리

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

3. 뷰

가상의 테이블, FROM절에 사용하는 뷰는 인라인 뷰라고 함

• 장점

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

4. WITH

서브쿼리를 이용하여 뷰로 사용할 수 있는 구문

---

☘️ 그룹 함수

1. ANSI/ISO 표준 데이터 분석 함수

집계함수, 그룹 함수, 윈도우 함수

2. 그룹 함수

합계 계산 함수, NULL을 빼고 집계함, 결과값 없는 행은 출력 안함

• ROLLUP : GROUP BY로 묶인 컬럼의 소계 계산, 계층 구조로 GROUP BY의 컬럼 순서가 바뀌면 결과 값 바뀜

GROUP BY ROLLUP(A,B)

A와 B별 소계 / A 소계 / 총합계

• CUBE : 조합 가능한 모든 값에 대해 다차원 집계

GROUP BY CUBE(A,B)

A와 B별 소계 / A 소계 / B소계 / 총합계

• GROUPING SETS : 특정 항목에 대한 소계 계산, GROUP BY의 컬럼 순서와 무관하게 개별적으로 처리함

GROUP BY GROUPING SETS(A,B)

A별 소계 / B별 소계

3. GROUPING

그룹 함수에서 생성되는 합계를 구분해주는 함수, 소계나 합계가 계산되면 1 아니면 0 반환

---

☘️ 윈도우 함수

여러 행 간의 관계정의 함수, 중첩 불가

1. 순위 함수

• RANK : 중복 순위 포함
• DENSE_RANK : 중복 순위 무시(중간 순위를 비우지 않음)
• ROW_NUMBER : 단순히 행 번호 표시, 값에 무관하게 고유한 순위 부여

2. 일반 집계 함수

SUM, MAX, MIN, AVG, COUNT

3. 행 순서 함수

• FIRST_VALUE, LAST_VALUE : 첫 값, 끝 값
• LAG, LEAD : 이전 행, 이후 행 랙릿

4. 비율 관련 함수

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

---

☘️ 윈도우 함수 문법

• PARTITION BY : 그룹핑 기준

• ORDER BY : 순위 지정 기준

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

  ° BETWEEN A AND B : 구간 지정

  1. N PRECEDING, N FOLLWING : N번째 앞 행, N번째 뒤 행
  2. UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING : 첫 행, 끝 행
  3. CURRENT ROW : 현재 행

  ° ROWS, RANGE : 행 지정, 값의 범위 지정

---

☘️ DCL

1. DCL : 유저를 생성하거나 권한을 제어하는 명령어

• GRANT : 권한부여

GRANT 권한 ON 오브젝트 TO 유저명

• REVOKE : 권한제거

REVOKE 권한 ON 오브젝트 TO 유저명

2. 권한

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

3. Oracle의 유저

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

4. ROLE
   권한의 집합, 권한을 일일이 부여하지 않고 ROLE로 편리하게 여러 권한을 부여할 수 있음

---

☘️ 절차형 SQL

절차형 SQL

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

• SQL 문의 연속적인 실행 및 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈 생성 가능

• PL/SQL
  블록구조 : 블록 내에 DML, 쿼리, IF나 LOOP 등을 사용할 수 있음

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

• T-SQL

사용자 정의 함수

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

트리거

DML문이 수행되었을때 자동으로 동작하는 프로그램
DCL과 TCL 실행 불가

프로시저

프로시저는 트리거와 다르게 EXECUTE로 실행
DCL과 TCL 실행 가능