Data 결합

✒️ 2025-05-28 16:43 내용 수정

다중 테이블연산과 VIEW를 이용한 테이블 결합

• SQL 편집기 작동은 DBeaver#단축키 참고
• 다중 테이블 연산, VIEW 참고.
• HR에서 제공하는 기본 테이블들을 이용하면 쉽게 데이터 검색 연습을 할 수 있다.

1. SUBQUERY 사용

1) WHERE절

• EMPLOYEES 테이블에서 이름이 'Diana'인 사람보다 입사일이 빠른 사원들의 정보를 사번, 이름, 입사일로 출력해보자.(입사일을 오름차순으로 정렬까지 해본다.)
• SUBQUERY를 사용하기 전 문제의 내용을 따라 단계별로 접근해본다.
  • 먼저 EMPLOYEES 테이블에서 이름이 'Diana'인 사람의 입사일을 확인한다.

SELECT HIRE_DATE 
FROM EMPLOYEES
WHERE FIRST_NAME = 'Diana'

2007-02-07 00:00:00.000

• 다음으로 2007-02-07 보다 입사일이 빠른 사원들의 정보를 사번, 이름, 입사일로 입사일 오름차순 기준으로 정렬해본다.

SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE 
FROM EMPLOYEES
WHERE HIRE_DATE < TO_DATE('2007-02-07', 'YYYY-MM-DD')
ORDER BY HIRE_DATE;

• 이제 두 query문을 합친다. 두 번째 query문의 날짜 조건은 첫 번째 query문의 결과이므로, 두 번째 query문의 WHERE 조건절 중에서 TO_DATE('2007-02-07', 'YYYY-MM-DD') 부분을 첫 번째 query문으로 바꾼다.

SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE 
FROM EMPLOYEES
WHERE HIRE_DATE < (SELECT HIRE_DATE 
					FROM EMPLOYEES
					WHERE FIRST_NAME = 'Diana')
ORDER BY HIRE_DATE;

2) HAVING절

-- EMPLOYEES 테이블에서 직종별 평균 급여를 출력하되, 
-- 평균 급여가 Peter Tucker 사원보다 크고,
-- 직종에 'MAN'를 포함하는 경우만 출력
SELECT JOB_ID, AVG(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE '%MAN%'
GROUP BY JOB_ID
HAVING AVG(SALARY) > (SELECT SALARY				
					  FROM EMPLOYEES
					  WHERE FIRST_NAME = 'Peter' AND LAST_NAME = 'Tucker');

3) FROM절

-- EMPLOYEES 테이블과 DEPARTMENTS 테이블을 이용해서
-- 부서 이름이 'Sales'인 부서에서 입사일이 2006년 이후인 직원 정보 출력
SELECT *
FROM (SELECT *
	  FROM EMPLOYEES e JOIN DEPARTMENTS d
	  ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
	  WHERE DEPARTMENT_NAME = 'Sales')
WHERE HIRE_DATE < TO_DATE('2006-01-01', 'YYYY-MM-DD');

• EMPLOYEES의 모든 컬럼에다가 부서 이름 컬럼까지 포함된 테이블 영역에서 특정 입사일 조건을 만족하는 직원의 전체 정보를 확인할 수 있다.
  

4) SELECT

-- EMPLOYEES 테이블과 DEPARTMENTS 테이블을 이용해서
-- 각 부서별 평균 급여와 전체 급여 평균을 출력
SELECT D.DEPARTMENT_NAME, AVG(E.SALARY) AS DEP_AVG,
		(SELECT AVG(SALARY)
		 FROM EMPLOYEES) AS TOTAL_AVG
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME;

• TOTAL_AVG 컬럼이 모든 행에 같은 값으로 들어가 있다.
  

2. JOIN을 사용한 여러 테이블 조합

• HK 계정에서 제공하는 테이블들을 이용해 여러 정보를 얻어본다.

• EMPLOYEES, DEPARTMENTS, LOCATION은 PK와 FK로 각각 연결되어 있는 관계이다.
  

