5. ORACLE 정리 :: 집합 연산자(UNION)와 JOIN문

1. 집합 연산자(UNION)

여러 select 결과물을 하나의 쿼리로 만드는 연산자

ex) 통합 로그인 시스템에서 사용자, 관리자, 사업자 회원의 데이터 테이블을 union 으로 묶어서 조회한다.

🚨 조건 첫번째 select문의 컬럼수 = 이후의 select문의 컬럼수 같을 수 없다면 임의값이라도 넣어줘야함 (null) 컬럼 타입 동일해야함 (문자, 문자 / 숫자, 숫자 등) 변환해서라도 맞춰주면 된다. → TO_CHAR 등

• 종류

아래처럼 구분 칼럼을 만들어놓으면 어느 테이블에서 온건지 쉽게 구분할 수 있게 된다.

create view v_avg_salary
as **select '부서' as div**, dept_code as cdoe, dept_title as title, floor(avg(salary)) as avg_sal
from employee join department on dept_id = dept_code
group by dept_code, dept_title
union
**select '직책' as div**, job_code, job_name, floor(avg(salary)) as avg
from employee join job using(job_code)
group by job_code, job_name;

1) UNION

• 중복값 제거됨

@ 작성방법
SELECT문 1개
UNION
다른 SELECT문

-- ex
SELECT DEPT_CODE, JOB_CODE, FLOOR(AVG(SALARY))
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL
GROUP BY DEPT_CODE, JOB_CODE
UNION
SELECT DEPT_CODE, NULL, FLOOR(AVG(SALARY)) -- 칼럼수가 같아야하니까 null을 넣어서라도 맞춰줘야함
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL
GROUP BY DEPT_CODE
UNION
SELECT NULL, NULL, FLOOR(AVG(SALARY))
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL;

2) UNION ALL

• UNION과의 차이점
  • 중복값도 포함되어서 하나의 테이블에 누적된다.

3) INTERSECT

• 중복값만 찾고 싶을 때 사용
• 혹은 여러 테이블에서 해당 조건 데이터만 찾아내고 싶을 때 사용

-- ex
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
INTERSECT
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000;

-- ex2
select emp_id, emp_name
from employee
union -- 누적
select dept_id, dept_title
from department
union -- 누적
select job_code, job_name
from job
minus -- 제거
select dept_id, dept_title
from department
where dept_id in ('D5', 'D6', 'D7')
intersect -- 위 데이터 중 이름에 '유'가 들어가는 사람들만 조회
select emp_id, emp_name
from employee
where emp_name like '%유%';

4) MINUS

• A에서 B와 중복되는 값을 빼고 조회

 

2. JOIN문

1) 개요

어떤 표기법을 따르는지에 따라서 작성 내용이 달라질 수 있다.

(1) 오라클 전용 구문

FROM절에 ,로 구분하여 합치게 될 테이블 명을 기술하고 WHERE절에 합칠 때 사용할 컬럼명을 기술

• 연결에 사용할 두 컬럼명이 다르면 그 자체로 사용 가능 같으면 테이블명.컬럼명으로 작성 구분
• 작성 예시

-- inner
SELECT EMP_NAME, SALARY, DEPT_TITLE FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID;

-- outer
SELECT EMP_NAME, SALARY, DEPT_TITLE FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+);

(2) ANSI 표준 구문

오라클이 아니어도 다른 database관리 프로그램에서도 사용가능한 방법으로 사용이 권장된다.

FROM A JOIN B ON 테이블A.컬럼명 = 테이블B.컬럼명

• B : 알고자하는 select 데이터가 있는 테이블
• select문의 내용은 from where 이후에 실행된다
• JOIN 기준으로 쓰는 컬럼은 유일한 값을 가지고 있어야 한다.(PK 설정된 컬럼값을 이용)
  • DEPARTMENT 테이블의 DEPT_ID 값은 PK(Primary Key)이다.
  • PK: null값이 있으면 안되는 필수 값
• 별칭 사용을 통해 간략하게 진행 가능하며, 혹은 연결하려는 컬럼 명이 같으면 별칭 또는 USING() 사용 중 한개 방법을 쓸 수 있다. 

-- 기본
SELECT JOB_NAME, SALARY, SALARY, BONUS
FROM EMPLOYEE JOIN JOB ON EMPLOYEE.JOB_CODE = JOB.JOB_CODE
WHERE JOB_NAME = '대리';

-- 별칭 사용
-- 같은 컬럼명의 데이터를 select하려면 어느 테이블 소속인지도 명시해줘야한다.
SELECT JOB_NAME, SALARY, SALARY, BONUS
FROM EMPLOYEE E JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
WHERE JOB_NAME = '대리';

-- 혹은 using 사용 : 연결할 컬럼명이 동일하면 사용할 수 있는 예약어
SELECT *
FROM EMPLOYEE JOIN JOB USING(JOB_CODE);

-- 당연히 group by, having, order by 함께 사용 가능하다
SELECT DEPT_TITLE, COUNT(*), AVG(SALARY) AS AVG
FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_ID = DEPT_CODE
GROUP BY DEPT_TITLE
HAVING AVG(SALARY) >= 3000000
ORDER BY 3;

2) JOIN의 종류

• 표준 구문 작성 예시

-- inner join
SELECT * FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID;

-- outer join : left, right를 추가해주면 outer
SELECT * FROM EMPLOYEE **LEFT** JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID; 

