SQL11: FROM절의 서브쿼리, lateral, with절, 스칼라 서브쿼리

데이터베이스 목차

이 글에서는 University Database가 사용된다.

FROM 절의 서브 쿼리

서브 쿼리의 결과는 테이블이기 때문에 FROM절에서도 서브 쿼리를 사용할 수 있다. 예시를 살펴보자.

ex) 교수의 평균 연봉이 6900 이상인 학과명(deptName)과 평균 연봉)

서브 쿼리 안에서 집계 함수를 사용할 때는 두 가지 방법으로 테이블명과 속성명을 정의해 줄 수 있다. 

첫 번째 방법은 서브 쿼리 안에서 속성별로 이름을 설정해주는 것이고, 두 번째는 서브 쿼리를 다 써준후 밖에서 테이블 명과 속성명을 정해주는 것이다.

SELECT deptName, avgSalary
FROM (SELECT deptName, avg(salary) as avgSalary
      FROM professor
      GROUP BY deptName)
WHERE avgSalary > 6900;

SELECT deptName, avgSalary
FROM (SELECT deptName, avg(salary)
      FROM professor
      GROUP BY deptName) AS deptAvg(deptName, avgSalary)
WHERE avgSalary > 6900;

---

lateral 절

from절에서 서브쿼리 외부에서 사용된 변수를 사용하기 전에 서브쿼리 앞에 lateral 키워드를 붙여주어야한다.

SELECT P1.name, P1.salary, avgSalary
FROM professor P1, lateral(SELECT avg(P2.salary) AS avgSalary
                           FROM professor P2
                           WHERE P1.deptName = P2.deptName);

앞서 표현된 P1을 뒤의 서브쿼리에서 사용하므로 반드시 lateral 키워드를 써주어야 한다.

---

with 절

임시로 테이블을 만들어두고, 다른 질의 작성시에 사용할때 사용하는 절이다.

With maxBudget (value) as
     (SELECT max(budget)
      FROM department)
SELECT deptName, budget
FROM department, maxBudget
WHERE department.budget = maxBudget.value;

with절을 이용해 maxBudget이라는 테이블을 만들어 두고, 이후 질의문에서 사용하고 있는 것을 볼 수 있다.

---

Scalar Subquery

하나의 column, 하나의 tuple만 존재하는 결과테이블을 만들어내는 서브쿼리를 Scalar Subquery라고 한다.

SELECT절에서 사용하기 위해서는 반드시 Scalar Subquery이어야 한다. 결과가 여러개의 속성과 튜플을 가지고 있다면 SELECT절에서는 사용할 수 없다.

SELECT deptName, (SELECT COUNT(*)
                  FROM professor p1
                  WHERE d1.deptName = p1.deptName)
FROM department d1;