sql220412.txt

조인?
관계형 데이터 베이스란 테이블들이 관계를 맺고 조작되는 원리를 가짐
테이블에는 각 유형에 맞는 데이터가 저장되고 테이블은 특정한 규칙에 따라 상호관계를 가짐
이를 위해 사용하는 기법이 조인
조인은 한 개 이상의 테이블과 테이블을 연결하여 사용하는 기법을 말함

조인 기법
동등조인 조인 조건이 정확히 일치하는 경우에만 결과 출력
외부조인 조인 조건이 정확히 일치하지 않아도 모든 결과 출력
자체조인 자체 테이블에서 조인하고자 할 때 사용

곱집합, 비동등

SELECT 테이블1.열1, 테이블2.열2
FROM 테이블1, 테이블2
WHERE 테이블1.열1 = 테이블2.열2

SELECT절에는 출력할 열 
FROM 접근하려는 테이블의 이름
WHERE 절에는 조인 조건을 기술

조회의 명확성을 위해서 열이름 앞에는 테이블 이름을 붙임
(테이블들에 동일한 이름이 존재할 수 있기 때문에)

SELECT *
FROM hr.employees A, hr.departments B
WHERE A.department_id = B.department_id;

부서에 대한 상세 정보를 보기 위해서 employee 테이블과 departments 테이블의
각각 키인 department_id를 조인한 결과임 
값이 같은 것끼리 연결되어 조회된 것을 볼 수 있음
열 전체를 보기 위해 *를 사용했음
여기에 조건을 추가해보자
부서의 상세 위치 정보를 확인해보자

SELECT A.employee_id A.department_id, B.department_name, C.location_id, C.city
FROM hr.employees A, hr.departments B, hr.locations C
WHERE A.department_id = B.department_id
AND B.location_id = C.location_id;

employees테이블과 departments 테이블은 department_id로 조인했고
AND 연산자를 사용하여 departments 테이블과 locations 테이블을
location_id 로 조인했음 실행결과를 보면 조인된 열의 데이터값을 기준으로 행들이 연결된 것을 
볼 수 있음 이처럼 AND 연산자를 사용하여 조인 조건식을 계속 이어나가면서
원하는 정보를 조회할 수 있게 됨

# 외부 조인이란? 모든 데이터를 연결함

동등 조인은 데이터값이 정확히 일치하는 경우에만 결과를 출력
데이터값이 일치하지 않으면 조회가 되지 않음

SELECT COUNT(*)
FROM hr.employees A, hr.departments B
WHERE A.department_id = B.department_id;

SELECT COUNT(*)
FROM hr.employees A;

1건의 차이가 나는 이유는 일치하지 않는 1건이 누락되었기 때문

외부 조인은 조건을 만족하지 않는 행도 모두 출력하는 기법
1. 동등조건을 만족하지 못해서 누락되는 행을 출력하기 위해 사용
2. 외부조인은 + 기호를 사용함
3. + 기호는 조인할 행이 없는 즉 데이터가 부족한 테이블 열이름 뒤에 기술함
4. + 기호는 외부 조인하려는 한쪽에만 기술할 수 있고 양쪽에는 기술할 수 없음
5. + 기호를 붙이면 데이터 값이 부족한 테이블에 null 값을 가지는 행이 생성되어
데이터 값이 충분한 테이블의 행들이 null행에 조인됨

SELECT *
FROM hr.employees A, hr.departments B
WHERE A.department_id = B.department_id(+)
ORDER BY A.employee_id;

employees 테이블과 departments 테이블을 department_id로 외부조인하여
department_id가 null인 값도 함께 출력하였음
이처럼 외부 조인은 일치하지 않는 데이터를 출력할 때 유용함

SELECT *
FROM hr.employees A, hr.departments B
WHERE A.department_id(+) = B.department_id
ORDER BY A.employee_id;

employee 테이블의 데이터가 부족하고 departments 테이블의 데이터가 충분하다는 의미가 되기
때문에 department 테이블의 일치하지 않는 department_id (데이터가 충분한 쪽) 가 employee
테이블의 부족한 데이터에 대해 null로 조인되었음 
따라서 같은 테이블 안의 열 department _name 정보도 같이 출력되는 결과를 확인할 수 있게 됨
> departments 테이블의 department_id에 대해 동등 조인 되지 못한 데이터도 모두 출력하겠다는
의미가 됨

자체 조인 : 자기 자신의 데이터와 연결

