[Oracle] 고급쿼리

devheyrin·2022년 7월 30일

JOIN from절 서브쿼리 with 레벨쿼리 상관쿼리 서브쿼리 컬럼 서브쿼리

Oracle

목록 보기

7/10

JOIN

사용하려는 컬럼이 하나 이상의 테이블에 있을 때 사용
32개까지 가능

종류

Eque join (Inner join) : 조건에 만족하는 행만 추출
Outer join (left * right) : 조건에 만족하는 행이 추출되고, 마스터 테이블의 모든 행이 함께 추출되는 조인
Cross join (데카르트의 곱) : 카티션 프로젝트, table * table, 경우의 수 추출, 조건이 없음

Self join : 물리적으로 자기 자신의 테이블과 조인되는 경우

select 
s.saname, 
s.deptno,
d.dname
from sawon s, dept d -- 별칭 부여 
where s.deptno = d.deptno; 

SANAME         DEPTNO DNAME
---------- ---------- ----------
홍길동             10 총무부
한국남             20 영업부
이순신             20 영업부
이순라             20 영업부
놀기만             20 영업부
류별나             20 영업부
채시라             20 영업부
이성계             30 전산부
무궁화             10 총무부
임꺽정             20 영업부
깨똥이             10 총무부

문법

T-SQL 문법

from 에 테이블 나열, where 조건절

컬럼을 원하는 곳에서 사용
사용된 컬럼을 가진 테이블 목록을 from~(, 구분자) 나열
사용된 테이블간의 관계를 조건식(where)으로 표현

예제

고객명, 전화번호, 고객담당자명을 추출

select 
g.goname, 
g.gotel, 
s.saname
from gogek g, sawon s
where g.godam = s.sabun; -- godam 이 null 이면 거짓으로 평가된다. 

GONAME     GOTEL          SANAME
---------- -------------- ----------
강민       343-1454       한국남
류완       123-1674       이순신
류민       123-1234       이순신
똘이       176-7677       놀기만
홍이       767-1234       무궁화
쇠돌       673-6774       무궁화
안나       767-1677       이미라
철이       673-1674       이미라

outer join 사용(gogek이 마스터)

select 
g.goname, 
g.gotel, 
s.saname
from gogek g, sawon s
where g.godam = s.sabun(+);

GONAME     GOTEL          SANAME
---------- -------------- ----------
강민       343-1454       한국남
류완       123-1674       이순신
류민       123-1234       이순신
똘이       176-7677       놀기만
홍이       767-1234       무궁화
쇠돌       673-6774       무궁화
안나       767-1677       이미라
철이       673-1674       이미라
캔디       673-1764
영희       144-1655

ANSI 문법

사용되는 테이블을 서술형으로 표현, on 조건절
T-SQL 문법보다 복잡하다.
유지보수가 매우매우매우어렵다.

select 
g.goname,
g.gotel, 
s.saname 
from gogek g
left join sawon s
on (g.godam = s.sabun);

GONAME     GOTEL          SANAME
---------- -------------- ----------
강민       343-1454       한국남
류완       123-1674       이순신
류민       123-1234       이순신
똘이       176-7677       놀기만
홍이       767-1234       무궁화
쇠돌       673-6774       무궁화
안나       767-1677       이미라
철이       673-1674       이미라
캔디       673-1764
영희       144-1655

ANSI 문법 중 natural join, using

관계되는 컬럼명이 같은 경우에만 사용가능한 문법
테이블 별칭 불가, (+) 기호 불가
inner join 만 가능

select 
saname, deptno, dname
from sawon
natural join dept;

select 
saname, deptno, dname
from sawon 
join dept 
using(deptno)

예제

사원명, 직책, 자기관리자명 추출

select 
s.sabun,
s.saname,
s.sajob,
m.saname
from sawon s, sawon m
where s.samgr = m.sabun(+);

