신나는 6주차가 시작되었다. 뭔가 이번 한 주도 후딱 지나갈 것 같은 느낌..
오늘은 평가 때문에 잠깐 엇나갔던 진도에서 돌아와 DQL 후반부를 진행하였다

📝목차

1. join
2. Set Operator
3. Sub Query – 단일행, 다중행

---

1. join

: 여러 테이블의 데이터(행)를 조건으로 하나의 가상 테이블로 모으는 문법

반복문으로 따지면, 이중 for문에 조건을 걸어 여러 테이블의 레코드들을 하나로 묶어서 운영하는 문법이다.

select * from DEPARTMENT;  부서코드 별 사업내용
select * from Job;  코드 별 직급 이름

1) join의 원리

select * from DEPARTMENT, Job; -- 오라클 문법

 for Department
      for job
 // 굳이 표현하면 이런 느낌..

• 조건 없이 사용하면, 앞쪽 테이블 각 행이 뒷 쪽 테이블의 각 행에 한 번씩 연결이 된다.

이렇게 출력되는 데이터는 의미가 없다. 따라서 의미가 있는 데이터를 출력하려면 조건을 부여해 사용한다.

예) 만약 부서명이 궁금한 상황이 왔다. 그리고 각 테이블엔 다음 컬럼이 있다.
employee : 부서 코드 | Department : 부서 코드에 따른 부서명
join을 이용해서 두 테이블을 연결하고자 한다.

select 
	emp_id, emp_name, dept_title 
from employee 
join department on (dept_code=dept_id) -- ANSI 표준
order by 1;

• on , where 을 통해 조건을 걸어준다.
• 조건의 결과가 거짓이면 행끼리 연결되지 않게 된다.
• 출력 대상 컬럼으로 두 테이블의 컬럼을 모두 사용할 수 있다.
• 조건에서 서로 존재하지 않는 행들은 출력되지 않는다. = 모두 false

1-1) 비교 컬럼의 이름이 같은 경우

별명처리 : as 로 구분해준다.

employee (job_code) | job (job_code)

select 
	emp_name, 
	A.job_code, 
	job_name  
from employee A 
join job B on (A.job_code=B.job_code)  -- 비교컬럼이 같기 때문에, 확실하게 구분해줘야 한다.
order by 1;

using : 비교 대상이 되는 컬럼명이 같을 때만 사용할 수 있는 문법이다.

select 
	emp_name, 
	A.job_code, 
	job_name
from employee A 
join job B using(job_code)  -- from employee A join job B on (A.job_code=B.job_code)와 같다.
order by 1;

---

2) join의 두 가지 문법

① Oracle 전용 문법 : 오라클에서만 사용되는 문법

select emp_id, emp_name, dept_title 
from employee, department 
where (dept_code=dept_id) 
order by 1;

② ANSI 표준 문법 : ANSI를 따르는 DBMS에선 통용된다.

select emp_id, emp_name, dept_title 
from employee join department on (dept_code=dept_id) 
order by 1;

---

3) join의 종류

cross join : cartesian product을 만드는 조건 없는 join

select * from DEPARTMENT, job; 

---

inner join (inner 생략 가능 : default) : 조건으로 의미있는 데이터를 만드는 join

select emp_id, emp_name, dept_title 
from employee (inner) join department on (dept_code=dept_id) 
order by 1;

위 조건으로 join 시, null 값은 조건에 대해 false가 되어서 반환되지 않는다. 따라서 공백값을 넣을지 말지는 사용자의 선택이다.
다만 inner join을 사용하는 순간 공백은 생략하고자 하는 의미가 강하다.

---

(left or right) outer join (outer는 생략 가능) : 각 테이블의 데이터 생략을 막는 join

• outer join 도 표준에 따라서 다르게 적용된다.

① left outer join : 왼쪽에 있는 테이블의 데이터 생략 X

[ ANSI ]

select emp_id, emp_name, dept_title 
from employee 
left outer join department on (dept_code = dept_id); 

오라클 : 값이 없는 테이블에 (+)을 추가해줌

select emp_id, emp_name, dept_title 
from employee, department where dept_code = dept_id(+);

---

② right outer join : 오른쪽에 있는 테이블의 데이터 생략 X

[ ANSI ]

select emp_id, emp_name, dept_title
from employee 
right outer join department on (dept_code = dept_id); 

오라클

select emp_id, emp_name, dept_title 
from employee, department where dept_code(+) = dept_id;  

---

③ full outer join : 양 테이블의 데이터 생략 X

[ ANSI ]

select emp_id, emp_name, dept_title 
from employee 
full outer join department on (dept_code = dept_id);  

---

self join : 테이블 2개를 쓰는 것은 맞지만, 둘은 같은 테이블이다.

-- employee의 MANAGER_ID: 각 직원 별 직속 상사의 ID
select * from employee; join employee;

예: 자신이 관리하고 있는 사원의 이름, 급여 등을 출력하세요.

select 
    e1.emp_name,  상사의 이름
    e2.emp_name,  직원의 이름
    e2.salary   직원의 월급
