샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.




No. 01

 

 

서브 쿼리(Sub Query)란?


서브 쿼리란 SQL문장 내에 쓰인 SELECT 문장을 의미하며, 이때 SQL문장은 메인 쿼리(Main Query)라 불린다.

서브 쿼리는 검색되는 결과의 유형에 따라 다음과 같이 나뉜다.


종  류

설  명

 단일 행 서브 쿼리

 서브 쿼리가 하나의 컬럼에서 하나의 행을 검색한다. 

 다중 행 서브 쿼리

 서브 쿼리가 하나의 컬럼에서 여러 개의 행을 검색한다. 

 다중 열 서브 쿼리 서브 쿼리가 여러 개의 컬럼을 검색한다. 


    • WHERE절, HAVING절과 같이 조건 절에 주로 쓰인다.
    • 서브 쿼리는 반드시 괄호로 묶는다.
    • 서브 쿼리는 메인 쿼리 실행 전에 실행된다.
    • 서브 쿼리의 검색된 결과 값은 메인 쿼리에 사용된다.



No. 02

 

 

단일 행 서브 쿼리


서브 쿼리 문장에서 단 하나의 행과 열을 검색하는 SQL문장이다.


기본 구조

SELECT [DISTINCT] 컬럼, 컬럼 …

FROM 테이블


WHERE 컬럼 <단일 행 연산자> (SELECT 컬럼

 FROM 테이블);

  • 반드시 서브 쿼리의 결과 값은 한 개만 검색돼야 한다.
  • 단일 행 연산자(=, <, >, <=, >=, !=) 오른쪽에 기술한다.
  • WHERE절에 기술된 컬럼의 타입은 서브 쿼리의 결과와 같아야 한다.


◈ 예제

select last_name, salary

from employees

where salary > (select salary

    from employees

    where last_name = 'Abel');


위의 SQL문은 Abel보다 급여가 높은 사원의 정보를 검색한 것이다.

서브 쿼리를 따로 검색해보면

select salary

from employees

where last_name = 'Abel';

  Abel의 급여는 11000이다.


즉, 결과적으로 보면 급여가 11000보다 큰 사원의 정보를 검색한 것이다.

서브쿼리를 사용함으로써 Abel의 급여가 11000 이라는 정보를 모르더라도 조건문을 만들 수 있다.



▶ 단일 행 서브 쿼리 에러

select employee_id, last_name, salary

from employees

where salary > (select salary

    from employees

    where last_name = 'Smith');

ORA-01427: single-row subquery returns more than one row

01427. 00000 -  "single-row subquery returns more than one row"


단일 행 연산자를 사용할 때는 위와 같은 경우를 조심해야 한다. 위의 SQL 문장이 에러가 발생하는 이유는 서브쿼리를 따로 실행시켜 보면 알겠지만 'Smith'라는 성을 가진 사원이 2명이라서 서브 쿼리의 결과 값이 두 개가 검색되기 때문이다.


select employee_id, last_name

from employees

where salary = (select min(salary)

    from employees

    group by department_id);

ORA-01427: single-row subquery returns more than one row

01427. 00000 -  "single-row subquery returns more than one row"


이번에도 마찬가지로 서브 쿼리의 결과가 여러 행이 나왔기 때문에 에러가 발생했다.

서브 쿼리를 group by 절로 묶어줌으로써 각 부서별로 최소 급여가 검색되어 12개의 행이 검색됐기 때문이다.


단일 행 연산자를 사용할 때는 반드시 결과 값은 한 개만 검색돼야 한다.



▶ 여러 번 사용된 단일 행 서브 쿼리

select employee_id, last_name, department_id, salary

from employees

where department_id != (select department_id from employees where last_name = 'Hunold')

and salary < (select salary from employees where last_name = 'Hunold');


서브 쿼리는 문장 내에 필요하다면 여러 번 사용할 수 있다.



▶ HAVING절에 사용한 서브 쿼리

select department_id, min(salary)

from employees

where department_id is not null

group by department_id

having min(salary) > (select min(salary)

      from employees

      where department_id = 50);


select department_id, max(avg(salary)) 최대평균

from employees

group by department_id;

ORA-00937: not a single-group group function

00937. 00000 -  "not a single-group group function"


select department_id

from employees

group by department_id

having avg(salary) = (select max(avg(salary))

      from employees

      group by department_id);


[SQL 10]에서 그룹 함수가 중첩된 경우 group by 절에 기술한 컬럼이라도 출력 할 수 없었음을 배웠다. 이런 경우 위 예제와 같이 서브 쿼리를 이용하면 해당 컬럼을 검색 할 수 있다.

이 구문은 전형적으로 쓰이는 구문이므로 구조를 기억해 두면 많은 부분에서 활용할 수 있다.



▶ 다른 테이블에서의 서브 쿼리

select employee_id, first_name

from employees

where department_id = (select department_id

   from departments

   where department_name = 'IT');


서브 쿼리에 사용하는 테이블은 메인 쿼리의 테이블과 달라도 상관없다.

위의 경우는 서브 쿼리 문장이나 조인 문장으로 작성할 수 있는 SELECT 문이다. 이런 경우는 작성자가 어떤 SQL을 작성해도 상관없다. 하지만, 메인 쿼리와 서브 쿼리에서 같은 테이블을 사용한다면 가능한 서브 쿼리로 쓸 것을 권한다.




+ Recent posts