SQLD SQL 기본 및 활용(5~8) - 13일차

목차
5.	GROUP BY절과 HAVING절
6.	ORDER BY절
7.	조인
8.	표준 조인

 

5. GROUP BY 절과 HAVING 절

● GROUP BY 절

- 각 행을 특정 조건에 따라 그룹으로 분리하여 계산하도록 하는 구문식

- 그룹에 대한 조건은 WHERE절에서 사용할 수 없음

- GROUP BY 절은 사용하면 데이터가 요약되므로 요약되기 전 데이터와 합께 출력할 수 없음

 

** 문법

 

예제) 부서별(DEPARTMENT_ID) 급여 총 합과 급여 평균 출력

 

예제) 직군(JOB_ID)별 급여 총 합과 급여 평균 출력

 

예제) GROUP BY의 잘못된 사용

 

-> GROUP BY 절에 DEPTNO가 같은 값끼리 묶여서 요약 정보만 SELECT 절에 표현 가능. 따라서 GROUP BY 컬럼과 집계 합수를 사용한 결과만이 전달 가능 => GROUP BY 절에 명시되지 않은 컬럼 전달 불가!

 

● HAVING 절

- 그룹 함수 결과를 조건으로 사용할 때 사용하는 절

- WHERE 절을 사용하여 그룹을 제한할 수 없으므로 HAVING 절에 전달

 

예제) 그룹 함수 조건을 WHERE 절에 전달하는 경우 발생하는 에러

 

예제) 그룹 함수 조건 HAVING 절에 전달

 

예제) WHERE 절과 HAVING 절 동시 사용

 

-> 순서상 WHERE 절을 먼저 수행, 원하는 데이터만 필터링한 후 GROUP BY에 의해 그룹연산을 수행한 뒤 HAVING 절에서 만족하는 데이터만 선택하여 출력함

 

6. ORDER BY 절

● ORDER BY 절

- 데이터는 입력된 순서대로 출력되나, 출력되는 행의 순서를 사용자가 변경하고자 할 때 ORDER BY 절을 사용

- 유일하게 SELECT 절에 정의한 컬럼 별칭 사용 가능

- SELECT 절에 선언된 순서대로의 숫자 전달 가능(컬럼명과 숫자 혼합 사용가능)

 

** 문법

 

● 정렬 순서 (오름차순)

- 한글 : 가, 나, 다, 라...

- 영어 : A, B, C, D...

- 숫자 : 1, 2, 3, 4...

- 날짜 : 과거 날짜부터 시작해서 최근 날짜로 정렬

 

예제) 문자 정렬

문자는 왼쪽부터 값이 작은 순서대로, 같은 값이면 두 번째 값이 작은 순서대로 정렬됨

 

예제) 숫자값을 문자값으로 바꾼 뒤 정렬

 

예제) 날짜 정렬(오름차순: 오래된 순서대로)

 

예제) SELECT 절 컬럼 순서를 사용한 정렬

 

● 복합 정렬

- 먼저 정렬한 값의 동일한 결과가 있을 경우 추가적으로 정렬 가능

 

예제) SALARY 값을 기준으로 내림차순으로 먼저 정렬 후, 동일한 SALARY 값이 있을 경우 HIRE_DATE 값으로 한 번 정렬

 

예제) 컬럼 별칭을 사용한 정렬

-> SELECT 절보다 늦게 수행되는 구문은 ORDER BY 절 뿐이므로 ORDER BY 절만 SELECT 절에서 정의된 컬럼 별칭 사용 가능

 

예제) 컬럼명 또는 컬럼별칭과 컬럼순서를 사용한 정렬

 

● NULL의 정렬

- NULL을 포함한 값의 정렬 시 ORACLE은 기본적으로 NULL을 마지막에 배치(SQL Server는 처음에 배치)

 

예제) NULL을 포함한 컬럼의 정렬 결과(ORACLE)

