쿼리 처리 순서

Oracle SQL 입문

Hadrien Lacroix

Content Developer

처리 순서가 중요한 이유

쿼리 최적화
- 원치 않는 결과 없음
- 더 빠른 실행

예시

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

예시

    SELECT BillingCountry, AVG(Total) > 100) AS Average
->  FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

예시

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
->  WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

예시

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
->  GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

예시

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
->  HAVING AVG(Total) > 100
    ORDER BY Average DESC

예시

->  SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

예시

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
->  ORDER BY Average DESC

무엇이 문제일 수 있을까?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

별칭은 WHERE, GROUP BY, HAVING에서는 사용할 수 없음
별칭은 ORDER BY에서는 사용할 수 있음

무엇이 문제일 수 있을까?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

집계값은 WHERE 절에서 필터링할 수 없음
집계값은 HAVING 절에서 필터링할 수 있음

무엇이 문제일 수 있을까?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100)
ORDER BY Average DESC

개별 행은 HAVING 절에서 필터링할 수 없음
개별 행은 WHERE 절에서 필터링할 수 있음

쿼리 실행 순서

FROM 및 JOIN: 조회할 데이터 결정
WHERE: 개별 행 필터링
GROUP BY: 행 그룹화
HAVING: 그룹 필터링
SELECT: 열 선택 및 열 함수 적용
DISTINCT: 중복 제거
UNION, UNION ALL, INTERSECT, MINUS: 집합 연산자 적용
ORDER BY: 행 정렬

연습해 봅시다!

Oracle SQL 입문