Group by
- 테이블 전체 행을 특정컬럼이 동일한 행끼리 그룹핑 처리
- group by 절이 없다면, 테이블 모든 행이 하나의 그룹으로 처리
- group by에 명시한 컬럼만 select절에 사용될 수 있음. 아닌 컬럼 사용시 오류 발생
- group by 는 null도 그룹으로 처리한다.
- 가상컬럼을 가지고도 group by가 가능하다.
- group by는 컬럼 순서가 중요하지 않다.
- group by에서 여러개의 컬럼을 그룹으로 할 경우, distinct 처리와 같이 두 컬럼의 값이 동일한 행을 그룹핑한다.
ex) 부서별로 급여 합계
select
dept_code,
sum(salary) sum_sal,
trunc(avg(salary)) avg_sal
from
employee
group by
dept_code --group_by에서는 null도 그룹으로 처리함
order by
sum(salary) desc;
ex ) 입사년도별 사원수 조회 ( 테이블 별칭에는 as 불가)
select
extract(year from hire_date) hire_year,
count(*) cnt
from
employee e
group by
extract(year from hire_date)
order by
hire_year; 
extract (~)와 같은 가상 컬럼도 조회할 수 있다는 것을 알 수 있다.
ex ) 부서별 직급별 인원수 조회.
-> distint와 같이 두 컬럼 간의 값이 동일한 행을 그룹핑함
select
dept_code,
job_code,
count(*) cnt
from
employee
group by
dept_code,job_code;
HAVING
-조건절, 그룹핑된 결과 행에 대해서 결과집합 포함여부를 처리하는 구문
ex ) 인턴사원을 제외하고, 부서별 평균 급여가 300만원 이상인 부서만 조회 (부서코드, 평균급여 )
select
dept_code,
trunc(avg(salary)) avg_sal
from
employee
where
dept_code is not null
group by
dept_code
having
avg(salary)>=3000000;
ex ) 관리하는 직원이 2명이상인 관리자 사원의 사번과 관리자 사원수를 조회
방법 1.
select
manager_id,
count(*) cnt
from
employee
where
manager_id is not null
group by
manager_id
having
count(*) >=2 ;방법 2.
select
manager_id,
count(*) cnt
from
employee
group by
manager_id
having
count(*) >=2 and manager_id is not null;방법 3.
select
manager_id,
count(*) cnt
from
employee
group by
manager_id
having
count(manager_id)>=2; --count자체가 null을 세지 않음답은 모두
로 나온다.
where대신 having에 조건을 적어줘도 되는 것이다.
ROLLUP | CUBE
- 그룹별 산출한 결과값의 소계를 제공하는 함수
- group by 절에서만 사용
- roll up : 지정한 컬럼에 대해서 단방향 소계
- cube : 지정한 컬럼에 대해서 양방향 소계
select
dept_code,
count(*)
from
employee
group by
rollup(dept_code)
order by
dept_code;
7행의 null은 dept_code가 없는 인턴사원 2명이고,
8행의 null은 총합을 rollup으로 계산한 값 24명이다.
이를 구분할 방법은 grouping을 사용하는 것이다.
grouping(Value)
실제데이터 (0) , 집계과정에서 생성된 데이터 (1) 을 구분
select
decode(grouping(dept_code),0,nvl(dept_code,'인턴'),1,'전체') dept_code,
count(*)
from
employee
group by
rollup(dept_code)
order by
dept_code;
JOIN
- 두개 이상의 테이블 레코드를 연결해서 가상테이블 relation을 생성하는 문법
- 기준컬럼을 가지고 두 테이블의 레코드를 연결
relation 생성하기
1. join 2. union 방법이 두가지 있다.
join을 쓰는 이유)
'송종기'사원의 부서명을 조회하려면
select dept_code from employee where emp_name='송종기';--D9
select dept_title from department where dept_id ='D9'; --총무부다음과 같이 두번을 거쳐야 해서 귀찮고 코드도 길다.
이를 한번에 해결하는 것이 Join 이다.
select d.dept_title
from
(employee e join department d
on e.dept_code = d.dept_id)
-- dept_code와 dept_id가 같은 행끼리 연결해서 붙인 것임.
where
e.emp_name='송종기';
JOIN 분류
1. EQUI-JOIN : 조인 기준절(on) 에 = 동등비교를 사용하는 조인 (사용률 95%)
2. NON-EQUI-JOIN : 조인 기준절(on)에 = 동등비교를 사용하지 않는 조인 (사용률 5%)
ex ) !=, between and , in, is null 등의 연산자
JOIN 분법 분류
1. ansi 표준문법 : join, on 키워드
2. oracle 전용문법 : , (콤마), where절 이용
EQUI-JOIN 분류
1. 내부조인 inner join : 교집합
2. 외부조인 outer join : 합집합
left outer join
right outer join
full outer join
3. 크로스조인 cross join
4. 셀프 조인 self join
5. 다중 조인 multiple join
INNER JOIN
- 내부 조인. 두 테이블간의 교집합을 의미한다.
- 각 테이블에서 기준컬럼이 null인 행, 상대테이블에서 매칭되는 행이 없는 행 제외.
- inner 키워드 생략 가능. (inner) join으로 작성
예시
employee | department 테이블 내부 조인.
employee.dept_code = department.dept_id
INNER JOIN에서
employee테이블에서 dept_code가 null인 2행 제외됨.
department테이블에서 employee.dept_code에서 사용되지 않은 D3,D4,D7행 제외
select * from employee;
select * from department;
select
e.emp_name,
d.dept_title
from
employee e inner join department d
on e.dept_code = d.dept_id;이너조인 결과 (22행 출력)
OUTER JOIN
외부조인
좌/우측 테이블 기준으로 조인. 기준이 된 테이블은 누락되는 행이 없다.
outer키워드 생략 가능 . left (outer) join | right (outer) join
ex ) left outer join
employee 테이블 기준 모든 행을 포함한다.
24행 = 22행( inner join ) + 2행 (null값인 인턴)
select
emp_name,
dept_title
from
employee e left outer join department d
on e.dept_code = d.dept_id;
inner join 결과에 비해 하동운과 이오리가 추가된 것을 알 수 있다.
ex ) right outer join
department 테이블 기준 모든 행을 포함한다.
25행 = 22행 ( inner join ) + 3행 (사원이 없는 부서 D3, D4, D7)
select
d.dept_title,
e.emp_name
from
employee e right outer join department d
on e.dept_code = d.dept_id;
마케팅부, 국내영업부, 해외영업 3부가 null값을 가짐에도 출력된 것을 확인할 수 있다.
ex) full outer join
좌측과 우측이 모두 기준이 되어 좌 우측 테이블에서 제외되는 행이 없다.
27행 = 22행( inner join ) + 2행( 인턴 ) + 3행 ( 사원없는부서 D3,D4,D7 )
select
e.emp_name,
d.dept_title
from
employee e full outer join department d
on e.dept_code = d.dept_id;
27행이 출력된 것을 확인할 수 있다.
이렇게 다음과 같이 이너조인, 아우터조인에 대해 알아보았다.
PK, FK도 언급하셨지만 짧게 하셔서 다음 내용에 요약하기로..!