[풀스택과정] 데이터베이스 8. 서브쿼리

* 이 글은 제가 공부하기 위해 최대한 사실에 입각해 내용을 적으려고 하지만 일부 내용들이 정확하지 않을 수 있습니다.

   혹시나 잘못된 부분이 있으면 너그럽게 이해해 주시고 피드백 부탁드려요!

---

1. 서브쿼리의 개념

서브쿼리

• 개요

  – 하나의 SQL 명령문의 결과를 다른 SQL 명령문에 전달하기 위해 두 개 이상의 SQL 명령문을 하나의

     SQL명령문으로 연결하여 처리하는 방법
  – 서브쿼리의 필요성
    • 예 : 교수 테이블에서 "전은지" 교수와 직급이 동일한 모든 교수의 이름 검색
    • 처리방식
      1. 교수 테이블에서 "전은지" 교수의 직급 검색 SQL 명령문 실행
      2. 교수 테이블의 직급 칼럼에서 1에서 얻은 결과 값과 동일한 직급을 가진 교수 검색 명령문 실행
    • 1, 2 SQL 명령문 처리과정을 통합하여 하나의 SQL 명령문으로 처리

  – 처리과정
    1. 서브쿼리는 메인쿼리가 실행되기 전에 한 번씩 실행됨
    2. 서브쿼리에서 실행된 결과가 메인 쿼리에 전달되어 최종적인 결과를 출력

 

2. 서브쿼리의 종류

단일행 서브쿼리

• 개요

  – 서브쿼리에서 단 하나의 행만을 검색하여 메인쿼리에 반환하는 질의문
  – 메인쿼리의 WHERE 절에서 서브쿼리의 결과와 비교한 경우에는 반드시 단일행 비교 연산자 중 하나만

     사용해야 함

    • 단일 행 비교 연산자 : =, >, >=, <, <>, <=
  – 서브쿼리의 결과로 하나의 행만이 출력되어야 함

    • 방법 1. 서브쿼리의 조건절에서 기본 키나 고유 키를 "=" 비교하는 방식
    • 방법 2. 서브쿼리의 SELECT 절에서 전체 집합을 대상으로 그룹 함수 사용

 

‘=’ 연산자를 이용한 단일행 서브쿼리

• 사용 예

– 사용자 아이디가 "jun123"인 학생과 같은 학년인 학생의 학번, 이름, 학년을 출력하여라

‘<’ 연산자를 이용한 단일행 서브쿼리

• 사용 예

– 101번 학과 학생들의 평균 몸무게보다 몸무게가 적은 학생의 이름, 학과번호, 몸무게를 출력하여라

단일행 서브쿼리

• 실습 예

– 20101번 학생과 학년이 같고, 키는 20101번 학생보다 큰 학생의 이름, 학년, 키를 출력하여라

다중 행 서브쿼리

• 개요

  – 서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리
  – 메인쿼리의 WHERE 절에서 서브쿼리의 결과와 비교한 경우에는 다중 행 비교 연산자를 사용하여 비교
    다중 행 비교 연산자 : IN, ANY, SOM, ALL, EXISTS
    다중 행 비교 연산자는 단일 행 비교 연산자와 결합하여 사용 가능

[표 9.1] 다중 행 비교 연산자

IN 연산자를 이용한 다중 행 서브쿼리

• 개요

  – 메인쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리 조건절이 참이 되는

     연산자
  – "=" 연산자를 OR로 연결한 것과 같은 의미
  – IN 연산자를 이용한 서브쿼리의 예

• 사용 예

– 정보미디어학부(부서번호:100)에 소속된 모든 학생의 학번, 이름, 학과 번호를 출력하여라

ANY 연산자를 이용한 다중 행 서브쿼리

• 개요

  – 메인쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리 조건절이 참이 되는

     연산자
  – ">, <" 등과 같은 범위 비교도 가능
  – ANY 연산자를 이용한 서브쿼리의 예

• 사용 예

– 모든 학생 중에서 4학년 학생 중에서 키가 제일 작은 학생보다 키가 큰 학생의 학번, 이름, 키를 출력하여라

ALL 연산자를 이용한 다중 행 서브쿼리

• 개요

  – 메인쿼리의 비교 조건에서 서브쿼리의 검색 결과와 모두 일치하면 메인쿼리 조건절이 참이 되는 연산자
  – ANY 연산자와 차이점

    •'> ANY' : 서브쿼리 결과 중에서 최소 값보다 크면 메인쿼리의 비교 조건이 참
    •'> ALL' : 서브쿼리의 결과 중에서 최대 값보다 크면 메인쿼리의 비교 조건이 참

• 사용 예

– 모든 학생 중에서 4학년 학생 중에서 키가 가장 큰 학생보다 키가 큰 학생의 학번, 이름, 키를 출력하여라

EXISTS 연산자를 이용한 다중 행 서브쿼리

• 개요

  – 서브쿼리에서 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이 되는 연산자
  – 서브쿼리에서 검색된 결과가 존재하지 않으면 메인쿼리의 조건절은 거짓

    •"선택된 레코드가 없습니다"라는 메시지 출력

  – NOT EXISTS

    • EXISTS와 상반되는 연산자
    • 서브쿼리에서 검색된 결과가 하나도 존재하지 않으면 메인쿼리의 조건절이 참이 되는 연산자

   

