ORACLE 서브쿼리

1. 서브쿼리(Sub Query)

서브 쿼리는 하나의 SELECT 문장에서 그 문장 안에 포함된 또 하나의 SELECT 문장

서브 쿼리를 포함하고 있는 쿼리문을 메인쿼리, 포함된 또 하나의 쿼리를 서브 쿼리라고 한다.

 

즉, 복잡한 쿼리문을 하나의 연속적인 다단계 쿼리문으로 변환하는 것

 

서브 쿼리는 일반적으로 SELECT, INSERT, UPDATE, DELETE와 같은 DML(DATA MANIPULATION LANGUAGE)문 모두에서 사용할 수 있다.

 

SELECT select_list
FROM TABLE 또는 View
WHERE 조건 연산자 (SELECT select_list FROM TABLE WHERE 조건);

위 문법에서 괄호 안에 있는 쿼리를 서브 쿼리 라고 부르고 나머지 괄호 밖에 있는 쿼리를 메인 쿼리라고 부른다.

 

2. 서브 쿼리의 종류

2.1 단일행 서브 쿼리(Single Row Sub Query)

단일 행 서브 쿼리는 수행 결과값이 1개의 행만 출력되는것을 말한다.

가장 일반적으로 많이 사용되는 유형이며 서브쿼리를 수행한 결과가 1건만 나오고 이 결과를 메인쿼리로 전달해서 메인쿼리를 수행하게 한다.

 

* 단일행 서브쿼리일 경우 WHERE절에서 사용되는 연산자

연산자	의미
=	같다
<>	같지 않다
>	크다
>=	크거나 같다
<	작다
<=	작거나 같다

 

 

2.2 다중행 서브쿼리

다중행 서브쿼리란 서브 쿼리에서 반환되는 결과가 2건 이상 출력되는 것을 말한다.

다중행 서브쿼리는 결과가 여러 건 출력되기 때문에 단일행 연산자를 사용할 수 없다.

그래서 다중행 서브쿼리의 경우 별도의 연산자가 존재한다.

 

* 다중행 서브쿼리에서 사용되는 연산자

연산자	의미
IN	서브쿼리 결과와 같은 값을 찾는다.
EXISTS	서브쿼리의 값이 있을 경우 메인쿼리를 수행한다.
>ANY	서브 쿼리 결과 중에서 최솟값을 반환한다.
<ANY	서브 쿼리 결과 중에서 최댓값을 반환한다.
<ALL	서브 쿼리 결과 중에서 최솟값을 반환한다.
>ALL	서브 쿼리 결과 중에서 최댓값을 반환한다.

 

위의 ANY와 ALL은 연산자의 방향에 따라 최댓값, 최솟값이 달라진다.

 

2.2.1 IN 연산자

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

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM table_name WHERE condition);

위 쿼리에서 WHERE 절에서 IN 연산자 다음 괄호 안에 있는 서브쿼리가 먼저 실행되고, 그 결과를 WHERE 절에서 비교한다. 만약 서브쿼리에서 선택된 값이 WHERE 절에서 비교하려는 값과 일치하면 해당 행을 반환한다.

 

<예시 1>

예를 들어, "Customers" 테이블에서 "City" 열의 값이 "Berlin", "London", "Madrid"인 모든 고객을 선택하려면 다음과 같이 쿼리를 작성할 수 있습니다.

SELECT * 
FROM Customers
WHERE City IN ('Berlin', 'London', 'Madrid');

위 쿼리는 "City" 열의 값이 "Berlin", "London", "Madrid" 중 하나인 모든 행을 반환합니다. 이와 같이 IN 연산자는 여러 값을 비교하고자 할 때 유용하게 사용됩니다.

 

<예시 2> 단일 행 비교 연산자로 출력하는 서브쿼리

예를 들어, employees 테이블에서 salary가 10000 이상인 사원들의 이름과 부서명을 출력하고자 할 때, 다음과 같은 쿼리를 작성할 수 있습니다.

SELECT employee_name, department_name
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM employees
  WHERE salary >= 10000
);

