SQL - 서브쿼리와 서브쿼리 분류(스칼라 서브쿼리)

서브쿼리

하나의 쿼리 안에 포함된 또 하나의 쿼리. 메인 쿼리가 서브쿼리를 포함하는 종속적인 관계. 조건문이 복잡해질 때 서브쿼리를 활용함.

• 알려지지 않은 기준을 이용한 검색에 유용
  ex) 연봉이 상위 5%인 여자를 직원 테이블에서 검색할 때
• 메인 쿼리가 실행되기 이전에 한 번만 실행 (서브쿼리부터 실행됨)
• 한 문장에서 여러 번 사용 가능(서브쿼리의 서브쿼리)

서브쿼리 사용시 주의사항

• 서브쿼리는 괄호와 함께 사용되어야 함
• 서브쿼리 안에서 ORDER BY 절은 사용할 수 없음
• 서브쿼리는 연산자의 오른쪽에 사용되어야 함 (메인쿼리 <연산자 서브쿼리)
• 서브쿼리는 오로지 SELECT문으로만 작성할 수 있음

 

0) 서브쿼리를 몰랐을 때, 검색했던 방법
사원 데브쏨의 급여를 알고 있는 상태에서 더 높은 급여를 받는 사원을 조회할 때

SELECT * FROM employee
WHERE 급여 > 4500;

1) 서브쿼리를 알면, 사원 데브쏨의 급여를 알지 못해도 검색 가능

SELECT * FROM employee
WHERE 급여 >
(SELECT 급여 FROM employee WHERE 이름='데브쏨');    /* 서브쿼리 */

 

2) emp 테이블에서 사원 번호가 7인 사원보다 나이가 어린 사원의 모든 컬럼을 조회 하는 쿼리를 작성

SELECT * FROM emp
where birthdate >
(select birthdate from emp where empnum = 7);

나이가 어린 = 기준이 되는 사원의 생년월일의 값보다 더 크다

 

 

3) emp 테이블에서 전 사원의 급여 평균보다 낮은 급여를 받는 사원의 모든 컬럼을 조회 하는 쿼리를 작성

SELECT * FROM emp
where sal < 
(select avg(sal)
from emp);

 

 

4) emp 테이블에서 부서번호가 40인 부서의 급여 평균보다 높게 받는 사원들의 모든 컬럼과 나이를 조회하는 쿼리를 작성

 

* 나이를 구하는 수식 (생년월일을 토대로 지금 년도에 몇 살인지 반환하는 수식)

FLOOR( (CAST(REPLACE(CURRENT_DATE,'-','') AS UNSIGNED) - 
       CAST(REPLACE(birthdate,'-','') AS UNSIGNED)) / 10000 ) as age

SELECT *,
FLOOR( (CAST(REPLACE(CURRENT_DATE,'-','') AS UNSIGNED) - 
       CAST(REPLACE(birthdate,'-','') AS UNSIGNED)) / 10000 ) as age

FROM emp
where sal > 
(select avg(sal)
from emp
where deptno = 40);

서브쿼리 = 부서번호가 40인 부서의 급여 평균

메인쿼리 = 이 급여 평균보다 높게 받는 사원들의 모든 컬럼, 나이

 

 

 

반환에 따른 서브쿼리 분류

단일 행 서브쿼리(Single-Row Subquery)

: 결과가 한 행만 나오는 서브쿼리. 서브쿼리가 결과를 1개의 값만 반환하고, 그 결과를 메인쿼리로 전달하는 쿼리

 

* 단일 행 서브쿼리 연산자

: = 같다, <> 같지 않다, > 크다, >= 크거나 같다, < 작다, <= 작거나 같다

 

ex) 사원번호가 1인 사람보다 더 많은 급여를 받는 모든 직원들을 조회

SELECT * FROM employee
WHERE 급여 >   /* 단일 행 연산자 */
(SELECT 급여 FROM employee WHERE 사원번호 = 1);

사원번호는 기본적으로 1개만 있으므로 한 개의 행만 반환함 = 단일 행

 

다중 행 서브쿼리

: 서브쿼리가 결과를 2개 이상 반환하고, 그 결과를 메인쿼리로 전달하는 쿼리

 

* 다중 행 서브쿼리 연산자

• IN : 하나라도 만족하면 반환
• ANY : 하나라도 만족하면 반환, 비교 연산 가능
• ALL : 전부 만족하면 반환, 비교 연산 가능
  
  
• < ANY : 최대값
• > ANY : 최소값
• > ALL : 최대값
• < ALL : 최소값

 