employees 테이블의 정보 중에는 manager_id 열이 있는데 이것은 담당 매니저의 정보를 담고 있음
데이터 값으로 담당 매니저의 employee_id 를 담고 있음
담당 매니저가 누구인지 조회하려면 결국 다시 employees 테이블을 조인해야 함
이렇게 자기 자신의 테이블을 조인하는 것을 자체조인이라고 함

employees 테이블에서 직원별 담당 매니저가 누구인지 조회

SELECT A.employee_id, A.first_name, A.last_name, A.manager_id
FROM hr.employees A, hr.employees B
WHERE A.manager_id = B.employee_id;SELECT A.employee_id, A.first_name, A.last_name, A.manager_id
FROM hr.employees A, hr.employees B
WHERE A.manager_id = B.employee_id;

자체 조인은 자기 자신의 테이블을 이용하여 조인함
같은 테이블을 사용하므로 각기 다른 별명을 사용하여 마치 서로 다른 두 개의 테이블을 조인하는 것처럼 보임 
자체 조인을 할 때 테이블은 동일한 열로 구성되기 때문에 조회할 때 반드시 별명.열이름의 형태로
명확하게 구별하여 조회함

SELECT A.employee_id, A.first_name, A.last_name, A.manager_id,
       B.first_name || ' ' || B.last_name manager_name
FROM hr.employees A, hr.employees B
WHERE A.manager_id = B.employee_id;

# 서브쿼리 : select 안에 select
서브쿼리는 select문 안에서 보조로 사용하는 또 다른 select문임 
서브쿼리는 select문을 효율적으로 작성할 수 있게 도와줌
복잡한 select 문을 작성할 때는 거의 필수로 사용되는 기법

1. 서브쿼리는 괄호로 묶어서 만듦 메인은 괄호가 없음
2. 메인 쿼리와 서브 쿼리를 연결하기 위해서는 단일행 연산자나 다중행 연산자를 사용함
3. 메인 쿼리와 서브쿼리 연결은 연산자에 따라 의미가 다름
4. 메인 쿼리는 연산자 왼쪽에 서브쿼리는 연산자 오른쪽에 기술함
5. 실행은 서브 쿼리부터 실행되고 메인쿼리가 실행됨
6. 서브쿼리의 서브쿼리 형태로도 계속 중첩하여 sql문을 작성할 수도 있음

단일행 서브쿼리 - 하나의 행을 검색하는 서브쿼리
다중행 서브쿼리 - 하나 이상의 행을 검색하는 서브쿼리
다중열 서브쿼리 - 하나 이상의 열을 검색하는 서브쿼리

1. 단일행 서브쿼리
단일행 서브쿼리는 서브쿼리로 얻은 한 개의 행 결과값을 메인 쿼리로 전달하는 서브쿼리임
조건식의 WHERE 절에 기술되는 열의 개수와 데이터타입은 메인 쿼리와 서브쿼리가 서로 같아야 함

employees 테이블의 last_name 이 'De Haan' 인 직원과 salary가 동일한 직원이 누가 있는지
단일행 서브쿼리를 이용하여 출력해보자

SELECT *
FROM hr.employees A
WHERE A.salary = (
                    SELECT salary
                    FROM hr.employees
                    WHERE last_name = 'De Haan'
                    )
;

De Haan의 salary는 17000으로 단일행임

즉 서브 쿼리의 결과값 17000을 메인 쿼리의 조건값으로 전달했음
메인 쿼리는 그 값을 받아 salary 가 17000인 모든 직원을 출력했음

SELECT salary
FROM hr.employees
WHERE last_name = 'De Haan'

SELECT *
FROM hr.employees A
WHERE A.salary = 17000;

물론 조회를 해서 17000이라는 값을 알아내고 직접 입력하여 조회할 수도 있음
하지만 이렇게 하면 select 를 두번 작성하여 실행해야 하고 논리적으로도 분리가 됨
이러한 비효율성을 최소화하고 논리적으로 사용할 수 있도록 함

SELECT *
FROM hr.employees A
WHERE A.salary = (
SELECT salary
FROM hr.employees
WHERE last_name = 'Cambrault');

single-row subquery returns more than one row

Cambrault는 2개 존재함
last_name이 'Cambrault'인 직원이 두 명 존재하고 각각 다른 두 개의 결과가 전달됨
하지만 단일행 서브쿼리이기 때문에 오라클이 오류를 출력하게 됨
이런 경우 다음의 다중행 서브쿼리를 사용해야 함

