• 해당 Query 안에서만 사용할 수 있는 임시 테이블을 만드는 개념
• WITH 절의 임시 테이블은 메모리를 차지하기 때문에, 리소스를 초과하는 경우 에러를 발생시킬 수 있음

기본 틀

WITH [TEMP TABLE NAME] AS
	(SELECT ~~
    FROM TABLE
    ...
    )
SELECT ~~
FROM [TEMP TABLE NAME]
;

예시

WITH emp_dept AS (
    SELECT e.emp_name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
)
SELECT * FROM emp_dept;

WITH avg_total_salary AS (
    SELECT AVG(salary) AS average_company_salary
    FROM employees
),
     
avg_dpt_salary AS (
    SELECT department,
         AVG(salary) AS average_department_salary
    FROM employees
    GROUP BY department
)
     
SELECT e.id,
       e.first_name,
       e.last_name,
       e.salary,
       average_department_salary,
       average_company_salary
FROM employees e JOIN avg_dpt_salary ads ON e.department = ads.department, avg_total_salary;

참고

• 제로베이스 강의
• GeeksForGeeks
• learnsql.com