1) 급여가 가장 큰 사람을 찾을 때

SELECT * FROM employee
WHERE 급여 IN  /* 다중 행 연산자 */
(SELECT max(급여) FROM employee GROUP BY 부서번호);

 

2) emp 테이블에서 MANAGER 업무를 가진 사원 중 제일 높은 급여를 받는 사원보다 높은 급여를 받는 사원을 조회하는 쿼리

SELECT * FROM emp
where sal >
(select max(sal)
from emp
where job = 'MANAGER');

 

3) emp테이블에서 각 부서별 급여를 제일 많이 받는 사원의 월급을 받는 사원들을 조회하는 쿼리

select *
from emp
where sal = any

(select max(sal) 
from emp 
group by deptno);

 

4) emp테이블에서 각 부서별 나이가 제일 많은 사원의 모든 컬럼을 조회하는 쿼리

select *
from emp
where birthdate in

(select min(birthdate)
from emp
group by deptno);

서브쿼리 = 각 부서별로 제일 많은 나이

메인쿼리 = 그 나이에 해당하는 사원

 

 

5) salaries 테이블에서 from_date가 2000-12-31 이전인 사람들의 급여 중 하나의 급여 보다 더 적은 급여를 받은 직원의 급여 정보를 모두 출력

select *
from salaries
where salary < any 
(select salary
from salaries
where from_date < '2000-12-31');

서브쿼리 = from_date가 2000-12-31 이전인 사람들의 급여

메인쿼리 = 이 급여보다 더 적은 급여를 받는 직원의 급여

 

 

6) salaries 테이블에서 from_date가 2000-12-31 이전인 사람들의 급여 중 모든 급여보다 적은 급여를 받은 직원의 급여 정보를 모두 출력

select *
from salaries
where salary < all
(select salary
from salaries
where from_date < '2000-12-31');

서브쿼리 = from_date가 2000-12-31 이전인 사람들의 급여

메인쿼리 = 그 모든 급여보다 적은 급여

 

 

 

위치에 따른 서브쿼리 분류

일반적인 서브쿼리

SELECT * FROM employee
WHERE 급여 >
(SELECT 급여 FROM employee WHERE 이름 = '데브쏨');

where절(꼬리)에 사용되는 서브쿼리가 가장 일반적인 형태의 서브쿼리

 

스칼라 서브쿼리

where절이 아니라, select절(머리)에서 사용하는 서브쿼리. 스칼라 서브쿼리는 오로지 한 행만 반환. 마치 JOIN을 사용한 것과 같은 결과를 나타냄. 데이터가 많을 때에는 JOIN을 쓰는 것보다 스칼라 서브쿼리를 쓰는게 빠르기 때문에 JOIN 대신 사용함.

 

AS ~ : 별명을 지어줌

 

0) 점수 테이블이 하나가 있고, 이름 테이블이 하나가 있는데, 점수 테이블에 id와 수학 점수가 있고, 이름 테이블에 id와 이름이 있음. 그래서 두 테이블에 중복되는 id를 기준으로 두 테이블을 연결해서 수학 점수와 이름을 한 행에 출력하고 싶어. 게다가 그 와중에 수학점수의 중간고사 평균을 middle_avg라는 별명을 지어줌. 겁나 복잡ㅎ

 

SELECT students.name, (
    SELECT math
    FROM middle_test as m
    WHERE m.student_id =    students.student_id
) AS middle_avg
FROM students;

where m.student_id에서 갑자기 m은 또 뭐냐? -> m은 middle_test 테이블을 다 써주기 귀찮아서 m 하나만 써준 거 -> as m 으로 m테이블 이라는 별명을 만든 거.

 

 

1) salaries 테이블에는 한 직원의 연도별 급여들이 들어있으며 스칼라 서브쿼리를 사용해 각 직원별 직원의 평균급여를 검색해야함. 조인을 사용하지 않고, 직원 번호와 평균 급여를 중복 없이 출력하기.
 - salaries 테이블에서 직원 번호(emp_no)과 평균급여(avg_salary) 두 가지를 검색
 - 평균 급여는 SELECT 절에서 서브쿼리를 이용해 직접 계산하며 별칭을 avg_salary로 지정
 - 중복 없이 검색하기 위해 DISTINCT 를 이용

select distinct emp_no,
(
select avg(salary)
from salaries as A
where A.emp_no = B.emp_no
) as avg_salary

from salaries as B;