๐Ÿ“Œ ์„œ๋ธŒ์ฟผ๋ฆฌ(SubQuery)๋ž€?

  • ์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” ๋‹ค๋ฅธ ์ฟผ๋ฆฌ ๋‚ด์— ํฌํ•จ๋œ ์ฟผ๋ฆฌ์ž…๋‹ˆ๋‹ค. ๋ฉ”์ธ ์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๋ฅผ ํ•„ํ„ฐ๋งํ•˜๊ฑฐ๋‚˜ ๊ณ„์‚ฐํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค.
  • ์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” ๊ด„ํ˜ธ๋กœ ๋‘˜๋Ÿฌ์‹ธ์•ผ ํ•˜๋ฉฐ, ๋น„๊ต ์—ฐ์‚ฐ์ž์˜ ์˜ค๋ฅธ์ชฝ์— ์œ„์น˜ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.
  • ์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” SELECT, FROM, HAVING, ORDER BY ์ ˆ์—์„œ ๋ชจ๋‘ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
  • ORDER BY ์ ˆ์€ ์„œ๋ธŒ์ฟผ๋ฆฌ ๋‚ด์— ํฌํ•จ๋  ์ˆ˜ ์—†์œผ๋ฉฐ, ์„œ๋ธŒ์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ์ฃผ ์ฟผ๋ฆฌ์˜ ๋งˆ์ง€๋ง‰์— ์œ„์น˜ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.
  • ์—ฐ๊ด€ ์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” ๋ฉ”์ธ ์ฟผ๋ฆฌ์˜ ์ปฌ๋Ÿผ์„ ์ฐธ์กฐํ•˜์—ฌ ์กฐ๊ฑด์„ ์ ์šฉํ•˜๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ์ž…๋‹ˆ๋‹ค.

 

1๏ธโƒฃ ๋‹จ์ผ ํ–‰ ์„œ๋ธŒ์ฟผ๋ฆฌ

์„œ๋ธŒ์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๊ฐ€ ํ•˜๋‚˜์˜ ํ–‰์„ ๋ฐ˜ํ™˜ํ•  ๋•Œ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋‹จ์ผํ–‰ ์—ฐ์‚ฐ์ž๋ฅผ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค.

  • =, <>, >, <, >=, <=
-- ์ด ์ฟผ๋ฆฌ๋Š” ์ง์›์˜ ๊ธ‰์—ฌ๊ฐ€ ํ‰๊ท  ๊ธ‰์—ฌ๋ณด๋‹ค ๋†’์€ ์ง์› ์ •๋ณด๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
SELECT employee_id, first_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

 

2๏ธโƒฃ ๋‹ค์ค‘ ํ–‰ ์„œ๋ธŒ์ฟผ๋ฆฌ

์„œ๋ธŒ์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๊ฐ€ ์—ฌ๋Ÿฌ ํ–‰์„ ๋ฐ˜ํ™˜ํ•  ๋•Œ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋‹ค์ค‘ํ–‰ ์—ฐ์‚ฐ์ž๋ฅผ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค.

  • IN : ์„œ๋ธŒ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๊ฐ€ ๋ฉ”์ธ ์ฟผ๋ฆฌ์˜ ์กฐ๊ฑด๊ฐ’๊ณผ ์ผ์น˜ํ•˜๋Š”์ง€ ํ™•์ธํ•ฉ๋‹ˆ๋‹ค. ํ•˜๋‚˜ ์ด์ƒ์˜ ์ผ์น˜๊ฐ€ ์žˆ์œผ๋ฉด ํ•ด๋‹น ํ–‰์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
-- ์ด ์ฟผ๋ฆฌ๋Š” 'David'๋ผ๋Š” ์ด๋ฆ„์„ ๊ฐ€์ง„ ์ง์›์ด ์†Œ์†๋œ ๋ถ€์„œ์˜ ๋ชจ๋“  ์ง์› ์ •๋ณด๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
SELECT *
FROM employees
WHERE department_id IN (SELECT department_id FROM employees WHERE first_name = 'David');
  • ANY : ์„œ๋ธŒ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ ์ค‘ ํ•˜๋‚˜๋ผ๋„ ๋ฉ”์ธ ์ฟผ๋ฆฌ์˜ ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ๊ฒฝ์šฐ ํ•ด๋‹น ํ–‰์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
