Additional SELECT Option

vencott·2021년 5월 19일
Databaseoracle
0

sinc 인턴교육

목록 보기
5/18

GROUP BY

  • 구문
    • GROUP BY column_name | expr

특정 데이터 집단을 하위 데이터 그룹으로 묶는 개념

그룹 함수는 GROUP BY와 연계해서 사용해야 의미가 있다

SELECT 절에 기술한 컬럼 중, 그룹 함수에 사용되지 않은 컬럼은 GROUP BY 절에 반드시 기술되어야 함

// EMP_ID는 GROUP BY절에 기술되지 않았으므로 에러
SELECT	  EMP_ID
		, SUM(SALARY)
FROM	EMPLOYEE;

// DEPT_ID는 GROUP BY절에 기술되어 있으므로 정상
SELECT		  DEPT_ID
			, SUM(SALARY)
FROM		EMPLOYEE
GROUP BY	DEPT_ID;
// GROUP BY
SELECT		  DEPT_ID
			, SUM(SALARY)
FROM		EMPLOYEE
GROUP BY	DEPT_ID;

SELECT		  DEPT_ID AS 부서
			, ROUND(AVG(SALARY), -4) AS 평균급여
FROM		EMPLOYEE
GROUP BY	DEPT_ID
ORDER BY	1;

SELECT		  DECODE(SUBSTR(EMP_NO, 8, 1)
							, '1', '남'
							, '3', '남'
							, '여')
			  AS 성별
			, ROUND(AVG(SALARY), -4) AS 평균급여
FROM		EMPLOYEE
GROUP BY	DECODE(SUBSTR(EMP_NO, 8, 1)
							, '1', '남'
							, '3', '남'
							, '여')
ORDER BY 	2;

컬럼 별칭이나 컬럼 기술 순서는 GROUP BY의 대상으로 사용할 수 없다

ROLLUP

첫번째 컬럼을 기준으로 누적 총계를 계산

// ROLLUP
SELECT		EMP_NAME
			, DEPT_ID
			, COUNT(*)
FROM 		EMPLOYEE
GROUP BY	ROLLUP(EMP_NAME, DEPT_ID);

HAVING

  • WHERE
    • 테이블에 포함된 원본 데이터를 제한하기 위해 사용
    • 그룹 함수를 사용할 수 없다
  • HAVING
    • GROUP BY에 의해 그룹화된 데이터에 대한 그룹 함수 실행 결과를 제한하기 위해 사용
// HAVING
SELECT		  DEPT_ID
			, SUM(SALARY)
FROM		EMPLOYEE
GROUP BY	DEPT_ID
HAVING		SUM(SALARY) = ( SELECT		MAX(SUM(SALARY))
							FROM		EMPLOYEE
							GROUP BY	DEPT_ID)

ORDER BY

SELECT 구문 실행 결과를 특정 컬럼 값 기준으로 정렬할 때 사용

컬럼 별칭이나 컬럼 기술 순서를 ORDER BY의 대상으로 사용 가능

  • 구문
    • ORDER BY 기준1 ASC/DESC [, 기준2 ASC/DESC, ...]

JOIN

JOIN - Oracle

둘 이상의 테이블을 논리적으로 합치는 것

조건(WHERE)을 주지 않는 JOIN은 의미가 없다

  • Inner JOIN
    • EQUI JOIN
      • WHERE 절에서 = 연산자를 사용해서 JOIN
    • NON-EQUI JOIN
      • 테이블이 업무적인 연관관계도 없고 WHERE 절에 = 연산자 사용하지 않고 JOIN
  • Outer JOIN
    • Inner JOIN 시 누락되는 데이터까지 포함해서 JOIN
    • 외래 키는 부모에 의존하고 NULL이 들어갈 수 있지만 현업에서 튜닝 이슈 상 NULL이 들어가는 경우는 거의 없다
    • Oracle에선 (+) 기호를 사용해서 Outer JOIN
      • (+) 기호가 붙어있는 다른 쪽의 모든 데이터를 출력한다고 생각
// EQUI JOIN / NON-EQUI JOIN
-- EQUI JOIN
SELECT	  EMP_NAME
		, DEPT_NAME
FROM	  EMPLOYEE E
		, DEPARTMENT D
WHERE	E.DEPT_ID = D.DEPT_ID;

-- NON-EQUI JOIN
SELECT	  EMP_NAME
		, SLALRY
		, SLEVEL
FROM	  EMPLOYEE E
		, SAL_GRADE S
