SQLD 정리 - 2과목 SQL 활용

표준조인

• 여러 RDBMS마다 SQL문법 차이가 있을 경우 호환성 문제 발생
• 즉 어떤 DRBMS에서도 작동하는 JOIN

INNER JOIN

• JOIN 조건에 충족하는 데이터만 출력되는 방식
• JOIN 조건을 OR 절을 사용하여 작성

위 표를 INNER JOIN할 경우

OUTER JOIN

• JOIN 조건에 충족하지 않아도 출력될 수 있는 방식

// 기본 조건
SELECT * FROM A1, A2
WHERE A1.COL1 = A2.COL1

• LEFT OUTER JOIN
  • SQL에서 왼쪽에 표기된 테이블의 데이터가 무조건 출력되는 조인
  • JOIN 데이터가 없는 경우 NULL 출력

SELECT * FROM A1 LEFT OUTER JOIN A2
ON A1.COL1 = A2.COL1

• RIGHT OUTER JOIN
  • SQL에서 오른쪽에 표기된 테이블의 데이터가 무조건 출력되는 조인
  • JOIN 데이터가 없는 경우 NULL 출력

SELECT * FROM A1 RIGHT OUTER JOIN A2
ON A1.COL1 = A2.COL1

 

• FULL OUTER JOIN
  • LEFT와 RIGHT OUTER JOIN의 중복을 제외한 합집합이 출력되는 조인

SELECT * FROM A1 FULL OUTER JOIN A2
ON A1.COL1 = A2.COL1

NATURAL JOIN

A 테이블과 B테이블에서 같은 이름을 가진 칼럼들이 모두 동일한 데이터를 가질 때 JOIN되는 방식

CROSS JOIN

A테이블과 B테이블 사이에 JOIN 조건이 없을 경우, 조합할 수 있는 모든 경우를 출력하는 방식

SORT MERGE JOIN

테이블을 정렬한 후에 테이블을 병합하면서 조인을 실행

---

집합연산자

각 쿼리의 결과 집합을 가지고 연산하는 명령어

• UNION ALL : 각 쿼리의 결과 집합의 합집합 (중복 행 그대로 출력)
• UNION : 각 쿼리의 결과 집합의 합집합 (중복 행 한 줄로 출력)
• INTERSECT : 각 쿼리의 결과 집합의 교집합 (중복 행 한줄로 출력)
• MINUS/EXCEPT : (앞 쿼리 결과 집합 - 뒤 쿼리 결과 집합)한 차집합 ( 중복 행 한줄로 출력)

---

서브쿼리

하나의 쿼리 안에 존재하는 또 다른 쿼리

• SELECT 절에 위치 시 : 스칼라 서브쿼리
  • 주로 SELECT 절에 위치하지만 컬럼이 올 수 이는 대부분 위치에서 사용
• FROM 절에 위치 시 : 인라인 뷰
  • FROM 절 등 테이블명이 올 수 있는 위치에 사용
• WHERE 절, HAVING 절에 위치 시 : 중첩 서브쿼리
  • 비연관 서브쿼리 : 메인 쿼리와 관계를 맺고 있지 않음
  • 연관 서브쿼리 : 메인 쿼리와 관계를 맺고 있음

뷰

• 특정 SELECT 문에 이름을 붙여 재사용이 가능하도록 저장해놓은 오브젝트
• 보안성, 독립성, 편리성의 특징

---

그룹 함수

• 집계 함수 : COUNT, SUM, AVG, MAX, MIN
• 총계 함수

ROLLUP : 전체 합계와 소그룹 간의 소계를 계산하는 함수

SELECT ID, 월, SUM(금액) AS 금액
FROM 매출
GROUP BY ROLLUP(ID, 월)

// ID별 합계, 전체 합계를 조회

CUBE : 다차원적인 소계를 계산. ROLLUP과 달리 GROUB BY 절에 명시한 모든 칼럼에 대해 소그룹 합계를 계산

SELECT ID, 월, SUM(금액) AS 금액
FROM 매출
GROUP BY CUBE(ID, 월)

// 전체 합계, 월별 합계, ID별 합계를 조회

GROUOING SETS : 특정 항목에 대한 소계를 계산하는 함수

SELECT ID, 월, SUM(금액) AS 금액
FROM 매출
GROUP BY GROUPING SETS(ID, 월)

// ID별 합계, 월별 합계를 조회

// GROUP BY GROUPING SETS (A, B, (A, B), ());
// 위 형태의 경우 A(A)와 B(B)별 합계, A와 B에 대한 전체(A,B) 합계, 전체() 합계 생성

---

윈도우 함수

• 순위 함수 : 순위를 기준으로 오름차순 혹은 내림차순으로 출력시 ORDER BY 사용
  • RANK : 중복값은 중복 등수로 적용, 중복된 등수는 건너뜀(1위, 1위, 3위, 4위...)
  • DENSE_RANK : 중복값은 중복 등수로 적용, 중복된 등수를 건너뛰지 않음 (1위, 1위, 2위, 3위 ...)
  • ROW_NUMBER : 중복값에 상관 없이 고유 등수 부여 (1위, 2위, 3위, 4위...)
• 집계 함수 : SUM, MAX, MIN, AVG, COUNT
• 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
• 비율 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

기타

• UNION ALL : 합집합 반환
• MINUS : 차집합 반환
• CONNECT BY : 계층형 구조를 탐색하는 질의

SELECT 칼럼, LEVEL AS 계층 깊이
FROM 테이블
START WTIH 시작 위치 조건
CONNECT BY PRIOR 하위코드 = 상위코드

// 정렬시 계층형 구조까지 보존하려면 ORDER SIBLINGS BY 절 사용
//  ORDER SIBLINGS BY 절을 사용하면 현재 노드의 형제 노드 사이에서 정렬을 수행 (테이블 X)
// MO CYCLE 옵션 사용시 중복 경로 발생시 쿼리 종료