데이터베이스 개론 & SQL - 4

 

서브쿼리

서브쿼리는 복잡한 조회를 위해 쿼리 안에 넣어진 쿼리를 의미한다. select를 포함한 insert, delete, update 에도 사용이 가능하다. 반대로 바깥 쿼리문을 아웃터 쿼리라고 한다. 서브쿼리를 사용할 때 주의할 점은 괄호 안에 써줘야 한다는 점이다.

 

차근차근히 개념에 접근하자면, id가 13인 사람의 생일보다 빠른 생일을 가진 사람을 조회하고자 한다.

1. id = 13인 사람의 생일을 조회

select birth_date from employee where id = 13;

2. 조회 결과를 토대로 전체 조회를 실시

select id, name, birth_date 
from employee where (1번 조회 결과) > birth_date;

3. 위의 두 쿼리문을 합쳐, 쿼리문 안에 쿼리문을 넣어준다

select id, name, birth_date 
from employee where (select birth_date from employee where id = 13) > birth_date;

 

다중 속성을 포함한 서브쿼리라면?

select id, name, position from employee
where (dept_id, sex)=(select dept_id, sex from employee where id = 1);

위의 예제를 보면 알다시피 id 가 1인 dept_id와 sex 컬럼을 조회하여 좌항의 아우터쿼리 컬럼과 비교한다. 이렇게 다중 컬럼을 조회할 수 있다. 다만 서브쿼리에 id를 읽어오지 않는건 아닌가 생각할 수 있는데 기본적으로 테이블을 select 하면 모든 컬럼이 읽어와지므로 굳이 추가해줄 필요가 없다.

 

서브쿼리에서 다중 튜플이 반환될 경우

아래의 이미지와 같이 다중 튜플이 조회되는 값을 기준으로 서브쿼리를 만들고자 할 때?

select empl_id from works_on 
where empl_id <> 5 and proj_id in (select proj_id from works_on where empl_id = 5);

서브쿼리 결과 상 프로젝트가 다수인 경우 = 을 사용해서 비교하지 못한다. (에러 발생) 이런 경우에는 in 연산자를 사용하여 조회된 내용을 포함하고 있으면 되게끔 처리해준다.

In 연산자

in () 뒤에 들어가는 값과 같다면 true를 반환 <-> not in ()

다만 위의 예시를 보면 works_on 테이블을 동시에 사용하고 있어서 헷갈릴 가능성이 크다. 가급적 as 를 사용해주는 게 좋지만 만에하나 as 를 해주지 않았다면 처리되는 기준으로는 모든 쿼리문 중 해당 attribute 이름을 가지는, 가장 가까운 테이블을 참조하게 된다는 점만 기억해두면 된다. 

 

서브쿼리가 2개 이상일 수 있을까?

만약 위의 예시에서 조회된 내용의 이름도 알고 싶다면 어떻게 해야할까? 답은 간단하다. works_on 테이블에는 이름 정보가 없으니 연결된 employee 테이블을 참고하여 이름 정보를 가져온다. 서브쿼리는 아래처럼 무한히 괄호 안에 중첩하여 써주면 된다.

select distinct id, name from employee where id in (
	select empl_id from works_on 
	where empl_id <> 5 and proj_id in (select proj_id from works_on where empl_id = 5)
);

지금 형태를 보자면 where 절이 무지막지하게 커졌음을 알 수 있다. 그럼 서브쿼리는 where 절에서만 사용이 가능한 것인가?

아니다. 서브쿼리는 from 절 안에서도 사용해줄 수 있다. 위의 결과를 바탕으로 서브쿼리를 작성해보자면,

select distinct id, name
from employee, (
	select empl_id from works_on 
	where empl_id <> 5 and proj_id in (select proj_id from works_on where empl_id = 5)
) as ResultT
where employee.id = ResultT.empl_id;

이런 식으로 나타낼 수 있으며 원래 참조해준 employee 테이블 말고, 가상의 테이블(뷰)을 만든 것이라 생각하면 된다. as로 별칭까지 야무지게 지어줬고 위의 결과와 동일한 결과를 도출해낸다.

 

exists