WHERE	E.SALARY BETWEEN S.LOWEST AND S.HIGHEST

-- OUTER JOIN
SELECT	  EMP_NAME
		, DEPT_NAME
FROM	  EMPLOYEE E
		, DEPARTMENT D
WHERE	E.DEPT_ID(+) = D.DEPT_ID;

관계형 데이터베이스이기 때문에 현업에서 JOIN이 없는 구문은 없다

JOIN - ANSI 표준

WHERE 절에서 JOIN 조건을 별도로 분리하고 'JOIN' 키워드를 명시적으로 사용

  • ON
    • [INNER] JOIN table2 ON(condition1 [AND condition2 ...])
    • 조건식을 명세
    • NON-EQUI JOIN
  • USING
    • [INNER] JOIN table2 USING(column1 [, ...])
    • 기준컬럼을 정의
      • 업무적인 연관성을 가지는 테이블 사이에서의 공통의 컬럼 = 부모의 기본키와 자식의 외래 키
      • 만약 부모의 기본키와 자식의 외래 키의 이름이 동일하지 않다면 기준 컬럼으로 정의할 수 없어 ON 사용
    • EQUI JOIN
    • 테이블의 별칭은 사용할 수 없음
// ANSI
-- NON-EQUI JOIN
SELECT	  EMP_NAME
		, SLALRY
		, SLEVEL
FROM	  EMPLOYEE E
		, SAL_GRADE S
WHERE	E.SALARY BETWEEN S.LOWEST AND S.HIGHEST

-- ANSI
SELECT	  EMP_NAME
		, SLALRY
		, SLEVEL
FROM	  EMPLOYEE E
JOIN	SAL_GRADE S ON(E.SALARY BETWEEN S.LOWEST AND S.HIGHEST);
// ANSI
-- OUTER JOIN
SELECT	  EMP_NAME
		, DEPT_NAME
FROM	  EMPLOYEE E
		, DEPARTMENT D
WHERE	E.DEPT_ID = D.DEPT_ID(+);

-- ANSI
SELECT	  EMP_NAME
		, DEPT_NAME
FROM	  EMPLOYEE E
JOIN	DEPARTMENT D USING(DEPT_ID);
// ON
-- EQUI JOIN이지만 부모의 기본키와 자식의 외래키의 이름이 달라 USING 대신 ON 사용
SELECT	  DEPT_NAME
		, LOC_DESCRIBE
FROM	DEPTARTMENT
JOIN	LOCATION ON (LOC_ID = LOCATION_ID);

OUTER JOIN

조건을 만족시키지 못하는 행까지 Result Set에 포함시킬 때 사용

LEFT | RIGHT | FULL [OUTER] JOIN ON / USING()

  • LEFT
    • JOIN 키워드를 기준으로 왼쪽에 존재하는 테이블의 모든 데이터를 출력
  • RIGHT
    • JOIN 키워드를 기준으로 오른쪽에 존재하는 테이블의 모든 데이터를 출력
  • FULL
    • JOIN 키워드 양쪽에 존재하는 두 테이블의 모든 데이터를 출력

NATURAL JOIN

NATURAL [INNER] JOIN table2

부모의 기본키와 자식의 외래키가 달라도 굳이 ON을 사용하지 않아도 알아서 매칭해서 INNER JOIN

부모의 기본키 이름이 A, 자식의 외래키 이름을 B로 사용할 때 NATURAL JOIN이 가능하나 만약 자식이 다른 속성의 이름으로 A를 사용할 경우 A끼리 EQUI JOIN 해버리므로 조심

CROSS JOIN

CROSS JOIN table2

카티션 프로덕트

하나의 레코드가 다른 테이블의 모든 레코드에 대응해서 JOIN

SELF JOIN

자기 자신과 JOIN

// SELF JOIN
-- 직원과 관리자의 이름을 출력
SELECT		  E.EMP_NAME AS 직원
			, M.EMP_NAME AS 관리자
FROM		EMPLOYEE E
JOIN		EMPLOYEE M ON (E.MGR_ID = M.EMP_ID)
ORDER BY	1;

-- 관리자가 없는 직원까지 출력 --> OUTER JOIN
SELECT		  E.EMP_NAME AS 직원
			, M.EMP_NAME AS 관리자
FROM		EMPLOYEE E
LEFT JOIN	EMPLOYEE M ON (E.MGR_ID = M.EMP_ID)
ORDER BY	1;