다중행 서브쿼리
사용법은 단일행 서브쿼리와 같음
다중행 서브쿼리는 하나 이상의 결과 행을 메인 쿼리에 전달하는경우 사용하고 다중행 연산자를 사용

IN / NOT IN / EXISTS / ANY / ALL ...

실습 ] employee 테이블에서 department_id 별로 가장 낮은 salary가 얼마인지 찾고
찾아낸 salary에 해당하는 직원이 누구인지 다중행 서브쿼리로 찾아보자

SELECT MIN(salary)
FROM hr.employees A
GROUP BY department_id
;

MIN(SALARY)
2100
6500
8300
17000
2500
10000
7000
4400
6000
4200
6900
6100

SELECT *
FROM hr.employees A
WHERE A.salary IN
(SELECT MIN(salary)
FROM hr.employees A
GROUP BY department_id)
;

그룹함수 min을 사용하여 department_id별 최저 salary를 조회하는 서브쿼리를 이용하여
다중행을 먼저 조회하고 결과를 메인 쿼리로 전달함
다중행 서브쿼리이기 때문에 = 연산자를 사용하지 않고 다중행 연산자인 IN 을 사용했음
IN 연산자는 하나 이상의 행을 받아서 처리함

3. 다중열 서브쿼리
다중열 서브쿼리는 메인쿼리와 서브쿼리를 비교하는 WHERE 조건식에서 비교되는 열이
여러 개일 때 사용하는 서브쿼리임

# employees 테이블에서 job_id 별로 가장 낮은 salary가 얼마인지 찾아보고
찾아낸 job_id 별로 salary에 해당하는 직원이 누구인지 다중열 서브쿼리를 사용하여 찾아보자   

SELECT job_id, MIN(salary)
FROM hr.employees
GROUP BY job_id
;

JOB_ID	MIN(SALARY)
AD_VP	17000
FI_ACCOUNT	6900
PU_CLERK	2500
SH_CLERK	2500
HR_REP	6500
PU_MAN	11000
AC_MGR	12008
ST_CLERK	2100
AD_ASST	4400
IT_PROG	4200
SA_MAN	10500
AC_ACCOUNT	8300
FI_MGR	12008
ST_MAN	5800
AD_PRES	24000
MK_MAN	13000
SA_REP	6100
MK_REP	6000
PR_REP	10000

SELECT *
FROM hr.employees A
WHERE (A.job_id, A.salary) IN
                                (SELECT job_id, MIN(salary)
                                FROM hr.employees
                                GROUP BY job_id)
;

그룹함수 MIN을 사용하여 job_id 별 최저 salary를 찾고 해당 값을 다중 열 서브쿼리를 통해
메인 쿼리에 전달하고 해당 직원이 누구인지 찾게되는 select 구문임
다중 열과 동시에 다중행이기 때문에 IN연산자를 사용해야 함
다중 열 서브쿼리를 사용하려면 조건식의 메인 쿼리와 서브쿼리의 각 열이 
일대일로 대응해야 하며 데이터타입도 동일해야 함

FROM절의 서브 쿼리
FROM절에는 테이블 뿐만 아니라 서브쿼리도 사용할 수 있음
FROM절에 서브쿼리를 사용하면 특정 조건식을 갖는 SELECT문을 테이블처럼 사용할 수 있음
이것을 통해서 SELECT문을 효율적이고 간결하게 작성할 수 있게 됨
마치 가상테이블과 같은 역할을 한다고 해서 VIEW와 비슷한 역할이라서 INLINE-VIEW라고도 부름

직원들 중에서 department_name 이 IT인 직원의 정보를 인라인 뷰를 통해 출력해보자

SELECT *
FROM hr.employees A,
(SELECT department_id, department_name
FROM hr.departments
WHERE department_name = 'IT') B
WHERE A.department_id = B.department_id
;

departments 테이블에서 department_name이 IT인 데이터 값을 조회하는
SELECT문을 마치 테이블처럼 FROM 절에 기술하였음
이것은 결국 department_id가 60이라는 테이블과 같다고 할 수 있음
이 서브쿼리에 B라는 별명을 주어 employees 테이블과 department_id로 동등 조인하였고
따라서 IT부서에 속한 모든 직원을 출력하였음
서브쿼리를 from절에 기술하면 select구문을 더욱 정교하게 작성할 수 있게 됨

DML (Data Manipulation Language) - 데이터 조작어
데이터 삽입 / 갱신 / 삭제
DDL (Data Definition Language) - 데이터 정의어
데이터베이스 생성 / 테이블 생성