문제) “과목이 SQL인 경우, 부서별 평균 점수를 구해서 평균이 80점 이상인 학과만 출력한다. 그 결과를 평균 점수가 높은 순으로 정렬해서 상위 1개만 본다.”
쿼리 작성
SELECT S.DEPT_ID, AVG(G.SCORE) AS AVG_SCORE -- SELECT 6
FROM STUDENT S -- FROM 1
JOIN GRADE G ON S.STUDENT_ID = G.STUDENT_ID -- JOIN / ON 2
WHERE G.SUBJECT = 'SQL' -- WHERE 3
GROUP BY S.DEPT_ID -- GROUP BY 4
HAVING AVG(G.SCORE) >= 80 -- HAVING 5
ORDER BY AVG_SCORE DESC -- ORDER BY 7
LIMIT 1; -- LIMIT 8
SELECT department, name, AVG(salary)
FROM employees
GROUP BY department, name;
✅ 해결 방법 2 : name 컬럼을 집계 해야 함!
SELECT department, MIN(name), AVG(salary)
FROM employees
GROUP BY department;
>>> MIN(name)은 알파벳 순으로 가장 앞선 이름을 가져옴
HAVING vs WHERE
1. WHERE 절
원본 데이터(행)을 걸러내는 조건
예시2) 급여(salary)가 5000 이상인 직원만 대상으로 부서별 평균을 구하기
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
WHERE salary >= 5000 -- 그룹화 전 필터링
GROUP BY department_id;
salary >= 5000 인 행만 남김
그 행들만 department_id 기준으로 그룹핑
그룹별 평균 계산
2. SELECT 절
가공된 값을 보여줄 때 사용 WHERE로 행 필터링 된 후, 최종적으로 결과에 보여질 열을 결정
SELECT name, AVG(score) AS avg_score
FROM students
GROUP BY name;
➡️ 이름과 평균 점수만 결과에 표시.
3. HAVING 절
GROUP BY 로 집계된 결과에 조건을 걸 때 사용 적용 시점 : 집계 이후에 실행 → WHERE 와 달리 집계 함수(avg, sum, count 등)를 조건식에서 사용 가능
SELECT class, AVG(score) AS avg_score
FROM students
GROUP BY class
HAVING AVG(score) >= 80;
서브쿼리
1. WHERE 절 서브쿼리 = 중첩(일반) 서브쿼리
서브쿼리가 먼저 실행되어 값을 반환하고, 메인쿼리는 그 반환된 값을 기준으로 where 조건을 적용한다는 뜻! 형태 : WHERE 컬럼 = (SELECT ...)
-- 서브쿼리 활용
-- 가장 나이가 많은 사람의 이름 찾기
SELECT 이름
FROM basic.theglory
WHERE 나이 = (
SELECT MAX(나이)
FROM basic.theglory);
➡️ WHERE 절에서 행을 거르긴 해야겠는데,,, 변동성이 있을 것 같다? => WHERE절에 서브쿼리를 사용해서 뽑아내라!!
언제 사용 돼?
행을 걸러낼 때 기준값을 동적으로 뽑아와야 하는 경우
이 조건에 해당하는 값이 다른 테이블에도 있는가? 같은 상황에 유용함! (?)
키워드
보다 크다/작다( >, <, =, !=)
단순 비교인데, 비교할 대상이 고정값이 아니라 쿼리 결과일 때, 서브쿼리 필요 예) 전체 평균보다 큰 급여
WHERE salary > (SELECT AVG(salary) FROM employees)
소속 여부 (IN, NOT IN)
- 목록에 속하는지 확인 → 그 목록을 만들어내는 쿼리가 서브쿼리 예) 부서가 '영업부'에 속한 직원만
WHERE department_id IN (SELECT department_id FROM departments WHERE name='영업부')
존재 여부 (EXISTS, NOT EXISTS)
관련 데이터가 존재하면/존재하지 않으면 → 서브쿼리의 결과 존재 유무 체크 예) 프로젝트를 배정받은 직원만
WHERE EXISTS (SELECT 1 FROM project_assignment p WHERE p.emp_id = e.emp_id)
최대/최소와 비교 (MAX, MIN)
최대값/최소값을 가진 행만 찾아라 → 그 최대, 최소값을 뽑는 서브쿼리 필요 예) 급여가 최고인 직원
WHERE salary = (SELECT MAX(salary) FROM employees)
평균보다 큰/작은 (AVG)
평균을 기준으로 비교 → 평균값을 내는 집계 서브쿼리 필요 예) 평균보다 급여가 높은 직원
WHERE salary > (SELECT AVG(salary) FROM employees)
조건이 있을 때 서브쿼리가 WHERE 절이나 HAVING 절에서 어떻게 쓰이는지???
1. WHERE 절에서 서브쿼리
WHERE 절은 행(Row) 단위로 조건을 거는 곳이죠? 즉, 집계가 일어나기 전에 필터링을 합니다.
2. HAVING 에서 서브쿼리
HAVING 절은 GROUP BY로 묶은 그룹 단위에 조건을 거는 곳인거 아시죠? 즉, 집계가 끝난 후에 필터링합니다.
2. FROM 절 서브쿼리 = 인라인 뷰 (가장 많이 사용)
기본 작성법
SELECT 별칭1.컬럼1, 별칭1.컬럼2, 별칭2.컬럼3
FROM (
SELECT 컬럼1, 컬럼2
FROM 테이블명
WHERE 조건
) AS 별칭1
JOIN (
SELECT 컬럼3, 컬럼4
FROM 테이블명
WHERE 조건
) AS 별칭2
ON 별칭1.공통컬럼 = 별칭2.공통컬럼;
FROM 절 서브쿼리는 보통 어떻게 활용하나요? (WITH 구문과 비교)
1. 집계 결과를 재활용
대표적인 활용은 중간 집계!!
한 번 group by 로 묶어서 평균, 합계, 순위 같은 값을 구한 후 그 결과를 가상 테이블처럼 활용
예시) 부서별 평균 급여보다 많이 받는 직원 찾기
SELECT E.EMP_NAME, E.SALARY, T.AVG_SAL
FROM (
SELECT DEPT_ID, AVG(SALARY) AS AVG_SAL
FROM EMP
GROUP BY DEPT_ID
) T
JOIN EMP E ON E.DEPT_ID = T.DEPT_ID
WHERE E.SALARY > T.AVG_SAL;
>>> ✅ 포인트: T 라는 인라인 뷰를 만들고, 바깥 쿼리에서 활용
2. 복잡한 계산 단순화
쿼리를 나눠서 읽기 쉽게 만듦
길면 가독성 저하, 인라인 뷰로 쪼개면 중간 결과 테이블을 보는 것처럼 단순!!
예시) 최근 3개월치 매출만 추려서 평균 계산
SELECT AVG(SALES) AS AVG_SALES
FROM (
SELECT *
FROM SALES
WHERE SALE_DATE >= ADD_MONTHS(SYSDATE, -3)
) LAST3;
>>> ✅ 포인트: 서브쿼리에서는 최근 3개월만 걸러놓고, 바깥 쿼리에서 평균만 구함!!
3. 순위/Top-N 문제 해결
rank, row_number 같은 윈도우 함수와 함께 많이 사용
"상위 3명", "과목별 1등" 같은 문제
예시) 과목별 1등 학생 뽑기
SELECT *
FROM (
SELECT STUDENT_ID, SUBJECT, SCORE,
ROW_NUMBER() OVER (PARTITION BY SUBJECT ORDER BY SCORE DESC) AS RN
FROM GRADE
) T
WHERE RN = 1;
>>> ✅ 포인트: 인라인 뷰에서 순위를 매겨 놓고, 바깥 쿼리에서 1등만 뽑음.
4. 여러 테이블/쿼리 합치기
UNION, 복잡한 JOIN 결과를 인라인 뷰로 감싸서 또 다른 JOIN이나 필터링에 활용
즉, 서브쿼리 결과를 하나의 테이블 처럼 사용!
상황
예시1: UNION 합친 결과 → 인라인 뷰로 감싸서 집계/필터링
예시2: JOIN 결과 → 인라인 뷰로 감싸서 또 다른 집계/연산
-- 예시1
SELECT PRODUCT_ID, SUM(AMOUNT) AS TOTAL_SALES
FROM (
SELECT PRODUCT_ID, AMOUNT
FROM SALES_2024
UNION ALL
SELECT PRODUCT_ID, AMOUNT
FROM SALES_2025
) AS ALL_SALES
GROUP BY PRODUCT_ID;
-- 예시2
SELECT DEPT_ID, AVG(SALARY) AS AVG_SAL
FROM (
SELECT E.EMP_ID, E.DEPT_ID, D.DEPT_NAME, E.SALARY
FROM EMP E
JOIN DEPT D ON E.DEPT_ID = D.DEPT_ID
) AS EMP_DEPT
GROUP BY DEPT_ID;
🚨 단, 이 가상 테이블은해당 쿼리 내에서만 유효하며 재사용은 불가능하다. (재사용은 WITH 구문에서 가능)
2. SELECT절 서브쿼리 = 스칼라 서브쿼리 (스칼라 값 = 단일 값)
기본 작성법
SELECT
컬럼1,
컬럼2,
(
SELECT 컬럼명
FROM 테이블명
WHERE 조건
) AS 별칭
FROM 테이블명;
SELECT
a.이름,
a.나이,
(
SELECT COUNT(*)
FROM basic.theglory2 b
WHERE b.이름 = a.이름
) AS same_name_cnt,
(
SELECT SUM(b.결제금액)
FROM basic.theglory2 b
WHERE b.이름 = a.이름
) AS same_name_sumamount
FROM basic.theglory a;
🚨 주의사항 스칼라 쿼리는 한 개의 값을 반환하며, 그 값을 기준 테이블의 각 행에 반복적으로 붙여주는 쿼리입니다. ▶ 해당 쿼리는 “전체 결과가 1행만 나온다”가 아니라, basic.theglory a의 행 수만큼 결과가 나옵니다. ▶ 왜냐하면 바깥 SELECT의 출력 행 개수는 테이블 a의 행 수로 결정되고, 괄호 안의 서브쿼리들은 각 행마다 계산되는 “스칼라 값(단일 값)”을 만들어 붙이기 때문이에요.
SELECT절 서브쿼리 = 스칼라 서브쿼리는 보통 어떻게 활용해요?
각 행마다 하나의 값을 붙이거나 비교할 때 사용 열을 하나 더 만드는 것처럼 쓸 수 있음
1. 예시1) 학생 이름과 학과 이름 같이 보여주기
SELECT S.NAME,
(SELECT D.DEPT_NAME
FROM DEPARTMENT D
WHERE D.DEPT_ID = S.DEPT_ID) AS DEPARTMENT_NAME
FROM STUDENT S;
>>> ✅ 포인트: 학생 테이블만 조회했는데,
스칼라 서브쿼리 덕분에 "학과 이름"을 붙일 수 있음!!
2. 예시2) 직원과 '부서 평균 급여' 같이 보여주기
SELECT E.EMP_NAME, E.SALARY,
(SELECT AVG(SALARY)
FROM EMP
WHERE DEPT_ID = E.DEPT_ID) AS DEPT_AVG_SAL
FROM EMP E;
>>> ✅ 포인트:
각 행마다 자기 부서 평균(DEPT_AVG_SAL)이 계산돼서 열처럼 붙는다
= SELECT 절 스칼라 서브쿼리의 전형적인 쓰임.
(각 직원의 급여와 자기 부서 평균 급여를 나란히 보여줄 수 있게 됨.)