-- 관리자의 관리자 이름을 출력
SELECT		  E.EMP_NAME AS 직원
			, M.EMP_NAME AS 관리자
			, S.EMP_NAME AS 임원
FROM		EMPLOYEE E
JOIN		EMPLOYEE M ON (E.MGR_ID = M.EMP_ID)
JOIN		EMPLOYEE S ON (M.MGR_ID = S.EMP_ID)
ORDER BY	1;

JOIN 연습

// JOIN 연습
SELECT		J.JOB_TITLE
			, E.EMP_NAME
			, D.DEPT_NAME
FROM		JOB J
JOIN		EMPLOYEE E USING(JOB_ID)
JOIN		DEPARTMENT D USING(DEPT_ID)
ORDER BY	1;
// JOIN 연습
SELECT	  S.SLEVEL
		, J.JOB_TITLE
		, E.EMP_NAME
		, E.SALARY
		, D.DEPT_NAME
		, L.LOC_DESCRIBE
		, C.COUNTRY_NAME
FROM	JOB J
JOIN	EMPLOYEE E USING(JOB_ID)
JOIN	DEPARTMENT D USING(DEPT_ID)
JOIN	LOCATION L ON(D.LOC_ID = L.LOCATION_ID)
JOIN	COUNTRY C USING(COUNTRY_ID)
JOIN	SAL_GRADE S ON(E.SALARY BETWEEN S.LOWEST AND S.HIGHEST)
WHERE	JOB_TITLE = '대리'
AND		LOC_DESCRIBE LIKE '아시아%';

SET Opertor

두개 이상의 쿼리 결과를 하나로 결합시키는 연산자

SELECT 절에 기술하는 컬럼 개수와 데이터 타입은 모든 쿼리에서 동일해야 함

Untitled

// UNION
SELECT	  EMP_ID
		,ROLE_NAME
FROM	EMPLOYEE_ROLE
UNION
SELECT	  EMP_ID
		, ROLE_NAME
FROM	ROLE_HISTORY;

// UNION ALL
SELECT	  EMP_ID
		,ROLE_NAME
FROM	EMPLOYEE_ROLE
UNION ALL
SELECT	  EMP_ID
		, ROLE_NAME
FROM	ROLE_HISTORY;

// INTERSECT
SELECT	  EMP_ID
		,ROLE_NAME
FROM	EMPLOYEE_ROLE
INTERSECT
SELECT	  EMP_ID
		, ROLE_NAME
FROM	ROLE_HISTORY;

// MINUS
SELECT	  EMP_ID
		,ROLE_NAME
FROM	EMPLOYEE_ROLE
MINUS
SELECT	  EMP_ID
		, ROLE_NAME
FROM	ROLE_HISTORY;

컬럼의 개수와 타입이 맞아야 한다

ORDER BY 구문은 SELECT 절 맨 마지막에 한번만 기술

각 SELECT 문의 컬럼/별칭이 중복될 경우 컬럼 기술 순서(INDEX)를 사용

UNION의 경우 IN으로 대체되는 경우가 많다

// UNION과 IN
-- UNIONSELECT		  EMP_NAME
			, '사원' 직급
FROM		EMPLOYEE
JOIN 		JOB USING(JOB_ID)
WHERE		JOB_TITLE = '사원'
UNION
SELECT		  EMP_NAME
			, '대리' 직급
FROM		EMPLOYEE
JOIN 		JOB USING(JOB_ID)
WHERE		JOB_TITLE = '대리'
ORDER BY	 2, 1;

-- INSELECT		  EMP_NAME
			, JOB_TITLE 직급
FROM		EMPLOYEE
JOIN 		JOB USING(JOB_ID)
WHERE		JOB_TITLE IN('대리', '사원')
ORDER BY	 2, 1;

Subquery

하나의 쿼리가 다른 쿼리에 포함되는 구조

다른 쿼리에 포함된 내부 쿼리(서브 쿼리)는 외부 쿼리(메인 쿼리)에 사용될 값을 반환하는 역할

  • 단일 행 서브쿼리
    • 단일 행 반환
    • 단일 행 비교 연산자(=, >. < 등) 사용
  • 다중 행 서브쿼리
    • 여려 행 반환
    • 다중 행 비교 연산자(IN, ANY, ALL 등) 사용
// Subquery
SELECT	  EMP_NAME
		, JOB_ID
		, SALARY
FROM	EMPLOYEE
WHERE	JOB_ID = (SELECT	JOB_ID
				  FROM		EMPLOYEE
				  WHERE		EMP_NAME = '나승원')
