데이터베이스 첫걸음 정리 - 23장. 서브쿼리

서브쿼리란?

서브쿼리는 SQL 명령문 안에 지정하는 하부 SELECT 명령으로 괄호로 묶어 지정합니다.

(SELECT 명령)

문법에는 간단하게 SELECT 명령이라고 적었지만 SELECT 구, FROM 구, WHERE 구 등 SELECT 명령의 각 구를 기술할 수 있습니다.

서브쿼리는 SQL 명령의 WHERE 구에서 주로 사용되며 WHERE 구는 SELECT, DELETE, UPDATE 구에서 사용할 수 있는데 이들 중 어떤 명령에서든 서브쿼리를 사용할 수 있습니다.

물론 상황에 따라 다른 구에서도 사용할 수 있습니다.

DELETE의 WHERE 구에서 서브쿼리 사용

먼저 DELETE 명령의 WHERE 구에서 서브쿼리를 사용하는 예를 살펴보겠습니다.

select * from sample;

sample 테이블에서 a 열의 값이 가장 작은 행을 삭제하려는 경우 어떻게 할 수 있을까요?

먼저, a의 최소값을 다음과 같이 구할 수 있을 것입니다.

SELECT MIN(a) FROM sample;

그 다음 검색된 a 값으로 WHERE로 조건식을 지정하여 삭제할 수 있을 것입니다.

이 과정을 서브쿼리를 사용하면 한번의 명령으로 처리할 수 있습니다.

DELETE FROM sample WHERE a = (SELECT MIN(a) FROM sample);

주의 해야할 점은 MySQL에서는 데이터 추가나 갱신의 경우 동일한 테이블로 서브쿼리를 사용할 수 없습니다. 이를 처리하기 위해서는 다음과 같이 임시테이블을 사용해야 합니다.

DELETE FROM sample 
WHERE a = (SELECT a from (SELECT min(a) as a from sample) as sample_t);

SQL에서의 변수 사용

현재 서브쿼리를 사용한 것을 변수를 사용했다고 가정하면 다음과 같이 표현할 수 있습니다.

변수 = (SELECT MIN(a) FROM sample);
DELETE FROM sample WHERE a=변수;

MySQL 클라이언트의 경우 다음과 같이 변수를 사용할 수 있습니다.

set @a = (SELECT MIN(a) FROM sample);
SELECT * FROM sample WHERE a=@a;

스칼라 값

서브쿼리를 사용할 때는 그 SELECT 명령이 어떤 값을 반환하는지 주의할 필요가 있습니다.

여러 가지 패턴 중에서도 다음과 같은 네 가지가 일반적인 서브쿼리 패턴입니다.

 

1. 하나의 값을 반환하는 경우

SELECT MIN(a) FROM sample;

2. 복수의 행이 반환되지만 열은 하나인 경우

SELECT id FROM sample;

3. 하나의 행이 반환되지만 열이 복수인 경우

SELECT MIN(a), MAX(id) FROM sample;

4. 복수의 행 복수의 열이 반환되는 경우

SELECT id, a FROM sample;

첫번째 패턴만 다른 패턴과 달리 하나의 값만 반환하고 있습니다.

이렇게 하나만 반환하는 값을 단일 값으로도 부르지만 이런 값을 데이터베이스 쪽에서는 스칼라 값이라고 부릅니다.

스칼라 값을 반환하는 SELECT 명령을 특별 취급하는 이유는 서브쿼리로서 사용하기 쉽기 때문입니다.

스칼라 값의 경우 단일 값이기 때문에 = 연산자로 값을 비교할 수 있습니다.

아까 삭제할때 사용했던 쿼리를 다시 살펴봅시다.

DELETE FROM sample WHERE a = (SELECT MIN(a) FROM sample);

위에서도 결과값이 스칼라 값이기 때문에 = 연산자로 비교할 수 있습니다.

이렇게 스칼라 값을 반환하는 서브쿼리를 스칼라 서브쿼리라고 부르기도 합니다.

앞서 HAVING 구를 설명할 때는 집계함수는 WHERE 구에서는 사용할 수 없다라고 이야기 했습니다.

하지만 스칼라 서브쿼리라면 WHERE 구에서 사용할 수 있기 때문에 집계함수를 사용해 집계한 결과를 조건식으로 사용할 수 있습니다.

GROUP BY로 그룹화한 경우에는 그룹화한 값이지 하나의 값이 아니므로 스칼라 값이라고 할 수 없습니다.

SELECT 구에서 서브쿼리 사용하기

SELECT 구에서도 서브쿼리를 사용할 수 있으며, 서브쿼리는 항상 스칼라 서브쿼리여야 합니다.

다음은 SELECT 구에서 서브쿼리 사용 예시입니다.

