[Oracle]2️⃣ 중급 단계 (고급 SQL 및 최적화)

2️⃣ 중급 단계 (고급 SQL 및 최적화)

중급 단계에서는 여러 테이블을 결합하는 조인(JOIN), 그룹화(GROUP BY), 서브쿼리(서브 SELECT 문), 고급 함수 활용, 인덱스 및 성능 최적화를 학습합니다.


---

✅ 1. 조인 (JOIN) 개념 및 활용

조인은 두 개 이상의 테이블을 연결하여 원하는 데이터를 조회하는 방법입니다.

(1) INNER JOIN (내부 조인)

두 테이블에서 공통된 값이 있는 경우만 반환합니다.

SELECT e.first_name, e.last_name, d.department_name  
FROM employees e  
INNER JOIN departments d ON e.department_id = d.department_id;

📌 설명: employees 테이블과 departments 테이블을 department_id 기준으로 조인하여 직원의 이름과 부서명을 가져옵니다.

✅ 출력 예시:
| first_name | last_name | department_name |
|------------|------------|----------------|
| John       | Doe        | IT             |
| Jane       | Smith      | HR             |


---

(2) LEFT JOIN (왼쪽 조인) & RIGHT JOIN (오른쪽 조인)

LEFT JOIN → 왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블에 일치하는 데이터가 없으면 NULL을 반환

RIGHT JOIN → 오른쪽 테이블의 모든 행을 반환하고, 왼쪽 테이블에 일치하는 데이터가 없으면 NULL을 반환


SELECT e.first_name, e.last_name, d.department_name  
FROM employees e  
LEFT JOIN departments d ON e.department_id = d.department_id;

📌 설명: employees 테이블의 모든 직원 정보를 유지하면서, departments에 없는 부서는 NULL로 표시

✅ 출력 예시:
| first_name | last_name | department_name |
|------------|------------|----------------|
| Alice      | Brown      | NULL           |


---

(3) FULL JOIN (완전 외부 조인)

양쪽 테이블의 모든 행을 반환하고, 매칭되지 않는 값은 NULL 처리됩니다.

SELECT e.first_name, e.last_name, d.department_name  
FROM employees e  
FULL JOIN departments d ON e.department_id = d.department_id;


---

✅ 2. 서브쿼리 (Subquery) 활용

서브쿼리는 다른 쿼리 내부에서 실행되는 쿼리입니다.

(1) 단일 행 서브쿼리

SELECT first_name, last_name  
FROM employees  
WHERE salary = (SELECT MAX(salary) FROM employees);

📌 설명: 전체 직원 중에서 최고 연봉을 받는 직원을 조회

✅ 출력 예시:
| first_name | last_name |
|------------|------------|
| Jane       | Smith      |


---

(2) 다중 행 서브쿼리 (IN, ANY, ALL, EXISTS)

✅ 부서별 평균 급여보다 높은 급여를 받는 직원 조회

SELECT first_name, last_name, salary, department_id  
FROM employees e  
WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

📌 설명: 각 부서의 평균 급여보다 높은 급여를 받는 직원을 조회

✅ 특정 조건을 만족하는 데이터 존재 여부 확인 (EXISTS)

SELECT first_name, last_name FROM employees e  
WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id);

📌 설명: 부서 정보가 존재하는 직원만 조회


---

✅ 3. 그룹화 및 집계 (GROUP BY & HAVING)

GROUP BY → 동일한 값끼리 묶어 집계

HAVING → 그룹화된 데이터에 조건을 적용


SELECT department_id, AVG(salary) AS avg_salary  
FROM employees  
GROUP BY department_id  
HAVING AVG(salary) > 5000;

📌 설명: 부서별 평균 급여가 5000 이상인 부서만 조회

✅ 출력 예시:
| department_id | avg_salary |
|--------------|------------|
| 10           | 6200       |


---

✅ 4. 고급 함수 활용

(1) 변환 함수 (TO_CHAR, TO_DATE, TO_NUMBER)

✅ 날짜를 문자열로 변환

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS current_time FROM dual;

✅ 문자열을 날짜로 변환

SELECT TO_DATE('2025-03-29', 'YYYY-MM-DD') FROM dual;


---

✅ 5. 인덱스와 성능 최적화

🔹 인덱스(INDEX)는 데이터 검색 속도를 향상시키는 구조

(1) 인덱스 생성

CREATE INDEX idx_emp_lastname ON employees(last_name);

📌 설명: last_name 열에 대한 인덱스를 생성하여 검색 속도를 개선

(2) 실행 계획 확인 (EXPLAIN PLAN)

EXPLAIN PLAN FOR SELECT * FROM employees WHERE last_name = 'Smith';  
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

📌 설명: SQL 문이 어떤 방식으로 실행되는지 분석


---

🔹 중급 단계 핵심 요약

✅ 조인(JOIN) → 여러 테이블을 결합하여 원하는 데이터 조회
✅ 서브쿼리(Subquery) → 특정 값을 계산하거나 조건에 맞는 데이터를 조회
✅ GROUP BY & HAVING → 데이터를 그룹화하고 조건을 적용
✅ 고급 함수(TO_CHAR, TO_DATE, TO_NUMBER) → 데이터 변환 및 형식 변경
✅ 인덱스(Index)와 성능 최적화 → 데이터 검색 속도를 향상

중급 단계가 끝나면 PL/SQL, 트리거, 성능 튜닝을 배우는 고급 단계로 넘어갈 수 있습니다.
추가로 설명이 필요한 부분이 있으면 말씀해주세요!