[ Oracle ] 집계함수(count,min,max,sum,avg) | 조인(Join) 종류

 

집계함수와 조인에 대해 알아보기 전에,

포스팅에 나오는 예제는 아래의 구조를 가진 테이블들이 사용됩니다.

 

 

 

 

 

emp 테이블은 위 그림과 같습니다.

 

위의 구조에서, dept 테이블에는 부서와 관련된 정보가 저장되어 있습니다.

 

 

집계함수(=복수행함수)

 

 

집계함수는 복수행함수입니다.

단일 행 단위로 실행시키는 것이 아닌 복수행단위로 실행시킵니다.

null 은 연산에서 열외시킵니다.

 

 

count

 

개수를 세는 함수입니다.

 

 

 

min

 

최소값을 알 수 있는 함수입니다.

숫자 뿐만 아니라 문자형도 연산 가능합니다.

 

 

 

max 

 

최대값을 알 수 있는 함수입니다.

숫자 뿐만 아니라 문자형도 연산 가능합니다.

 

 

 

 

avg 

 

값들의 평균을 알 수 있는 함수입니다.

숫자만 파라미터로 들어갈 수 있습니다.

 

 

 

 

sum

 

값들의 총 합을 알 수 있는 함수입니다.

숫자만 파라미터로 들어갈 수 있습니다.

 

 

 

count 는 전체 데이터단위로도 가능합니다.

 

이게 무슨 소리냐면,

다른 집계함수들은 파라미터로 컬럼명을 주는 반면 count 는 row 단위로 count 가 가능하다는 것입니다.

 

 

그렇기 때문에 count(*) 이런 식으로 해도 에러가 나지 않습니다.

 

 

 

 

조인의 종류

 

교차조인(cross join) = cartesian product

 

dept 안에 있는 데이터는 4개 입니다.

emp 안에 있는 데이터는 14개죠.

 

select d.deptno,dname,empno,ename,sal
from emp e, dept d;

 

 

 

위의 쿼리문과 같이 모든 데이터들을 조인해서 보여주는 것이 교차조인입니다.

 

 

내부조인(inner join) : theta, [non-]equi, natural

 

 

아래의 그림으로 설명 가능합니다.

A 와 B 의 교집합을 의미합니다.

 

 

 

 

 

--  부서별로 사원의 sal이 낮은 순으로 deptno,dname,empno,ename,sal출력

select d.deptno,d.dname,e.empno,e.ename,e.sal from dept d join emp e on d.deptno=e.deptno
order by 1 asc,5 asc;

 

 

dept 테이블과 emp 테이블을 조인하여 출력했습니다.

 

on 뒤에는 그 두 테이블의 관계를 적습니다.

 

일반적으로는 외래키나 프라이머리키를 적는데, 아닌 경우도 존재합니다.

 

외부조인(outer join) : [left | right | full] [outer] join

 

 

right 조인은 아래 그림과 같습니다.

 

left 조인은 아래 그림과 같습니다.

 

 

 

 

 

full 조인은 아래 그림과 같습니다.

 

 

 

 

 

셀프조인(self join)

 

 

 

 

 

오늘 포스팅에서 다뤘던 것들을 토대로 예제들을 많이 만들어봤습니다.

 

 

-- 3) 부서별로 사원의 sal이 낮은 순으로 deptno,dname,empno,ename,sal출력

select d.deptno,d.dname,e.empno,e.ename,e.sal from dept d join emp e on d.deptno=e.deptno
order by 1 asc,5 asc;

select d.deptno,dname,empno,ename,sal
from emp e, dept d;

-- 4) 부서별 사원은 몇명인지 empno,dname,부서별 인원수 출력

select e.deptno,dname, 
count(*) as "부서별 인원수"
from dept d join emp e on d.deptno=e.deptno
group by e.deptno,d.dname;

-- 4 upgrade
-- 부서별 사원은 몇명인지 deptno,dname, 부서별 인원수를 출력하되
-- 5명 이상인 부서만 부서별 인원수 높은 순으로 출력하라

select e.deptno,dname, 
count(*) as "부서별 인원수"
from dept d join emp e on d.deptno=e.deptno
group by e.deptno,d.dname
having count(*)>=5
order by 3 desc;

-- 4 upgrade!!
-- 부서별 사원은 몇명인지 20번 이상 부서만 deptno,dname, 부서별 인원수를 출력하되
-- 5명 이상인 부서만 부서별 인원수 높은 순으로 출력하라

select e.deptno,dname, 
count(*) as "부서별 인원수"
from dept d join emp e on d.deptno=e.deptno
where e.deptno >=20
group by e.deptno,d.dname
having count(*)>=5
order by 3 desc;