[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #8

<목차>

• SELECT 문장 실행 순서

• Top N 쿼리

---

<< ORDER  BY 절 >>

* ORDER BY 절은 SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용.

* ORDER BY 절에 칼럼 명 대신에 SELECT 절에서 사용한 ALIAS 명이나 순서를 나타내는 정수도 사용이 가능.

* 위치는 SQL 문의 가장 마지막에 위치.

* 특징

• 별도로 방식을 지정하지 않으면 기본값은 오름차순(ASC).

• 숫자형 데이터 타입은 오름차순으로 정렬했을 때 가장 작은 값부터 출력.

• 날짜형 데이터 타입은 오름차순으로 정렬했을 때 가장 빠른 날짜부터 출력.

• Oracle 에서는 NULL 값을 가장 큰 값으로 간주.

• SQL Server 에서는 NULL 값을 가장 작은 값으로 간주.

---

* 예시 - 문제 (ORDER BY)

   : 선수 테이블에서 선수들의 이름, 포지션, 백넘버를 출력하라. 단, 포지션을 기준으로 내림차순하여 정렬.

 

* 예시 - 결과 (ORDER BY)

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 
FROM PLAYER 
ORDER BY 포지션 DESC;
 
/*
선수명  포지션 백넘버 키 
------ ------ ----- ----
정학범               173 
차상광               186 
안익수               174 
백영철  MF     22    173 
조태용  MF     7     192 
올리베  MF     29    190 
김리네  MF     26    188 
쟈스민  MF     33    186 
480개의 행이 선택되었다.
*/

---> [결과 해석]

• 포지션을 기준으로 내림차순 정렬을 했지만, 결과에서는 NULL 값이 가장 앞에 출력됨.

• Oracle에서는 NULL 값을 가장 큰 값으로 취급한다는 것을 알 수 있음.

• SQL Server 에서는 이와 반대로 NULL 값이 가장 작은 값으로 취급됨.

 

 

* 예시 - 문제 (ORDER BY, 여러 칼럼)

   : 키 정보가 존재하는 선수들을 출력하는데 키가 큰 순서대로, 키가 같을 때는 백넘버가 작은 순서대로 정렬한다.

 

* 예시 - 결과 (ORDER BY, 여러 칼럼)

SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE HEIGHT IS NOT NULL 
ORDER BY HEIGHT DESC, BACK_NO ASC;
 
/* 
[SELECT 절의 칼럼 순서를 정수로 매핑하여 사용하는 경우]
ORDER BY 4 DESC, 3 ASC;
 : 가독성이 떨어지므로 가능한 칼럼명이나 ALIAS명 사용을 권고.
*/
 
 
/*
선수명 포지션 백넘버 키 
----- ------ ----- ----
서동명 GK     21    196 
권정혁 GK     1     195 
김석   FW     20    194 
정경두 GK     41    194 
이현   GK     1     192 
황연석 FW     16    192 
미트로 FW     19    192 
김대희 GK     31    192 
조의손 GK     44    192 
김창민 GK     1     191 
우성용 FW     22    191 
최동석 GK     1     190 
샤샤   FW     10    190 
447개의 행이 선택되었다.
*/

 

1. SELECT 문장 실행 순서

5. SELECT 칼럼명 [ALIAS명] 
1. FROM 테이블명 
2. WHERE 조건식 
3. GROUP BY 칼럼(Column)이나 표현식 
4. HAVING 그룹조건식 
6. ORDER BY 칼럼(Column)이나 표현식;

1. FROM : 발췌 대상 테이블을 참조. 

2. WHERE : 발췌 대상 데이터가 아닌 것은 제거.

3. GROUP BY : 행들을 소그룹화 한다.

4. HAVING : 소그룹화 된 데이터 중 조건에 맞는 데이터만 출력

5. SELECT : 데이터 값을 출력/계산.

6. ORDER BY : 데이터를 정렬.

* 위의 순서는 옵티마이저가 SQL 문장의 SYNTAX, SEMANTIC 에러를 점검하는 순서이기도 하다.

(옵티마이저 : SQL 문을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진.)

 

[중요]
 GROUP BY 절에서 그룹핑 기준 칼럼을 정의하면 FROM 절에 정의된 테이블의 구조를 그대로 가져가지 않는다. GROUP BY 절의 그룹핑 기준에 사용된 칼럼과 집계 함수에 사용될 수 있는 숫자형 데이터 칼럼들로 집합을 새로 생성한다.
 즉, GROUP BY 이후 수행되는 SELECT 절과 ORDER BY 절에서는 그룹핑 기준 칼럼 외의 개별 칼럼을 사용하는 경우 에러가 발생한다. 그룹핑 기준 칼럼이나 숫자 데이터의 집계 함수만 허용된다.

---> 예시

SELECT JOB, SAL 
FROM EMP 
GROUP BY JOB 
HAVING COUNT(*) > 0 
ORDER BY SAL;
/* SELECT JOB, SAL -- ERROR; GROUP BY 표현식이 아니다. */
/* 
1. SELECT 절에서 JOB, SAL을 조회하는데 이전에 GROUP BY 절에서 JOB칼럼을 그룹핑.
2. SAL은 그룹핑 기준 칼럼(JOB)이 아니므로 에러가 발생.
*/
 
 
SELECT JOB 
FROM EMP 
GROUP BY JOB 
HAVING COUNT(*) > 0 
ORDER BY SAL;
/* ORDER BY SAL -- ERROR; GROUP BY 표현식이 아니다. */
/*
1. ORDER BY 절에서 SAL을 정렬하는데 이전에 GROUP BY 절에서 JOB칼럼을 그룹핑.
2. SAL은 그룹핑 기준 칼럼(JOB)이 아니므로 에러가 발생.
*/
 
 
SELECT JOB 
FROM EMP 
GROUP BY JOB 
HAVING COUNT(*) > 0 
ORDER BY MAX(EMPNO), MAX(MGR), SUM(SAL), COUNT(DEPTNO), MAX(HIREDATE);
/*
1. JOB 칼럼으로 그룹핑 한 후에 SELECT에서는 그룹핑 기준 칼럼만을 조회함 -- 에러 발생 X
2. SELECT 후 ORDER BY 절에서 정렬의 기준이 숫자 형식 칼럼의 집계 함수  -- 에러 발생 X
*/
 
/*
[실행결과]
JOB 
--------- 
MANAGER 
PRESIDENT 
SALESMAN 
ANALYST 
CLERK 
5개의 행이 선택되었다.
*/

 

2. Top N 쿼리

[Oracle]

 Oracle에서 순위가 높은 N개의 행을 추출하기 위해 ORDER BY 절과 WHERE 절의 ROWNUM 조건을 같이 사용하는 경우가 있다.

* 데이터의 추출(WHERE)이 먼저 일어난 후에 데이터의 정렬(ORDER BY)이 일어나므로 WHERE 절이나 ORDER BY 절을 통해서는 원하는 결과를 얻을 수 없다.

 

* 예시 - 문제

   : 사원 테이블에서 급여가 높은 3명만 내림차순으로 출력하라.

 

* 예시 - 결과 (잘못된 결과)

SELECT ENAME, SAL 
FROM EMP 
WHERE ROWNUM < 4 
ORDER BY SAL DESC;
 
/*
ENAME  SAL 
------ ---- 
ALLEN  1600 
WARD   1250 
SMITH  800 
3개의 행이 선택되었다.
*/

---> 무작위로 3개의 데이터가 추출된 후에 그 3개의 데이터 중에서 급여 기준 내림차순 정렬을 행하기 때문에 원하는 데이터가 아니다. 

 

* 예시 - 결과 (올바른 결과)

SELECT ENAME, SAL 
FROM (
    SELECT ENAME, SAL 
    FROM EMP 
    ORDER BY SAL DESC) 
WHERE ROWNUM < 4 ;
 
/*
ENAME  SAL 
------ ---- 
KING   5000 
SCOTT  3000 
FORD   3000 
3개의 행이 선택되었다.
*/

---> 급여 기준으로 내림차순 정렬을 전체 데이터에 대해서 진행한 뒤에 ROWNUM을 통해 가장 위 3개의 데이터를 추출했다. (급여가 가장 많은 3명 추출) : 인라인 뷰(Inline View) 사용

 

[SQL Server]

 SQL Server에서는 TOP 조건을 사용하게 되면 별도의 처리 없이 기능을 수행할 수 있다.

 

* 예시 - 문제

   : 사원 테이블에서 급여가 가장 높은 2명을 내림차순으로 출력하라.

 

* 예시 - 결과

SELECT TOP(2) ENAME, SAL 
FROM EMP 
ORDER BY SAL DESC;
 
/*
ENAME  SAL 
------ ---- 
KING   5000 
SCOTT  3000 
2개의 행이 선택되었다.
*/

 

* 예시 - 결과 (1, 2등과 같은 급여가 존재하는 경우 같이 출력하라.)

SELECT TOP(2) WITH TIES ENAME, SAL 
FROM EMP 
ORDER BY SAL DESC;
 
/*
ENAME  SAL 
------ ---- 
KING   5000 
SCOTT  3000 
FORD 3000 
3개의 행이 선택되었다.
*/

---> WITH TIES 옵션을 통해 동일 수치의 데이터를 추가로 더 추출할 수 있다. 만약 SAL = 3000인 사원이 10명이 있으면 총 11개의 행이 선택된다.

 

 

 

출처

http://www.dbguide.net/db.db?cmd=view&boardUid=148196&boardConfigUid=9&categoryUid=216&boardIdx=134&boardStep=1

데이터 전문가 지식포털 DBGuide.net

질문은 댓글로 남겨주시면 되겠습니다. 감사합니다.