위 쿼리에서는 employees 테이블의 salary가 10000 이상인 사원들의 부서 ID를 서브쿼리를 사용해 가져와 IN 연산자를 통해 해당 부서에 속한 모든 사원들의 이름과 부서명을 출력합니다.

 

2.2.2 ALL 연산자

ALL 조건은 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참이다.

즉, 찾아진 값에 대해서 AND 연산을 해서 모두 참이면 참.

 

<예제>

다음은 ALL 연산자를 사용하여 부서별 평균 급여보다 높은 급여를 받는 모든 직원을 반환하는 예제입니다.

SELECT employee_name, salary
FROM employees
WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department);

이 예제에서는 서브쿼리에서 AVG 함수를 사용하여 각 부서의 평균 급여를 계산합니다. 그런 다음 WHERE 절에서 ALL 연산자를 사용하여 부서별 평균 급여보다 높은 급여를 받는 모든 직원을 찾습니다.

 

2.2.3 ANY 연산자

ANY 조건은 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상만 일치하면 참이다.

(서브쿼리의 결과 중 어떤 하나라도 조건식을 만족하는 경우에 참(true)을 반환)

> ANY 는 찾아진 값에 대해 하나라도 크면 참이다. 그러므로 찾아진 값 중에서 가장 작은 값 즉, 최소값보다 크면 참이 된다.

ANY 연산자는 '=' '<' '>' '<=' '>=' '<>' 등의 비교 연산자와 함께 사용됩니다.

 

<예시>

* 예시1

SELECT *
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 50);

위의 예제1 에서, 서브쿼리는 부서 50에 속한 모든 직원의 급여를 반환합니다. 그런 다음, 메인 쿼리에서는 직원들 중에서 부서 50의 모든 직원들보다 높은 급여를 받는 직원들을 검색합니다.

 

* 예시2

SELECT *
FROM employees
WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

위의 예제2 에서, 서브쿼리는 각 부서의 평균 급여를 계산합니다. 그런 다음, 메인 쿼리에서는 각 직원의 급여가 해당 부서의 평균 급여보다 높은 직원을 검색합니다.

내가 GPT한테 물어본거

- GROUP BY 가 아닌 WHERE 을 쓰면 안돼?

위 예제에서 WHERE 절을 사용하면 동작하지 않습니다. WHERE 절은 단일 행 연산자를 사용하는데 비해, 서브쿼리에서 AVG 함수를 사용하여 집계를 수행하면 결과가 그룹화되어 있기 때문입니다. ANY 연산자는 서브쿼리의 결과 중 하나 이상의 값이 해당 비교에 참이면 참을 반환합니다. 따라서 위 예제에서는 salary가 해당 부서의 평균보다 큰 직원의 모든 행을 반환합니다. WHERE 절을 사용하면 서브쿼리에서 집계 함수를 사용할 수 없으므로 이러한 쿼리를 작성할 수 없습니다.

 

2.2.4 EXISTS 연산자

EXISTS 연산자는 서브 쿼리문에서 주로 사용하며, 서브 쿼리의 결과 값이 참이 나오기만 하면 바로 메인 쿼리의 결과 값을 리턴한다.

서브 쿼리의 결과 값이 존재하지 않는다면 어떤 값도 리턴하지 않는다.

쿼리 속도 면에서 서브 쿼리사용시 IN보다 EXISTS가 훨씬 빠르다. NOT EXISTS도 사용가능하다.

 

<예시>

employees 테이블에서 job_id가 'SA_MAN'인 사원이 있는지 검사하려면 다음과 같은 SQL문을 작성할 수 있습니다.

SELECT *
FROM employees
WHERE EXISTS (SELECT *
              FROM employees
              WHERE job_id = 'SA_MAN');

위의 SQL문에서 서브쿼리는 employees 테이블에서 job_id가 'SA_MAN'인 행이 존재하는지 검사합니다. 이 때 EXISTS 연산자는 서브쿼리의 결과 집합이 비어 있지 않은 경우에만 참(true)을 반환합니다. 따라서 위의 SQL문은 job_id가 'SA_MAN'인 사원이 존재하는 경우에만 employees 테이블의 모든 열을 반환합니다.