서브쿼리 내 결과가 최소 1개 이상의 튜플을 가지고 있다면, 존재 여부를 true, false 로 반환한다. <-> not exists

select id, name 
from project
where exists (
	select distinct proj_id from works_on 
	where works_on.proj_id = project.id and empl_id in (7, 12)
);

-- 같은 의미 다른 표현

select id, name 
from project
where id in (
	select distinct proj_id from works_on 
	where empl_id in (7, 12)
);

위의 예제는 in 으로도 사용할 수 있지만 exists 를 사용하여 보면, 서브쿼리 바깥의 project 테이블이 서브쿼리 안에서 사용되고 있음을 알 수 있다. 이는 correlated subquery (상관관계 서브쿼리) 라고 한다. 

correlated subquery

보통의 서브쿼리는 1번 실행되고 그 결과값을 아우터쿼리측에서 활용하는 용도로 사용된다. 하지만 상관관계 서브쿼리는 아우터쿼리쪽의 컬럼과 튜플을 가져온 뒤 서브쿼리에서 계산하게 되고 서브쿼리의 결과값을 아우터쿼리에서 활용하게 되는 순서라고 생각하면 된다. 

In 과 Exists
RDBMS의 종류와 버전에 따라 다르지만... 근래에는 많은 개선으로 성능 차이가 거의 없다고 한다

 

2000년대생이 없는 부서의 id, name 을 조회하면서 not exists 사용해보기

select id, name 
from department
where not exists (
	select distinct dept_id from employee where dept_id = id and birth_date >= '2000-01-01'
);

datatime 타입은 ' 작은 따옴표로 묶어진 'YYYY-MM-DD' 형태로 사용하면 날짜가 된다. 그래서 위처럼 사용하여도 되는 것이다. exists 를 쓸 때는 조건절에 in 에서 추가되는 조건을 꼭 명시적으로 나타내줘야 한다는 것을 잊지말자.

 

Any = some

서브쿼리가 반환한 결과들 중 하나라도 비교연산이 true 면 true를 반환 

select e.id, e.salary, e.name 
from department d, employee e
where d.leader_id = e.id and e.salary < any (
	select salary
	from employee e2 
	where id <> d.leader_id and dept_id = e.dept_id 
);

any 를 위해서 리더보다 높은 연봉을 받는 부서원이 있는 리더의 id, salary, name 을 조회하는 쿼리문이다. 

 

컬럼에 추가된 서브쿼리

위의 예시에서 추가로 부서에서 최고의 연봉을 확인하고 싶다면, 서브쿼리를 컬럼에 사용해줄 수도 있다.

select e.id, e.salary, e.name,
	(select max(salary) from employee where dept_id = e.dept_id) as dept_max_salary
from department d, employee e
where d.leader_id = e.id and e.salary < any (
	select salary
	from employee e2 
	where id <> d.leader_id and dept_id = e.dept_id 
);

from 절과 where 절은 별반 달라진바 없다. 다만 컬럼쪽에 서브쿼리를 추가하고 max() 라는 함수를 사용하여 커스텀 컬럼을 만들어줬다. 즉 컬럼 위치에서도 서브쿼리를 사용해줄 수 있다.

• 서브쿼리의 위치
  • select 뒤 컬럼
  • where 절
  • from 절에서 가상 테이블

 

all

any와 달리 서브쿼리 안의 결과가 비교 연산자와 모두 true 여야 true 를 리턴하는 함수이다.

select e.id, e.name, e.position 
from employee e, works_on wo 
where e.id = wo.empl_id and wo.proj_id <> all (
	select proj_id from works_on where empl_id = 13
);

위의 예시는 id 13인 임직원과 같이 프로젝트를 안해본 사원의 목록을 반환하는 예시이다.

 

 

추가로 서브쿼리가 있는 쿼리문에선 서브쿼리부터 해석해주고, 아우터쿼리를 읽어주는 순서가 자연스럽고
직접 작성해줄 땐 아우터쿼리부터 적어주고, 서브쿼리를 적어주는게 자연스러운 것 같다.
애초에 서브쿼리의 의의가 두 쿼리를 합쳐주는 것이므로 따로 쿼리문을 작성 후 합쳐줘도 되는 것 같다.