DATABASE_Subquery
๐ DB ( DataBase )
๐ Subquery
Subquery ๋ ๋ฌด์์ธ๊ฐ?
- ์๋ธ ์ฟผ๋ฆฌ ( subquery) ๋ ๋ค๋ฅธ ์ฟผ๋ฆฌ ๋ด๋ถ์ ํฌํจ ๋์ด ์๋ SELECT ๋ฌธ์ ์๋ฏธํ๋ค.
- ์ฌ๋ฌ ๋ฒ์ select ๋ฌธ์ ์คํํ์ฌ ์ป์ ์ ์๋ ๊ฒฐ๊ณผ๋ฅผ ํ๋์ ์ฟผ๋ฆฌ๋ก ๊ฒฐ๊ณผ๋ฅผ ์ป์ ์ ์๋ค.
- ์๋ธ ์ฟผ๋ฆฌ๋ฅผ ํฌํจํ๊ณ ์๋ ์ฟผ๋ฆฌ๋ฅผ ์ธ๋ถ ์ฟผ๋ฆฌ ( outer query ) ๋๋ ๋ฉ์ธ ์ฟผ๋ฆฌ๋ผ๊ณ ๋ถ๋ฅด๋ฉฐ, ์๋ธ ์ฟผ๋ฆฌ๋ ๋ด๋ถ ์ฟผ๋ฆฌ ( inner query )๋ผ๊ณ ๋ ๋ถ๋ฅธ๋ค.
- ๋ฉ์ธ์ฟผ๋ฆฌ๋ณด๋ค ๋จผ์ ์คํ๋๋ค.
- ์๋ธ ์ฟผ๋ฆฌ๋ ๋น๊ต ์ฐ์ฐ์์ ์ค๋ฅธ์ชฝ์ ๊ธฐ์ ํด์ผํ๊ณ ๋ฐ๋์ ๊ดํธ ( () )๋ก ๊ฐ์ธ์ ธ ์์ด์ผ๋ง ํ๋ค.
- SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT ๋ฌธ์ VALUES, UPDATE๋ฌธ์ SET ์ ์ฌ์ฉํ ์ ์๋ค.
- ์๋ธ ์ฟผ๋ฆฌ๋ ๋จ์ผ ํ(โ=, >, <, >=, <=โ) ๋๋ ๋ค์ค ํ(โIN, ANY, ALLโ) ๋น๊ต ์ฐ์ฐ์์ ๊ฐ์ด ์ฌ์ฉ๋๋ค.
์ฌ๋ฒ์ด 100์ธ ์ฌ์์ ๋ถ์์ด๋ฆ์?
-> join ์ฌ์ฉ
select department_name
from employees e inner join departments d
on e.department_id = d.department_id
where e.employee_id = 100;
-> subquery ์ฌ์ฉ
select department_name
from departments
where department_id = ( select department_id
from employees
where employee_id = 100);
Nested Subquery
- subquery๊ฐ where ์ ์์ ์ฌ์ฉ๋ ๊ฒ์ด Nested Subquery๋ผ ํ๋ค.
Nested Subquery - ๋จ์ผ ํ
- subquery์ ๊ฒฐ๊ณผ๊ฐ ๋จ์ผ ํ์ ๋ฆฌํดํ๋ค.
- ๋จ์ผ ํ ๋น๊ต ์ฐ์ฐ์๋ก๋ โ=, >, <, >=, <=โ ์ด ์๋ค.
-> 'adam'๊ณผ ๊ฐ์ ๋ถ์์ ๊ทผ๋ฌดํ๋ ์ฌ์์ ์ฌ๋ฒ, ์ด๋ฆ, ๋ถ์๋ฒํธ
select employee_id, first_name, department_id
from employees
where department_id = (
select department_id
from employees
where first_name='adam'
);
Nested Subquery - ๋ค์ค ํ
- subquery์ ๊ฒฐ๊ณผ๊ฐ ๋ค์ค ํ์ ๋ฆฌํดํ๋ค.
- ๋ค์ค ํ ๋น๊ต ์ฐ์ฐ์๋ก๋ โIN, ANY, ALLโ ์ด ์๋ค.
-> IN
-> ๊ทผ๋ฌด ๋์๊ฐ 'seattle' ์ธ ์ฌ์์ ์ฌ๋ฒ, ์ด๋ฆ
select employee_id, first_name
from employees
where department_id in (
select department_id
from department
where location_id = (
select location_id
from locations
where city = 'seattle'
)
);
-> ANY ( ์ ์ด๋ ํ๋๋ง ๋ง์กฑํ๋ฉด true )
-> ๋ชจ๋ ์ฌ์ ์ค ์ ์ด๋ ( ์ต์ ๊ธ์ฌ์ ๋ณด๋ค ) 30๋ฒ ๋ถ์์์ ๊ทผ๋ฌดํ๋ ์ฌ์์ ๊ธ์ฌ๋ณด๋ค ๋ง์ด ๋ฐ๋ ์ฌ์์ ์ฌ๋ฒ,์ด๋ฆ,๊ธ์ฌ,๋ถ์๋ฒํธ
select employee_id, first_name, salary, department_id
from employees
where salary > any (
select salary
from employees
where department_id = 30
);
-> ALL ( ๋ชจ๋ ๋ง์กฑํ๋ฉด true )
-> 30๋ฒ ๋ถ์์์ ๊ทผ๋ฌดํ๋ ๋ชจ๋ ( ์ต๋ ๊ธ์ฌ์ ๋ณด๋ค ) ์ฌ์๋ค ๋ณด๋ค ๊ธ์ฌ๋ฅผ ๋ง์ด ๋ฐ๋ ์ฌ์์ ์ฌ๋ฒ, ์ด๋ฆ, ๊ธ์ฌ, ๋ถ์๋ฒํธ
select employee_id, first_name, salary, department_id
from employees
where salary > all (
select salary
from employees
where department_id = 30
);
Nested Subquery- ๋ค์ค ์ด
- subquery์ ๊ฒฐ๊ณผ๊ฐ ๋ค์ค ์ด์ ๋ฆฌํดํ๋ค.
-> ์ปค๋ฏธ์
์ ๋ฐ๋ ์ฌ์ ์ค ๋งค๋์ ์ฌ๋ฒ์ด 148์ธ ์ฌ์์ ๊ธ์ฌ์ ๋ถ์๋ฒํธ๊ฐ ์ผ์นํ๋ ์ฌ์์ ์ฌ๋ฒ, ์ด๋ฆ
select employee_id, first_name
from employees
where (salary, depmartment_id) in (
select salary, department_id
from employees
where commission_pct is not null
and manager_id = 148
);
Inline View Subquery
- subquery๊ฐ from ์ ์์ ์ฌ์ฉ๋ ๊ฒ์ด Inline View Subquery๋ผ ํ๋ค.
- subquery๊ฐ from ์ ์ ์ฌ์ฉ๋๋ฉด View ์ฒ๋ผ ๊ฒฐ๊ณผ๊ฐ ๋์ ์ผ๋ก ์์ฑ๋ ํ ์ด๋ธ๋ก ์ฌ์ฉ ๊ฐ๋ฅํ๋ค.
- ์์์ ์ธ ๋ทฐ์ด๊ธฐ ๋๋ฌธ์ DB์ ์ ์ฅ๋์ง๋ ์๋๋ค.
- ๋์ ์ผ๋ก ์์ฑ๋ ํ ์ด๋ธ์ด๊ธฐ ๋๋ฌธ์ column์ ์์ ๋กญ๊ฒ ์ฐธ์กฐํ ์ ์๋ค.
-> ๋ชจ๋ ์ฌ์์ ํ๊ท ๊ธ์ฌ๋ณด๋ค ์ ๊ฒ ๋ฐ๋ ์ฌ์๋ค๊ณผ ๊ฐ์ ๋ถ์์์ ๊ทผ๋ฌดํ๋ ์ฌ์์ ์ฌ๋ฒ, ์ด๋ฆ, ๊ธ์ฌ, ๋ถ์๋ฒํธ
select employee_id, first_name, salary, department_id
from (
select distinct department_id
from employees
where salary < (select avg(salary) from employees)
) d join employees e
on d.department_id = e.department_id;
-> limit ํ์ฉ ( MYSQL์ ์ฅ์ )
-> ๋ชจ๋ ์ฌ์์ ์ฌ๋ฒ , ์ด๋ฆ, ๊ธ์ฌ ์ถ๋ ฅ
-> ์ฌ์ ์ ๋ณด๋ฅผ ๊ธ์ฌ์์ผ๋ก ์ ๋ ฌ
-> ํ ํ์ด์ง๋น 5๋ช
์ด ์ถ๋ ฅ๋๊ณ
-> ํ์ฌํ์ด์ง๊ฐ 3ํ์ด์ง๋ผ๊ณ ๊ฐ์ ํด๋ผ ( 11๋ฑ ~ 15๋ฑ ์ถ๋ ฅ )
select employee_id, first_name, salary
from employees
order by salary desc limit 10, 5;
Scalar Subquery
- subquery๊ฐ select ์ ์์ ์ฌ์ฉ๋ ๊ฒ์ด Scalar Subquery๋ผ ํ๋ค.
- ํ ๊ฐ์ ํ๋ง ๋ฐํํ๋ค.
-> 60๋ฒ ๋ถ์์ ๊ทผ๋ฌดํ๋ ์ฌ์์ ์ฌ๋ฒ, ์ด๋ฆ, ๊ธ์ฌ, ๋ถ์๋ฒํธ, 60๋ฒ๋ถ์์ ํ๊ท ๊ธ์ฌ
select employee_id, first_name, salary, departname_id,
(select avg(salary) from employees where department_id = 60) as avg60
from employees
where department_id = 60;
Subquery ํ์ฉ
CREATE
-> employees table ์ emp_copy๋ผ๋ ์ด๋ฆ์ ๋ณต์ฌ
create table emp_copy
select *
from employees;
-> 50๋ฒ ๋ถ์์ ์ฌ๋ฒ, ์ด๋ฆ, ๊ธ์ฌ, ๋ถ์๋ฒํธ๋ง emp50์ด๋ผ๋ ํ
์ด๋ธ ์์ฑ
create table emp50
select employee_id, first_name, salary, department_id
from employees
where department_id=50;
INSERT
-> employees table ์์ ๋ถ์๋ฒํธ๊ฐ 80์ธ ์ฌ์์ ๋ชจ๋ ์ ๋ณด๋ฅผ emp_blank์ insert
insert into emp_blank
select *
from employees
where department_id = 80;
UPDATE
-> employees table ์ ๋ชจ๋ ์ฌ์์ ํ๊ท ๊ธ์ฌ๋ณด๋ค ์ ๊ฒ ๋ฐ๋ emp50 table์ ์ฌ์์ ๊ธ์ฌ๋ฅผ 500 ์ธ์
update emp50
set sal = sal + 500
where sal < (select avg(salary) from employees);
DELETE
-> employees table ์ ๋ชจ๋ ์ฌ์์ ํ๊ท ๊ธ์ฌ๋ณด๋ค ์ ๊ฒ ๋ฐ๋ emp50 table์ ์ฌ์์ ํด์ฌ
delete from emp50
where sal < (select avg(salary) from employees);
DATABASE Subquery ๋ง๋ฌด๋ฆฌ
DB์ ๊ฝ์ธ Subquery๋ฅผ ๋ฐฐ์๋ณด์๋ค.
ํ์คํ Join๊ณผ Subquery๋ฅผ ๋ฐฐ์ฐ๋ ์ ๊ทผํ ์ ์๋ ๋ฐ์ดํฐ๋ค์ด ํจ์ฌ ๋ง์ด ๋์ด๋ฌ๋ค.
๋ฌผ๋ก ๋ณต์กํ๊ฒ ๊ตฌํ๋ฉด ์ด๋ ต์ง๋ง ์ด๋์ ๋ ๊ตฌ์กฐ๋ ๋ค ์ดํดํ ๊ฒ ๊ฐ๋ค.
Subquery๋ ๋๊ฐ์ด ๊ตญ์ด ! ๋ฅผ ์ํด์ผํ๋ค.. ใ
ใ
ํ
์ด๋ธ์ ๊ตฌ์กฐ๋ ์ ์์์ผํ๊ณ ..
๊ทธ๋์ผ ์ฟผ๋ฆฌ๋ฌธ์ ์ ์งค์ ์์ ๊ฒ ๊ฐ๋ค.
๋ง์ด ์ฌ์ฉํ๋ ๋งํผ ์ ์์๋ฌ์ผํ๋ค !!