where절 외의 서브쿼리

인자 where절 외의 select, from 절 서브쿼리에 대해 알아보자

 

1. select 절 서브쿼리

select문은 스칼라 서브쿼리만 가능하다. select절의 역할에 대해 생각해보면 당연하다.

상품 라인별 평균 소비자가격을 검색해보면

select productLine, name, MSRP 소비자가격,
		(
			select ROUND(AVG(MSRP), 2)
			  from products Y
			 where Y.productLine = X.productLine
		) '상품라인별 평균 소비자가격'
  from products X
 order by 1;
 
 
 select city 지점,
		(
			select count(*)
			  from employees Y
			 where Y.officeCode = X.officeCode
		) 직원수
  from offices X
 order by city;

지점명과 지점에 근무하는 직원의 수를 검색하면 아래 쿼리와 같다.

둘 다 연관, 단일값 서브쿼리다. 이전페이지에서 두개에 대해 설명했다. 

 

2. from절 서브쿼리(inline view)

from절 서브쿼리는 임시 테이블을 생성하기 위해 사용된다.

지금까지 사용했던 with와 동일한 동작을 한다.

 

inline view가 where절 서브쿼리와 다른 점

- 메인 쿼리에서 inline view의 컬럼을 자유롭게 참조

- order by 절을 사용할 수 있다.

 

권장 소비자가격이 가장 비싼 5개 상품을 검색

select name, scale, buyPrice, MSRP
  from (
    select *
      from products
     where MSRP is not null
     order by MSRP DESC
    ) as temp
 limit 5;

 

3. having절 서브쿼리

where절 서브쿼리가 존재하는 것 처럼 having절 서브쿼리도 존재한다.

 

Motorcycles의 평균 구매단가보다 적은 평균 구매단가를 갖는 상품라인과 평균 구매단가를 검색

select productLine 상품라인, avg(buyPrice) '평균 구매단가'
  from products
 group by productLine 
having avg(buyPrice) < (
                    select avg(buyPrice)
                      from products
                     where productLine = 'Motorcycles'
                    );

 

정리해보자면 서브쿼리의 용도는

1. 테이블 필터로 사용(where절 서브쿼리)

2. 새로운 컬럼을 생성(select절 서브쿼리)

3. 임시 테이블을 생성(from절 서브쿼리)

 

하지만 결국 일 하면서 느낀건 복잡한 서브쿼리를 사용해서 성능 최적화를 하려면

join과 서브쿼리의 적절한 사용이 필수였다.

 

4. update절 서브쿼리

우선 원래 있던 테이블에 컬럼을 추가하고

alter table offices
add column noOfEmployees tinyint;

서브쿼리를 이용해 데이터를 삽입해보자

update offices o
   set o.noOfEmployees = (
                        select count(*)
                          from employees e
                         where e.officeCode = o.officeCode 
                        );

select city, noOfEmployees
  from offices o ;

 

짠 데이터가 삽입됐다.

 

insert into employees (employeeId, lastName, firstName, extension
						email, jobTitle, officeCode)
values (
        (
            select max(employeeId) + 1
              from employees 
        ),
        'Julia', 'Roberts', 'x1111', 'julia@gmail.com', 'Sales Rep', 1
    )

표준SQL에서는 사용할 수 있지만 MySql에서는 같은 테이블에서 SELECT해 INSERT/UPDATE할 수 없다. 그래서 야는 에러가 뜬다. 이놈 이거 아주 이단아다.

 

사실 이렇게 insert할 일은 거의 없다. auto_increment 옵션을 써주면 되니까