[Oracle Database] JOIN, GROUP BY, 집계 함수, HAVING (2023.03.22~23)

2023.03.22~23 학습내용 

 

쿼리

데이터베이스에게 특정한 데이터를 보여달라는 클라이언트(사용자)의 요청

데이터베이스에서 원하는 정보를 가져오는 코드

 

🔎 여러 테이블 한 번에 사용하기

 

스키마 : 테이블(객체)을 저장하는 공간

스키마는 각 계정마다 존재하며 이름이 계정과 같다. 

 

. 접근 연산자

현재 접속한 계정을 기준으로 스키마를 생략해줌

 

 

FROM 뒤에는 콤마(,)를 사용하여 테이블을 2개 이상 쓸 수 있다. 

 

SELECT *
    FROM 직원, 직원연락처 ; 

실행하면
직원 테이블의 튜플(11개) * 직원연락처의 튜플(13개) = 총 143개의 튜플이 출력됨
직원 테이블의 컬럼(9개) + 직원연락처 테이블의 컬럼(3개) = 총 12개의 컬럼이 출력됨

 

이 때부터는 컬럼명 앞에 테이블명을 써야 함.

두 개의 테이블 모두에 같은 컬럼을 가지고 있을 수 있기 때문

 

SELECT 직원.직원ID
             , 직원.이름
             , 직원.나이
             , 직원.연봉
             , 직원연락처.직원ID
             , 직원연락처.연락처
    FROM 직원, 직원연락처
 WHERE 직원.직원ID = 직원연락처.직원ID ; 

 

테이블명에 별칭을 줘서 간단하게 작성할 수 있음

별칭을 쓴 이후부터는 별칭만 사용 가능 (테이블명 사용할 수 없다)

 

SELECT A.직원ID
              , A.이름
              , A.나이
              , A.연봉
              , B.직원ID
              , B.연락처
    FROM 직원 A, 직원연락처 B
 WHERE A.직원ID = B.직원ID ; 

 

🔎 FROM - JOIN

📖 JOIN의 의미와 원리

JOIN 여러 테이블을 연결해 사용하는 것
조인을 사용하는 가장 큰 이유는 여러 테이블에서 필요한 칼럼들을 한 번에 검색할 수 있기 때문

 

⚙ 카티션조인

두 테이블의 모든 튜플에 대해 모든 경우의 수를 붙인 것

 

 

테이블 간에 특정 컬럼으로 연결을 하여 서로 연관된 데이터만 남김

테이블 간에 특정 컬럼으로 연결 → 조인 조건

그 외의 조건은 일반 조건

 

 

 

동등 JOIN

조인 조건이 모두 = 인 경우

대부분 동등 조인 사용

 

비동등 JOIN

조인 조건이 하나라도 = 가 아닌 경우

 

SELECT A.직원ID
              , A.이름
              , A.나이
              , A.연봉
              , B.직원ID
              , B.연락처
    FROM 직원 A, 직원연락처 B
 WHERE A.직원ID <= B.직원ID ; 

 

 

⚙ OUTER 조인

특정 테이블 기준으로 조인에 실패한 대상도 출력

INNER JOIN은 조인에 성공한 대상만 출력하는 대신 OUTER JOIN은 특정 테이블을 기준으로 조인에 실패한 대상도 출력

(+) 기호가 있으면 OUTER JOIN

 

특징

1. (+) 기호는 오라클에서만 사용함

2. (+) 기호가 붙은 쪽의 반대쪽 테이블을 기준으로 보여줌

3. (+) 기호가 없으면 INNER JOIN

 

 

📖 ANSI(AMERICA NATIONAL STANDARD INSTITUTE) 문법 

모든 DBMS에서 사용가능한 조인 문법

 

오라클 조인과 ANSI의 차이점 

오라클 조인
- WHERE 부분에 조인 조건을 사용
- FROM이 깔끔하지만 WHERE가 복잡함
- 아우터 조인은 (+) 기호를 이용해서 표시

ANSI
- FROM 뒤에 ON 부분에 조인조건 사용
- WHERE는 일반조건만 입력하면 되므로 깔끔한 대신 FROM 부분이 복잡함
- LEFT(RIGHT) OUTER JOIN으로 명시하여 (+) 기호가 필요 없음 
  → (+) 이 붙은 반대쪽 테이블의 방향으로 LEFT 혹은 RIGHT를 결정
- 따로 (+) 조건이 없다면 INNER JOIN으로 연결

 

📖 FULL OUTER JOIN 

양쪽 테이블 모두 OUTER JOIN을 하고 싶을 때 FULL OUTER JOIN 사용 