• DEPARTMENTS, LOCATIONS, COUNTRIES는 PK와 FK로 각각 연결되어 있는 관계이다.
  

1) INNER JOIN

-- 위의 테이블들을 이용해서 사원의 이름, 부서 이름, 도시, 국가를 출력한다.
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, L.CITY, C.COUNTRY_ID
FROM EMPLOYEES e JOIN DEPARTMENTS d 
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN LOCATIONS l 
ON D.LOCATION_ID = L.LOCATION_ID
JOIN COUNTRIES c 
ON L.COUNTRY_ID = C.COUNTRY_ID;

2) OUTER JOIN

2-1) LEFT OUTER JOIN

-- EMPLOYEES 테이블과 DEPARTMENTS 테이블로
-- 사원이 어느 부서에 있는지 조회
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES e LEFT OUTER JOIN DEPARTMENTS d 
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

• 사원 중 Kimberely만 부서가 존재하지 않아 NULL값이 나온다.
  

2-2) RIGHT OUTER JOIN

-- 위의 정보를 RIGHT OUTER JOIN을 써서 비교해보기
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES e RIGHT OUTER JOIN DEPARTMENTS d 
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

• 몇몇 부서에는 사원이 없어 NULL 값이 나온다.
  

2-3) FULL OUTER JOIN

-- FULL OUTER JOIN을 써서 위의 두 결과와 비교해보기
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES e FULL OUTER JOIN DEPARTMENTS d 
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

• 모든 사원과 모든 부서 이름이 나오며, 부서가 없는 Kimberely 사원과, 사원이 없는 몇몇 부서들도 모두 확인할 수 있다.
  

3. UNION을 사용한 테이블 결합

1) UNION

-- EMPLOYEES 테이블과 DEPARTMENTS 테이블을 이용해서
-- 사원 이름과 부서 이름의 모든 값을 출력
SELECT E.FIRST_NAME FROM EMPLOYEES e 
UNION
SELECT D.DEPARTMENT_NAME FROM DEPARTMENTS d
ORDER BY FIRST_NAME;

• 부서 이름과 사원 이름이 모두 섞여 있고, 중복된 내용이 없다.
  

2) UNION ALL

-- EMPLOYEES 테이블과 DEPARTMENTS 테이블을 이용해서
-- 사원 이름과 부서 이름의 모든 값을 출력
SELECT E.FIRST_NAME FROM EMPLOYEES e 
UNION ALL
SELECT D.DEPARTMENT_NAME FROM DEPARTMENTS d
ORDER BY FIRST_NAME;

• 부서 이름과 사원 이름이 모두 섞여 있고, 위의 결과와 다르게 중복된 내용이 존재한다.
  

4. VIEW 생성 및 수정

• Oracle에서는 ALTER VIEW로 이미 존재하는 VIEW의 정의를 변경할 수 없다. 이 경우엔 CREATE OR REPLACE를 사용해야 한다.
• Oracle에서는 ALTER VIEW를 사용해 제약 조건을 정의, 수정, 삭제할 수 있으며, 읽기 모드 설정을 변경할 수 있다.
• 참고 자료 : stackoverflow How to delete a column from a view, Oracle ALTER VIEW

-- EMPLOYEES 테이블, DEPARTMENTS 테이블, LOCATION 테이블을 이용해서
-- 사원 이름, 직종, 입사일, 급여, 보너스, 부서 이름, 도시 정보를 저장한 VIEW 생성
CREATE OR REPLACE VIEW INFO AS -- VIEW가 존재하면 수정, 없다면 새로 생성
(
	SELECT E.FIRST_NAME, E.JOB_ID, E.HIRE_DATE, E.SALARY, (E.COMMISSION_PCT*E.SALARY) AS BONUS,
			D.DEPARTMENT_NAME,
			L.CITY
	FROM EMPLOYEES e JOIN DEPARTMENTS d 
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
	JOIN LOCATIONS l 
	ON D.LOCATION_ID = L.LOCATION_ID
);

SELECT * FROM INFO;