집계 함수 - 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수 - SELECT 절, HAVING 절, ORDER BY 절에 사용 가능 특수 경우 - 일반적으로 집계 함수는 GROUP BY 절과 같이 사용되지만 아래와 같이 테이블 전체가 하나의 그룹이 되는 경우에는 GROUP BY 절 없이 단독으로도 사용 가능
GROUP BY 사용할 때 - WHERE 절을 통해 조건에 맞는 데이터를 조회했지만 테이블에 1차적으로 존재하는 데이터 이외의 정보, 예를 들면 각 팀별로 선수가 몇 명인지, 선수들의 평균 신장과 몸무게가 얼마나 되는지, 각 팀에서 가장 큰 키의 선수가 누구인지 등의 2차 가공정보도 필요 - GROUP BY 절은 SQL 문에서 FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용됨
특징 - GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용 - 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행 - GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없음 - 집계 함수는 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행되므로 WHERE 절에는 올 수 없음 - WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거 - GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력 - HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치
HAVING 절 - K-리그 선수들의 포지선별 평균키를 구하는데, 평균키가 180cm 이상인 정보만 표시하고 싶음
- DECODE 함수는 Oracle에서만 사용 가능
NULL 사용 시기 - 빈만을 NULL이 아닌 ZERO로 표현하기 위해 NVL(Oracle)/ISNULL(SQLServer) 함수를 사용하는 경우가 많음 - 다중 행 함수를 사용하는 경우는 오히려 불필요한 부하가 발생하므로 굳이 NVL 함수를 다중 행 함수 안에 사용할 필요 없음 - 다중 행 함수는 NULL인 행을 대상에서 제외 - 100명 중 10명의 성적이 NULL 값일 때 평균을 구하는 다중 행 함수 AVG를 사용하며 NULL 값이 아닌 90명의 성적에 대해 평균값을 구함 - NULL은 연산의 대상이 아니므로 불필요하게 쓰지 말 것 - SUM(CASE MONTH WHEN 1 THEN SAL ELSE 0 END)처럼 ELSE 절에서 0을 지정하면 불필요하게 0이 SUM 연산에 사용되므로 자원이 불필요하게 사용됨 - 같은 이유로 Oracle의 DECoDE 함수는 4번째 인자를 지정하지 않으면 NULL 값이 Default로 할당되므로 굳이 지정하지 않아도 됨
ORDER BY
ORDER BY - SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용 - ORDER BY 절에 칼럼명 대신에 SELECT 절에서 사용한 ALIAS명이나 칼럼 순서를 나타내는 정수도 사용 가능 - 별도로 정렬 방식을 지정하지 않으면 기본적으로 오름차순이 적용되며, SQL 문장의 제일 마지막에 위치 - ASC 오름차순, DESC 내림차순 - Oracle은 NULL 값을 가장 큰 값으로, SQLServer는 NULL을 가장 작은 값으로 취급
특징 - 기본적인 정렬 순서는 오름차순(ASC) - 숫자형 데이터 타입은 가장 작은 값부터, 날짜형 데이터 타입은 날짜 값이 가장 빠른 값이 먼저 출력 - Oracle에서는 NULL 값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 마지막에, 내림차순으로 정렬했을 경우에는 가장 먼저 위치 - SQLServer에서는 NULL 값을 가장 작은 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 먼저, 내림차순으로 정렬했을 경우에는 가장 마지막에 위치
ORDER BY 표시 방법 - 칼럼명 사용 - 칼럼명 + ALIAS 사용 - 칼럼 순서번호 + ALIAS 사용
SELECT 문장 수행 단계 5. SELECT 칼럼명 [ALIAS명] // 데이터 값을 출력/계산 1. FROM 테이블명 // 발췌 대상 테이블 참조 2. WHERE 조건식 // 발췌 대상 데이터가 아닌 것은 제거 3. GROUP BY 칼럼이나 표현식 // 행동을 소그룹화 4. HAVING 그룹조건식 // 그룹핑된 값의 조건에 맞는 것만을 출력 6. ORDER BY 칼럼이나 표현식 // 정렬 - 위 순서는 옵티마이저가 SQL 문장의 SYNTAX, SEMANTIC 에러를 점검하는 순서 - 차례대로 검사하다가 문제가 생기면 뒷내용 안봄
주의사항(1): SELECT와 ORDER BY - ORDER BY 절에 SELECT 절에 없는 내용이 나와도 됨 - 예외적인 상황으로 FROM 절에 없는 내용을 뒤에서 원래 다룰 수 없으나, SELECT DISTINCT를 지정하거나 SQL 문장에 GROUP BY 절이 있거나, SELECT 문에 UNION 연산자가 있으면 열 정의가 SELECT 절에 표시되어야 함 - 관계형 데이터베이스가 데이터를 메모리에 올릴 때 행 단위로 모든 칼럼을 가져오는 것이므로, SELECT 절에서 일부 칼럼만 선택해도 ORDER BY 절에서 메모리에 올라와 있는 다른 칼럼의 데이터 사용 가능
주의사항(2): 인라인 뷰 - 인라인 뷰의 칼럼을 메인 쿼리에서도 사용 가능 - 인라인 뷰에 없는 것을 메인 쿼리에서는 사용할 수 없음
주의사항(3): GROUP BY - GROUP BY 절을 쓰게 되면, 여기에 사용된 컬럼을 기반으로 새로운 데이터 집합을 생성하게 됨(FROM 절처럼 테이블을 통채로 가져오지 않음) - GROUP BY에서 지정하지 않은 컬럼을 SELECT나 ORDER BY에 쓰면 인식안됨
TOP N 쿼리 - ROWNUM(Oracle의 TOP N 쿼리) - Oracle에서 순위가 높은 N개의 row를 추출하기 위한 방법 - Oracle의 경우 정렬이 완료된 후 데이터의 일부가 출력되는 것이 아니라, 데이터의 일부가 먼저 추출된 후(ORDER BY 절은 결과 집합을 결정하는데 관여하지 않음) 데이터에 대한 정렬 작업이 일어나게 됨
TOP N 쿼리 - TOP(N)(SQLServer의 TOP N 쿼리) - Oracle과 달리 별도로 인라인 뷰를 만들 필요X - WITH TIES: 같은 수치를 갖는 데이터가 있으면 다 출력
JOIN
JOIN 필요성 - 선수들의 소속팀에 대한 정보, 프로 축구팀의 전용구장에 대한 정보 - 다른 정보가 들어있는 두 개 이상의 테이블과 연결 또는 결합하여 데이터를 출력하는 경우가 빈번함
JOIN의 개념 - 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것 - 일반적으로 사용되는 SQL 문장의 상당수가 JOIN이라고 볼 수 있음 - 정규화로 인해 분리된 테이블들을 다시 조립하기 위한 명령어
JOIN의 원리 - 일반적인 경우 행들은 PRIMARY KEY(PK)나 FOREIGN KEY(FK) 값의 연관에 의해 JOIN이 성립 - 어떤 경우에는 이러한 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능
ex) 디디의 STADIUM_NAME을 알고 싶다 -> 선수 테이블의 TEAM_ID가 K06 - 팀 테이블에서 K06의 STADIUM_ID가 C02 - 운동장 테이블에서 C02에 해당하는 STADIUM_NAME이 부산아시아드경기장.
JOIN의 주의사항 - FROM 절에 여러 테이블이 나열되더라도 SQL에서 데이터를 처리할 떄는 단 두 개의 집합 간에만 조인이 일어난다는 것 - FROM 절에 A, B, C 테이블이 나열되었더라도 특정 2개의 테이블만 먼저 조인 처리되고, 2개의 테이블이 조인되어서 처리된 새로운 데이터 집합과 남은 한 개의 테이블이 다음 차례로 조인되는 것 - 예를 들어 A, B, C, D 4개의 테이블을 조인하고자 할 경우 옵티마이저는 (((A JOIN B) JOIN C) JOIN B)와 같이 순차적으로 조인을 처리 - 이 때 테이블의 조인 순서는 옵티마이저에 의해서 결정되고, 이것이 주요 튜닝 포인트가 됨
EQUI(등가) JOIN - 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용 - INNER JOIN이라고도 함 - 대부분 PK <-> FK의 관계를 기반으로 함 - 그러나 일반적으로 테이블 설계 시에 나타난 PK <-> FK의 관계를 이용하는 것이지 반드시 PK <-> FK의 관계로만 EQUI JOIN이 성립하는 것은 아님 - JOIN의 조건은 WHERE 절에 기술하게 되는데 '=' 연산자로 표현 - SELECT 테이블1.칼럼명, 테이블2,칼럼명,.. FROM 테이블1, 테이블2 WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2; =FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.칼럼명1 = 테이블2.칼럼명2; - N개의 테이블을 JOIN하기 위해서는 N-1개의 조건이 필요
- 2개의 테이블을 조인하려고 할 때 겹치는 것이 TEAM_ID
- SELECT PLAYER.PLAYER_NAME, PLAYER.BACK.NO, PLAYER.TEAM_ID, TEAM.TEAM_NAME, TEAM.REGION_NAME FROM PLAYER, TEAM WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID
NOn EQII JOIN - 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우 '=' 연산자가 아닌 다른(Between, >, >=, <, <= 등) 연산자 사용
- 사원들의 급여가 급여등급 테이블의 등급으로 표시되기 위해서는 '=' 연산자로 JOIN을 이용할 수가 없음
- 사원 테이블에 들어 있는 데이터를 기준으로 급여등급 테이블의 어느 등급에 속하는지 1:!로 임시 매칭해보면 아래와 같이 변형 가능
- SELECT E.ENAME 사원명, E.SAL 급여, S.GRADE 급여등급 FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL; - SMITH의 SAL은 800, 700~1200 사이에 있기 때문에 급여등급 테이블의 GRADE 1과 1:1 매칭