AND		SALARY > (SELECT	SALARY
				  FROM		EMPLOYEE
				  WHERE		EMP_NAME = '나승원');

SELECT		  EMP_NAME
			, JOB_ID
			, SALARY
FROM		EMPLOYEE
WHERE		SALARY = (SELECT	MIN(SALARY)
					  FROM		EMPLOYEE);

SELECT		DEPT_NAME
			, SUM(SALARY)
FROM		EMPLOYEE
LEFT JOIN	DEPARTMENT USING(DEPT_ID)
GROUP BY	DEPT_ID, DEPT_NAME
HAVING		SUM(SALARY) = (SELECT	MAX(SUM(SALARY))
						   FROM		EMPLOYEE
						   GROUP BY	DEPT_ID);
// 다중 행 다중 열
SELECT	  EMP_ID
		, EMP_NAME
		, SALARY
FROM	EMPLOYEE
WHERE	(SALARY, DEPT_ID) IN (SELECT	  MIN(SALARY)
										, DEPT_ID
							  FROM		EMPLOYEE
							  GROUP BY	DEPT_ID);

NOT IN 을 쓸때 Subquery에서 리턴되는 결과에 NULL이 포함되서는 안된다

// NOT IN
SELECT		  EMP_ID
			, EMP_NAME
			, '관리자' AS 구분
FROM		EMPLOYEE
WHERE		EMP_ID IN(SELECT	MGR_ID
				 	  FROM		EMPLOYEE)
UNION
SELECT		  EMP_ID
			, EMP_NAME
			, '직원'
FROM		EMPLOYEE
WHERE		EMP_ID NOT IN(SELECT	MGR_ID
					 	  FROM		EMPLOYEE
					  	  WHERE		MGR_ID IS NOT NULL)
ORDER BY	3, 1;

다중 행 서브쿼리

결과집합 범위 내에선 ANY

범위를 벗어나는건 ALL

  • < ANY
    • 비교 대상 중 최대 값보다 작음

  • ANY

    • 비교 대상 중 최소 값보다 큼
  • = ANY
    • IN 연산자와 동일
  • < ALL
    • 비교 대상 중 최소 값보다 작음

  • AL

    • 비교 대상 중 최대 값보다 큼
// ANY
SELECT		  EMP_NAME
			, SALARY
FROM		EMPLOYEE
JOIN		JOB USING(JOB_ID)
WHERE		JOB_TITLE = '대리'
AND			SALARY > ANY
						(SELECT	SALARY
						 FROM	EMPLOYEE
						 JOIN	JOB USING(JOB_ID)
						 WHERE	JOB_TITLE = '과장');

INLINE VIEW

서브쿼리를 FROM절에 쓸 수 있다

VIEW = 가상의 테이블

WHERE의 Subquery와 INLINE VIEW의 성능 차이가 있다

// INLINE VIEW
-- 직급별 평균급여를 조회하라
SELECT		  JOB_ID
			, TRUNC(AVG(SALARY), -5)
FROM 		EMPLOYEE
GROUP BY	JOB_ID;

-- 직급별 평균급여를 받는 사원을 조회
SELECT	  EMP_NAME
		, JOB_TITLE
		, SALARY
FROM 	EMPLOYEE
JOIN 	JOB USING(JOB_ID)
WHERE	(JOB_ID, SALARY) IN (SELECT		  JOB_ID
										, TRUNC(AVG(SALARY), -5)
						     FROM		EMPLOYEE
						     GROUP BY	JOB_ID);

-- INLINE VIEW
SELECT		  E.EMP_NAME
			, J.JOB_TITLE
			, SALARY
FROM		(SELECT		  JOB_ID
						, TRUNC(AVG(SALARY), -5) JOBAVG
			 FROM		EMPLOYEE
			 GROUP BY	JOB_ID) V
JOIN		EMPLOYEE E ON(E.SALARY = V.JOBAVG AND E.JOB_ID = V.JOB_ID)
JOIN		JOB J ON(J.JOB_ID = E.JOB_ID);

EXISTS, NOT EXISTS

Subquery의 결과집단의 존재 유무만 판단

EXISTS(SELECT NULL FROM EMPLOYEE WHERE E.EMP_ID = MGR_ID)

스칼라 서브쿼리

출처: SHINSEGAE I&C 인턴십

profile
vencott
Backend Developer
이전 포스트

Additional SELECT 함수

다음 포스트

DDL 테이블

0개의 댓글