* 쿼리 블럭: WITH 쿼리 블럭 이름 AS (subquery)
- 사용자의 임시 테이블스페이스에 저장
- in-line view 또는 임시 테이블로 쓰임
* Benefits
- easy to read
- write once, read multiple times (reusability)
- performance improvement
----------------
WITH
dept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total > (SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;
* Guidelines
- only with SELECT
- visibility of query name: all WITH elements and their subqueries defined after it
the main query block and its subqueries
- 쿼리 이름과 테이블 이름이 같다면 쿼리 이름이 우선권을 가짐
- WITH 절에 하나 이상의 쿼리 블럭이 올 수 있으며 콤마로 구분
'Oracle > SQL Fundamentals II' 카테고리의 다른 글
15일차 # 7-7: Top-Down vs. Bottom-Up (0) | 2012.04.24 |
---|---|
15일차 # 7-3 ~ 5: Hierarchical Query (계층 질의) (0) | 2012.04.24 |
14일차 # 6-20: Delete Case (Correlated DELETE) (0) | 2012.04.23 |
14일차 # 6-17: Update Case (Correlated UPDATE) (0) | 2012.04.23 |
14일차 # 6-14: EXISTS 연산자 (0) | 2012.04.23 |