


-- 개선 이전 쿼리
SELECT A.COL1, A.COL2,
(SELECT COUNT(*) FROM TABLE_B WHERE TABLE_B.ID = A.ID) AS COUNT1,
(SELECT COUNT(*) FROM TABLE_B WHERE TABLE_B.ID = A.ID AND TABLE_B.TYPE = 'X') AS COUNT2
FROM TABLE_A A
WHERE A.STATUS = 'ACTIVE';
-- 개선 후 쿼리
WITH CTE AS (
SELECT ID,
COUNT(*) AS TOTAL_COUNT,
SUM(CASE WHEN TYPE = 'X' THEN 1 ELSE 0 END) AS TYPE_X_COUNT
FROM TABLE_B
GROUP BY ID
)
SELECT A.COL1, A.COL2,
C.TOTAL_COUNT,
C.TYPE_X_COUNT
FROM TABLE_A A
LEFT JOIN CTE C ON A.ID = C.ID
WHERE A.STATUS = 'ACTIVE';
-- 개선 이전 쿼리
SELECT A.ID, SUM(B.AMOUNT)
FROM TABLE_A A
JOIN TABLE_B B ON A.ID = B.ID
WHERE B.STATUS = 'COMPLETE'
GROUP BY A.ID
HAVING SUM(B.AMOUNT) > 1000;
-- 개선 후 쿼리
-- 임시 테이블 활용
SELECT ID, SUM(AMOUNT) AS TOTAL_AMOUNT
INTO #TEMP_TABLE
FROM TABLE_B
WHERE STATUS = 'COMPLETE'
GROUP BY ID;
SELECT A.ID, T.TOTAL_AMOUNT
FROM TABLE_A A
JOIN #TEMP_TABLE T ON A.ID = T.ID
WHERE T.TOTAL_AMOUNT > 1000;

