[SQL] 서브 쿼리 활용하기

Joney의 SW 공부 블로그·2023년 2월 5일

SQL

목록 보기

10/14

서브 쿼리를 사용하여 여러 SELECT문을 한번에 실행하기

서브 쿼리

productorder테이블에서 price가 평균 이상인 레코드를 가져오기
- 평균을 계산하는 SELECT문을 하나, 평균 이상의 레코드를 가져오는 SELECT를 하나, 총 두개의 SELECT문을 실행
SELECT문에 적은 다른 SELECT문을 서브 쿼리라 부름
- 바깥의 SELECT문은 메인 쿼리라 부름

SELECT
	order_id, price
FROM
	productorder
WHERE
	price >= (
    	SELECT
        	AVG(price)
        FROM
        	productorder
    );

서브 쿼리를 적는 곳

주로 WHERE문에 적는 경우가 많지만 SELECT문에 적을 때도 있음
productorder 테이블에서 price 순으로 3건의 order_id와 price, productorder 테이블의 전체 레코드 수를 가져오기

SELECT
	order_id, price
	(
    	SELECT
        	count(*)
        FROM
        	productorder
    ) AS order_count
FROM
	productorder
ORDER BY
	price
LIMIT
	3;

결과가 여러개인 서브쿼리

여러 결과를 사용하려면?

연산자를 사용해서 여러개의 결과에서 일치하는 값을 찾을 수 있음

연산자	사용법	의미
IN	a IN (서브 쿼리)	a가 서브 쿼리 결과 중 어느 것과 일치하면 1을 반환
NOT IN	a NOT IN (서브 쿼리)	a가 서브 쿼리의 결과 중 어느 것에도 일치하지 않으면 1을 반환
ANY	a 연산자 ANY (서브 쿼리)	서브 쿼리의 결과 중 어느 것과 a의 연산 결과가 1이라면 1을 반환
ALL	a 연산자 ALL (서브 쿼리)	서브 쿼리의 결과 전체와 a의 연산 결과가 1이라면 1을 반환

productorder테이블의 안에 있는 price 값이 700 이상인 customer_id를 서브 쿼리에서 가져옴
중복 레코드를 DISTINCT로 제거
IN연산자를 사용해서 customer 테이블에서 고객 정보 가져오기

SELECT
	customer_id, customer_name
FROM
	customer
WHERE
	customer_id IN
		(
        	SELECT
            	DISTINCT customer_id
           	FROM
            	productorder
            WHERE
            	price >= 700
        );

ANY연산자 ALL연산자를 사용하기

productorder 테이블에서 product_id 마다 quantitiy의 합계값을 서브 쿼리로 취함
product 테이블에서 서브 쿼리의 결과 중 하나라도 stock이 작으면 그 레코드를 가져옴
- ALL의 경우라면 서브 쿼리의 모든 값보다 stock이 작아야 그 레코드를 가져오게 됨

SELECT
	*
FROM
	product
WHERE stock < ANY
	(
    	SELECT
        	SUM(quantity)
        FROM
        	productorder
        GROUP BY
        	product_id
    );

복수행의 서브 쿼리에서 주의할 점

서브 쿼리에 NULL이 있으면 결과가 달라질 수도 있음

5 IN (1, 2, 3) # 0
5 IN (1, 2, NULL) # NULL
2 IN (1, 2, NULL) # 1

서브 쿼리에서 NULL 제거

SELECT
	*
FROM
	테이블명
WHERE
	컬럼명 IN 
        (
            SELECT
                컬럼명
            FROM
                테이블명
            WHERE
                컬럼명 IS NOT NULL
        );

productorder 테이블 안에서 price의 내림차순으로 상위 3개의 레코드의 customer_id를 서브쿼리로 가져와서, customer 테이블에서 해당하는 cunstomer_id와 customer_name을 가져오기

아래와 같이 서브쿼리에 LIMIT를 하고 IN을 하면 오류가 발생

SELECT
	customer_id, customer_name
FROM
	customer
WHERE
	customer_id IN
		(
        	SELECT
            	customer_id
           	FROM
            	productorder
            ORDER BY
            	price DECS
            LIMIT 3
        );

LIMIT을 쓰려면 서브쿼리를 중첩해야함

서브 쿼리가 테이블이 되는 예시

SELECT
	customer_id, customer_name
FROM
	customer
WHERE
	customer_id IN
		(
        	SELECT
            	customer_id
           	FROM
            	(
                    SELECT
                        customer_id
                    FROM
                        productorder
                    ORDER BY
                        price DECS
                    LIMIT 3
                ) AS tmp
            	
        );

상관 서브 쿼리

보통의 서브 쿼리가 있는 SELECT문에서는 서브 쿼리가 먼저 실행되고 그 결과를 메인 쿼리에 넣고 메인 쿼리를 실행
상관 서브 쿼리는 서브 쿼리를 메인 쿼리와 연계해서 실행하는 것
상관 서브 쿼리에서는 먼저 메인 쿼리를 실행하고 메인 쿼리의 1레코드마다 서브 쿼리를 실행
- 컬럼이 어느 테이블에서 온 것인지 나타내기 위해 테이블명과 컬럼명을 "."으로 연결
product 테이블에 있는 상품 중에서 productorder 테이블의 정보로부터 상품마다의 합계 매상 개수가 3보다 큰 상품의 정보 가져오기

SELECT
	product.product_id
	product.product_name
FROM
	product
WHERE
	3 < (
    	SELECT
        	SUM(quantity)
        FROM
        	productorder
        WHERE
        	product.product_id = productorder.product_id
    );

customer 테이블에서 customer_id와 customer_name에 대해서 고객마다의 합계 구입 금액을 productorder테이블에서 가져오기

SELECT
	a.customer_id
	a.customer_name
	(
    	SELECT
        	SUM(b.price)
        FROM
        	productorder AS b
        WHERE
        	a.customer_id = b.customer_id
    )
FROM
	customer AS a;

EXISTS 연산자

EXISTS연산자는 상관 서브 쿼리의 결과에 사용하는 연산자

연산자	사용법	의미
EXISTS	EXISTS(서브쿼리)	서브 쿼리의 결과가 존재하면 1을 반환

product 테이블의 레코드 중, productorder 테이블에 정보가 있는 product_id만을 가져오기 (매상이 있던 상품 정보만 가져오기)

SELECT
	a.product_id
	a.product_name
FROM
	product AS a
WHERE
	EXISTS (
    	SELECT
        	*
        FROM
        	productorder AS b
        WHERE
        	a.product_id = b.product_id
    );