[Database]서브 쿼리(MySQL)

*서브 쿼리(SubQuery)

-> 서브 쿼리란 말 그대로 다른 쿼리 내부에 포함되어 있는 쿼리를 말한다.

-> SELECT 문을 이용한 데이터 조회는 결과적으로 검색 조건을 만족하는 또 하나의 테이블을 만들어 내는 것을 의미한다고 볼 수 있다. 이러한 점을 활용하여 검색 결과 테이블을 활용하여 다시 쿼리를 진행하는 것을 서브 쿼리라고 생각하면 이해하기 쉽다.

 

*서브 쿼리의 종류

-> 서브쿼리는 쿼리의 위치가 어디에 있느냐에 따라서 세 가지 종류로 나눌 수 있다.

(1) 중첩 서브 쿼리(Nested Subquery) : WHERE절에 사용하는 서브 쿼리.

(2) 인라인 뷰(Inline View) : FROM절에 사용하는 서브 쿼리.

(3) 스칼라 서브 쿼리(Scalar Subquery) : SELECT절에 사용하는 서브 쿼리.

 

*MySQL Sample Database

-> 아래 데이터베이스는 MySQL에서 기본적으로 제공해주는 연습용 SQL 테이블 데이터이다. 해당 데이터를 이용하여 쿼리를 연습해보도록 하겠다.

www.mysqltutorial.org/mysql-sample-database.aspx/

 

*서브 쿼리의 사용

-> 예를 들어 특정 이름을 가진 고객의 주문 정보를 조회한다고 생각해보자. 먼저 Join을 이용하면 다음과 같은 방법을 사용해볼 수 있다.

-- 이름이 "Atelier graphique"인 고객의 주문 정보
select orderNumber, orderDate
from customers c join orders o
on c.customerNumber = o.customerNumber
where customerName = "Atelier graphique";

 하지만, 서브 쿼리를 이용하면 테이블을 굳이 조합하지 않더라도 해당 고객의 정보를 서브 쿼리를 이용하여 가져 온 뒤 쿼리를 연결하여 정보를 조회할 수도 있다.

-- 이름이 "Atelier graphique"인 고객의 주문 정보
select orderNumber, orderDate
from orders
where customerNumber = (select customerNumber
			from customers
                      where customerName = "Atelier graphique");

 어떤 검색 조건을 만족하는 테이블을 조회하는 방법은 이와 같이 여러가지 방법이 사용될 수 있다. 따라서, 적절한 경우에 적절한 쿼리를 사용하여 테이블을 조회하는 방법을 고민하는 것이 바람직하다(상황에 따라 어떤 쿼리를 쓰느냐에 따라 효율이 달라질 수 있다).

 

*중첩 서브 쿼리(Nested Subquery) : WHERE절에 사용하는 서브 쿼리.

(1) 단일 행 서브쿼리 : 서브 쿼리의 결과가 1개의 행을 도출하는 경우 단일 행 서브쿼리라고 한다. 위의 예시는 단일 행 서브쿼리라고 볼 수 있다.

-- 전체 상품의 평균 가격보다 가격이 높은 주문 정보
select *
from orderdetails
where priceEach > (select avg(buyPrice)
		from products)
order by priceEach;

(2) 다중 행 서브쿼리 : 서브 쿼리의 결과가 여러개의 행을 도출하는 경우 다중 행 서브쿼리라고 한다. 

-- IN : 서브 쿼리 결과중 하나의 값이라도 같은 값이 존재한다면
-- 구매 가격이 40 ~ 45사이인 물건들의 주문 내역
select orderNumber, productCode, priceEach
from orderdetails
where productCode in (select productCode
			from products
			where buyPrice between 40 and 45);

 다중 행 서브 쿼리를 사용하는 경우, 서브 쿼리로 출력되는 결과행이 다수이기 때문에 IN, ANY, ALL과 같은 복수 행 연산자를 사용하여야 한다. 

-- ANY : 서브 쿼리 결과에 대하여 어느 하나의 값이라도 만족하면 결과 반환.
-- 전체 물건의 구매 가격에 대하여 하나라도 주문 가격이 낮은 경우
select orderNumber, productCode, priceEach
from orderdetails
where priceEach < any (select buyPrice
		from products);

-- ALL : 서브 쿼리 결과에 대하여 모든 값이 만족해야만 결과 반환.
-- 전체 물건의 구매 가격에 대하여 항상 주문 가격이 높은 경우
select orderNumber, productCode, priceEach
from orderdetails
where priceEach > all (select buyPrice
		from products);

(3) 다중 열 서브쿼리 : 서브 쿼리의 결과가 여러 열을 도출하는 경우 다중 열 서브쿼리라고 한다.

-- 사무실 코드가 1인 사원들의 매니저, 직업이 일치하는 사원들의 사번과 이름
select employeeNumber, lastName, firstName
from employees
where (reportsTo, jobTitle) in (select reportsTo, jobTitle
				from employees
				where officeCode = 1);

 

*인라인 뷰(Inline View) : FROM절에 사용하는 서브 쿼리.

-> 앞서 SELECT 문을 이용한 데이터 조회는 결과적으로 검색 조건을 만족하는 또 하나의 테이블을 만들어 내는 것을 의미한다고 하였었다. 인라인 뷰는 이러한 점을 적극 활용하여 FROM절에 동적으로 생성된 테이블을 배치하여 질의를 수행하는 방법이다.

-- 모든 고객의 평균 신용 한도보다 신용 한도가 큰 고객들의 담당 직원
select *
from (select customerNumber, salesRepEmployeeNumber, creditLimit
	  from customers
	  where creditLimit > (select avg(creditLimit) from customers)) a join employees e
on a.salesRepEmployeeNumber = e.employeeNumber
order by creditLimit;

 

*스칼라 서브 쿼리(Scalar Subquery) : SELECT절에 사용하는 서브 쿼리.

-> 마찬가지로 데이터 조회를 통해 만들어진 결과를 SELECT 절에 배치하여 결과를 형성한다. 원리를 바탕으로 생각하면 크게 이해하기 어렵지 않다.

-- 고객번호가 120 ~ 150번인 고객들의 고객 정보와 담당 직원 이름
select customerNumber, customerName, salesRepEmployeeNumber, 
(select lastName from employees where customers.salesRepEmployeeNumber = employees.employeeNumber) as Counselor
from customers
where customerNumber between 120 and 150;

 

*서브 쿼리와 DDL, DML

-> 쿼리의 결과가 또 하나의 테이블을 반환한다는 점을 이용하여 CREATE, INSERT, UPDATE, DELETE문에 다양하게 활용할 수 있다.

-- CREATE 문에서 테이블을 복사하는 경우
create table student2
select * from student;

create table student2
select studentNo, studentName, grade from student;

-- 테이블의 구조만 복사하고 싶은 경우(WHERE절을 활용한다).
create table student2
select * from student
where false;

-- INSERT 문에서 테이블 데이터를 복사하는 경우
insert into student2
select * from student;