SELECT
	(SELECT COUNT(*) FROM product) AS sq1,
	(SELECT COUNT(*) FROM sample) AS sq2;

MySQL에서는 FROM구를 생략할 수 있습니다.

SET 구에서 서브쿼리 사용하기

UPDATE의 SET 구에서도 서브쿼리를 사용할 수 있습니다.

다음은 SET 구에서 서브쿼리를 사용하는 예시입니다.

UPDATE sample 
SET a = (SELECT a from (SELECT max(a) as a from sample) as sample_t);

SET 구도 마찬가지로 스칼라 서브쿼리를 사용해야 합니다.

FROM 구에서 서브쿼리 사용하기

FROM 구에서도 서브쿼리를 사용할 수 있습니다.

다만 FROM 구에는 스칼라 서브쿼리가 아니어도 괜찮습니다.

SELECT * FROM (SELECT * FROM sample) sq;

위의 쿼리를 보면 SELECT 명령 안에 SELECT 명령이 들어있는 것 처럼 보이는데 이를 네스티드 구조 또는 중첩구조나 내포구조라고 부릅니다.

서브쿼리 뒤의 sq는 테이블의 별명을 붙인 것입니다.

현재는 테이블의 별명을 붙인 상황으로 서브쿼리에 별명을 붙이고 싶다면 다음과 같이 AS 키워드를 사용하면 됩니다.

SELECT * FROM (SELECT * FROM sample) AS sq;

Oracle에서는 AS를 붙이면 에러가 발생하므로 주의해야 합니다.

이와 같은 네스티드 구조는 몇 단계로든 구성할 수 있으며, 서브쿼리안에 추가적으로 서브쿼리를 작성하면 됩니다.

SELECT * FROM (SELECT * FROM (SELECT * FROM sample) sq1) sq2;

INSERT 명령과 서브쿼리

INSERT 명령과 서브쿼리를 조합해 사용할 수도 있습니다.

INSERT 명령에는 VALUES 구의 일부로 서브쿼리를 사용하는 경우와 VALUES 구 대신 SELECT 명령을 사용하는 두가지 방법이 있습니다.

먼저 VALUES 구의 값으로 서브쿼리를 사용하는 예를 살펴봅시다. 이때, 서브쿼리는 스칼라 서브쿼리로 지정해야 하며 자료형도 일치해야 합니다.

INSERT INTO sample VALUES (
	(SELECT COUNT(*) FROM product),
	(SELECT COUNT(*) FROM users)
);

이번에는 VALUES 구 대신에 SELECT 명령을 사용하는 예를 살펴보겠습니다. (현재 예제에서는 괄호를 붙이지 않아 서브쿼리라고 부르기 어려울 수 있습니다.)

INSERT INTO sample SELECT 1, 2;

이렇게 INSERT와 SELECT를 같이 사용하는 명령을 INSERT SELECT라고 부릅니다.

INSERT SELECT의 경우 반환하는 값이 스칼라 값일 필요가 없으며 SELECT가 반환하는 자료형이 일치하고 열의 개수가 일치하면 됩니다.

INSERT SELECT 명령은 데이터 복사나 이동을 할 때 유용하게 사용됩니다.

만약, 열 구성이 똑같은 테이블의 경우 다음 쿼리로 완전 복사를 할 수 있습니다.

INSERT INTO 테이블명 SELECT * FROM 테이블명;

정리

• 서브쿼리는 SQL 명령문 안에 지정하는 하부 SELECT 명령으로 괄호로 묶어 지정한 명령문을 의미합니다.
• MySQL에서는 데이터 추가나 갱신의 경우 동일한 테이블로 서브쿼리를 사용할 수 없습니다. 이를 처리하기 위해서는 임시테이블을 사용해야 합니다.
• MySQL 클라이언트의 경우 @변수명 형태로 값을 변수로 만들어 사용할 수 있습니다.
• 스칼라 값은 하나만 존재하는 값으로, 단일 값이라고 할 수 있습니다.
• 스칼라 값을 반환하는 서브쿼리를 스칼라 서브쿼리라고 부릅니다.
• WHERE에 집계함수를 사용할 수 없지만 스칼라 서브쿼리를 사용하면 집계함수를 사용한 것과 같은 결과를 얻을 수 있습니다.
• 서브쿼리를 사용하여 명령을 중첩시킨 형태를 네스티드 구조라고 합니다.
• INSERT 명령에서 서브쿼리를 사용하는 경우는 VALUES 구의 일부로 서브쿼리를 사용하는 경우와 VALUES 구 대신 SELECT 명령을 사용하는 두가지 방법이 있습니다.
• VALUES 구 대신 SELECT 명령을 사용하는 경우를 INSERT SELECT라고 부르며 데이터 복사나 이동하는 경우에 많이 사용됩니다.