서브쿼리(Subquery)를 알아보자

 

들어가기 전에

데이터베이스에 저장된 데이터를 가져올 때 조인(JOIN) 을 자주 사용하는데 서브쿼리와의 차이가 무엇인지 궁금해 알아보려고한다.

 

서브쿼리

서브쿼리(subquery)란 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미한다.

서브쿼리를 포함하고 있는 쿼리를 외부쿼리(outer query)라고 부르며, 서브쿼리는 내부쿼리(inner query)라고 부른다.

서브쿼리는 괄호() 로 감싸져서 표현된다.

 

서브쿼리 실행 → 메인(부모) 쿼리 실행

 

select * from main_table # 메인쿼리
where target_id in (
    # 서브쿼리
    select id from sub_table
    where id < 500
);

 

• 서브쿼리는 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 말한다.
• 괄호() 안에 있는 쿼리를 서브쿼리라 말한다.
• 서브쿼리(=자식쿼리, 내부쿼리)
  : 메인쿼리 컬럼 사용 가능
• 메인쿼리(=부모쿼리, 외부쿼리)
  : 서브쿼리 컬럼 사용 불가

 

 

서브쿼리 장점

• 서브쿼리는 쿼리를 구조화시키므로, 쿼리의 각 부분을 명확히 구분할 수 있게 해준다.
• 서브쿼리는 복잡한 JOIN 이나 UNION 과 같은 동작을 수행할 수 있는 또 다른 방법을 제공
• 서브쿼리는 복잡한 JOIN 이나 UNION 보다 좀 더 읽기 편함 (가독성이 좋음)

 

SELECT col1, (SELECT ...) # 스칼라 서브쿼리(Scalar Sub Query): 하나의 컬럼처럼 사용 (표현 용도)
FROM (SELECT ...) # 인라인 뷰(Inline View): 하나의 테이블 처럼 사용 (테이블 대체 용도)
WHERE col = (SELECT ...) # 일반 서브쿼리: 하나의 변수(상수)처럼 사용 (서브쿼리의 결과에 따라 달라지는 조건)

 

중첩 서브쿼리(Nested Subquery)

• WHERE 문에 나타나는 서브쿼리

select name, height
from user
where height > 177;
# 조건값을 상수로 할때

select name, height
from user
where height > (select height from user where name in ('김철수'));
# 조건값을 select 로 특정할 때 (단 결과가 값이 하나여야됨)

select name, height
from user
where height = any(select height from user where addr in ('전남'));
# 조건에 값이 여러개 들어올땐 any.
# any 는 in 과 동일한 의미
# or 를 의미한다

select *
from city
where population > all(select population from city where district = 'New York');
# all 은 도출된 모든 조건값에 대해 만족할 때
# and 를 의미한다

 

인라인 뷰(Inline View)

• FROM 문에 나타나는 서브쿼리
• 참고로 서브쿼리가 FROM 절에 사용되는 경우 무조건 AS 별칭을 지정해 주어야 한다.

 

SELECT EX1.name, EX1.salary
FROM (
    SELECT *
    FROM employee AS Ii
    WHERE Ii.office_worker='사원'
) EX1; # 서브쿼리 별칭

 

스칼라 서브쿼리(Scalar Subquery)

• SELECT 문에 나타나는 서브쿼리
• 딴 테이블에서 어떠한 값을 가져올 때 쓰임
• 하나의 레코드만 리턴이 가능하며, 두개 이상의 레코드는 리턴할 수 없다.
• 일치하는 데이터가 없더라도 NULL 값을 리턴할 수 있다. 이는 원래 그룹함수의 특징중에 하나인데 스칼라 서브쿼리 또한 이 특징을 가지고 있다.

SELECT D.DEPTNO, (SELECT MIN(EMPNO) FROM EMP WHERE DEPTNO = D.DEPTNO) as EMPNO
FROM DEPT D
ORDER BY D.DEPTNO

 

DEPTNO	EMPNO
10	1
20	10
30	100
40	1000
50	10000
60	100000
70
80

 

서브쿼리 실행 조건

• 서브쿼리는 SELECT 문으로만 작성 할 수 있다. (SELECT 문 쿼리할때 밖에 사용할 때가 없다)
• 반드시 괄호()안에 존재한다.
• 괄호가 끝나고 끝에 ;(세미콜론)을 쓰지 않는다
• ORDER BY 를 사용할 수 없다.

 

서브쿼리 사용 가능한 곳

