HAVING

HAVING

: GROUP BY 되거나 집계된 데이터 필터링

SELECT GROUP_NAME,
       ROUND(AVG(HEIGHT), 2),
       MAX(HEIGHT),
       MIN(HEIGHT),
       AVG(FOOT_SIZE)
FROM ARTIST
GROUP BY GROUP_NAME
HAVING AVG(FOOT_SIZE) > 240;

 

---

HAVING과 GROUP BY 순서를 바꿔도 실행이 된다.
(논리적으로 GROUP BY 먼저 수행한 후 HAVING이 처리된다.)

SELECT GROUP_NAME,
       ROUND(AVG(HEIGHT), 2),
       MAX(HEIGHT),
       MIN(HEIGHT),
       AVG(FOOT_SIZE)
FROM ARTIST
HAVING AVG(FOOT_SIZE) > 240
GROUP BY GROUP_NAME;

---

HAVING이 GROUP BY 없이 단독으로 사용 가능
(GROUP BY가 없어도 집계만 되어있으면 HAVING 사용 가능)

SELECT MAX(HEIGHT),
       MIN(HEIGHT)
FROM ARTIST
HAVING COUNT(*) > 0; -- 조회되는 행이 1개라도 있으면 집계를 해라,

---

 

HAVING과 WHERE 절과의 차이

실행 결과 같음 

 

[차이]  where절이 group by 전에 실행된다

HAVING : group by 하거나 집계하지 않았을때 사용 불가	WHERE : group by 집계 이전에 실행되어 집계 함수 사용 불가