Today I Learned

오늘도 면접 준비를 하면서 SQL 공부를 진행했다. 오늘은 복수행 서브쿼리에 대해 공부했다.

---

복수행 서브쿼리

• 단일행 서브쿼리 : 위의 예시들 처럼 서브쿼리가 단 하나의 레코드만 반환하는 서브쿼리.
  단 하나의 값만 반환하므로 결과물에 대해 비교 연산자를 쓸 수 있다.

• 복수행 서브쿼리 : 여러 행의 레코드를 반환하는 서브쿼리.
  하나의 컬럼에 복수의 레코드나 복수 컬럼에 복수 레코드 둘 다 복수행 서브쿼리다.
  당연히 복수의 값이 나오기 때문에 해당 서브쿼리는 >, <, = 같은 비교 연산자를 쓸 수 없다.
  (우연히 하나의 결과만 나온다면 오류 없이 결과가 나올 수는 있다.)

---

하나의 컬럼에 복수의 레코드

서브 쿼리의 결과가 컬럼은 하나인데 레코드가 여러개 일 경우이다.

• 사용할 수 있는 연산자

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

---

1. IN 예시

   productorder 테이블의 price 값이 700 이상인 customer_id의 정보를 customer 테이블에서 가져와라.

• 아래의 쿼리를 실행하면 price가 700이상인 customer_id를 productorder에서 가져온다.
  customer_id 한 컬럼에 여러 레코드가 같이 담겨져 온다.

SELECT DISTINCT customer_id
FROM productorder
WHERE price >= 700

• 위의 쿼리를 서브쿼리로 바꿔 WHERE구에서 IN 연산자를 활용해 customer 테이블에서 결과를 가져온다.

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

• IN 연산자는 이처럼 일치 여부 즉, = 등호 연산자의 역할을 한다.

---

2. ANY / ALL 예시

   productorder테이블을 product_id로 그룹화 했을 때, 각 그룹의 quantity 총합보다 stock이 적은 product 테이블의 정보를 가져와라.

• 우선 서브 쿼리의 결과를 먼저 보면 아래와 같이 SUM(quantity) 하나의 컬럼에 여러 레코드가 나열되어 있다.

• 위의 서브 쿼리를 메인 쿼리 안에 넣어 ANY로 비교하면 아래와 같은 결과물이 나온다.

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

---

NULL 주의점

• IN이나 ANY 연산자에서 서브쿼리의 결과에 일치하는 것이 없고, 서브쿼리 결과물 중 NULL이 있으면 반환값이 NULL이 된다.

10 IN (1,5,8) # 결과는 0
10 IN (1,10,NULL) # 결과는 1
10 IN (1,5,NULL) # 결과는 NULL

• 위의 경우 서브쿼리의 결과에서 NULL을 제외하는 방식을 사용하면 된다.

SELECT * FROM 테이블명
WHERE 컬럼명 IN
	(SELECT 컬럼명 FROM 테이블명
    WHERE 컬럼명 IS NOT NULL); # 서브쿼리의 결과물에서 NULL을 제외한다.

---

IN + LIMIT

• IN/ALL/ANY/SOME 연산자에 LIMIT이 들어간 서브쿼리를 사용할 수 없다.

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

Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

• 사용하기 위해선 서브쿼리 안에 서브쿼리로 LIMIT을 사용하면 된다.

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

• 위의 방식에서는 가장 안쪽의 서브쿼리가 먼저 실행되어 LIMIT으로 레코드 수를 제한하며, 직접적으로 IN과 LIMIT이 만나지 않기 때문에 오류가 뜨지 않는다. 그리고 가장 안쪽의 서브쿼리엔 AS로 별명을 붙여야한다.

---

서브쿼리의 결과가 테이블 형태

복수행 서브쿼리 중에서도 서브쿼리의 결과가 복수의 컬럼을 가지는 경우일때, 결과는 1개의 테이블로 간주된다.

• 서브쿼리의 결과가 테이블이면 반드시 AS로 별명을 붙여야 한다.
  붙이지 않으면 Error Code: 1248. Every derived table must have its own alias라는 결과가 뜬다.

• 이 형태는 하나의 테이블로 메인쿼리의 FROM구에서 이용되는 경우가 많다.

예시

productorder 테이블을 customer_id로 그룹화하고 그룹마다 구입 횟수를 카운트 해 그 평균값을 구하라.

• 서브쿼리는 아래의 결과처럼 테이블로 나온다.

SELECT customer_id, COUNT(*) AS C
FROM productorder
GROUP BY customer_id

• 서브쿼리를 메인쿼리의 FROM구에 별명을 붙여 넣는다.

SELECT AVG(c)
FROM 
	(
    SELECT customer_id, COUNT(*) AS C
    FROM productorder
    GROUP BY customer_id
    ) AS a;