SQL 동일연산

집합연산자

 

1. INTERSECT

#[1] intersect
select team_id, player_name, position
from player
where team_id = 00
intersect
select team_id, player_name,  position 
from player
where position = 'GK'

#[2] where and
select team_id, player_name, position
from player
where team_id = 00 and where position = 'GK'

 

2. MINUS

#[1] minus
select team_id, player_name, position
from player
where team_id = 00
minus 
select team_id, player_name, position
from player
where position = 'A'

#[2] and, <>
select team_id, player_name, position
from player
where team_id = 00 and position <> 'A'

#[3] not in 
select team_id, player_name, position
from player
where team_id not in (select team_id from player where position = 'A')

 

JOIN

1. where, on, using

# using 절 -> using절에는 별칭 X
select t.region-name, t.team_name, t.stadium_id, s.stadium_name
from team t inner join stadium s
using(stadium_id)

# on절 -> 별칭사용가능
select team.region-name, team.team_name, team.stadium_id, stadium.stadium_name
from team inner join stadium
on team.stadium_id = stadium.stadium_id

# where 절 -> 별칭
select t.region-name, t.team_name, t.stadium_id, s.stadium_name
from team t , stadium s
where t.stadium_id = s.stadium_id

# where 절
select team.region-name, team.team_name, team.stadium_id, stadium.stadium_name
from team , stadium
where team.stadium_id = stadium.stadium_id

#natural join -> 두테이블간 동일한 칼럼이 stadium_id밖에 없는 경우
select region-name, team_name, stadium_id, stadium_name
from team natural join stadium

 

2. EQUI JOIN

#[1] 별칭 미사용
select emp.name, emp.deptno, dept.dname
from emp, dept
where emp.deptno = dept.deptno

#[2] 별칭사용
select e.name, e.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno

#[3] 접두어 일부 생략(조인의 기준이 되는 컬럼은 필수)
select name, e.deptno, dname
from emp e, dept d
where e.deptno = d.deptno

#[4] ERROR -> FROM절에서 별칭 정의후에는 다른절에서 테이블명 사용 불가(생략은 가능)
select emp.name, emp.deptno, dept.dname
from emp e, dept d
where e.deptno = d.deptno

 

3.outer join

#[1]full outer join
select e.name, e.deptno, d.dname
from emp e full outer join dept d
where e.deptno = d.deptno

#[2]left outer join / union / right outer join
select e.name, e.deptno, d.dname
from emp e left outer join dept d
where e.deptno = d.deptno
union
select e.name, e.deptno, d.dname
from emp e right outer join dept d
where e.deptno = d.deptno

 

 

고급집계

1. cube

#[1] cube
select dname, job, count(*)직원수, sum(sal) 급여합
from 
group by cube(dname, job)

#[2] union
select dname, job, count(*)직원수, sum(sal) 급여합
from 
group by rollup (dname, job)
union
select dname, job, count(*)직원수, sum(sal) 급여합
from 
group by rollup (job, dname)

 

2. 누적합

select a.no, a.date, sum(cnt) as '누적값'
from  emp a, emp b
where a.no = b.no
and a.date >= b.date 
group by a.no, a.date


select no, date, 
	sum(cnt)over(partition by date row between unbounded preceding and current row) as '누적합'
from emp

 

order by 절 뒤에 옵션 생략시 range unbounded preceding 

-> 가장 위의 행부터 현재 행까누적치 데이터 -> 거기서 sum/max/min 을 정함