SABUN SANAME     SAJOB      SANAME
---------- ---------- ---------- ----------
        13 무궁화     부장       홍길동
        17 이성계     부장       홍길동
         2 한국남     부장       홍길동
        11 류별나     과장       한국남
         7 놀기만     과장       한국남
         3 이순신     과장       한국남
        14 채시라     사원       이순신
         5 이순라     사원       이순신
        19 임꺽정     사원       놀기만
         4 이미라     대리       이성계
         8 채송화     대리       이성계

     SABUN SANAME     SAJOB      SANAME
---------- ---------- ---------- ----------
         6 공부만     과장       이성계
        20 깨똥이     과장       무궁화
        12 류명한     대리       깨똥이
        15 최진실     사원       류명한
         9 무궁화     사원       류명한
        18 강감찬     사원       이미라
        16 김유신     사원       이미라
        10 공부해     사원       이미라
         1 홍길동     회장
       111 dd

부서별 급여합계

select 
d.dname "부서명",
sum(s.sapay) "급여합"
from sawon s, dept d
where s.deptno = d.deptno
group by d.dname;

부서명         급여합
---------- ----------
전산부          13718
총무부          17400
영업부          17200

부서명, 사원명, 직책, 관리자명, 관리자직책 추출(단, 관리자가 없는 사원도 추출)

select 
d.dname "부서명", 
s.saname "사원명",
s.sajob "직책",
m.saname "관리자명", 
m.sajob "관리자직책"
from dept d, sawon s, sawon m
where s.samgr = m.sabun(+) and d.deptno(+) = s.deptno;

부서명     사원명     직책       관리자명   관리자직책
---------- ---------- ---------- ---------- ----------
총무부     무궁화     부장       홍길동     회장
전산부     이성계     부장       홍길동     회장
영업부     한국남     부장       홍길동     회장
영업부     류별나     과장       한국남     부장
영업부     놀기만     과장       한국남     부장
영업부     이순신     과장       한국남     부장
영업부     채시라     사원       이순신     과장
영업부     이순라     사원       이순신     과장
영업부     임꺽정     사원       놀기만     과장
전산부     이미라     대리       이성계     부장
전산부     채송화     대리       이성계     부장

부서명     사원명     직책       관리자명   관리자직책
---------- ---------- ---------- ---------- ----------
전산부     공부만     과장       이성계     부장
총무부     깨똥이     과장       무궁화     부장
총무부     류명한     대리       깨똥이     과장
총무부     최진실     사원       류명한     대리
총무부     무궁화     사원       류명한     대리
전산부     강감찬     사원       이미라     대리
전산부     김유신     사원       이미라     대리
전산부     공부해     사원       이미라     대리
총무부     홍길동     회장

고객명, 전화, 담당자명, 직책, 부서명 추출(단, 담당자가 없는 고객도 추출)

select 
g.goname "고객명", 
g.gotel "전화", 
s.saname "담당자명",
s.sajob "직책",
d.dname "부서명"
from gogek g, sawon s, dept d
where g.godam = s.sabun(+) and s.deptno = d.deptno(+);

고객명     전화           담당자명   직책       부서명
---------- -------------- ---------- ---------- ----------
쇠돌       673-6774       무궁화     사원       총무부
홍이       767-1234       무궁화     부장       총무부
똘이       176-7677       놀기만     과장       영업부
류민       123-1234       이순신     과장       영업부
류완       123-1674       이순신     과장       영업부
강민       343-1454       한국남     부장       영업부
철이       673-1674       이미라     대리       전산부
안나       767-1677       이미라     대리       전산부
영희       144-1655
캔디       673-1764

-- ANSI 문법 
select 
g.goname "고객명", 
g.gotel "전화", 
s.saname "담당자명",
s.sajob "직책",
d.dname "부서명"
from gogek g 
left outer join sawon s
on (g.godam = s.sabun)
left outer join dept d
on (s.deptno = d.deptno);

직책별 부서별 급여합계

select 
s.sajob "직책",
sum(case d.dname when '영업부' then s.sapay else 0 end) "영업부",
sum(case d.dname when '관리부' then s.sapay else 0 end) "관리부",
sum(case d.dname when '전산부' then s.sapay else 0 end) "전산부",
sum(case d.dname when '총무부' then s.sapay else 0 end) "총무부",
sum(sapay) "급여합계"
from sawon s, dept d
where s.deptno = d.deptno
group by rollup(s.sajob);