-- ์ด ์ฟผ๋ฆฌ๋Š” 'David'์˜ ๊ธ‰์—ฌ ์ค‘ ์ตœ์†Ÿ๊ฐ’๋ณด๋‹ค ํฐ ๋ชจ๋“  ์ง์›์˜ ์ •๋ณด๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
SELECT *
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE first_name = 'David');
-- ์ด ์ฟผ๋ฆฌ๋Š” 'David'์˜ ๊ธ‰์—ฌ ์ค‘ ์ตœ๋Œ“๊ฐ’๋ณด๋‹ค ์ž‘์€ ๋ชจ๋“  ์ง์›์˜ ์ •๋ณด๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
SELECT *
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE first_name = 'David');
  • ALL : ์„œ๋ธŒ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๊ฐ€ ๋ชจ๋‘ ๋ฉ”์ธ ์ฟผ๋ฆฌ์˜ ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ๊ฒฝ์šฐ ํ•ด๋‹น ํ–‰์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
-- ์ด ์ฟผ๋ฆฌ๋Š” 'David'์˜ ๊ธ‰์—ฌ ์ค‘ ์ตœ๋Œ“๊ฐ’๋ณด๋‹ค ํฐ ๋ชจ๋“  ์ง์›์˜ ์ •๋ณด๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
SELECT *
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE first_name = 'David');
-- ์ด ์ฟผ๋ฆฌ๋Š” 'David'์˜ ๊ธ‰์—ฌ ์ค‘ ์ตœ์†Ÿ๊ฐ’๋ณด๋‹ค ์ž‘์€ ๋ชจ๋“  ์ง์›์˜ ์ •๋ณด๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
SELECT *
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE first_name = 'David');
  • EXISTS : ์„œ๋ธŒ์ฟผ๋ฆฌ๊ฐ€ ๋น„์–ด ์žˆ์ง€ ์•Š์€์ง€ ํ™•์ธํ•ฉ๋‹ˆ๋‹ค. ๋น„์–ด ์žˆ์ง€ ์•Š์œผ๋ฉด ํ•ด๋‹น ํ–‰์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
-- ์ด ์ฟผ๋ฆฌ๋Š” ํ•ด๋‹น ๋ถ€์„œ์— ์ง์›์ด ์กด์žฌํ•˜๋Š” ๊ฒฝ์šฐ๋งŒ ์ง์› ์ •๋ณด๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.
SELECT employee_id, first_name
FROM employees e
WHERE EXISTS (SELECT * FROM departments d WHERE e.department_id = d.department_id);

 

3๏ธโƒฃ ์Šค์นผ๋ผ ์„œ๋ธŒ์ฟผ๋ฆฌ(Scalar SubQuery) : SELECT ์ ˆ์—์„œ ๋‹จ์ผ ๊ฐ’ ๋ฐ˜ํ™˜

  • ์Šค์นผ๋ผ: ๋‹จ์ผ ๊ฐ’์„ ์˜๋ฏธํ•ฉ๋‹ˆ๋‹ค.
  • ์ •์˜: SELECT ์ ˆ์— ์‚ฌ์šฉํ•˜๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ๋กœ, ์ฃผ๋กœ ๋‹จ์ˆœํ•œ JOIN์„ ๋Œ€์ฒดํ•  ๋ชฉ์ ์œผ๋กœ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค.
  • ์žฅ์ : ํŠน์ • ํ…Œ์ด๋ธ”์˜ 1๊ฐœ ์ปฌ๋Ÿผ๋งŒ ๊ฐ€์ ธ์˜ฌ ๋•Œ JOIN๋ณด๋‹ค ๋” ์œ ์šฉํ•˜๊ฒŒ ํ™œ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
