서브쿼리, 집합 연산자 ,단일 행 함수 , GROUP BY, HAVING

1. 서브 쿼리 

 

서브 쿼리는 SQL문에 완전한 SELECT문이 포함되며, 원래 SQL문은 메인 쿼리, 내부 SELECT문은 서브 쿼리라고 부른다. 서브 쿼리의 종류는 단일 행 서브 쿼리, 다중 행 서브 쿼리, 다중 열 서브 쿼리가 있다. 

 

1-1 단일 행 서브쿼리 

 

===============================================

SELECT 컬럼 ...

FROM 테이블

WHERE 컬럼 단일_행_연산자 (SELECT 문장 : Sub query문)

......;

===============================================

• 단일 행 연산자가사용됨으로 반드시 서브 쿼리의 결과 값은 한 개만 검색돼야 한다.
• 서브 쿼리는 반드시 괄호로 묶는다.
• 서브 쿼리는 메인 쿼리 실행 전에 실행된다.
• 서브 쿼리의 검색된 결과 값은 메인 쿼리에 사용된다.
• 단일 행 연산자 오른쪽에 기술한다.
• WHERE절에 기술된 열의 숫자와 타입은 SELECT절과 1:1 대응 관계가 되어야 한다.

 

1-2 다중 행 서브 쿼리

 

=====================================================

SELECT 컬럼 ...

FROM 테이블

WHERE 컬럼 다중_행_연산자 (SELECT 문장 : Sub query문)

......;

=====================================================

• 서브 쿼리에 여러 행이 검색되는 경우 사용
• 다중 행 연산자를 이용
• 다중 행 연산자의 종류(IN, ANY, ALL)
• IN은 나열된 값 중에 하나만 일치하면 된다.
• ANY : 나열된 값 일부와 일치하면 된다.
• ALL : 나열된 값 전체와 일치해야 한다. 
• 컬럼 > ALL → 컬럼 > MAX() : 가장 큰값보다크다
• 컬럼 < ALL → 컬럼 < MIN() : 가장 작은값보다작다.
• 컬럼 > ANY → 컬럼> MIN() : 가장작은값보다크다.
• 컬럼 < ANY → 컬럼 < MAX() : 가장큰값보다작다.

 - ALL은 범위가 좁고, ANY는 범위가 넓다. 

 

1-3 다중 열 서브 쿼리 

 

==============================================

SELECT 컬럼 ...

FROM 테이블

WHERE (컬럼1, 컬럼2, ...) IN (SELECT 문장 : Sub query문)

......;

==============================================

• IN 연산자만 있으며, 반드시 써야할 수도 있음
• 서브 쿼리의 SELECT문에 여러 개의 컬럼을 검색
• 여러 개의 컬럼을 검색하는 서브 쿼리 문을 이용할 때는 반드시 비교 대상 컬럼과 대응돼야 함
• 다중 열 서브쿼리에서 서브 쿼리의 검색 결과가 단지 하나의 행이면 = 연산자가 가능은 하지만 되도록 IN 연산자를 이용

 

2. 집합 연산자

 

===========================================

SELECT ...

 [UNION ALL | UNION | INTERSECT | MINUS]

SELECT ...

===========================================

• UNION : 합집합, 검색된 결과의 합으로 중복이 제거, 중복을 제거하기 때문에 정렬이 내부적
• UNION ALL : 합집합, 검색된 결과의 합으로 중복이 허용, 정렬 안함
• INTERSECT : 교집합 , 양쪽 모두에 포함된 결과만 검색
• MINUS : 차집합, 첫 번재 검색 결과에서 두 번째 검색 결과를 제외한 나머지만 검색

3. 단일 행 함수

• 단일 행 함수를 이용하면 검색된 데이터에 대해서 각 행별로 연산된 결과 값을 얻을 수 있으며 중첩이 가능하다.
• 단일 행 함수는 응용프로그램의 오류를 줄이고 개발 시간을 단축 할 수 있다.
• 인수로는 컬럼명, 수식, 변수, 상수 등을 사용할 수있다 =  함수명(인수1, 인수2, ...)
• 단일 행 함수는 SELECT절이나 WHERE절 또는 HAVING 절 등 값이 필요한거의 모든 구문에 사용이 가능하다.
• 단일 행 함수의 종류 - 문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수...

 

 

3-1 문자 함수

함수명	기능
LOWER	문자열을 소문자로 변환 : LOWER('ORACLE') → oracle
UPPER	문자열을 대문자로 변환 : UPPER('oracle') → ORACLE
INITCAP	첫 번째 문자는 대문자로 나머지는 소문자로 변환 : INITCAP('oracle') → Oracle
SUBSTR	문자열내 지정된 위치의 문자열을 검색 : SUBSTR('oracle', 1, 2) → or
INSTR	문자의 위치 반환 : INSTR('ORACLE', 'A') → 3
TRIM	접두(접미) 글자를 잘라냄 : TRIM('o' FROM 'oracle') → racle
LENGTH / LENGTHB	문자열의 길이나 BYTE를 반환 : LENGTH('디비') → 2 LENGTHB('디비') → 4
LPAD/ RPAD	지정된 문자열의 길이 만큼 빈 부분에 문자를 채움 : LPAD('20000', 10, '*') → *****20000

 

 

3-2 문자 치환 함수

