오라클 서브쿼리(단일행, 다중행, 다중 칼럼, 상호연관 서브쿼리의 활용 방법)

오라클 서브쿼리(단일행, 다중행, 다중 칼럼, 상호연관 서브쿼리의 활용 방법)

오라클 데이터베이스에서 서브쿼리(Subquery)는 하나의 SQL 명령문의 처리 결과를 다른 SQL 명령문에 전달하여 새로운 결과를 만드는데 사용됩니다. 서브쿼리는 주로 메인쿼리의 조건절에서 비교하기 위해 사용되며, 단일행 서브쿼리, 다중행 서브쿼리, 다중 칼럼 서브쿼리, 그리고 상호연관 서브쿼리로 구분됩니다. 이번 글에서는 각각의 서브쿼리 유형과 활용 방법에 대해 알아보겠습니다.

단일행 서브쿼리

서브쿼리 결과가 하나의 행만 반환하는 경우에 사용합니다. 메인쿼리의 조건절에서 서브쿼리의 결과와 비교할 때는 단일행 비교연산자(>, =, >=, <, <>, <=)를 사용해야 합니다.

ex)사용자 아이디가 ‘jun123’인 학생과 같은 학년인 학생의 학번, 이름, 학년을 출력
SELECT studno, name, grade
FROM student
WHERE grade = (SELECT grade FROM student WHERE userid = ‘jun123’);

다중행 서브쿼리

서브쿼리 결과가 여러 행을 반환하는 경우에 사용합니다. 다중행 비교 연산자(IN, ANY, SOME, ALL, EXISTS)를 사용하여 메인쿼리 절과 비교할 수 있습니다.

IN 연산자

서브쿼리 결과와 하나라도 일치하면 메인쿼리 조건절이 참이 됩니다.

예) 부서번호 100에 소속된 모든 학생의 학번, 이름, 학과번호 출력

SELECT name, grade, deptno
FROM student
WHERE deptno IN (SELECT deptno FROM department WHERE college = 100);

ANY 연산자

서브쿼리 결과와 하나라도 일치하면 메인쿼리 조건절이 참이 됩니다. >, <와 같은 범위 비교도 가능합니다.

예) 모든 학생 중 4학년 학생 중 키가 제일 작은 학생보다 키가 큰 학생의 학번, 이름, 키 출력
SELECT studno, name, height
FROM student
WHERE height > ANY (SELECT height FROM student WHERE grade = ‘4’);

ALL 연산자

서브쿼리 결과와 모두 일치하면 메인쿼리 조건절이 참이 됩니다.

예)모든 학생 중 4학년 학생 중 키가 가장 큰 학생보다 키가 큰 학생의 학번, 이름, 키 출력

SELECT studno, name, height
FROM student
WHERE height > ALL (SELECT height FROM student WHERE grade = ‘4’);

EXISTS 연산자

서브쿼리 결과가 하나라도 존재하면 메인쿼리 조건절이 참이 됩니다.

예)보직 수당을 받는 교수가 한 명이라도 있으면 모든 교수의 교수번호, 이름, 급여, 보직 수당 그리고 급여와 보직 수당의 합을 출력합니다.

SELECT profno, name, sal, comm, SAL + NVL(comm, 0) AS total_sal
FROM professor
WHERE EXISTS (SELECT profno FROM professor WHERE comm IS NOT NULL);

다중 칼럼 서브쿼리

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

PAIRWISE 비교 방법

메인쿼리와 서브쿼리의 비교 대상 칼럼을 쌍으로 묶어서 행별로 비교합니다.

예)학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력합니다.

SELECT name, grade, weight
FROM student
WHERE (grade, weight) IN (SELECT grade, MIN(weight) FROM student GROUP BY grade);

UNPAIRWISE 비교 방법

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

예)학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력합니다.

SELECT name, grade, weight
FROM student
WHERE grade IN (SELECT grade FROM student GROUP BY grade)
AND weight IN (SELECT MIN(weight) FROM student GROUP BY grade);

상호연관 서브쿼리

메인쿼리절과 서브쿼리 간에 검색 결과를 교환하는 서브쿼리로, 메인쿼리와 서브쿼리간 결과 교환을 위해 서브쿼리의 WHERE 조건절에서 메인쿼리의 테이블과 연결합니다. 쿼리 성능(속도) 저하가 될 수 있으므로 사용할 경우 주의가 필요합니다.

예)각 학과 학생의 평균 키보다 키가 큰 학생의 이름, 학과번호, 키를 출력합니다.\
SELECT name, deptno, height
FROM student s1
WHERE height > (SELECT AVG(height) FROM student s2 WHERE s2.deptno = s1.deptno)
ORDER BY deptno;

이번 글에서는 오라클 데이터베이스에서 사용되는 서브쿼리의 유형과 각각의 활용 방법을 알아보았는데요, 단일행, 다중행, 다중 칼럼, 상호연관 서브쿼리의 각각 특징과 예들을 통하여 각 유형을 이해하고, 효과적인 데이터 검색과 조건 처리에 활용할 수 있으며, 이를 통해 더욱 효율적이고 정확한 데이터 활용을 수행할 수 있습니다.

이전 글 – 오라클 데이터 무결성 제약조건 정리 (기본키,외래키,참조키, 제약조건 생성, 추가, 삭제, 비활성화)

이전 글 – 오라클 인덱스에 대해 알아보자 (데이터베이스 실무에서 효율적으로 사용 할 수 있는 Oracle Index)

이전 글 – 오라클 뷰(VIEW) 조회 및 사용방법 (개념과 특징,종류, 장단점, 인라인뷰)

이전 글 – 오라클 사용자 권한 제어 (4가지 – 시스템 권한, 객체 권한, 롤, 동의어)

이전 글 – 오라클(데이터 웨어하우스) 분석 함수(효과적인 데이터 다차원 분석을 위한 강력한 도구)

이전 글 – 오라클 스케줄러와 JOB 관리 방법

이전 글 – 오라클 테이블 복구(테이블 데이터 복원하기)