-- `JOIN`์„ ์‚ฌ์šฉํ•˜์—ฌ ์ง์›๊ณผ ๋ถ€์„œ ์ •๋ณด๋ฅผ ํ•จ๊ป˜ ์กฐํšŒโœจ
SELECT E.FIRST_NAME,
D.DEPARTMENT_NAME
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID;
-- `์Šค์นผ๋ผ ์„œ๋ธŒ์ฟผ๋ฆฌ`๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ง์› ์ด๋ฆ„๊ณผ ๋ถ€์„œ ์ด๋ฆ„ ์กฐํšŒโœจ
SELECT FIRST_NAME,
(SELECT DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) AS DEPARTMENT_NAME
FROM EMPLOYEES E;

 

4๏ธโƒฃ ์ธ๋ผ์ธ ๋ทฐ(Inline View) : FROM ์ ˆ์—์„œ ํ…Œ์ด๋ธ”์ฒ˜๋Ÿผ ์ทจ๊ธ‰

  • ์ •์˜: ์ธ๋ผ์ธ ๋ทฐ๋Š” FROM ์ ˆ์— ์„œ๋ธŒ์ฟผ๋ฆฌ๊ฐ€ ํฌํ•จ๋œ ๊ฒฝ์šฐ๋ฅผ ์˜๋ฏธํ•ฉ๋‹ˆ๋‹ค. ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ” ๋˜๋Š” ๋ทฐ์ฒ˜๋Ÿผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
  • ๋น„๊ต: ๋ทฐ๋Š” ๋…๋ฆฝ์ ์ธ SELECT ๋ฌธ์œผ๋กœ, FROM ์ ˆ์— ์˜ค๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ๋„ ํ•˜๋‚˜์˜ ๋ทฐ๋กœ ๊ฐ„์ฃผํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด๋Ÿฌํ•œ ์ด์œ ๋กœ FROM ์ ˆ์— ์˜ค๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ์ธ๋ผ์ธ ๋ทฐ๋ผ๊ณ  ๋ถ€๋ฆ…๋‹ˆ๋‹ค.
  • ํ™œ์šฉ: ์ธ๋ผ์ธ ๋ทฐ๋Š” ๊ฒŒ์‹œํŒ ํŽ˜์ด์ง•๊ณผ ๊ฐ™์€ ๋‹ค์–‘ํ•œ ์ƒํ™ฉ์— ์œ ์šฉํ•˜๊ฒŒ ์‚ฌ์šฉ๋  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
-- ๊ฒŒ์‹œํŒ ํŽ˜์ด์ง• : 11๋ฒˆ์งธ๋ถ€ํ„ฐ 20๋ฒˆ์งธ ๊ฒŒ์‹œ๊ธ€ ์กฐํšŒ
SELECT *
FROM (
SELECT ROWNUM AS RN, -- ROWNUM์„ ๊ฐ€์ ธ์™€์„œ RN์ด๋ผ๋Š” ๋ณ„์นญ์„ ๋ถ€์—ฌ (ROWNUM์€ 1๋ถ€ํ„ฐ ์‹œ์ž‘ํ•˜๋ฏ€๋กœ ์ค‘๊ฐ„ ๋ฐ์ดํ„ฐ๋งŒ ๊ฐ€์ ธ์˜ฌ ์ˆ˜ ์—†์Œโœจ)
TITLE,
CONTENT
FROM (
SELECT TITLE, CONTENT
FROM POSTS
ORDER BY CREATED_DATE DESC -- ๋‚ด๋ฆผ์ฐจ์ˆœ ์ •๋ ฌํ•˜์—ฌ ์ตœ์‹  ๊ฒŒ์‹œ๊ธ€์ด ๋จผ์ € ์˜ค๋„๋ก ํ•จโœจ
)
)
WHERE RN BETWEEN 11 AND 20; -- โœ”RN์„ ๊ธฐ์ค€์œผ๋กœ 11๋ฒˆ์งธ๋ถ€ํ„ฐ 20๋ฒˆ์งธ ๊ฒŒ์‹œ๊ธ€์„ ์กฐํšŒโœจ

 

 

 

yewon31