실무에서는 자주 안쓰이지만 시험에 자주 나옴

오라클에는 해당 문법이 없다. (+)는 한쪽에만 사용할 수 있음

 

3개의 테이블 조인하는 법

 

🔎 GRUOP BY

📖 GROUP BY 문법

집계를 구할 수 있는 문법

특정 컬럼(표현식)을 기준으로 튜플(행)을 그룹화(묶어서) 각각 단일행으로 표기

 

GROUP BY는 실제로 출력되는 튜플(행)이 감소함

따라서 입력할 수 있는 컬럼이 제한됨 → GROUP BY에서 사용한 컬럼만 사용 가능, 대신 집계함수를 사용할 수 있음

 

실행순서

① FROM ② WHERE③ GROUP BY④ SELECT

 

 

 

📖 집계함수 (다중행 함수라고도 함)

여러 행을 입력 받아 집계해 하나의 결과로 출력

⚙ COUNT() ★ 많이 쓰이는 함수

그루핑할 컬럼 기준으로 행의 개수를 출력

다른 집계함수와 달리 COUNT는 COUNT(*)를 쓸 수 있음 (다른 함수는 * 사용 불가)

모든 자료형에 이용할 수 있음 (행의 개수를 세는 것이므로)

 

에스터리스크(*)의 경우 NULL이 있던말든 행 자체를 카운트함 → NULL을 포함한 행의 개수 출력

특정 컬럼이 입력되어 있으면 NULL이 있으면 NULL은 제외하고 값이 들어간 행의 숫자를 센다. 

 

 

GROUP BY가 없어도 집계 함수를 사용할 수 있음 

WHY? FROM에 입력된 하나의 테이블을 하나의 그룹으로 보기 때문

단, SELECT에 어떤 컬럼도 사용할 수 없음 (집계함수는 사용가능)

 

 

⚙ MAX(), MIN() : 최댓값, 최솟값

NULL 데이터를 무시한다 → NULL  데이터를 포함한 행은 포함하지 않는다. 

모든 자료형에 이용 가능하다

 

 

날짜형일 때

 

문자열일 때

 

⚙ AVG() : 평균

NULL 데이터는 무시 (ex. 1, 2, null, 3 의 평균 → 1, 2, 3의 평균과 같다)

숫자형만 가능

실행 방식에 주의

 

전부 NULL 값일 경우 NULL값을 출력 → S0006의 경우

 

AVG값 반올림하여 출력

 

 

⚙ SUM() : 합계

NULL 데이터 무시

숫자형에만 이용가능

 

 

🔎 HAVING

집계가 완료된 대상을 필터링하는 문법

 

실행순서

FROM → WHERE → GROUP BY →  HAVING → SELECT

 

HAVING 사용하기 전

 

HAVING 사용한 후

 

HAVING이 필요한 이유

집계함수에 대한 조건을 줄 수 있기 때문

WHERE절은 집계함수에 대한 조건을 줄 수 없음

→ 집계 함수를 쓰려면 GROUP BY가 먼저 처리되어야 함

→ GROUP BY 전에 처리되는 WHERE절에서는 집계 함수 사용 못함

→ HAVING은 GROUP BY 이후 실행되므로 집계함수를 사용할 수 있음

 

 

 

HAVING 에서도 GROUP BY에서 사용한 컬럼만 사용할 수 있음. 단, 집계함수 처리하면 사용할 수 있음

4번의 경우 SELECT가 가장 마지막에 수행되므로 연봉합계 별칭은 HAVING 단계에서 존재하지 않음. 따라서 오류 발생

 

HAVING은 WHERE절과 똑같이 NULL 조건 사용 가능

 

 

🔎 ORDER BY

📖 ORDER BY 문법

 

실행순서

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

 

특정 컬럼을 기준으로 데이터를 오름차순/내림차순 정렬

오름차순이 기본 (ASC 생략)

 

 

내림차순 : 컬럼명 뒤 DESC 입력

 

 

여러가지 컬럼을 가지고 정렬할 수 있음

 

 

일반적으로 SELECT에 입력되지 않은 칼럼도 사용할 수 있음

주의할 점) GRUOP BY가 써있으면 GROUP BY에서 사용한 컬럼만 사용 가능 (실행순서 때문)

 

칼럼이름 외에 AS 명칭이나 숫자로도 표현 가능

 

1. 숫자인 경우 (3) 

SELECT에 있는 3번째 컬럼을 기준으로 정렬함을 의미함

이 경우 실무에서는 잘 사용하지 않음

 

 

별칭으로 정렬