[Oracle] ๐Ÿ’พ ์„œ๋ธŒ์ฟผ๋ฆฌ

shrยท2022๋…„ 2์›” 16์ผ
0

Concept

๋ชฉ๋ก ๋ณด๊ธฐ
4/13
post-thumbnail

์„œ๋ธŒ์ฟผ๋ฆฌ


ํ•˜๋‚˜์˜ ์ฟผ๋ฆฌ ๋ฌธ์žฅ ๋‚ด์— ํฌํ•จ๋œ ๋˜ ํ•˜๋‚˜์˜ ์ฟผ๋ฆฌ ๋ฌธ์žฅ์„ ๋งํ•œ๋‹ค. ๋น„๊ต ์—ฐ์‚ฐ์ž์˜ ์˜ค๋ฅธ์ชฝ์— ๊ธฐ์ˆ ํ•ด์•ผ ํ•˜๊ณ  ๋ฐ˜๋“œ์‹œ ๊ด„ํ˜ธ ์•ˆ์— ๋„ฃ์–ด์•ผ ํ•œ๋‹ค. ๋ฉ”์ธ ์ฟผ๋ฆฌ๊ฐ€ ์‹คํ–‰๋˜๊ธฐ ์ „์— ํ•œ ๋ฒˆ ์‹คํ–‰๋œ๋‹ค.


์„œ๋ธŒ์ฟผ๋ฆฌ์˜ ์ข…๋ฅ˜


  • select์ ˆ ์„œ๋ธŒ ์ฟผ๋ฆฌ : Scalar Subqueries, ์Šค์นผ๋ผ ์„œ๋ธŒ์ฟผ๋ฆฌ
  • from์ ˆ ์„œ๋ธŒ ์ฟผ๋ฆฌ : Inline-View Subqueries, ์ธ๋ผ์ธ ๋ทฐ ์„œ๋ธŒ์ฟผ๋ฆฌ
  • where์ ˆ ์„œ๋ธŒ ์ฟผ๋ฆฌ : Nested Subqueries, ์ค‘์ฒฉ ์„œ๋ธŒ์ฟผ๋ฆฌ

select์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ (์Šค์นผ๋ผ ์„œ๋ธŒ์ฟผ๋ฆฌ)


-- 'ALLEN'์˜ ๋ถ€์„œ๋ช…์„ ๊ตฌํ•˜์‹œ์˜ค.
select ename, (select d.dname from dept d where d.deptno=emp.deptno) as dept_name from emp where ename='ALLEN';

select์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๋Š” ๋ฉ”์ธ ์ฟผ๋ฆฌ์—์„œ selectํ•  ์ˆ˜ ์žˆ๋„๋ก ๋ฐ˜๋“œ์‹œ ๋‹จ์ผ ํ–‰์ด๋‚˜ ๋‹จ์ผ ๊ฐ’์œผ๋กœ ๋ฆฌํ„ด๋˜์–ด์•ผ ํ•œ๋‹ค.


from์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ (์ธ๋ผ์ธ ๋ทฐ ์„œ๋ธŒ์ฟผ๋ฆฌ)


from์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๋Š” ๋ฐ˜๋“œ์‹œ ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”๋กœ ๋ฆฌํ„ด๋˜์–ด์•ผ ํ•œ๋‹ค. ์ธ๋ผ์ธ ๋ทฐ๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ์ฟผ๋ฆฌ๋กœ๋Š” TOP N ์ฟผ๋ฆฌ, ํŽ˜์ด์ง• ์ฟผ๋ฆฌ ๋“ฑ์ด ์žˆ๋‹ค.

TOP-N ์ฟผ๋ฆฌ

์ƒ์œ„ N๊ฐœ์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์ถœ๋ ฅํ•˜๋Š” ์ฟผ๋ฆฌ์ด๋‹ค.

-- ์ตœ๊ณ  ๊ธ‰์—ฌ 3๋ช…
select * from (select * from emp order by sal desc) where rownum<=3;

์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ์“ฐ์ง€ ์•Š๊ณ  where์ ˆ๊ณผ order by์ ˆ์„ ์“ฐ๊ฒŒ ๋˜๋ฉด, where์ ˆ์ด ๋จผ์ € ์‹คํ–‰๋˜์–ด order by์™€ ์ƒ๊ด€์—†์ด ๋žœ๋ค์œผ๋กœ ์ •๋ ฌ๋œ 3๋ช…์ด ์ถœ๋ ฅ๋œ๋‹ค. ๋”ฐ๋ผ์„œ ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ์ด์šฉํ•ด order by๋กœ ์ •๋ ฌ์„ ๋จผ์ € ์‹คํ–‰ํ•ด ์ฃผ๋Š” ๊ฒƒ์ด๋‹ค.

ํŽ˜์ด์ง• ์ฟผ๋ฆฌ

TOP-N ์ฟผ๋ฆฌ๋ฅผ ์ด์šฉํ•˜์ง€๋งŒ ์กฐ๊ธˆ ๋” ๋ณต์žกํ•˜๋‹ค. 1-5 ํŽ˜์ด์ง€๋Š” TOP-N ์ฟผ๋ฆฌ๋กœ ์‰ฝ๊ฒŒ ๊ตฌํ˜„ํ•  ์ˆ˜ ์žˆ์ง€๋งŒ 6-10 ํŽ˜์ด์ง€๋ถ€ํ„ฐ๋Š” rownum์˜ ํŠน์„ฑ์ƒ(1์ด ์žˆ์–ด์•ผ 2๊ฐ€ ์žˆ๊ณ , 2๊ฐ€ ์žˆ์–ด์•ผ 3์ด ์žˆ๋‹ค.) rownum<=10์„ ๋จผ์ € ์‹คํ–‰ํ•˜๊ณ , rownum>=6์„ ์‹คํ–‰ํ•ด์•ผ ํ•œ๋‹ค.