직책           영업부     관리부     전산부     총무부   급여합계
---------- ---------- ---------- ---------- ---------- ----------
과장             7400          0       4003       4500      15903
대리                0          0       4206       1800       6006
부장             3000          0       2803       3000       8803
사원             6800          0       2706       3100      12606
회장                0          0          0       5000       5000
                17200          0      13718      17400      48318

Cross Join

모든 경우의 수 출력

select 
goname, dname 
from gogek, dept;

GONAME     DNAME
---------- ----------
류민       총무부
강민       총무부
영희       총무부
철이       총무부
류완       총무부
캔디       총무부
똘이       총무부
쇠돌       총무부
홍이       총무부
안나       총무부
류민       영업부

-- 중략 

GONAME     DNAME
---------- ----------
철이       관리부
류완       관리부
캔디       관리부
똘이       관리부
쇠돌       관리부
홍이       관리부
안나       관리부

40 개의 행이 선택되었습니다.

JOIN 이 필요한데 참조관계일 수 없는 경우 - 범위 안의 값인 경우도 있음

급여 - 급여등급

SUBQUERY

서브쿼리는 먼저 실행된다.
서브쿼리는 값을 반환한다.
64개까지 가능하다.
반드시 ()로 묶는다.

종류

단일행 서브쿼리 : 서브쿼리의 결과 행이 단일행인 경우(=, ≠, >, <, ≥, ≤)
다중행 서브쿼리 : 서브쿼리의 결과 행이 다중행인 경우(in, any, all, exists) ⭐

셀프 조인으로 풀기 - 예제

이순신보다 급여를 많이 받는 사람 - 서브쿼리 사용 X, 셀프 조인

select 
p.saname,
p.sapay
from sawon p, sawon e
where e.saname = '이순신' and e.sapay < p.sapay;

이순신과 같은 부서에서 근무하는 사람

-- 이순신의 부서 구하기  
select 
deptno
from sawon where saname = '이순신';
=> 20 

select deptno, saname 
from sawon 
where 
deptno = (
	select 
	deptno
	from sawon 
	where saname = '이순신'
);

DEPTNO     SANAME
---------- ----------
        20 한국남
        20 이순신
        20 이순라
        20 놀기만
        20 류별나
        20 채시라
        20 임꺽정

서브쿼리의 결과가 2개 이상인 경우 - IN 사용!

select deptno, saname 
from sawon 
where 
deptno in (
	select 
	deptno
	from sawon 
	where saname = '무궁화'
);

회사에서 가장 급여를 많이 받는 사람을 추출

select 
saname, sapay
from sawon
where 
sapay = (
	select 
	max(sapay)
	from sawon
);

SANAME          SAPAY
---------- ----------
홍길동           5000

회사의 평균 급여보다 많이 받는 사람을 추출

select 
saname, sapay
from sawon
where 
sapay > (
select 
avg(sapay)
from sawon
);

SANAME          SAPAY
---------- ----------
홍길동           5000
한국남           3000
이순신           3500
채시라           3400
이성계           2803
무궁화           3000
깨똥이           4500
공부만           4003
이미라           2503

10번 부서에서 전체 사원의 평균급여보다 많이 받는 사람을 추출

select
deptno, saname, sapay 
from sawon
where 
sapay > (
select 
avg(sapay)
from sawon
)
and deptno = 10;

DEPTNO SANAME          SAPAY
---------- ---------- ----------
        10 홍길동           5000
        10 무궁화           3000
        10 깨똥이           4500

10번 부서에서 최대급여, 최소급여를 받는 사람을 추출

select 
deptno, saname, sapay
from sawon
where 
(sapay = (
	select 
	max(sapay)
	from sawon
	where deptno = 10
	)
	or 
sapay = (
	select 
	min(sapay)
	from sawon
	where deptno = 10
	))
and deptno = 10;

