sql 서브쿼리 활용하기(쿼리 중첩해서 사용하는 방법)

서브쿼리(sub query)는 말 그대로 쿼리 안에 쿼리가 들어가있는 형태를 말한다. 서브쿼리는 SELECT, FROM, WHERE, HAVING 등 쿼리문 어디에도 들어갈 수 있으며 서브쿼리를 잘 활용하면 아주 꽤 많은 문제를 쉽게 해결할 수 있다. 서브쿼리와 메인쿼리(main query)의 관계에 따라서 Non-correlated 서브쿼리와 correlated 서브쿼리로 나눠서 생각할 수도 있는데 이는 서브쿼리를 더 잘 활용하기 위한 개념상의 구분 정도로 생각하면 된다.

 

 

SELECT 절 서브쿼리 사용

 

다음과 같은 2개의 예제 테이블이 있다. 하나는 vendor에 대한 정보가 담긴 테이블이고 다른 하나의 테이블은 invoice에 대한 정보가 담긴 테이블이다. 이 두 테이블은 vendor_id로 연결되어있다.

예제 테이블 1

예제 테이블 2

 

여기서 vendor_name과 해당 vendor의 가장 최근의 invoice_date를 함께 SELECT하고싶다. 다음과 같이 쿼리를 짤 수 있다. 여기서 메인쿼리와 서브쿼리가 Correlated 되어있기 때문에 vendor_id에 해당되는 MAX(invoice_date)이 한 row에 SELECT 되는 것을 확인할 수 있다. 

 

SELECT vendor_name, (SELECT MAX(invoice_date) FROM invoices WHERE vendor_id=vendors.vendor_id) 
AS latest_invoice FROM vendors
ORDER BY latest_invoice DESC;

 

예제1 결과

FROM 절 서브쿼리 사용

FROM절 서브쿼리는 서브쿼리를 inline view로 사용한다는 생각하면된다. 또 From 절에 들어가는 서브쿼리는 반드시 alias를 지정해줘야한다. 예제 1의 결과에서 latest_invoice가 2018년 7월 이후인 데이터만 SELECT 한다고 생각해보자.

 

SELECT * FROM
(
SELECT vendor_name, (SELECT MAX(invoice_date) FROM invoices WHERE vendor_id=vendors.vendor_id) 
AS latest_invoice FROM vendors
) a
WHERE latest_invoice > '2018-07-31';

 

예제2 결과

WHERE 절 서브쿼리 사용

WHERE 절에 서브쿼리를 사용해서 aggregate함수를 조건으로 넣어줄수도 있다. invoices 테이블에서 vendor_id별로 평균 invoice_total보다 큰 invoice_total을 가진 row만을 SELECT한다. vendor_id로 메인쿼리와 서브쿼리가 correlated 되어있다. 

예제 테이블 3

SELECT vendor_id,invoice_number, invoice_total
FROM invoices i
WHERE invoice_total > (SELECT AVG(invoice_total) FROM invoices WHERE vendor_id = i.vendor_id);

예제3 결과

2021.07.18 - [Mysql query] - sql 셀프조인(self join) 쿼리 쓰는 이유와 간단한 예제

sql 셀프조인(self join) 쿼리 쓰는 이유와 간단한 예제