from employee e1 
join employee e2 on (e1.emp_id = e2.manager_id);

-- 그룹화 추가
select 
    e1.emp_name, 
    count(*),    
    sum(e2.salary)
from employee e1 join employee e2 on (e1.emp_id = e2.manager_id)  
group by e1.emp_name;

---

다중 join : 2개뿐이 아닌, 여러 개의 join도 가능하다.

select
    emp_name,
    dept_title,
    job_name
from employee e 
left join department d on (dept_code = dept_id)  -- 첫 번째 join
join job j on (e.job_code=j.job_code)  -- 첫 join에 따른 두 번째 join : 넘어오면서 한번 걸러진다.
order by 1; 

---

2. Set Operator

두 개 이상의 테이블을 합치는 건 같지만, 원리가 Join과 다르다.

join : 레코드를 따라 컬럼을 추가시킴

• 종류 : UNION / UNION ALL / INTERSECT / MINUS
• 용도 : 데이터 양식은 똑같은데, 의미가 다른 경우가 있다. 그럴 때 전체 조회를 할 때 사용한다.

UNION : 컬럼의 개수와 자료형이 같다는 가정 하에서만 사용

1) UNION

: 중복되는 데이터는 한 번만 출력되게 병합.

- 열의 개수만 맞으면 병합이 된다.

select emp_id, emp_name, emp_no -- 3개 
from employee 
union
select * from department;  -- 3개

- 상응하는 두 테이블의 모든 열의 자료형이 맞아야 한다.

select salary from employee  -- 숫자
union
select dept_id from department;  -- 문자
-- 병합 X

select * from tmp1
union
select * from tmp2;

---

2) UNION ALL

: 중복되는 데이터까지 포함해 출력되게 병합

select * from tmp1
union all
select * from tmp2;

---

3) INTERSECT

: 중복되는 데이터만 출력

select * from tmp1
intersect
select * from tmp2;

---

4) MINUS

: 첫 번째 select 결과에서 두 번째 select 결과를 뺄셈하여 남는 첫 번째 데이터만 출력

select * from tmp1  ABC (중심은 첫번째)
minus
select * from tmp2;  BCD

---

3. Sub Query – 단일행, 다중행

: Main Query + (Sub Query) : 메인 쿼리 내부에 쿼리를 하나 더 넣는 형식.

• 쿼리 안의 쿼리를 다루는 문법
• join과 어느 정도 호환이 된다.
• 서브쿼리의 종류는 반환 종류에 따라 단일행, 다중행, 다중열, 다중열+행 으로 나뉜다.
• 문법적으로 order by 는 차단되어 있다.
• 중첩 서브쿼리도 가능하다.
• where 뿐만이 아니라, 모든 곳에서 사용될 수 있다.

종류

1) 단일행 서브쿼리

: 서브 쿼리의 반환값이 단일행일 때.

예) 특정 직원의 매니저 이름을 알아내세요.

join, subquery 미사용

select * from employee where emp_name = '전지연';
select emp_name from employee where emp_id = 214;

단일행 쿼리

select emp_name from employee 
where emp_id= -- 메인 쿼리
(select manager_id from employee where emp_name='전지연'); -- 서브 쿼리
-- 딱 한 개의 값이 출력된다.

join

select e2.emp_name 
from employee e1 
join employee e2 on (e1.manager_id = e2.emp_id)
where
    e1.emp_name='전지연';

테이블이 연결되어도, 내부엔 테이블 구분이 존재한다.

예) 전 직원의 평균 급여보다 많은 급여를 받는 직원의 사번, 이름, 직급코드, 급여를 출력하세요

select emp_id, emp_name, job_code, salary 
from employee 
where 
    (select avg(salary) from employee)<=salary;  -- where에 그룹함수 사용가능하다는 이점이 있다.
select avg(salary) from employee;

---

2) 다중행 서브 쿼리

: 서브 쿼리의 결과가 여러 행을 반환할 때.

• 서브쿼리의 결과로 여러 행을 반환하기 때문에 =(같다) 비교를 사용할 수 없다.
• 따라서 in, any, all 을 이용해 여러 값 중 하나를 고르게 한다.

예) 송종기나, 박나라가 속한 부서의 직원 이름, 부서코드, 급여를 출력하시오

join, subquery 미사용

select dept_code from employee where emp_name = '송종기';
select dept_code from employee where emp_name = '박나라';
select emp_name, dept_code, salary from employee where dept_code in ('D5', 'D9');

다중행 쿼리

select emp_name, dept_code, salary from employee
where dept_code 
in (select dept_code from employee where emp_name in ('송종기', '박나라'));

예) J3 직급인 사람들의 급여보다 더 큰 급여를 가진 직원의 이름과 급여를 출력

단일행

select emp_name, salary 
from employee
where salary > (select max(salary) from employee where job_code='J3');  -- 단일행

대소비교

select emp_name, salary 
from employee
where salary > all (select salary from employee where job_code='J3');  
-- all : 다중행의 모든 결과 중 1개

select emp_name, salary 
from employee
where salary > any (select salary from employee where job_code='J3');  
-- any : 다중행의 모든 결과