SELECT
    p.product_name,
    p.price,
    (
        SELECT AVG(p2.price)
        FROM products p2
        WHERE p2.category_id = p.category_id
    ) AS category_avg_price,
    p.price - (
        SELECT AVG(p2.price)
        FROM products p2
        WHERE p2.category_id = p.category_id
    ) AS diff_from_avg
FROM products p;

실행 완료 또는 조회 결과가 표시됩니다.

SELECT
    p.product_name,
    p.price,
    cat_avg.avg_price
FROM products p
JOIN (
    SELECT category_id, AVG(price) AS avg_price
    FROM products
    GROUP BY category_id
) AS cat_avg ON p.category_id = cat_avg.category_id
WHERE p.price > cat_avg.avg_price;

SELECT customer_name, email
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
);

SELECT customer_name, email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
);

SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM blacklist b
    WHERE b.customer_id = o.customer_id
);

SELECT product_name, price
FROM products
WHERE price = (
    SELECT MAX(price) FROM products
);

SELECT product_name, price, category_id
FROM products p
WHERE price = (
    SELECT MAX(price)
    FROM products p2
    WHERE p2.category_id = p.category_id
);

[바깥 쿼리가 서브쿼리를 만난다]
   │
   └─ 바깥 컬럼을 참조하나? (상관 여부)
        │
        ├─▶ 예: 상관 서브쿼리
        │      → 바깥 행 1건마다 다시 평가 (N행이면 N번)
        │      예) SELECT ..., (SELECT AVG(price) FROM p2 WHERE p2.cat=p.cat)
        │          → 상품 수만큼 안쪽 SELECT 반복
        │
        └─▶ 아니오: 비상관 서브쿼리
               → 한 번 평가하고 결과를 재사용
               ├─ WHERE IN·EXISTS → 대개 세미조인으로 변환(첫 매칭에서 멈춤)
               ├─ FROM 인라인 뷰   → 결과 집합을 한 번 만들어 테이블처럼 조인
               └─ WITH CTE         → 한 번 평가 (PG12+는 대개 본문에 인라인)

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

WITH active_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
),
customer_stats AS (
    SELECT
        o.customer_id,
        COUNT(*)            AS order_count,
        SUM(o.total_amount) AS total_spent,
        AVG(o.total_amount) AS avg_order_value
    FROM orders o
    WHERE o.customer_id IN (SELECT customer_id FROM active_customers)
    GROUP BY o.customer_id
),
vip_customers AS (
    SELECT
        customer_id,
        total_spent,
        CASE
            WHEN total_spent >= 1000000 THEN 'GOLD'
            WHEN total_spent >= 500000  THEN 'SILVER'
            ELSE                              'BRONZE'
        END AS vip_grade
    FROM customer_stats
)
SELECT
    c.customer_name,
    c.email,
    v.total_spent,
    v.vip_grade
FROM customers c
JOIN vip_customers v ON c.customer_id = v.customer_id
ORDER BY v.total_spent DESC;

WITH RECURSIVE org_tree AS (
    SELECT
        id,
        name,
        manager_id,
        0         AS depth,
        name::TEXT AS path
    FROM employees
    WHERE id = 1

    UNION ALL

    SELECT
        e.id,
        e.name,
        e.manager_id,
        ot.depth + 1,
        ot.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT
    REPEAT('  ', depth) || name AS indented_name,
    depth,
    path
FROM org_tree
ORDER BY path;

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_id IN (
        SELECT order_id
        FROM order_items
        WHERE product_id IN (
            SELECT product_id
            FROM products
            WHERE category = 'ELECTRONICS'
        )
    )
);

WITH electronics AS (
    SELECT product_id FROM products WHERE category = 'ELECTRONICS'
),
electronics_orders AS (
    SELECT DISTINCT order_id FROM order_items
    WHERE product_id IN (SELECT product_id FROM electronics)
),
qualified_customers AS (
    SELECT DISTINCT customer_id FROM orders
    WHERE order_id IN (SELECT order_id FROM electronics_orders)
)
SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM qualified_customers);

WITH recent_orders AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE order_date > '2024-01-01'
)
SELECT * FROM recent_orders WHERE customer_id = 42;