내가 헷갈리는 것들만 표로 보기위해 간단하게 표로 정리.
(GPT로 생성했으므로 틀렸을 가능성도 있음)

엔티티

식별자

?

함수 사용 가능여부

무결성 원칙

Row limiting

Regex

pivot

Top N 쿼리

인덱스

서브쿼리

서브쿼리 사용 위치 및 이름

join

트랜잭션 속성

셀프조인

셀프조인으로 계층형 구현하기

SELECT e.employee_name AS employee, m.employee_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_name;

cte

CTE는 Common Table Expression의 약자이며, SQL에서 일시적인 결과 집합을 정의하는 방법입니다. CTE는 쿼리 내에서 재사용 가능한 임시 결과 집합을 만들고, 이를 읽기 쉬운 방식으로 쿼리에서 사용하도록 해줍니다. CTE는 WITH 절을 사용하여 정의됩니다.

CTE의 특징

1. 임시 결과 집합: CTE는 쿼리 내에서만 유효하며, 쿼리 실행이 끝나면 사라집니다.
2. 가독성 향상: 복잡한 쿼리를 여러 번 작성하지 않고, 하나의 이름을 붙여 여러 번 사용할 수 있어 가독성이 높아집니다.
3. 재귀적 쿼리: CTE는 재귀적으로 사용할 수 있어 계층형 데이터를 조회할 때 유용합니다.

CTE의 문법

WITH cte_name AS (
    -- CTE 정의
    SELECT columns
    FROM table
    WHERE condition
)
-- CTE를 사용하는 쿼리
SELECT * FROM cte_name;

• WITH: CTE를 정의하는 키워드.
• cte_name: 정의된 CTE의 이름.
• CTE 본문: CTE 내에서 실행할 쿼리입니다.
• 쿼리 본문: CTE 정의 후, 정의된 CTE를 마치 테이블처럼 쿼리에서 사용할 수 있습니다.

예시 1: 단순 CTE

WITH DepartmentCTE AS (
    SELECT department_id, department_name
    FROM departments
    WHERE department_id > 10
)
SELECT * FROM DepartmentCTE;

• DepartmentCTE는 departments 테이블에서 department_id > 10인 데이터를 가져오는 임시 결과 집합입니다.
• 이 CTE는 다음 쿼리에서 마치 테이블처럼 사용됩니다.

예시 2: 재귀적 CTE (계층형 쿼리)

재귀적 CTE는 자기 자신을 참조하는 CTE로, 부모-자식 관계를 표현할 수 있습니다. 예를 들어, 직원들 간의 상사-부하 관계를 다룰 때 유용합니다.

WITH EmployeeHierarchy AS (
    -- Anchor member: 최고 경영자 (manager_id가 NULL인 직원)
    SELECT employee_id, employee_name, manager_id, 1 AS LEVEL
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive member: 각 직원의 하위 직원을 찾기
    SELECT e.employee_id, e.employee_name, e.manager_id, eh.LEVEL + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, employee_name, manager_id, LEVEL
FROM EmployeeHierarchy
ORDER BY LEVEL, manager_id;

설명:

1. Anchor member: manager_id가 NULL인 최고 경영자부터 시작하여 계층의 루트를 정의합니다.
2. Recursive member: UNION ALL을 사용하여 재귀적으로 하위 직원을 찾습니다. manager_id가 employee_id와 일치하는 직원을 찾고, LEVEL을 1씩 증가시킵니다.
3. 결과: 각 직원과 그들의 상사, 계층이 몇 번째 레벨인지를 출력합니다.

CTE 사용의 장점

1. 쿼리 재사용: CTE를 정의한 후 여러 번 사용할 수 있으므로, 복잡한 쿼리에서 중복을 줄일 수 있습니다.
2. 가독성 향상: CTE는 쿼리를 잘게 나누어 읽기 쉽게 만들고, 이해하기 쉬운 이름을 지정함으로써 쿼리의 가독성을 높여줍니다.
3. 재귀적 쿼리 처리: CTE는 재귀적으로 데이터를 처리할 수 있어 계층형 데이터나 자기 참조적인 데이터를 다룰 때 매우 유용합니다.
4. 성능 최적화: 복잡한 서브쿼리를 CTE로 변환하여 쿼리 성능을 최적화할 수 있습니다.

CTE와 서브쿼리의 차이

• CTE는 쿼리 상단에서 정의되고, 다시 사용할 수 있는 임시 결과 집합으로 처리됩니다. 여러 번 호출할 수 있고, 재귀적으로 사용할 수 있습니다.
• 서브쿼리는 쿼리 내에 직접 삽입되는 형태로, 단 한번만 사용됩니다. 서브쿼리 안에서 복잡한 논리를 처리할 수 있지만, 재귀적인 처리는 불가능합니다.

결론

CTE는 일시적인 결과 집합을 정의하여, 복잡한 쿼리를 간결하고 가독성 좋게 작성할 수 있는 방법입니다. 특히 재귀적 CTE를 사용하면 계층형 데이터를 처리하는 데 유용합니다. CTE는 단일 쿼리 내에서 재사용 가능하며, 복잡한 로직을 더 간결하게 처리할 수 있습니다.

출처:

https://yunamom.tistory.com/389
https://chatgpt.com/