SQLite에서 WITH 절은 어떻게 사용하나요?

Q1: SQLite에서 WITH 절이란 무엇인가요?
A1: WITH 절은 공통 테이블 표현식(CTE, Common Table Expression)을 정의하는 구문으로, 쿼리 내에서 임시로 사용할 수 있는 이름 있는 결과 집합을 만듭니다. 복잡한 쿼리를 가독성 높게 작성하는 데 도움을 줍니다.

Q2: SQLite에서 WITH 절을 사용하는 기본 문법은 어떻게 되나요?
A2:
```sql
WITH cte_name AS (
SELECT ...
)
SELECT ...
FROM cte_name;
```
`cte_name`은 임시 테이블 이름이며, 괄호 안에 정의된 SELECT 문 결과를 참조할 수 있습니다.

Q3: WITH 절을 사용하면 어떤 장점이 있나요?
A3:
- 복잡한 쿼리를 여러 단계로 나누어 읽기 쉽고 유지보수하기 좋음
- 동일한 하위 쿼리를 여러 번 작성할 필요 없이 재사용 가능
- 재귀 쿼리를 작성할 수 있음 (SQLite 3.8.3부터 지원)

Q4: WITH 절에서 여러 개의 CTE를 정의할 수 있나요?
A4: 네, 쉼표(,)로 구분하여 여러 CTE를 연속해서 정의할 수 있습니다. 예:
```sql
WITH cte1 AS (SELECT ...),
cte2 AS (SELECT ... FROM cte1)
SELECT * FROM cte2;
```

Q5: SQLite에서 재귀 CTE는 어떻게 사용하나요?
A5: 재귀 CTE는 WITH 절 뒤에 RECURSIVE 키워드를 붙이고, 자기 자신을 참조하는 쿼리를 작성합니다.
예:
```sql
WITH RECURSIVE cnt(x) AS ( SELECT 1
UNION ALL
SELECT x+1 FROM cnt WHERE x < 10
)
SELECT * FROM cnt;
```
위 쿼리는 1부터 10까지 숫자를 생성합니다.

Q6: WITH 절로 만든 CTE는 어디서 참조할 수 있나요?
A6: WITH 절에서 정의된 CTE는 그 바로 뒤에 오는 단일 SQL 문 내에서만 참조 가능합니다. 다른 쿼리에서는 재사용할 수 없습니다.

Q7: WITH 절 사용 시 주의할 점이 있나요?
A7:
- CTE는 임시 테이블처럼 동작하지만 인덱스는 없으며 성능에 영향을 줄 수 있음
- 복잡한 재귀 CTE는 리소스 소모가 클 수 있으므로 조건을 명확히 작성할 것
- SQLite 버전 3.8.3 이전에서는 WITH 절 미지원

Q8: 예제를 통해 WITH 절 사용법을 보여주세요.
A8:
```sql
WITH recent_orders AS (
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date > date('now', '-30 days')
)
SELECT customer_id, COUNT(*) AS recent_order_count
FROM recent_orders
GROUP BY customer_id;
```
위 쿼리는 최근 30일간 주문한 고객별 주문 건수를 구합니다.

---

요약하면, SQLite의 WITH 절은 복잡한 쿼리를 모듈화하여 가독성을 높이고 재귀 쿼리 작성도 지원하는 강력한 도구입니다.

SQLite에서 `WITH` 절은 공통 테이블 표현식(Common Table Expression, CTE)을 정의하는 데 사용됩니다.

CTE는 쿼리 내에서 재사용할 수 있는 임시 결과 집합을 생성하는 방법으로, 복잡한 쿼리를 더 간결하고 읽기 쉽게 만들어 줍니다.

`WITH` 절은 주로 서브쿼리의 복잡성을 줄이고, 쿼리의 가독성을 높이며, 여러 번 사용되는 서브쿼리를 한 번만 정의하여 성능을 개선하는 데 유용합니다.

기본 구문 `WITH` 절의 기본 구문은 다음과 같습니다: ```sql WITH cte_name AS ( -- CTE 정의를 위한 쿼리 SELECT column1, column2 FROM table_name WHERE condition ) SELECT * FROM cte_name; ``` 예제 다음은 `WITH` 절을 사용하여 직원의 평균 급여를 계산하고, 이를 기반으로 급여가 평균 이상인 직원 목록을 조회하는 예제입니다.

```sql WITH AverageSalary AS ( SELECT AVG(salary) AS avg_salary FROM employees ) SELECT e.name, e.salary FROM employees e, AverageSalary a WHERE e.salary > a.avg_salary; ``` 이 예제에서 `AverageSalary`라는 CTE를 정의하여 `employees` 테이블의 평균 급여를 계산합니다.

이후 메인 쿼리에서는 이 CTE를 사용하여 평균 급여보다 높은 급여를 받는 직원의 이름과 급여를 조회합니다.

여러 CTE 사용하기 `WITH` 절에서는 여러 개의 CTE를 정의할 수 있습니다.

각 CTE는 쉼표로 구분됩니다.

다음은 두 개의 CTE를 사용하는 예제입니다.

```sql WITH DepartmentCount AS ( SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id ), HighSalaryEmployees AS ( SELECT name, salary, department_id FROM employees WHERE salary > 100000 ) SELECT d.department_id, d.employee_count, h.name, h.salary FROM DepartmentCount d JOIN HighSalaryEmployees h ON d.department_id = h.department_id; ``` 이 예제에서는 `DepartmentCount` CTE를 사용하여 각 부서의 직원 수를 계산하고, `HighSalaryEmployees` CTE를 사용하여 급여가 100,000 이상인 직원 목록을 가져옵니다.

마지막으로 두 CTE를 조인하여 부서 ID, 직원 수, 직원 이름 및 급여를 조회합니다.

재귀 CTE SQLite는 재귀 CTE도 지원합니다.

재귀 CTE는 자기 자신을 참조하여 반복적으로 데이터를 처리할 수 있는 기능을 제공합니다.

다음은 재귀 CTE의 예입니다.

```sql WITH RECURSIVE EmployeeHierarchy AS ( SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL -- 최상위 관리자 선택 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN EmployeeHierarchy eh ON e.manager_id = eh.id ) SELECT * FROM EmployeeHierarchy; ``` 이 예제에서는 `EmployeeHierarchy`라는 재귀 CTE를 정의하여 최상위 관리자부터 시작하여 모든 직원의 계층 구조를 조회합니다.

첫 번째 쿼리는 최상위 관리자를 선택하고, 두 번째 쿼리는 현재 CTE의 결과를 사용하여 하위 직원을 찾습니다.

결론 SQLite에서 `WITH` 절은 쿼리의 가독성을 높이고 복잡한 쿼리를 단순화하는 데 매우 유용한 도구입니다.

CTE를 사용하면 쿼리의 구조를 명확하게 하고, 여러 번 사용되는 서브쿼리를 효율적으로 관리할 수 있습니다.

또한 재귀 CTE를 통해 계층적 데이터를 처리하는 데도 유용합니다.

이러한 기능들은 데이터베이스 쿼리를 작성할 때 매우 강력한 도구가 될 수 있습니다.