DEPTNO     SANAME          SAPAY
---------- ---------- ----------
        10 홍길동           5000
        10 무궁화           1100

다중 컬럼 서브쿼리

이순신과 같은 부서, 직책인 사람 추출
많이 쓰지 않음
주의사항
- 컬럼의 순서, 개수, 타입이 일치해야 한다.
- 비교 연산자는 같은 연산자만 사용 가능하다.
- 널을 포함한 컬럼이 존재하는 경우 처리하여 비교해야한다. → 0과 구분하기 위해 -1로 대체

select 
saname, 
deptno, 
sajob 
from sawon
where 
(nvl(deptno, -1), sajob) = (select nvl(deptno, -1), sajob from sawon where saname = '이순신');

각 부서에서 최대급여를 받는 사람을 추출 (다중컬럼)

select 
saname, 
deptno, 
sapay
from sawon
where 
(deptno, sapay) in 
(select 
	deptno, 
	max(sapay) 
	from sawon
	group by deptno
);

SANAME         DEPTNO      SAPAY
---------- ---------- ----------
홍길동             10       5000
이순신             20       3500
공부만             30       4003

상관쿼리

상호관련 서브쿼리
- 외부쿼리의 단일 후보행이 서브쿼리로 들어가서 비교연산된 후,
- 다시 외부쿼리의 후보행과
각 부서에서 평균급여보다 많이 받는 사람 추출

select deptno, saname, sapay from sawon s
where sapay > 
(select 
	avg(sapay) 
	from sawon t 
	where t.deptno = s.deptno
);

DEPTNO SANAME          SAPAY
---------- ---------- ----------
        10 홍길동           5000
        20 한국남           3000
        20 이순신           3500
        20 채시라           3400
        30 이성계           2803
        10 무궁화           3000
        10 깨똥이           4500
        30 공부만           4003
        30 이미라           2503

각 부서에서 최대급여를 받는 사람을 추출 (상관쿼리)

select 
deptno, 
saname, 
sapay 
from sawon s
where sapay = 
(select max(sapay)
	from sawon t
	where t.deptno = s.deptno
);

DEPTNO SANAME          SAPAY
---------- ---------- ----------
        10 홍길동           5000
        20 이순신           3500
        30 공부만           4003

컬럼 서브쿼리 = 스칼라 서브쿼리

사원명, 급여, 직책평균급여(자신이 속한 직책의 평균급여) 를 추출

select 
saname, 
sajob,
sapay, 
(select avg(sapay) from sawon t where t.sajob = s.sajob) "직책평균"
from sawon s;

-- 직책별 평균 
select  
sajob, 
avg(sapay)
from sawon
group by sajob;

select 
saname, 
sajob,
sapay,
(select avg(sapay) from sawon t where t.sajob = s.sajob) "직책평균"
from sawon s
where sapay > (select avg(sapay) from sawon t where t.sajob = s.sajob)
;

Top & N 서브쿼리 (rownum 이용)

최대값 또는 최소값을 가진 일부의 행을 추출하는 서브쿼리 기법
where 보다 먼저 평가되기 위해 from 절 서브쿼리 사용
from 절 서브쿼리(inline-view) : 가상 테이블 개념, 남발 금지!

예제

급여를 가장 많이 받는 사람 3명 추출

select 
name, pay 
from (
	select 
	saname name, sapay pay
	from sawon 
	order by sapay desc
);

회사에서 가장 오래 근무한 5명을 추출

select 
name, hire -- from 절에서 사용한 컬럼명을 사용한다. 
from (
select saname name, sahire hire
from sawon order by hire)
where rownum <= 5;

select 
name, hire
from (
select saname name, sysdate - sahire hire
from sawon order by hire desc)
where rownum <= 5;

NAME       HIRE
---------- --------
홍길동     80/01/01
김유신     81/04/01
이미라     83/04/01
이성계     84/05/01
무궁화     84/08/01

Exists, Not Exists

부하직원이 없는 사람을 추출

select 
saname, sabun, samgr
from sawon
where sabun not in (select distinct nvl(samgr, 0) from sawon);