select * from 
(select rownum as rnum, new_desc.* 
from (select * from emp order by empno desc) new_desc 
where rownum<=10) 
where rnum>=6;

rownum์€ select๋ฌธ๋งˆ๋‹ค ๋”ฐ๋กœ ๋ถ€์—ฌ๋˜๊ธฐ ๋•Œ๋ฌธ์— ์„œ๋ธŒ์ฟผ๋ฆฌ์˜ rownum์„ ์ €์žฅํ•ด ์ฃผ๊ณ  ๋ฉ”์ธ ์ฟผ๋ฆฌ์—์„œ ์ €์žฅํ•œ rownum์„ ์จ์•ผ ํ•œ๋‹ค

์ž‘์„ฑ ์ˆœ์„œ

  1. ํ…Œ์ด๋ธ”์˜ ์ •๋ ฌ์„ ๋จผ์ € ํ•˜๋Š” select๋ฌธ์„ ์ž‘์„ฑํ•œ๋‹ค. ๊ทธ๋ฆฌ๊ณ  ๊ทธ select๋ฌธ์— ๋ณ„์นญ์„ ๋ถ™์—ฌ ์ค€๋‹ค. (๋’ค์—์„œ ์ด์šฉํ•ด์•ผ ํ•จ)
  2. โ‰ค ๋ฒ”์œ„๋ฅผ ๊ฑธ์–ด ์ฃผ๋Š” select๋ฌธ์„ ์ž‘์„ฑํ•œ๋‹ค. 1์˜ select๋ฌธ์— ๋ณ„์นญ์„ ๊ฑธ์–ด ์ฃผ์—ˆ๊ธฐ ๋•Œ๋ฌธ์— ๋ณ„์นญ.*์œผ๋กœ ๋‚ด์šฉ์„ ๋ฝ‘์•„ ์˜ฌ ์ˆ˜ ์žˆ๋‹ค. ๊ทธ๋ฆฌ๊ณ  ์ด select๋ฌธ์˜ rownum๋„ ์ถœ๋ ฅํ•˜๋ฉด์„œ ๋ณ„์นญ์„ ๋ถ™์—ฌ ์ค€๋‹ค. (๋’ค์—์„œ ์จ์•ผ ํ•จ)
  3. โ‰ฅ ๋ฒ”์œ„๋ฅผ ๊ฑธ์–ด ์ฃผ๋Š” select๋ฌธ์„ ์ž‘์„ฑํ•œ๋‹ค. ์ด๋•Œ 2์˜ rownum์— ๋ณ„์นญ์„ ๊ฑธ์–ด ์ฃผ์—ˆ๊ธฐ ๋•Œ๋ฌธ์— 2๋ฒˆ select๋ฌธ์˜ rownum์„ ์ด์šฉํ•  ์ˆ˜ ์žˆ๋‹ค.

where์ ˆ ์„œ๋ธŒ ์ฟผ๋ฆฌ (์ค‘์ฒฉ ์„œ๋ธŒ์ฟผ๋ฆฌ)


๊ฐ€์žฅ ๋Œ€์ค‘์ ์ธ ์„œ๋ธŒ์ฟผ๋ฆฌ์ด๋‹ค. ๋‹จ์ผ ํ–‰, ๋ณต์ˆ˜ ํ–‰ ๋ชจ๋‘ ๋ฆฌํ„ด์ด ๊ฐ€๋Šฅํ•˜๋‹ค.

-- ์ตœ๊ณ  ๊ธ‰์—ฌ์ž์˜ job
select job from emp where sal=(select max(sal) from emp);

-- 7369๋ฒˆ ์‚ฌ์›์˜ ๋ถ€์„œ๋ช… (join ๊ธˆ์ง€)
select dname from dept where deptno=(select deptno from emp where empno=7369);

-- ํ‰๊ท  ๊ธ‰์—ฌ ์ด์ƒ์˜ ๊ธ‰์—ฌ๋ฅผ ๋ฐ›๋Š” ์‚ฌ์› ๋ช…๋‹จ
select ename from emp where sal>=(select avg(sal) from emp);

-- 'ALLEN'๊ณผ ๊ฐ™์€ ๋ถ€์„œ์— ๊ทผ๋ฌดํ•˜๋Š” ์‚ฌ์› ์ถœ๋ ฅ
select ename from emp where deptno=(select deptno from emp where ename='ALLEN') and ename!='ALLEN';
-- and ename!='ALLEN'์€ ALLEN์„ ์ถœ๋ ฅ์—์„œ ์ œ์™ธํ•˜๋Š” ์‹์ด๋‹ค.

-- SMITH์™€ ๊ฐ™์€ job์„ ๊ฐ€์ง„ ์‚ฌ์› ์ถœ๋ ฅ
select ename from emp where job=(select job from emp where ename='SMITH');
profile
๋ชปํ•˜๋‹ค ๋ณด๋ฉด ์ž˜ํ•˜๊ฒŒ ๋˜๋Š” ๊ฑฐ์•ผ ๏ฝฅแด—๏ฝฅฬฅฬฅฬฅ

0๊ฐœ์˜ ๋Œ“๊ธ€