※ 모든 데이터는 Oracle에서 제공하는 학습용 데이터를 사용하였다.
SQL의 sub Query 문제풀이 중 한 문제를 발견하였다.
EMLPYEES 테이블에서 가장 많은 사원이 속해 있는 부서 번호와, 사원 수를 출력하라.
문제에서 요구하는 것은
-
<가장 많은 사원>이 속해 있는 <부서 번호>
-
사원 수
이 문제를 해석하면
-
사원들은 각자의 부서(부서번호)를 가지고 있다.
-
즉, 사원의 수는 부서의 수와 동일하다. (둘 중 하나의 값이 null이 아닌 한)
-
각 부서의 수를 비교하면 가장 많은 부서의 수, 즉 가장 많은 사원이 속한 부서를 확인할 수 있다.
위 세개의 전제를 가지고 코드를 작성해보자.
SELECT MAX(COUNT(DEPARTMENT_ID))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
;우선 EMPLOYEES 테이블에서 SELECT로 '가장 많은 사원'을 가져와준다.
여기서 중요한 것은 바로 'GROUP BY' 이다.
GROUP BY 절은 특정 컬럼을 기준으로 집계를 내는데 사용된다.
위 코드를 해석해보면
DEPARTMENT_ID(부서번호)를 기준으로
EMPLOYEES 테이블에서
각 DEPARTMENT_ID의 수를 집계하고, 그중에서 최대값을 선택하겠다.
라는 뜻이다.
만약 저기서 GROUP BY를 사용하지 않고
단순하게 사용하면 어떻게 될까?
SELECT MAX(COUNT(DEPARTMENT_ID))
FROM EMPLOYEES
;물론 에러가 난다.
에러의 내용은 대충 문법에 오류가 있다는 것이다.
왜 오류가 날까?
답은 MAX 함수를 제거하고 실행해보면 간단하게 알 수 있다.
SELECT COUNT(DEPARTMENT_ID)
FROM EMPLOYEES
;위 코드를 실행하면 106(null 제외)이라는 숫자가 나온다.
이유는 EMPLOYEES 테이블에서 부서번호의 <총개수>를 세었기 때문이다.
만약 50번 부서가 4개, 60번 부서가 3개, 70번 부서가 3개 있다고 가정해보자.
그럼 총 부서의 개수는 10개가 나오므로 결과값은 10으로 출력된다.
여기서 MAX 함수를 사용해봤자 10이라는 하나의 수를 가지고 최대값을 구할 수 없기에 오류가 나는 것이다.
때문에 GROUP BY 함수를 사용하여 DEPARTMENT_ID를 기준으로 집계를 하여야 한다.
그럼 위 사진처럼 부서별 개수를 알 수 있고,
이것은 각 부서에 있는 사원들의 수와 동일하므로
'100번 부서에는 6명의 사원이 있다.' 라는 것을 알 수 있다.
그러면 이제 전제는 갖춰졌으니 다시 문제로 돌아와보자.
EMLPYEES 테이블에서 가장 많은 사원이 속해 있는 부서 번호와, 사원 수를 출력하라.
우리가 현재까지 알고 있는 것은 가장 많은 사원이 속해있는 부서와 그 부서의 사원수이다.
앞서 보았던 결과창을 보면 가장 많은 부서는 50번 부서로, 45명의 사원들이 속해있다는것을 알 수 있다.
하지만 문제에서는 부서 전체의 사원수가 아닌 '가장 많은 사원이 속해 있는' 이라는 전제를 깔았으므로,
앞서 사용한 GROUP BY 절을 사용한 코드를 서브쿼리(sub query)로 사용하여 문제를 해결해야 한다.
우선, 문제에서 원한 <부서 번호>와 <사원 수>를 출력하는 구문을 만들어보자.
SELECT DEPARTMENT_ID , COUNT(DEPARTMENT_ID)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
;COUNT(DEPARTMENT_ID)가 사원 수를 출력한다는 것은 앞서 설명했으니 생략한다.
<그럼 여기서 추가할 것은 "가장 많은" 이니까 COUNT문에 MAX 함수를 넣으면 되는거 아닌가?>
라는 생각을 할 수도 있다.
그럼 MAX 함수를 넣으면 어떻게 되는가?
not a single-group group function
이라는 오류가 뜨게 된다.
이 오류가 뜨는 이유는 간단하다.
SELECT DEPARTMENT_ID는 여러 개의 부서를 전부 보여줄태고,
MAX(COUNT(DEPARTMENT_ID))는 최대값 단 하나의 값만 보여줄 텐데
서로의 수가 일치하지 않기 때문에 발생하는 오류이다.
이럴 때 사용하는 것이 바로 서브쿼리(sub query)이다.
서브 쿼리란 단순하다.
함수를 사용하여 조회된 값을 조건에 넣으면 된다는 것이다.
이 말이 무슨 말이냐?
하나의 쿼리만으로 문제를 해결할 수 없을때,
또다를 쿼리의 값을 사용하여 해결한다는 것이다.
SELECT MAX(COUNT(DEPARTMENT_ID))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
;즉, 우리가 처음에 살펴본 이 코드의 값인 "45" 라는 수를 사용한다는 것이다.
<그럼 45를 그대로 코딩하면 되는거 아닌가?>
라는 질문이 있을 수 있다.
언젠가 회사에서 부서 이동이 있을 수 있고,
사원들이 퇴사하거나 입사하는 등 데이터 변동이 종종 있을 것이다.
그런데 "45"라고 하드코딩을 해버리면 조회를 할 때마다 새롭게 값을 입력해야 하는 번거로움이 있다.
때문에 코드 자체를 사용하면 데이터가 변동되어도
그에 따라서 결과값도 유동적으로 바뀔 것이기 때문에, 그것이 바람직하기 때문에
하드코딩 사용은 지양하자.
그럼 위 코드를 서브쿼리로 사용하여 마지막 코드를 살펴보겠다.
SELECT DEPARTMENT_ID , COUNT(DEPARTMENT_ID)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(DEPARTMENT_ID) = (SELECT MAX(COUNT(DEPARTMENT_ID))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID)
;참고로 GROUP BY 절을 사용할 때에는 HAVING 절을 사용하여 조건을 걸 수 있다.
위에 코드를 해석하자면
- SELECT(조회)
👉 DEPARTMENT_ID(부서번호)와 COUNT(DEPARTMENT_ID)(사원수)를- FROM(위치)
👉 EMPLOYEES 테이블에서- GROUP BY(기준)
👉 DEPARTMENT_ID (DEPARTMENT_ID를 기준으로)- HAVING(조건)
👉 COUNT(DEPARTMENT_ID)에 괄호 안에 값을 대입하는 것을 조건으로 결과를 조회하겠다.
즉, HAVING 절에서 사용된 서브쿼리를 COUNT(DEPARTMENT_ID)에 대입하면
SELECT 문에서 사용된 COUNT는 서브쿼리의 값이 된다.
그럼 앞서 구한 50번 부서와 그 부서의 인원인 45명이 출력이 된다.