단일행	기능
TRANSLATE	문자 단위로 치환된 값을 반환 : TRANSLATE('oracle', 'a','#') → or#cle
REPLACE	문자열 단위로 치환된 값을 반환: REPLACE('oracle', 'or', '##') → ##acle

 

 

3-3 숫자 함수 

함수명	기능
ROUND	반올림해서 소수 이하 n자리까지 출력 : ROUND(123.4567, 3) → 123.457
TRUNC	소수이하 n 자리 미만을 절삭 : TRUNC(123.4567, 3) → 123.456
MOD	m을 n으로 나눈 나머지를 계산 : MOD(10, 4) → 2
POWER	m의 n승을 계산  : POWER(2, 4) → 16
CEIL	m 보다 큰 가장 작은 정수를 검색 : CEIL(2.34) → 3
FLOOR	m 보다 작은 가장 큰 정수를 검색 : FLOOR(2.34) → 2
SQRT	제곱근을 검색 : SQRT(9) → 3
SIGN	음수: -1, 양수: 1, 0 이면 0을 검색 : SIGN(-3) → -1

 

3-4 날짜 함수 

함수명	기능
ROUND	형식에 따라 반올림 : ROUND(sysdate, 'DD') → '2021/09/23'
TRUNC	형식에 따라 절삭 : TRUNC(sysdate, 'YYYY') → '2021/01/01'
MONTHS_ BETWEEN	두 날짜간의 기간을 월수로 계산 :  MONTHS_BETWEEN('2013/09/01', '2013/01/01') → 8
LAST_DAY	날짜를 포함한 달의 마지막 날짜를 검색 :  LAST_DAY('2013/09/24') → 2013/09/30
ADD_ MONTHS	날짜에n 달을더한날짜를검색한다. ADD_MONTHS('2011/07/01', 23) → 2013/06/01
NEXT_DAY	날짜 이후 지정된 요일에 해당하는 날짜를 검색 :  NEXT_DAY('2013/07/14', '일요일') → 2013/07/21

 

3-5 변환 함수

함수명	기능
TO_CHAR	날짜나 숫자를 지정된 형식의 문자로 변환 : TO_CHAR(날짜, 형식), TO_CHAR(숫자, 형식)
TO_DATE	문자를 지정된 형식의 날짜로 해석 : TO_DATE(문자, 형식)
TO_NUMBER	문자를 숫자로 해석 , 대부분의 경우 오라클의 자동 형변환에 의해 SQL 구문 작성시에는 사용빈도가 매우 낮다

• TO_CHAR : 데이터를 출력하거나 전달할 때 서식에 맞추어 가공하는 기능을제공 이를 통해 응용프로그램의 부담을 줄이고 가독성과 생산성을 높임
• TO_DATE : 사용자가 입력하거나 전달받은 문자열을 시스템이 정확한 형식에 따라 날짜로 해석하도록 하는함수로 날짜데이터에 반드시 사용

 

4. 그룹 함수

함수	설명
MAX(컬럼)	컬럼값 중에 최대값을 반환
MIN(컬럼)	컬럼값 중에 최소값을 반환
AVG(컬럼)	평균값을 반환
COUNT(컬럼| *)	NULL이 아닌 행의수를 반환(*는모든행의 수를 반환)
SUM(컬럼)	컬럼의 합계를 반환
STDDEV(컬럼)	표준편차를 반환
VARIANCE(컬럼)	분산을 반환

※ 그룹함수에서 NULL은 무시

 

5. GROUP BY

 

=========================

SELECT ...

FROM 테이블 ...

WHERE 조건 ...

GROUP BY 컬럼

ORDER BY 정렬_대상 ... ;

=========================

• GROUP BY 절은 컬럼의 동일한 값을 바탕으로 소그룹에 대한 통계정보를 검색
• SELECT 절에 그룹함수와 같이쓰인 일반컬럼이 GROUP BY 절에 기술되지 않으면 카디널리티(cardinality)가 일치하지 않아 ‘ORA-00937: not a single-group group function’에러가 발생
• 결과값이 정렬되길 원한다면 반드시 ORDER BY절을 추가
• GROUP BY 절은 정렬을 수행

6. 카디널리티와 정렬

• 값의 개수(기수)를 의미
• SELECT 문의 각 컬럼은 반드시 카디널리티가 같아야됨
• '그룹 함수와 같이 검색되는 모든 컬럼은 반드시 GROUP BY절에 기술
• GROUP BY 절에 기술되어도 그룹함수와 같이 검색되지 않는 경우도 있음
• 카디널리티가 일치하는 것이 중요
• 오라클은 해시알고리즘을 이용함으로 정렬되지 않음
• 정렬된 결과를 위해서는 반드시 ORDER BY 절을 이용

 

 

7. HAVING

 

========================

SELECT ...

FROM 테이블 ...

WHERE 조건 ...

GROUP BY 컬럼

HAVING [그룹조건] ...

ORDER BY 정렬_대상 ... ;

=======================

• HAVING : 조건 중에 그룹함수를 포함하는 조건을 기술
• HAVING절은 그룹된 결과에 대한 조건이므로 가능한 GROUP BY절 뒤에 기술하는 것을 권함
• 일반조건은 WHERE 절에 기술
• 그룹된 컬럼(GROUP BY 절에 기술된)에 대한 조건은 HAVING절에 기술가능 > 권하지 않음