-> NULLS LAST가 기본이므로 NULL이 마지막에 배치

 

-> NULLS FIRST로 NULL 정렬 위치 변경 가능

 

7. 조인

● JOIN(조인)

- 여러 테이블의 데이터를 사용하여 동시 출력하거나 참조할 경우 사용

- FROM 절에 조인할 테이블 나열

- 동일한 열 이름이 여러 테이블에 존재할 경우 열 이름 앞에 테이블 이름이나 테이블 Alias 붙임

 

● 조인 종류

1. 조건의 형태에 따라

  1) EQUI JOIN(등가 JOIN) : JOIN 조건이 동등 조건인 경우

  2) NON EQUI JOIN : JOIN 조건이 동등 조건이 아닌 경우

 

2. 조인 결과에 따라

  1) INNER JOIN : JOIN 조건에 성립하는 데이터만 출력하는 경우

  2) OUTER JOIN : JOIN 조건에 성립하지 않는 데이터도 출력하는 경우 (LEFT/RIGHT/FULL OUTER JOIN으로 나뉨)

 

3. NATRUAL JOIN : 조인조건 생략 시 두 테이블에 같은 이름으로 자연 연결되는 조인

4. CROSS JOIN : 조인조건 생략 시 두 테이블의 발생 가능한 모든 행을 출력하는 조인

5. SELF JOIN : 하나의 테이블을 두 번 이상 참조하여 연결하는 조인

 

● EQUI JOIN(등가 JOIN)

- 조인 조건이 '='(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 얻는 조인 방법

 

 

** 문법(ORACLE 표준)

 

예제) EMP 테이블과 DEPT 테이블 사용하여 각 직원의 이름과 부서명을 함께 출력

 

 

● NON-EQUI JOIN

- 테이블을 연결짓는 조인 컬럼에 대한 비교 조건이 '<', BETWEEN A AND B와 같이 '=' 조건이 아닌 연산자를 사용하는 경우의 조인조건

 

** 문법

 

예제) EMP 테이블의 급여를 확인하고 SAL_GRADE에 있는 급여 등급 기준에 따라 직원이름과 급여, 급여등급 출력

 

 

● 세 테이블 이상의 조인

- 관계를 잘 파악하여 모든 테이블이 연결되도록 조인 조건 명시

- 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 수행 필요!

 

 

8. 표준 조인

● 표준조인

- ANSI 표준으로 작성되는 INNER JOIN, CROSS JOIN, NATURAL, JOIN, OUTER JOIN을 말함

 

● INNER JOIN

- 내부 조인이라고 하며, 조인 조건이 일치하는 행만 추출(ORACLE 조인 기본)

- ANSI 표준의 경우 FROM절에 INNER JOIN 혹은 줄여서 JOIN을 명사

- ANSI 표준의 경우 USING이나 ON 조건절을 필수적으로 사용

 

● ON 절

- 조인할 양 컬럼의 컬럼명이 서로 다르더라도 사용 가능

- 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

- INEER JOIN과 대비되는 조인방식

- JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용

- 테이블 기준 방향에 따라 LEFT OUTER JOIN, 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 테이블의 PROFND가 NULL인 경우는 데이터가 생략됨(INNER JOIN 수행)

-> 지도교수가 없는 학생 정보 출력 시 OUTER JOIN 수행

-> 이 때, 기준이 되는 데이터(생략되지 않았으면 하는 쪽)는 STUDENT 테이블!!

  => LEFT OUTER JOIN

 

ORACLE 표준)

-> ORACLE 표준은 WHERE 절에 조인 조건을 작성하므로 LEFT OUTER JOIN을 기술하지 X

-> WHERE 절에서 기준이 되는 테이블(STUDNET) 반대 테이블 조건 컬럼 뒤에 (+) 를 붙임

 

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 문법)

 

※ 출처 : 홍쌤의 데이터랩 - SQLD