SQL 쿼리문 정리 - Subquery

 

[ subquery ] - where 문

 

outer query : 바깥의 query를 뜻

inner query, sub query, nested query : 안의 query를 뜻

 

outer query의 attribute 값과 inner query의 colum을 비교한다.

이때 inner query가 outer query 값에 영향을 받을 때 Correlated subquery 라고 부른다.

 

 

 

* in, some, all : outer query의 attribute 와 inner query의 attribute에 해당되는 colum과 비교한다

 

1. in : 포함여부(true, false) 즉 outer query가 inner query에 포함되는 결과만 반환

  즉 intersect와 동일한 결과

 

2. not in : 포함하지 않는 여부(true, false) 즉 outer query가 inner query에 포함되지 않는 결과만 반환

  즉 except와 동일한 결과

 

Q : Find courses offered in Fall 2009 and in Spring 2010

-- 1. intersect 교집합을 통한 결과
(select course_id
from section
where semester = 'Fall' and year = 2009)

intersect

(select course_id
from section
where semester = 'Spring' and year = 2010);

-- 2. in : 포함 여부를 통한 결과
select distinct course_id
from section
where semester = 'Fall' and year = 2009 and course_id in
	(select course_id
	from section
	where semester = 'Spring' and year = 2010);

 

Q : Find courses offered in Fall 2009 but not in Spring 2010

-- 1. except 차집합을 통한 결과
(select course_id
from section
where semester = 'Fall' and year = 2009)

except

(select course_id
from section
where semester = 'Spring' and year = 2010);

-- 2. not in : 비포함 여부를 통한 결과
select distinct course_id
from section
where semester = 'Fall' and year = 2009 and course_id not in
	(select course_id
	from section
	where semester = 'Spring' and year = 2010);

 

Q : Find the total number of (distinct) students who have taken course sections taught by the instructor with ID 10101

-- 1. in을 통한 결과
select count(distinct ID)
from takes
where (course_id, sec_id, semester, year) in
	(select course_id, sec_id, semester, year
	from teaches
	where teaches.ID = '10101');

-- 2. join, using 과 where 조건을 통한 결과
select count(distinct takes.ID)
from takes join teaches using (course_id, sec_id, semester, year)
where teaches.ID = '10101';

 

3. comp + some : outer query 중 inner query 간의 comp를 만족하는 결과를 반환.

  즉 at least one 과 동일한 의미이다.

 

보통 <, > 와 some의 경우 해당 colum의 min값과 비교되는 의미이다.

 

Q : Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.

-- 1. some을 통한 연산 결과
select name
from instructor
where instructor.salary > some
	(select salary
	from instructor
	where dept_name = 'Biology');

-- 2. as 와 Cartesian product를 통한 결과
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';

 

4. some 과 in 비교

- = some (r) 는 in (r) 과 동일한 결과이다. : r에 만족하는 값이 있는지 여부이기 때문이다.

- <> some (r) 은 not in (r) 과 완전 다른 결과이다. : r에 만족하지 않는 값이 있는지 여부 / r에 만족하는것이 존재하면 안된다

 

5. comp + all : outer query의 값이 항상 inner query의 값과 comp를 만족하는 결과를 반환.

 

보통 <, >와 all의 경우 해당 colum의 max 값과 비교되는 의미이다.

 

Q : Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department.

select name, salary
from instructor
where salary > all
	(select salary
	from instructor
	where dept_name = 'Biology');

 

 

6. all 과 in 비교

- <> all (r) 는 not in (r) 과 동일한 결과이다. : r에 만족하는 것이 존재하면 안된다는 뜻.

 

7. exists, not excists : 공집합 여부를 반환.

  exists : 존재하는 경우, 즉 outer query 값을 기준으로 inner query가 특정 comp를 만족하는 결과가 있을 경우를 반환한다.

이때 inner query가 outer query 값에 영향을 받을 때 Correlated subquery 라고 부른다.

 

Q : Find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester

-- exists를 통해 공집합이 아는 경우, 즉 comp에 맞는 값이 있을 경우 결과를 출력
select course_id
from section as S
where semester = 'Fall' and year = 2009 and exists
	(select *
	from section as T
	where semester = 'Spring' and year = 2010
		and S.course_id = T.course_id);

 

Q : Find all students who have taken all courses offered in the Biology department.

-> Biology 과목을 모두 수강한 학생들을 찾아라

 

-> 해당 조건을 만족하는 학생은 수강한 과목들 중 Biology 과목들을 지니고 있다

-> Biology 과목 - 해당 학생의 과목들 = 공집합을 만족한다

 

-> 즉 학생들 중 "Bio - 학생의 과목 = 공집합" 을 만족하는 학생들을 출력하면 된다!

-- 1. not exists 와 except를 통한 집합관계를 통한 결과
select *
from student as S
where not exists ( -- 공집합을 만족하는 학생 S를 찾아 출력하자
	-- 공집합 : Bio 전체과목 - 특정 학생 s가 수강한 과목
	-- 즉 학생 s가 Bio 전체과목을 들은 경우만 공집합이 성립된다
	
	-- Biology 소속 과목들 전체 : 3가지
	(select course_id
	from course
	where dept_name = 'Biology')

	except
	
	-- 학생 s의 수강한 과목들 : S의 학생목록별로 달라진다 (Correlated subquery)
	(select T.course_id
	from takes as T
	where S.id = T.id));