MySQL에서 서브쿼리를 포함할 수 있는 외부쿼리는 SELECT, INSERT, UPDATE, DELETE, SET, DO 문이 있다.

이런 서브쿼리는 또 다시 다른 서브쿼리 안에 포함될 수 있다.

• SELECT
• FROM
• WHERE
• HAVING
• ORDER BY
• INSERT 문의 VALUES 부분 대체제
• UPDATE 문의 SET 부분 대체제

 

서브쿼리 실전 예제

CREATE TABLE employee (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64),
  salary INT,
  office_worker VARCHAR(64)
)

INSERT INTO employee VALUES(1,'허사장',20000000,'사장');

INSERT INTO employee (name,salary,office_worker) VALUES('유부장',10000000,'부장');
INSERT INTO employee (name,salary,office_worker) VALUES('박차장',5000000,'차장');
INSERT INTO employee (name,salary,office_worker) VALUES('정과장',4000000,'과장');
INSERT INTO employee (name,salary,office_worker) VALUES('정대리',3895000,'대리');
INSERT INTO employee (name,salary,office_worker) VALUES('노사원',2500000,'사원');
INSERT INTO employee (name,salary,office_worker) VALUES('하사원',2000000,'사원');
INSERT INTO employee (name,salary,office_worker) VALUES('길인턴',1000000,'인턴');

 

id	name	salary	office_worker
1	허사장	20,000,000	사장
2	유부장	10,000,000	부장
3	박차장	5,000,000	차장
4	정과장	4,000,000	과장
5	정대리	3,895,000	대리
6	노사원	2,500,000	사원
7	하사원	2,000,000	사원
8	길인턴	1,100,000	인턴

 

중첩 서브쿼리 - 단일 행

# Nested Subquery - 단일행
# 정대리라는 사람의 직급을 구하시오.
select office_worker
from employee
where office_worker = (select office_worker from employee where name = '정대리')

# output
employee # TABLE
office_worker
대리

 

중첩 서브쿼리 - 복수(다중) 행

• IN, ANY, ALL, EXISTS 등의 연산자로 얻은 서브쿼리 결과 여러개의 행을 반환.

# Nested Subquery - 복수(다중) 행
# 정대리보다 급여가 높은 사람들을 구하시오.
select *
from employee
where salary > (
    select salary
    from employee
    where name = '정대리'
)

# output
employee # TABLE
id name   salary     office_worker
1  허사장 20,000,000 사장
2  유부장 10,000,000 부장
3  박차장  5,000,000 차장
4  정과장  4,000,000 과장

# 직급이 사원인 사람들을 구하시오.
select *
from employee
where office_worker in (
    select office_worker
    from employee
    where office_worker = '사원'
)

# output
employee # TABLE
id name   salary    office_worker
6  노사원 2,500,000 사원
7  하사원 2,000,000 사원

 

인라인 뷰(Inline View)

# 인라인 뷰(Inline View)
# 잘못된 구문 - 꼭 파생 테이블엔 별칭을 정해줘야 한다
select * from (select * from employee where office_worker='사원') (X)
/* SQL 오류 (1248): Every derived table must have its own alias */

# 직급이 사원인 사람들의 이름과 급여를 구하시오.
select EX1.name, EX1.salary
from (
    select *
    from employee as Ii
    where Ii.office_worker='사원'
) EX1;

# output
EX1 # TABLE
name   salary
노사원 2,500,000
하사원 2,000,000

 

스칼라 서브쿼리(Scalar Subquery)

# 스칼라 서브쿼리(Scalar Subquery)
# 정대리 급여와 테이블 전체 평균 급여를 구하시오.
select name, salary, (
    select ROUND(AVG(salary),-1)
    from employee) as '평균급여'
from employee
where name = '정대리';

# output
employee # TABLE
name   salary    평균급여
정대리 3,895,000 6,061,880

 

INSERT 문 서브쿼리 (INSERT Subquery)

# 테이블2의 정보를 뽑아서 그 데이터를 테이블1에 넣어준다.
# values() 들어갈 자리를 서브쿼리로 대체 했다.
insert into table1 (select * from table2);

 

DELETE 문 서브쿼리 (DELETE Subquery)

# 인턴의 정보를 구해와서 삭제한다
delete from employee
where id = (select id from employee where office_worker = '인턴');

 

UPDATE 문 서브쿼리 (UPDATE Subquery)

# 인턴에 정보를 구해와서 급여를 10만원 인상한다.
update employee set salary=(salary+100000)
where id = (select id from employee where office_worker = '인턴');