2-5 GROUP BY 절
GROUP BY 절
각 행을 특정 조건에 따라 그룹으로 분리하여 계산하도록 하는 구문식
GROUP BY 절에 그룹을 지정할 컬럼을 전달 (여러 개 전달 가능)
만약 그룹 연산에서 제외할 대상이 있다면 미리 WHERE절에서 해당 행을 제외함
(WHERE절이 GROUP BY절보다 먼저 수행되므로)
그룹에 대한 조건은 WHERE절에서 사용할 수 없음
SELECT 절에 집계 함수를 사용하여 그룹연산 결과 표현
GROUP BY절을 사용하면 데이터가 요약되므로 요약되기 전 데이터와 함께 출력할 수 있음
문법
예제) 부서별(DEPARTMENT_ID) 급여 총 합과 급여 평균 출력
예제) 직군(JOB_ID)별로 급여 총 합과 급여 평균 출력
예제) GROUP BY의 잘못된 사용
-> GROUP BY 절에 DEPTNO를 사용하면 DEPTNO가 같은 값은 값끼리 묶여서 요약 정보만 SELECT절에 표현 가능 따라서 GROUP BY 컬럼과 집계 함수를 사용한 결과만이 전달 가능
-> GROUP BY 절에 명시되지 않은 컬럼 전달 불가!
HAVING 절
그룹 함수 결과를 조건으로 사용할 때 사용하는 절
WHERE 절을 사용하여 그룹을 제한할 수 없으므로 HAVING 절에 전달
HAVING 절이 GROUP BY 절 앞에 올 수는 있지만 뒤에 쓰는 것을 권장
내부적 연산 순서가 SELECT 절보다 먼저이므로 SELECT절에서 선언된 Alias 사용 불가
예제) 그룹 함수 조건을 WHERE절에 전달하는 경우 발생한 에러
예제) 그룹 함수 조건 HAVING 절에 전달
예제) WHERE절과 HAVING절 동시 사용
-> 순서상 WHERE 절을 먼저 수행, 원하는 데이터만 필터링 한 후 GROUP BY에 의해 그룹연산을 수행한 뒤 HAVING절에서 만족하는 데이터만 선택하여 출력함
2-6 ORDER BY 절
ORDER BY 절
데이터는 입력된 순서대로 출력되나, 출력되는 행의 순서를 사용자가 변경하고자 할 때 ORDER BY절을 사용
ORDER BY 뒤에 명시된 컬럼 순서대로 정렬 -> 1차 정렬, 2차 정렬 전달 가능
정렬 순서를 오름차순(ASC), 내림차순(DESC)으로 전달 (생략시 오름차순 정렬)
유일하게 SELECT 절에 정의한 컬럼 별칭 사용 가능
SELECT절에 선언된 순서대로의 숫자로도 사용 가능
문법
정렬 순서 (오름차순)
한글 : 가, 나, 다, 라 ...
영어 : A, B, C, D ...
숫자 : 1, 2, 3, 4 ...
날짜 : 과거 날짜부터 시작해서 최근 날짜로 정렬
예제) 문자 정렬 예제
예제) 날짜 정렬(오름차순 : 오래된 순서대로)
예제) SELECT절 컬럼 순서를 사용한 정렬
복합 정렬
먼저 정렬한 값의 동일한 결과가 있을경우 추가적으로 정렬 가능
-> 1차 정렬한 값이 같은 경우 그 값 안에서 2차 정렬 컬럼값의 정렬이 일어남
예제) SALARY 값을 기준으로 내림차순으로 먼저 정렬 후 동일한 SALARY 값이 있을 경우 HIRE_DATE 값으로 한 번 정렬
예제) 컬럼 별칭을 사용한 정렬
-> SELECT절보다 늦게 수행되는 구문은 ORDER BY절 뿐이므로 ORDER BY절만 SELECT절에서 정의된 컬럼 별칭 사용 가능
2-7 조인
JOIN(조인)
여러 테이블의 데이터를 사용하여 동시 출력하거나 참조 할 경우 사용
FROM절에 조인할 테이블 나열
ORACLE 표준은 테이블 나열 순서 중요하지 X, ANSI 표준은 OUTER JOIN시 순서 중요
WHERE 절에서 조인 조건을 작성(ORACLE 표준)
동일한 열 이름이 여러 테이블에 존재할 경우 열 이름 앞에 테이블 이름이나 테이블 Alias 붙임
N개의 테이블을 조인하려면 최소 N-1개의 조인 조건이 필요
ORACLE 표준과 ANSI 표준이 서로 다름
조인 종류
1. 조건의 형태에 따라
1) EQUI JOIN(등가 JOIN) : JOIN 조건이 동등 조건인 경우
2) NON EQUI JOIN : JOIN 조건이 동등 조건이 아닌 경우
2. 조인 결과에 따라
1) INNER JOIN : JOIN 조건에 성립하는 데이터만 출력하는 경우
2) OUTTUR JOIN : JOIN 조건에 성립하지 않은 데이터도 출력하는 경우
(LEFT/RIGHT/FULL OUTER JOIN 으로 나뉨)
3.NATURAL JOIN : 조인조건 생략 시 두 테이블에 같은 이름으로 자연 연결되는 조인
4.CROSS JOIN : 조인조건 생략 시 두 테이블의 발생 가능한 모든 행을 출력하는 조인
5.SELF JOIN : 하나의 테이블을 두 번 이상 참조하여 연결하는 조인
EQUI JOUN(등가 JOIN)]
조인 조건이 '='(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 얻는 조인 방법
SQL 명령문에서 가장 많이 사용하는 조인 방법
FROM 절에 조인하고자 하는 테이블을 모두 명시
FROM 절에 명시하는 테이블은 테이블 별칭(Alias) 사용 가능
WHERE 절에 두 테이블의 공통 컬럼에 대한 조인 조건을 나열
문법 (ORACLE 표준)
예제) EMP 테이블과 DEPT 테이블을 사용하여 각 직원의 이름과 부서명을 함께 출력
세 테이블 이상의 조인
관계를 잘 파악하여 모든 테이블이 연결되도록 조인 조건 명시
N개 테이블의 경우 최소 N-1개의 조인 조건 필요
예제) EMPLOYEES, DEPARTMENTS, LOCATIONS 테이블 조인
-> 만약 필수 조인조건이 하나라도 생략될 경우 카타시안곱 발생
(정상 조인보다 더 많은 수의 행이 리턴)
SELF JOIN
한 테이블 내 각 행끼리 관계를 갖는 경우 조인 기법
한 테이블을 참조할 때마다(필요할 때마다) 명시해야 함
테이블명이 중복되므로 반드시 테이블 별칭 사용
예제) EMPLOYEES 테이블에서의 각 직원이름과 매니저 이름을 함께 출력
-> E1의 MANAGER_ID와 E2의 EMPLOYEE_ID가 같은 라인만 출력!
-> EMPLOYEE_ID가 100인 직원은 MANAGER_ID가 NULL이므로 조건에 맞지 않아 생략됨
(ORACLE은 INNER JOIN이 기본 조인 연산임)
-> 조인 조건이 일치하지 않은 데이터를 추가적으로 출력할 경우 OUTER JOIN 수행 필요!
응용 예제) EMP 테이블에서 상위관리자(매니저)보다 급여가 많은 직원 출력
중요 1) 테이블 한 번 스캔 시(한 행만 읽었을 경우) 매니저 정보는 없으므로 셀프 조인 필요
중요 2) 원하는 정보를 모두 한 행으로 출력 후 조건 선택 가능
중요 3) 조인조건과 일반조건을 각자의 위치에 전달
(ORACLE은 모두 WHERE절 기술, ANSI표준은 조인조건은 ON절, 일반조건만 WHERE절)
2-8 표준 조인
표준조인
ANSI 표준으로 작성되는 INNER JOIN, CROSS JOIN, NATURAL JOIN, OUTER JOIN을 말함
INNER JOIN
내부 조인이라고 하며, 조인 조건이 일치하는 행만 추출 (ORACLE 조인 기본)
ANSI 표준의 경우 FROM 절에 INNER JOIN 혹은 줄여서 JOIN을 명시
ANSI 표준의 경우 USING 이나 ON 조건절을 필수적으로 사용
ON절
조인할 양 컬럼의 컬럼명이 서로 다르더라도 사용 가능
ON 조건의 괄호는 옵션 (생략가능)
컬럼명이 같을 경우 테이블 이름이나 별칭을 사용하여 명확하게 지정 (테이블 출처 명확히)
ON 조건절에서 조인조건 명시, WHERE절에서는 일반조건 명시
(WHERE절과 ON절을 쓰임에 따라 명확히 구분)
문법
예제) EMP 테이블과 DEPT 테이블을 사용하여 각 직원의 이름과 부서명을 함께 출력(EQUI JOIN)
-> ORACLE 표준은 FROM 절에 테이블을 컴마로 구분, WHERE 절에 조인 조건 나열
-> ORACLE 은 INNER JOIN이 기본 조인 연산이므로 별도의 문법 존재 안함
USING 조건절
조인할 컬럼명이 같을 경우 사용
Alias 나 테이블 이름 같은 접두사 붙이기 불가
괄호 필수
문법
예제) USING 절을 이용한 사원 이름과 부서이름 조회
NATURAL JOIN
두 테이블 간의 동일한 이름을 가지는 모든 컬럼들에 대해 EQUI JOIN을 수행
USING, ON, WHERE 절에서 조건 정의 불가
JOIN에 사용된 컬럼들은 데이터 유형이 동일해야 하며 접두사를 사용불가
문법
예제) NATURAL 조인을 이용한 사원 이름, 부서명 출력
예제) NATURAL JOIN시 주의
-> NATURAL JOIN 은 동일한 이름의 모든 컬럼을 조인 컬럼으로 사용하므로 조인 컬럼의 값이 모두 같을 때만 결과가 리턴됨
-> STUDENT와 PROFESSOR 테이블에는 NAME 컬럼과 PROFNO 컬럼이 컬럼명이 서로 동일함
CROSS JOIN
테이블 간 JOIN 조건이 없는 경우 생성 가능한 모든 데이터들의 조합
(Cartesian product(카타시안 곱)) 출력
양쪽 테이블 행의 수가 곱한 수의 데이터 조합 발생 (m*n)
문법
예제) CROSS 조인
-> 내용이 길어 일부만 출력했지만, 총 56건이 출력됨
(EMP 14건, DEPT 4건이므로 14X4=56)
OUTER JOIN
INNER JOIN과 대비되는 조인방식
JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용
두 테이블 중 한쪽에 NULL을 가지면 EQUI JOIN시 출력되지 않음 -> 이를 출력 시 OUTER JOIN 사용
테이블 기준 방향에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN으로 구분
OUTER JOIN의 종류
1) LEFT OUTER JOIN
FROM 절에 나열된 왼쪽 테이블에 해당하는 데이터를 읽은 후, 우측 테이블에서 JOIN 대상 읽어옴
즉, 왼쪽 테이블이 기준이 되어 오른쪽 데이터를 채우는 방식
우측 값에서 같은 값이 없는 경우 NULL 값으로 출력
2) RIGHT OUTER JOIN
LEFT OUTER JOIN 의 반대
즉, 오른쪽 테이블이 기준이 되어 왼쪽 테이블 데이터를 채우는 방식
FROM절에 테이블 순서를 변경하면 LEFT OUTER JOIN으로 수행 가능
3) FULL OUTER JOIN
두 테이블 전체 기준으로 결과를 생성하여 중복 데이터를 삭제 후 리턴
LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과의 UNION 연산 리턴과 동일함
ORACLE 표준에는 없음
예제) LEFT OUTER JOIN 예제
STUDENT 테이블과 PROFESSOR 테이블을 조인하여 1,4학년 학생들의 이름, 학년, 지도교수이름 출력
-> STUDENT, PROFESSOR 테이블을 PROFNO로 연결하면 학생, 지도교수 정보와 함께 출력 가능
-> STUDENT 테이블의 PROFNO가 NULL인 경우는 데이터가 생략됨(INNER JOIN 수행)
-> 지도교수가 없는 학생 정보 출력 시 OUTER JOIN 수행
-> 이 때, 기준이 되는 데이터 (생략되지 않았으면 하는 쪽)는 STUDENT 테이블!!
-> LEFT OUTER JOIN
ORACLE 표준)
-> ORACLE 표준은 WHERE 절에 조인 조건을 작성하므로 LEFT OUTER JOIN을 기술하지 X
-> WHERE 절에서 기준이 되는 테이블(STUDENT) 반대 테이블 조건 컬럼 뒤에 (+)를 붙임
ANSI 표준)
-> ANSI 표준에서는 조인의 종류를 FROM절에 테이블과 테이블 사이 명시
-> 조인 조건을 바로 뒤에 ON절에 나열
-> WHERE 절은 ON 절 밑에 전달 (순서 중요)
예제) FULL OUTER JOIN
위 조인 결과를 FULL OUTER JOIN 수행 (ANSI 표준으로)
-> 7004,7005번 학생 정보는 LEFT OUTER JOIN 에서도, RIGHT OUTER JOIN에서도도 출력됨
-> LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과를 동시 출력 (중복 데이터는 한 번 만)
-> ORACLE에서는 지원하지 않는 문법((+) 기호를 양 방향 전달 시 에러 발생)
-> 성능적으로 좋지 않기 때문에 사용 시 주의 필요
ORACLE 문법)
참조
해당 글은 홍쌤의 데이터랩의 영상강의를 기반으로 작성되었습니다.