-- 2. Bio 과목수 = (takes의 Bio 과목 중 takes의 id가 학생 id와 동일) 과목수 비교를 통한 결과
select *
from student as S
where (select count(distinct course_id)
	  from course
	  where dept_name = 'Biology')
	  =
	  (select count(distinct T.course_id)
	  from takes as T, course as C
	  where 
	  	S.id = T.id and
	  	T.course_id = C.course_id and
	  	C.dept_name = 'Biology');

 

 

[ subquery ] - from 문

 

where 문에 inner query 와 마찬가지로

from 문에 새로운 relation을 넣을 수 있다

만들어진 새로운 relation에서 where 문에 조건을 추가할 수 있다.

 

다만 일반적으로 from 절의 relation의 경우 Correlated subquery가 될 수 없다.

 

Q : Find the average instructors' salaries of those departments where the average salary is greater than 42000.

select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary 
	  from instructor 
	  group by dept_name) as x
where avg_salary > 42000;

-- as 뒤에 새로운 relation 이름과 type을 명시할 수 있다
select dept_name, avg_salary
from (select dept_name, avg(salary)
	 from instructor
	 group by dept_name) as dept_avg(dept_name, avg_salary)
where avg_salary > 42000;

 

Q : Find the instructors's name, salary, dept_name from instructor, and avg_salary where the salary is greater than avg_salary (avg_salary = dept_name의 avg(salary)값)

-- 1. ,를 통한 결합과 where의 조건을 통한 결과
select name, salary, A.dept_name, avg_salary
from instructor as A, 
	(select dept_name, avg(salary) as avg_salary
	from instructor
	group by dept_name) as B
where A.dept_name = B.dept_name and A.salary > B.avg_salary;

-- 2. join 과 using을 통한 결합과 where의 조건을 통한 결과
select name, salary, dept_name, avg_salary
from instructor join
	(select dept_name, avg(salary) as avg_salary
	from instructor
	group by dept_name) as B using (dept_name)
where salary > avg_salary;

-- 3. natural join을 통한 결합, where를 통한 조건 결과
select name, salary dept_name, avg_salary
from instructor natural join 
	(select dept_name, avg(salary) as avg_salary
	 from instructor
	 group by dept_name) as x
where salary > avg_salary;

-- 4. lateral를 통한 correlated 방식
-- , 과 lateral의 where 에서 dept_name을 통하여 결합 후 where의 조건을 통한 결과
select name, salary, dept_name, avg_salary
from instructor as l1, lateral
	(select avg(salary) as avg_salary
	from instructor as l2
	where l1.dept_name = l2.dept_name) as x
where salary > avg_salary;

 

 

 

[ with ] : 임시의 relation 생성

 

1. lateral : 만약 from 절을 correlated subquery로 하고싶을 경우 join 앞에 명시한다.

Q : find the instructors's name, salary, avg_salary (avg_salary = dept_name의 avg(salary)값)

-- lateral 키워드를 사용하여 correlated subquery로 사용이 가능하다.
select name, salary, avg_salary
from instructor l1, lateral
	(select avg(salary) as avg_salary
	from instructor l2
	where l2.dept_name = l1.dept_name) as x;

 

 

2. with : 임시로 새로운 relation을 만든다

새로운 relation을 통해 계속 비교해야 하는경우 with를 통해 만들어 놓고 비교하는 방식으로 사용된다

 

Q : Find all departments with the maximum budget

-- with 를 통해 max_budget relation을 임시로 생성한다
with max_budget(value) as
(select max(budget)
from department)
-- 만들어진 max_budget relation과 결합하여 결과를 구한다
select dept_name
from department, max_budget
where department.budget = max_budget.value;

 

Q : Find all departments where the total salary is greater than the average of the total salary at all departments

-- 1. 각 dept_name 별 합산 relation을 구한다
with dept_total(dept_name, value) as
	(select dept_name, sum(salary)
	from instructor
	group by dept_name),
-- 2. 위의 dept_name 별 평균값을 통해 전체학과의 평균값 relation을 구한다
	dept_total_avg(value) as
	(select avg(value)
	from dept_total)
-- 3. 위에서 만든 dept_name 별 합산, 전체 합산을 통하여 where 조건에 맞게 출력한다
select dept_name, dept_total.value, dept_total_avg.value
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value;

 

 

 

 

[ subquery ] - select 문

select 문에서 subquery가 사용되는 경우 attribute 값이 결과로 나오는 subquery 만 가능하다

이 때 반환값이 scalar 값일 경우 scalar subquery 라고 한다

 

 

Q : Find the instructors's counts of each department

select dept_name, (select count(*)
-- 교수수 구하기 : from 교수에서 dept_name이 같은 수를 반환
	from instructor
	where department.dept_name = instructor.dept_name) 
	as num_instructors
from department;