참조되는 컬럼값이 (NULL이 있으면 안되는) 필수관계일때는 INNER JOIN
예) 사원과 부서. 사원에게 부서는 필수 사항이다. 
참조되는 컬럼값이 선택관계일때는 OUTER JOIN 추천!! 
예) 사원과 사원용 사물함. 사원에게 사원용 사물함 이용은 선택 사항이다.
인턴 사원에게 부서는 없을 수도 있다. 출력하려면 outer join 필수!

(1) INNER JOIN

기준 컬럼값과 일치하는 값이 있는 ROW만 가져오는 연결 → 일치값이 없는 행은 제외됨

기본적으로 JOIN은 INNER JOIN 형태이다.

(2) OUTER JOIN

기준 컬럼값과 일치하지 않은 값도 가져오는 연결
→ 대신 전체 ROW를 가져올 테이블을 별도로 설정해야 한다.
→ 일치하는 값이 없는 ROW(선택관계)는 NULL값을 넣어서 연결해준다

• 선택관계 : 직원 사물함은 신청한 사람과 신청하지 않은 사람이 있을 수 있음

OUTER JOIN은 반드시 JOIN 앞에 방향을 명시해야한다.

• 해당 방향의 테이블 데이터를 모두 가져오겠다는 뜻
• 없는 데이터는 Null로 들어온다

3) LEFT / RIGHT JOIN

-- outer left: 사원이 없는 부서 조회하기
SELECT DEPT_TITLE
FROM DEPARTMENT **LEFT** JOIN EMPLOYEE ON DEPT_CODE = DEPT_ID
GROUP BY DEPT_TITLE
HAVING COUNT(EMP_ID) = 0;

SELECT DEPT_TITLE
FROM EMPLOYEE RIGHT JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
WHERE DEPT_CODE IS NULL;

SELECT *
FROM DEPARTMENT LEFT JOIN EMPLOYEE ON DEPT_CODE = DEPT_ID
WHERE EMP_ID IS NULL; -- 사원이라면 null값이 있으면 안되는 pk값으로 조건문을 쓰는 것이 좋다.

-- 다른 방법
SELECT DEPT_TITLE
FROM DEPARTMENT LEFT JOIN EMPLOYEE ON DEPT_CODE = DEPT_ID
GROUP BY DEPT_TITLE
HAVING COUNT(EMP_ID) = 0; -- 칼럼명을 적으면 해당 칼럼에서 Null인 데이터는 제외됨

4) CROSS JOIN

기준 테이블의 한개 row에 다른 테이블 전체 row를 연결하는 join

데이터 수 = 행의 컬럼수 * 또 다른 행의 컬럼 수

-- cross join
select * from employee cross join department order by 2;

select * from employee cross join location;

5) SELF JOIN

자기 자신을 연결 → 자신을 참조하는(동일값이 있는) 컬럼이 있어야함

• 반드시 별칭을 사용해야 함

-- 사원의 매니저 정보를 조회하기: 사원번호, 사원명, 매니저의 사원번호, 매니저 사원명
-- 오라클 구문
select e.emp_id, e.emp_name, 
        m.emp_id as 매니저_사원번호, m.emp_name as 매니저_사원명
from employee m, employee e 
where m.manager_id = e.emp_id;

-- 같은 결과: 표준표기법
select e.emp_id, e.emp_name, e.manager_id, m.emp_name
from employee m join employee e on e.manager_id = m.emp_id;

=> 같은 테이을 별칭을 통해 두개로 나누고, 사원 테이블에서는 매니저 아이디로,
매니저 테이블에서는 사원 아이디로 연결을 하게 된다.
최종 출력을 위해서 select에는 사원 테이블의 아이디와 이름, 매니저 아이디, 그리고
매니저 테이블의 사원아이디를 기술한다.

6) NON_EQU JOIN

비동등 조인

지정 컬럼 값이 일치하는 것이 아니라 값의 범위에 포함되는 행들을 연결하는 것

-- 표준 구문
SELECT EMP_ID, SAL_GRADE.SAL_LEVEL
FROM EMPLOYEE JOIN SAL_GRADE ON SALARY BETWEEN MIN_SAL AND MAX_SAL;

7) 다중 조인

join된 테이블에 join, join …

계속 join on을 덧붙이면 된다.

단, 조인이 되기 전에 테이블에 없는 칼럼명을 먼저 쓰지 않도록 순서에 유의해야 한다.

-- 사원의 사원명, 부서명, 직책명, 급여, 보너스 조회하기
SELECT * FROM JOB;
SELECT * FROM DEPARTMENT;
SELECT * FROM EMPLOYEE;
SELECT EMP_NAME, DEPT_TITLE, JOB_NAME, SALARY, BONUS
FROM EMPLOYEE LEFT JOIN DEPARTMENT ON DEPT_ID = DEPT_CODE
			        --JOIN JOB J ON J.JOB_CODE = EMPLOYEE.JOB_CODE;
                JOIN JOB USING(JOB_CODE); --using으로 함축

-- 사원명, 부서명, 직책명, 지역명, 지역코드, 국가코드 조회
-- 부서가 없는 인턴 사원도 사원이기 때문에 함께 출력되도록 모두 left를 붙여주었다.
SELECT EMP_NAME, DEPT_TITLE, JOB_NAME, LOCAL_NAME, L.LOCAL_CODE, NATIONAL_CODE
FROM EMPLOYEE LEFT JOIN DEPARTMENT ON DEPT_ID = DEPT_CODE
            LEFT JOIN LOCATION L ON L.LOCAL_CODE = LOCATION_ID
            LEFT JOIN NATIONAL USING(NATIONAL_CODE)
            [LEFT ]JOIN JOB USING(JOB_CODE);