7. SQL의 활용(1)

1. 서브쿼리의 특징과 활용 

(1) 서브쿼리(Subquery)의 개념과 종류 

• 서브쿼리의 개념
  • 서브쿼리(Subquery)란? 
    • 메인 쿼리 내부에 포함된 하위 쿼리
    • 하나의 SQL문 안에 포함된 또 다른 SQL문
• 서브쿼리의 종류
  • Nested Subquery 
    • WHERE 절에 위치
    • 1개 이상의 칼럼 또는 1개 이상의 행을 반환 

      SELECT name, position FROM t_player
      WHERE team_id =
      (SELECT team_id FROM t_player WHERE name = 'Ruby')
      ORDER BY name;

• 서브쿼리의 종류
  • 연관 서브쿼리
    • 메인 쿼리와 관계가 있음(메인 쿼리의 칼럼이 존재함)
  • 비연관 서브쿼리 
    • 메인 쿼리와 관계가 없음 
  • Inline View
    • FROM 절에 위치
    • 뷰(View)와 동일한 방식으로 사용(임시 뷰)
  • Scalar SubQuery 
    • SELECT 절에 위치
    • 단일 컬럼, 단일 행을 반환

> 반환 데이터 형태에 따라 '단일행 서브쿼리, 다중행 서브쿼리, 다중컬럼 서브쿼리'로 구분할 수 있음

2. JOIN의 특징과 활용

(1) 조인(JOIN)의 개념과 유형

• JOIN의 개념 
  • 둘 이상의 테이블을 논리적 관계를 기준으로 연결하여 결과 집합을 만드는 기법
  • 하나의 SQL문으로 여러 테이블의 데이터를 연관하여 조회하는 기법
• JOIN의 유형
  • EQUI JOIN
    • 테이블 간 컬럼 값들이 정확히 일치하는 경우에 사용 
    • 대부분 PK와 FK 관계를 기반으로 함 
    • 형식
      • SELECT 컬럼(들) FROM 테이블1 A, 테이블2 B WHERE A.컬럼명=B.컬럼명 
  • NON-EQUI JOIN 
    • 테이블 간의 조인 조건이 동등 비교(=)가 아닌 다른 비교 연산자를 사용하는 조인 방식
      • BETWEEN, >, >=, <, <=, != 등의 연산자
  • INNER JOIN
    • 행에 동일한 값이 있는 컬럼 조인 (가장 일반적으로 사용)
    • 조건에 충족되는 데이터만 출력
    • 특징
      • USING 혹은 ON 조건 절 필수
      • CROSS JOIN 혹은 OUTER JOIN과 동시에 사용할 수 없음
      • 동일한 컬럼명이 있을 경우 ALIAS(별칭) 사용
  • NATURAL JOIN
    • 두 테이블 간에 같은 이름을 가진 모든 컬럼을 자동으로 매칭하여 조인하는 방식
  • OUTER JOIN 
    • 상대 테이블에서 대응되는 행을 찾지 못하거나, NULL 값이 포함된 행들을 다루기 위한 조인
    • LEFT OUTER JOIN
      • 왼쪽의 테이블을 기준으로 결과를 생성
      • 왼쪽 테이블의 행들을 모두 표시한 후
      • 대응되는 행이 없을 경우 NULL로 대체
    • RIGHT OUTER JOIN 
      • 오른쪽 테이블을 기준으로 결과를 생성
      • 오른쪽 테이블의 행들을 모두 표시한 후
      • 대응되는 행이 없을 경우 NULL로 대체
    • FULL OUTER JOIN
      • 양쪽 테이블 모두를 기준으로 결과를 생성 
      • 대응되는 행이 없을 경우 양쪽 모두 NULL로 대체
    • CROSS JOIN
      • 연결된 테이블의 Cartesian Product를 반환 (모든 조합)

# EQUI JOIN
SELECT user_t.name USER_NAME, team_t.name
TEAM_NAME FROM user_t, team_t
WHERE user_t.team_id = team_t.team_id;



# NON-EQUI JOIN
SELECT e.employee_name, e.salary, s.grade
FROM employees e
JOIN salary_grades s ON e.salary BETWEEN s.lower_salary
AND s.upper_salary;



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



# NATURAL JOIN
SELECT employee_id, first_name, last_name, department_name
FROM employees
NATURAL JOIN departments;



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

• JOIN의 유형: 수행 방식에 따른 유형

  • SELF JOIN 
    • 동일한 테이블을 두 번 이상 참조하여 조인하는 방식 (별칭 사용 필수)
  • NESTED LOOP JOIN
    • 선행 테이블을 하나씩 접근하며 추출한 값으로 연결할 테이블을 조인
• • SORT MERGE JOIN
    • 대상 테이블의 처리 범위를 각자 접근 및 정렬한 결과를 차례로 스캔하며 연결 조건으로 머지 (Merge)

3. View의 특징과 활용

(1) 뷰(View)의 개념과 특징

• View의 개념
  • 하나 이상의 기본 테이블이나 다른 뷰를 이용하여 생성된 가상의 테이블
  • 장점: 보안성, 편의성
• View의 특징
  • 데이터딕셔너리에 뷰의 정의만 저장
  • 데이터를 위한 저장공간 할당은 안 됨
  • View의 형식
    • 생성
      • CREATE [OR REPLACE] VIEW 뷰이름 [(컬럼1, 컬럼2, ...)]
      • AS SELECT 문
      • [WITH CHECK OPTION [CONSTRAINT 제약조건명]]
      • [WITH READ ONLY]
    •  삭제
      • DROP VIEW 뷰이름

CREATE VIEW other_view
AS SELECT name, age, deptno FROM user_t;

종류	설명
단순 뷰 (Simple View)	- 하나의 기본 테이블에 의해 정의된 뷰로 기본 테이블과 동일한 DML 문 사용 - 뷰에 대한 무결성 제약조건도 기본 테이블의 제약조건이 적용됨
복합 뷰 (Complex View)	- 두 개 이상의 기본 테이블로 구성된 뷰 - 무결성 제약조건, 표현식, GROUP BY 사용 유무에 따라 DML 명령문을 제한적으로 사용
인라인 뷰(Inline View)	- FROM 절에서 참조하는 테이블의 크기가 클 경우, 필요한 행과 컬럼만으로 구성된 집합을 재정의하여 질의문을 효율적으로 구성 - FROM 절에서 서브쿼리를 사용하여 생성한 임시 뷰로 SQL 쿼리 실행 범위 내에서만 존재하는 임시 뷰