[SQL] 집합 연산자와 집계함수 - UNION/ INTERSECT/ MINUS, COUNT/ AVG/ SUM/ STDDEV/ VARIANCE 등

[ 집합 연산자 ]

• 집합 연산자는 카타시안 곱이 아니라 세로로 붙이는 것이기 때문에 컬럼의 개수와 데이터 타입이 동일해야 함.(컬럼명은 달라도 됨)

1. UNION

• 중복을 제외한 합집합
• 두 조회 결과를 합치되 중복된 행은 1번만 출력

-- 부서번호 10인 사원과 급여 3000 이상인 사원을 합쳐서 조회
SELECT ENAME
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT ENAME
FROM EMP
WHERE SAL >= 3000;

-- 결과 : SAL 컬럼의 이름으로 ENAME 값들이 붙어나옴
SELECT SAL FROM EMP
UNION
SELECT ENAME FROM EMP2;

 

2. UNION ALL

• 중복을 포함한 합집합
• 두 조회 결과를 합치되 중복된 행도 그대로 모두 출력

-- 부서번호 10인 사원과 급여 3000 이상인 사원을 합쳐서 조회
SELECT ENAME
FROM EMP
WHERE DEPTNO = 10
UNION ALL
SELECT ENAME
FROM EMP
WHERE SAL >= 3000;

 

3. INTERSECT

• 교집합
• 두 SELECT 결과에 공통으로 존재하는 행만 출력

SELECT ENAME, DEPTNO
FROM EMP
INTERSECT
SELECT ENAME, DEPTNO
FROM EMP2;

 

4. MINUS(Oracle 전용)

• 차집합
• ORDER BY는 맨 마지막에 한 번만 사용

-- 전체 사원 중에서 부서번호 10인 사원을 제외
SELECT ENAME
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT ENAME
FROM EMP
WHERE DEPTNO = 20
ORDER BY ENAME;

DB	차집합 연산자
Oracle	MINUS
MySQL	없음 (JOIN / NOT EXISTS 사용)
PostgreSQL	EXCEPT
SQL Server	EXCEPT

 

[ 그룹함수 - 집계함수 ]

• 여러 행을 하나의 값으로 반환

함수	설명	NULL 처리
COUNT(*)	전체 행 수	NULL 포함
COUNT(col)	특정 컬럼 행 수	NULL 제외
SUM(col)	합계	NULL 제외
AVG(col)	평균	NULL 제외
MAX(col)	최댓값	NULL 제외
MIN(col)	최솟값	NULL 제외
STDDEV(col)	표준편차(Standard Deviation)	NULL 제외
VARIANCE(col)	분산(Variance)	NULL 제외
LISTAGG(col, sep)	값을 문자열로 합침	NULL 제외

 

1. COUNT : 전체 또는 특정 그룹 행의수

-- 전체 행 수 (NULL 포함)
SELECTCOUNT(*)FROM EMP;
-- 결과: 5

-- SAL이 NULL이 아닌 행 수
SELECTCOUNT(SAL)FROM EMP;
-- 결과: 4

 

2. SUM : 합계

-- SAL 합계 (NULL 제외)
SELECT SUM(SAL)FROM EMP;
-- 결과: 3000 + 2500 + 4000 + 3500 = 13000

 

3. AVG : 평균

• 딥러닝, 머신러닝에서는 avg대신 mean이 평균을 의미

-- SAL 평균 (NULL 제외)
SELECT AVG(SAL)FROM EMP;
-- 결과: 13000 / 4 = 3250

 

4. MAX / MIN : 최댓값 /최솟값

SELECT MAX(SAL), MIN(SAL)
FROM EMP;
-- 결과: MAX = 4000, MIN = 2500

 

5) STDDEV / VARIANCE : 표준편차 / 분산 계산

SELECT STDDEV(SAL), VARIANCE(SAL)
FROM EMP;

6. LISTAGG (Oracle) : 값을 문자열로 합침

-- 모든 사원 이름을 하나의 문자열로
SELECT LISTAGG(ENAME,', ')
       WITHINGROUP (ORDERBY ENAME)AS NAME_LIST
FROM EMP;

UNION/ INTERSECT/ MINUS, COUNT/ AVG/ SUM/ STDDEV/ VARIANCE 등