• EXISTS 연산자를 이용한 다중 행 서브쿼리의 처리과정

• 사용 예

– 보직수당을 받는 교수가 한 명이라도 있으면 모든 교수의 교수 번호, 이름, 보직수당 그리고 급여와

보직수당의 합을 출력하여라 

NOT EXISTS 연산자를 이용한 다중 행 서브쿼리

• 실습 예

– 학생 중에서 "goodstudent"이라는 사용자 아이디가 없으면 1을 출력하여라

 

다중 칼럼 서브쿼리

• 개요

  – 서브쿼리에서 여러 개의 칼럼 값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리
  – 메인쿼리의 조건절에서도 서브쿼리의 칼럼 수만큼 지정해야 함

  – 종류
    • PAIRWISE : 칼럼을 쌍으로 묶어서 동시에 비교하는 방식
    • UNPAIRWISE : 칼럼별로 나누어서 비교한 후, AND 연산을 하는 방식

  – PAIRWISE, UNPAIRWISE 서브쿼리 비교

PAIRWISE 다중 칼럼 서브쿼리

• 개요

  – 메인쿼리와 서브쿼리의 비교 대상 칼럼을 쌍으로 묶어서 행별로 비교하는 방법
  – 메인쿼리와 서브쿼리에서 비교하는 칼럼의 수는 반드시 동일해야 함

• 사용법

• 사용 예

– PAIRWISE 비교 방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하여라.

 

UNPAIRWISE 다중 칼럼 서브쿼리

• 개요

  – 메인쿼리와 서브쿼리의 비교 대상 칼럼을 분리하여 개별적으로 비교한 후 AND 연산에 의해 최종 결과를

     출력
  – 각 칼럼이 동시에 만족하지 않더라도 개별적으로 만족하는 경우에는 비교 조건이 참이 되어 결과를 출력

     가능

• 사용법

• 사용 예

– UNPAIRWISE 비교 방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하여라.

상호연관 서브쿼리

• 개요

  – 메인쿼리절과 서브쿼리 간에 검색 결과를 교환하는 서브쿼리
  – 메인쿼리와 서브쿼리 간의 결과를 교환하기 위하여 서브쿼리의 WHERE 조건절에서 메인쿼리의

     테이블과 연결
  – 주의
    • 행을 비교할 때마다 결과를 메인으로 반환하는 관계로 처리성능이 저하될 수 있음

• 사용법

• 사용 예

– 각 학과 학생의 평균 키보다 키가 큰 학생의 이름, 학과 번호, 키를 출력하여라

 

3. 데이터베이스 실무에서 서브쿼리 사용 시 주의사항

실무에서 서브쿼리 사용시 주의사항

• 단일행 서브쿼리에서 오류가 발생하는 경우

  – 복수 행 값을 반환하는 서브쿼리와 단일행 비교연산자 함께 사용하는 경우
  – 반환되는 칼럼의 수와 메인쿼리에서 비교되는 칼럼 수가 일치하지 않는 경우
  – 복수행을 출력하는 서브쿼리와 "="단일행 연산자로 비교하는 경우

 

• 메인쿼리와 서브쿼리 칼럼의 수가 일치하지 않는 경우

• 사용 예

– 101번 학과 교수 중에서 최소 급여를 받는 교수의 이름, 직급, 급여를 출력하여라.

 

• ORDER BY 절 사용

  – 서브쿼리 내에서 ORDER BY 절 사용하면 오류 발생

• 서브쿼리의 결과가 NULL인 경우
• 사용 예

– 2002년에 입사한 교수의 평균 급여보다 많은 급여를 받는 교수의 교수 번호, 이름, 급여를 출력하여라.

 

실습

 Blake와 같은 부서에 있는 모든 사원에 대해서 사원 이름과 입사일을 디스플레이하라.

 

 평균 급여 이상을 받는 모든 사원에 대해서 사원 번호와 이름을 디스플레이하는 질의문을 생성하라.        단, 출력은 급여 내림차순 정렬하라.

 부서 번호와 급여가 보너스를 받는 어떤 사원의 부서 번호와 급여에 일치하는 사원의 이름,

    부서 번호 그리고 급여를 디스플레이하라

 

 

Scalar Subquery 

SQL: 1999 Scalar Subquery

  • Scalar Subquery는 질의 수식으로부터 유도된 Scalar 값 지정하기 위해 사용
  • Oracle8i에서는 다음과 같이 제한된 경우에만 지원
  • Oracle9i에서 Scalar 서브 쿼리는 유효한 수식이 쓰일 수 있는 모든 곳에서 사용 가능
  • Scalar Subquery는 오직 하나의 값만 반환
  • 반환되는 값의 데이터 형은 서브 쿼리에서 선택된 데이터 형과 일치
  • 소량의 데이터의 경우에는 효과적이나 대량의 데이터의 경우 성능 저하 가능
  • Scalar Subquery의 사용

 

1) Select List에서의 Scalar Subquery

2) Where 절에서의 Scalar Subquery

3) Order By 절에서의 Scalar Subquery

4) CASE 수식에서의 Scalar Subquery

 

5) 함수에서의 Scalar Subquery