select 
* 
from dept
where exists
(select deptno from dept where dname = '영업부');

select 
sabun, saname 
from sawon s
where not exists 
(select sabun 
from sawon t 
where t.samgr = s.sabun);

부서의 급여합계가 전체 사원의 급여합계의 30%를 초과하는 부서명과 급여합계를 추출

-- FROM 절 서브쿼리 
select 
d.dname, s.sum
from 
(select deptno, sum(sapay) sum 
from sawon
group by deptno) s, dept d
where s.deptno = d.deptno
and s.sum > (select sum(sapay)*0.3 from sawon);

select
d.dname,
sum(s.sapay) 
from sawon s, dept d
where s.deptno = d.deptno
group by d.dname
having sum(s.sapay) > (select sum(sapay)*0.3 from sawon);

with 명령

from 절 서브쿼리를 보완하기 위한 명령으로 복잡한 쿼리를 단순한 테이블로 사용하기 위한 명령
from 절 서브쿼리보다 with을 사용해야!
형식

    with table_name1 as (select ~~),
    		 table_name2 as (select ~~),
    select ~~~ from table1, table2, ... ;

    with dept_tot as (select dname, sum(sapay) d_sum 
    		 from sawon natural join dept group by dname),
    		 all_tot as (select sum(sapay) a_sum from sawon)
    select dname, d_sum from dept_tot dt, all_tot at
    where dt.d_sum > at.a_sum*0.3;

Level Query

오라클에만 있음
데이터가 카테고리 형태로 구성된 경우 상위 또는 하위 데이터를 추출하는 쿼리 기법(자신의 레벨은 1 → 한 단계 올라갈 때마다 +1)
14번 사원의 상위 조직도를 추출

select level, sabun, saname, sajob from sawon
connect by sabun = prior samgr -- 상위 조직도 -> 더 높은 쪽에 prior 붙여줌
start with sabun = 14;

LEVEL      SABUN SANAME     SAJOB
---------- ---------- ---------- ----------
         1         14 채시라     사원
         2          3 이순신     과장
         3          2 한국남     부장
         4          1 홍길동     회장

-- 자기 자신은 제외하고 추출
select level, sabun, saname, sajob from sawon
where sabun != 14  -- 추출 전 자기 자신 제외 
connect by sabun = prior samgr
start with sabun = 14;

LEVEL      SABUN SANAME     SAJOB
---------- ---------- ---------- ----------
         2          3 이순신     과장
         3          2 한국남     부장
         4          1 홍길동     회장

2번 사원의 하위 조직도를 추출

select level, sabun, saname, sajob, deptno from sawon
connect by prior sabun = samgr -- 하위 조직도 -> 더 낮은 쪽에 prior 붙여줌 
start with sabun = 2;

LEVEL      SABUN SANAME     SAJOB          DEPTNO
---------- ---------- ---------- ---------- ----------
         1          2 한국남     부장               20
         2          3 이순신     과장               20
         3          5 이순라     사원               20 -- 직속상관 - 이순신
         3         14 채시라     사원               20 -- 직속상관 - 이순신
         2          7 놀기만     과장               20
         3         19 임꺽정     사원               20 -- 직속상관 - 놀기만
         2         11 류별나     과장               20 -- 부하직원 없음

devheyrin

개발자 헤이린

이전 포스트

[Oracle] 단일행 함수, 다중행 함수

다음 포스트

[Oracle] 고급쿼리

Oracle

JOIN

종류

문법

T-SQL 문법

예제

ANSI 문법

ANSI 문법 중 natural join, using

예제

Cross Join

JOIN 이 필요한데 참조관계일 수 없는 경우 - 범위 안의 값인 경우도 있음

SUBQUERY

종류

셀프 조인으로 풀기 - 예제

다중 컬럼 서브쿼리

상관쿼리

컬럼 서브쿼리 = 스칼라 서브쿼리

Top & N 서브쿼리 (rownum 이용)

예제

Exists, Not Exists

with 명령

Level Query

[Oracle] 단일행 함수, 다중행 함수

[Oracle] SQL 명령의 종류

0개의 댓글