ORACLE SQL 구문(Join과 subQuery)
Ⅱ. Join 과 subQuery
1. Join
1-1. Equijoin : 두 개의 테이블들 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용되는 방법으로 대부분 PK, FK의 관계를 기반으로 함
ex) select p.player_name, p.back_no, t.region_name, t.team_name
from player_t p, team_t t
where p.tema_id = t.team_id;
1-2. Non-Equijoin : 두 개의 테이블들 간에 칼럼 값들이 일치하지 않는 경우에 사용되는 방법
ex) select e.ename 사원명, e.sal 급여, s.salgrade 급여등급
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
1-3. Outer join : 두 개의 테이블들 간에 Join을 걸었을 경우 Join의 조건을 만족하지 않는
경우에도 그 데이터들을 보고자 하는 경우에 사용, (+) 연산자 사용.
ex) select t.region_name 연고지명, t.team_name 팀명, s.stadium_name 구장명,
s.seat_count 좌석수
from team_t t, stadium_t s
where t.stadium_id (+) = s.stadium_id;
1-4. Self join : 같은 테이블에 있는 행들을 갖고 다른 두개의 테이블인 것처럼 Join하는 것.
ex) select worker.id 사원번호, worker.name 사원명, manager.name 관리자명
from emp worker, emp manager
where worker.mgr = manager.id;
1-5. 2개 이상의 table Join
ex) select p.player_name 선수명, p.position 포지션, t.region_name 연고지명,
t.team_name 팀명, s.stadium_name 구장명
from player_t p, team_t t, stadium_t s
where p.team_i = t.team_id
and t.stadium_id = s.stadium_id
and p.position = 'MF'
order by 선수명;
2. SET 연산자
: 두개 이상의 테이블에서 Join을 사용하지 않고 연관된 데이터를 조회해 오는 방법.
여러 개의 SQL문을 연결하여 조회한 데이터를 결합하는 방식을 사용.
첫 번째 두 번째
select select
UNION : X, Y, Z, O, P, Q
X O UNION ALL : X, Y, Z, Z, O, P, Q
Z P INTERSECT : Z
MINUS : X, Y
Y Q
2-1. UNION : 여러 개의 SQL문의 결과에 대한 합집합으로 자동으로 정렬작업(sorting)
ex) select team)_id 팀명, player_name 선수명, position 포지션, back_no 백넘버, height 키
from player_t
where team_id = 'K02'
union
select team_id 팀명, player_name 선수명, position 포지션, back_no 백넘버, height 키
from player_t
where team_id = 'K07';
*union이라는 set연산자를 사용할 경우 order by절이 없음에도 정렬되어 결과가 출력됨.
2-2. UNION ALL : 여러 개의 SQL문의 결과에 대한 합집합과 공통부분을 더한 합집합.
ex)select team_id 팀명, player_name 선수명, position 포지션, back_no 백넘버, height 키
from player_t
where team_iid = 'K02'
union all
select team_id 팀명, player_name 선수명, position 포지션, back_no 백넘버, height 키
from player_t
where position = 'GK'
order by 1, 2, 3, 4, 5;
*union all이라는 set연산자를 사용할 경우는 SQL문 순서에 따라 그 결과를 게속 덧붙여 출력.
2-3. MINUS : 앞의 SQL문의 결과에서 뒤의 SQL문의 결과의 차집합.
ex)select team_id 팀명, player_name 선수명, position 포지션, back_no 백넘버, height 키
from player_t
where team_iid = 'K02'
minus
select team_id 팀명, player_name 선수명, position 포지션, back_no 백넘버, height 키
from player_t
where position = 'MF'
order by 1, 2, 3, 4, 5;
2-4. INTERSECT : 여러 개의 SQL문의 결과에 대한 교집합.
ex)select team_id 팀명, player_name 선수명, position 포지션, back_no 백넘버, height 키
from player_t
where team_iid = 'K02'
intersect
select team_id 팀명, player_name 선수명, position 포지션, back_no 백넘버, height 키
from player_t
where position = 'GK'
order by 1, 2, 3, 4, 5;
2-5. 카티션프로덕트(Cartesian Product) = cross join
: 2개 이상의 테이블에서 데이터를 조회할 경우 잘못된 join조건이나 join조건을 생략한 경우 첫 번째 테이블의 모든 행들이 두 번째 테이블의 모든 행과 곱한 개수만큼 결과를 리턴함.
3. SubQuery
: 하나의 SELECT문안에 포함되어 있는 또 다른 SELECT문을 말하는 것으로 알려지지 않은 기준에 의한 데이터 검색을 위해서 사용됨.
▶ 동작방식에 따른 SubQuery 분류
Nested Subquery : Subquery의 결과가 Main Query의 조건으로 사용되는 제공자의 역할.
Subquery가 먼저 수행됨.
Correlated(상호연관) Subquery : Main Query의 결과가 Subquery의 조건으로 사용되는
확인자 역할. Main Query의 각 행에 대해서 Subquery가 수행
▶ SubQuery의 데이터 형태에 따른 분류
3-1. 단일행(Single Row) SubQuery
: SubQuery의 실행 결과인 단 하나의 행이 Main Query에 제공됨.
ex) select player_name 선수명, position 포지션, back_no 백넘버
from player_t
where height <= (select avg(height) from player_t)
order by player_name;
3-2. 다중행(Multi Row) SubQuery
:SubQuery의 실행결과인 여러 행이 Main Query에 제공되는 형태.
ex) select region_name 연고지명, team_name 팀명, e_team_name 영문팀명
from team_t
where team_id IN (select team_id
from player_t
where player_name = '김현수‘);
order by team_name;
• IN : SubQuery의 결과값에 있는 임의의 값과 동일한 조건을 의미한다.
ex) select player_name 선수명, position 포지션, height 키
from player_t
where height >= ANY(select height
from player_t
where position = 'GK'
and team_id = 'K02');
• ANY : SubQuery의 결과로 리턴된 어느 결과값 보다 작은 데이터를 모두 조회하는 연산자
ex) select player_name 선수명, position 포지션, height 키
from player_t
where height >= ALL(select height
from player_t
where position = 'GK'
and team_id = 'K02');
• ALL :SubQuery의 결과로 리턴된 최대 결과값 보다 큰 조건을 모두 조회하는 연산자
3-3. 다중칼럼(Multi Column) SubQuery
: SubQuery의 실행 결과에 여러 개의 칼럼이 반환되어 Main Query의 조건절에 사용되어 질 때
그 칼럼들 전체를 동시에 비교하는 경우
ex) select team_id 팀명, player_name 선수명, position 포지션, back_no 백넘버, height 키
from player_t
where (team_id, height) IN (select team_id, min(height)
from player_t
group by team_id)
order by team_id, player_name;
3-4. 상호연관(Correlated) SubQuery - 다른 방법으로 구현 가능하면 사용 자제!!
: SubQuery 내에 Main Query의 칼럼들이 사용되므로, Main Query의 각 행에 대해 마지막
행에 대해 마지막 행에 도달할 때까지 SubQuery가 매번 실행되는 형태로 확인자 역할 수행.
ex) select t.team_name 팀명, p.player_name 선수명, p.position 포지션, p.back_no 백넘버
, p.height 키
from player_t p, team_t t
where p.height < (select avg(s.height)
from player_t s
where s.team_id = p.team_id
and s.height is not null
group by s.team_id)
and p.team_id = t.team_id
order by 선수명;
3-5. Having절에서의 SubQuery
: 그룹함수와 함께 쓰이며, 그룹에 대한 제한조건을 표현할 때 사용.
ex) select p.team_id 팀코드, t.team_name 팀명, avg(p.height) 평균키
from player_t p, team_t t
where p.team_id = t.team_id
group by p.team_id, t.team_name
having avg(p.height) < (select avg(height)
from player_t
where team_id = 'K02');
3-6. From절에서의 SubQuery (InLine View)
: from절에는 원래 테이블명이 오지만 SubQuery가 온다는 것은 SubQuery에 의해 나온
결과가 마치 테이블인 것처럼 사용되는 것.
ex) select t.team_name 팀명, p.player_name 선수명, p.back_no 백넘버
from (select team_id, player_name, back_no
from player_t
where position = 'MF') p, team_t t
where p.team_id = t.team_id
order by 선수명;
3-7. Top-N SubQuery
: 정렬된 데이터를 만드는 SubQuery나 InLine View에서 나온 결과의 행수를 제한하는
역할을 RowNum을 사용하여 수행하는 SubQuery.
ex) select player_name 선수명, position 포지션, back_no 백넘버, height 키
from (select player_name, position, back_no, height
from player_t
where height is not null
order by height desc)
where rownum < 10 ;