[SELECT] SUBQUERY

Ⅰ. SUBQUERY의 기초

SUBQUERY란? : 메인 쿼리를 보조하는 쿼리. SELECT 절에서는 사용할 수 없다.

 

단일행 서브쿼리 : 서브쿼리의 결과값이 1행 * 1열 : 일반 비교 연산 가능

--전체 학생의 수학 점수 평균 보다 수학 점수가 높은 학생의 이름
SELECT STUDENT_NAME
FROM STUDENT
WHERE MATH>(SELECT AVG(MATH) FROM STUDENT);

---

다중행 서브쿼리 : 서브쿼리의 결과값이 N행 * 1열 : IN/NOT IN, 비교연산자 + ANY/ALL

--운동부의 영어 점수와 동일한 수학 점수를 가진 사람
SELECT STUDENT_NAME
FROM STUDENT
WHERE MATH IN (SELECT ENGLISH FROM STUDENT WHERE CLUB='운동부');

--운동부의 어느 수학 점수보다(가장 작은 점수보다)는 수학 점수가 높은 학생의 이름
SELECT STUDENT_NAME
FROM STUDENT
WHERE MATH>ANY(SELECT MATH FROM STUDENT WHERE CLUB='운동부');

--운동부의 모든 수학 점수보다(가장 큰 점수보다)도 수학 점수가 높은 학생의 이름
SELECT STUDENT_NAME
FROM STUDENT
WHERE MATH>ALL(SELECT MATH FROM STUDENT WHERE CLUB='운동부');

---

다중열 서브쿼리 : 서브쿼리의 결과값이 1행 * N열 : 순서쌍

SELECT STUDENT_NAME
FROM STUDENT
WHERE (MATH, SCIENCE) = (SELECT MATH, SCIENCE              
                               FROM STUDENT
                               WHERE STUDENT_NAME='NABI');

---

다중행 다중열 서브쿼리 : 서브쿼리의 결과값이 N행 * N열

SELECT STUDENT_NAME, MATH, SCIENCE
FROM STUDENT
WHERE (CLUB, MATH) IN (('봉사부', 70), ('운동부', 80),
                      ('영화감상부', 90), ('미술부', 100));

 

Ⅱ. INLINE-VIEW

 

INLINE-VIEW SUBQUERY : FROM절에 서브쿼리를 사용하여 가상의 VIEW를 테이블로 사용한다.

VIEW의 특성상 컬럼명이 산술연산, 그룹함수인 경우는 메인쿼리에서 조회가 불가능하므로, 해당 부분에 별칭을 붙여 조회한다.

SELECT STUDENT_NAME
FROM (SELECT STUDENT_NAME, ENGLISH FROM STUDENT WHERE MATH > 80)
WHERE ENGLISH > 80;

---

ROWNUM : 행을 1부터 N번까지 순서를 매기는 방법

SELECT ROWNUM, STUDENT_NAME, MATH
FROM (SELECT STUDENT_NAME, MATH
      FROM STUDENT
      ORDER BY MATH)
WHERE ROWNUM <= 10;

---

RANK()는 조회와 동시에 명시한 조건으로 랭크를 매겨 출력. SELECT에서만 사용할 수 있다. 

RANK() OVER( ... ) : 특정 컬럼값의 순위를 매기는 방법. 공동 순위가 있다면 다음은 건너뛴다.

DENSE_RANK() OVER( ... ) : 공동 순위가 있어도 다음 순위가 존재.

SELECT STUDENT_NAME, RANK() OVER(ORDER BY MATH DESC) AS 등수
FROM STUDENT;

SELECT *
FROM (SELECT STUDENT_NAME, RANK() OVER(ORDER BY MATH DESC) AS 등수
FROM STUDENT)
WHERE 등수<5;--공동 3위라면 4위 존재하지 않음

SELECT *
FROM (SELECT STUDENT_NAME, DENSE_RANK() OVER(ORDER BY MATH DESC) AS 등수
FROM STUDENT)
WHERE 등수<5;--공동 3위라도 4위 존재