23강. 서브쿼리
서브쿼리는 SELECT 명령에 의한 데이터 질의로, 상부가 아닌 하부의 부수적인 질의를 의미한다.
서브쿼리
(SELECT 명령)- 서브쿼리는 SQL 명령문 안에 지정하는 하부
SELECT명령으로 괄호로 묶어 지정한다. - 문법에는 간단하게
SELECT 명령이라고 적었지만SELECT 구,FROM 구,WHERE 구등SELECT명령의 각 구를 기술할 수 있다. - 특히 서브쿼리는 SQL 명령의 WHERE 구에서 주로 사용된다.
WHERE구는SELECT,DELETE,UPDATE구에서 사용할 수 있는데 이들 중 어떤 명령에서든 서브쿼리를 사용할 수 있다.
1. DELETE의 WHERE 구에서 서브쿼리 사용하기
- 만약
a열의 값이 가장 작은 행을 제거하고 싶다면DELETE명령과SELECT명령을 결합시켜서 제거할 수 있다.
최솟값을 가지는 행 삭제하기
DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample54);-
서브쿼리를 사용하면 이렇게
DELETE와SELECT를 결합시킬 수 있다. -
괄호로 둘러싼 서브쿼리 부분을 먼저 실행한 후
DELETE명령을 실행한다고 생각하면 이해하기 쉬울 것이다. -
단,
MySQL에서는 위 예제 쿼리를 실행할 수 없음에 주의해야 한다. -
대신
DELETE명령을SELECT명령으로 바꾸면 실행할 수 있다.MySQL에서 예제를 실행하면You can't specify target table 'sample54' for update in FROM clause라는 에러가 발생한다. 데이터를 추가하거나 갱신할 경우 동일한 테이블을 서브쿼리에서 사용할 수 없도록 되어있기 때문이다.- 에러를 발생하지 않고 실행하려면 다음과 같이 인라인 뷰로 임시 테이블을 만들도록 처리하면 된다.
DELETE FROM sample54 WHERE a = (SELECT a FROM (SELECT MIN(a) AS a FROM sample54) AS x);
-
한편 SQL에는 순차형 언어에서처럼 변수가 존재하지 않는다. 만약 변수를 사용할 수 있다고 한다면, 다음과 같이 정리해 표현할 수 있을 것이다.
변수 = (SELECT MIN(a) FROM sample54);
DELETE FROM sample54 WHERE a = 변수;- 사실 이렇게 변수를 사용하는 것은 가능하다.
- 구현방법에는 여러 가지가 있으므로 자세히 설명할 수는 없지만 변수를 사용할 수 있다는 것은 알아두자.
클라이언트 변수
앞서 언급한 변수에 관한 것으로, mysql 클라이언트에 한해 다음과 같이 구현할 수 있다.
이때 @a가 변수가 되고 set이 변수에 대입하는 명령이 된다.
mysql> set @a = (SELECT MIN(a) FROM sample54);
mysql> DELETE FROM sample54 WHERE a = @a;2. 스칼라 값
- 서브쿼리를 사용할 때는 그
SELECT명령이 어떤 값을 반환하는지 주의할 필요가 있다. - 여러 가지 패턴 중에서도 다음과 같은 네 가지가 일반적인 서브쿼리의 패턴이다.
서브쿼리의 패턴
패턴 1. 하나의 행과 하나의 값을 반환하는 패턴
SELECT MIN(a) FROM sample54;
패턴 2. 복수의 행이 반환되지만 열은 하나인 패턴
SELECT no FROM sample54;
패턴 3. 하나의 행이 반환되지만 열이 복수인 패턴
SELECT MIN(a), MAX(no) FROM sample54;
패턴 4. 복수의 행, 복수의 열이 반환되는 패턴
SELECT no, a FROM sample54;- 이때 패턴1만 다른 패턴과 다르다. 이는 다른 패턴과 달리 하나의 값을 반환하기 때문이다.
단일 값으로 통용되지만 데이터베이스 업계에서는스칼라 값이라 불리는 경우가 많으므로 기억해 두자.
SELECT 명령이 하나의 값만 반환하는 것을 '스칼라 값을 반환한다'고 한다!
- 스칼라 값을 반환하는
SELECT명령을 특별 취급하는 이유는 서브쿼리로서 사용하기 쉽기 때문이다. - 이처럼 스칼라 값을 반환하도록
SELECT명령을 작성하고자 한다면SELECT구에서 단일 열을 지정한다. - 복수 열을 반환하도록 하면 패턴 3이나 4가 되어버리기 때문이다.
SELECT구에서 하나의 열을 지정하고,GROUP BY를 지정하지 않은 채 집계함수를 사용하면 결과는 단일한 값이 된다.- 만약
GROUP BY로 그룹화를 하면 몇 가지의 그룹으로 나뉘어져 버릴 가능성이 있기 때문에 결과적으로 단일한 값이 반환되지 않을 수 있다. - 또한,
WHERE조건으로 하나의 행만 검색할 수 있다면 단일 값이 되므로 스칼라 값을 반환하는SELECT명령이 된다. - 통상적으로 특정한 두 가지가 서로 동일한지 여부를 비교할 때는 서로 단일한 값으로 비교한다.
- 즉,
WHERE구에서 스칼라 값을 반환하는 서브쿼리는=연산자로 비교할 수 있다는 뜻이다.
DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample54);- 여기에서 서브쿼리 부분은 스칼라 값을 반환하는
SELECT명령으로 되어있으므로=연산자를 사용해 열a의 값과 비교할 수 있다. - 반대로 스칼라 값을 반환하지 않도록 만들기란 간단하다. 서브쿼리 부분을 변경하면 스칼라 값을 반환하지 않도록 할 수 있다.
SELECT구에서 다른 열을 지정하거나GROUP BY를 지정하면 바로 에러가 발생한다.
= 연산자를 사용하여 비교할 경우에는 스칼라 값끼리 비교할 필요가 있다!
- 스칼라 값을 반환하는 서브쿼리를 특별히
스칼라 서브쿼리라 부르기도 한다. - 앞서
HAVING구를 설명할 때 '집계함수는WHERE구에서는 사용할 수 없다'라고 설명했었다. - 하지만
스칼라 서브쿼리라면WHERE구에 사용할 수 있으므로 집계함수를 사용해 집계한 결과를 조건식으로 사용할 수 있다. - 이와 비슷한 문제로 '
GROUP BY에서 지정한 열 이외의 열을SELECT구에 지정하면 에러가 난다'라는 것도 있었다. - 하나의 그룹에 다른 값이 여러 개 존재할 경우는 스칼라 값이라고 할 수 없다.
3. SELECT 구에서 서브쿼리 사용하기
- 서브쿼리는
SELECT구,UPDATE의SET구 등 다양한 구 안에서 지정할 수 있다. - 문법적으로 서브쿼리는
하나의 항목으로 취급한다. - 단, 문법적으로는 문제없지만 실행하면 에러가 발생하는 경우가 자주 있다. 이는 스칼라 값의 반환여부에 따라 생기는 현상으로, 서브쿼리를 사용할 때는 스칼라 서브쿼리로 되어있는지 확인해야 한다.
SELECT구에서 서브쿼리를 지정할 때는 스칼라 서브쿼리가 필요하다.
SELECT 구에서 서브쿼리 사용하기
SELECT
(SELECT COUNT(*) sample51) AS sq1,
(SELECT COUNT(*) sample54) AS sq2;- sample51 테이블의 행 개수와 sample54 테이블의 행 개수를 각 서브쿼리로 구한다.
- 여기서 한 가지 주의할 점이 있는데 서브쿼리가 아닌 상부의
SELECT명령에는FROM구가 없다는 것이다.MySQL등에서는 실제로FROM구를 생략할 수 있다. - 하지만
Oracle등 전통적인 데이터베이스 제품에서는FROM를 생략할 수 없다. - 이때
Oracle에서는 다음과 같이FROM DUAL로 지정하면 실행할 수 있다. DUAL은 시스템 쪽에서 데이터베이스에 기본으로 작성되는 테이블이다.
SELECT 구에서 서브쿼리 사용하기(Oracle의 경우)
SELECT
(SELECT COUNT(*) sample51) AS sq1,
(SELECT COUNT(*) sample54) AS sq2 FROM DUAL;4. SET 구에서 서브쿼리 사용하기
UPDATE의SET구에서도 서브쿼리를 사용할 수 있다.
SET 구에서 서브쿼리 사용하기
UPDATE sample54 SET a = (SELECT MAX(a) FROM sample54);SET구에서 서브쿼리를 사용할 경우에도 스칼라 값을 반환하도록 스칼라 서브쿼리를 지정할 필요가 있다.- 위
UPDATE명령을 실행하면 a 열 값이 모두 a 열의 최댓값으로 갱신된다. - 사실 이런 경우, 서브쿼리는 상부의
UPDATE명령과 관련이 있는 조건식으로 지정되지 않으면 별 의미가 없다(차차 설명함).
5. FROM 구에서 서브쿼리 사용하기
FROM구에서도 서브쿼리를 사용할 수 있다.FROM구에 서브쿼리를 지정하는 경우에도 서브쿼리의 기술방법은 같다.- 괄호로
SELECT명령을 묶으면 된다. 다만FROM구에는 기본적으로 테이블을 지정하는 만큼 다른 구와는 조금 상황이 다르다. - 한편
SELECT구나SET구에서는 스칼라 서브쿼리를 지정해야 하지만FROM구에 기술할 경우에는 스칼라 값을 반환하지 않아도 된다. 물론 스칼라 값이라도 상관없다.
FROM 구에서 서브쿼리 사용하기
SELECT * FROM (SELECT * FROM sample54) sq;SELECT명령 안에SELECT명령이 들어있는 듯 보이는데, 이를네스티드(nested) 구조, 또는중첩구조나내포구조라 부른다.- sq는 테이블의 별명으로,
Sub Query의 이니셜에서 따온 것이다. SELECT구에서는 열이나 식에 별명을 붙일 수 있다.- 마찬가지로
FROM구에서는 테이블이나 서브쿼리에 별명을 붙일 수 있다. - 테이블에는 이름이 붙여져 있지만 서브쿼리에는 이렇다 할 이름이 붙여져 있지 않다.
- 별명을 붙이는 것으로 비로소 서브쿼리의 이름을 지정한다.
- 이 때도
SELECT구에서 별명을 붙일 때처럼AS키워드를 사용하여 지정한다(단,Oracle에서는AS를 붙이면 에러가 발생한다.Oracle에서는AS를 붙이지 않는다). - 중첩구조는 몇 단계로든 구성할 수 있다. 다음과 같이 3단계 구조라도 상관없다.
FROM 구에서 서브쿼리 사용하기(3단계)
SELECT * FROM (SELECT * FROM (SELECT * FROM sample54) sq1) sq2;- 위의 예제처럼 테이블 한 개를 지정하는 데 3단계 중첩구조로 작성하지는 않는다. 사실 의미가 없기 때문이다.
실제 업무에서 FROM 구에 서브쿼리를 지정하여 사용하는 경우
- 앞서
LIMIT구에 관해서 설명할 때Oracle에는LIMIT구가 없다고 했습니다. ROWNUM으로 행 개수를 제한할 수 있지만, 정렬 후 상위 몇 건을 추출하는 조건은 붙일 수 없었다.- 이는
ROWNUM의 경우WHERE구로 인해 번호가 할당되기 때문이다. - 하지만
FROM구에서 서브쿼리를 사용하는 것으로Oracle에서도 정렬 후 상위 몇 건을 추출한다는 행 제한을 할 수 있다.
Oracle에서 LIMIT 구의 대체 명령
SELECT * FROM (
SELECT * FROM sample54 ORDER BY a DESC
) sq
WHERE ROWNUM <= 2;6. INSERT 명령과 서브쿼리
INSERT명령과 서브쿼리를 조합해 사용할 수도 있다.INSERT명령에는VALUES구의 일부로 서브쿼리를 사용하는 경우와,VALUES구 대신SELECT명령을 사용하는 두 가지 방법이 있다.- 먼저,
VALUES구의 값으로 서브쿼리를 사용하는 경우 서브쿼리는 스칼라 서브쿼리로 지정할 필요가 있다. 물론 자료형도 일치해야 한다.
VALUES 구에서 서브쿼리 사용하기
INSERT INTO sample541 VALUES(
(SELECT COUNT(*) FROM sample51),
(SELECT COUNT(*) FROM sample54)
);결과
|a|b|
|-|-|
|5|3|
INSERT SELECT
VALUES구 대신에SELECT명령을 사용하는 예를 살펴보자.
SELECT 결과를 INSERT하기
INSERT INTO sample541 SELECT 1, 2;결과
|a|b|
|-|-|
|5|3|
|1|2|
- 흔히
INSERT SELECT라 불리는 명령으로INSERT와SELECT를 합친 것과 같은 명령이 되었다. - 위 예제에서는
SELECT가 결괏값으로 1과 2라는 상수를 반환하므로,INSERT INTO sample 541 VALUES (1, 2)의 경우와 같다. - 이때
SELECT명령이 반환하는 값이 꼭 스칼라 값일 필요는 없다. SELECT가 반환하는 열 수와 자료형이INSERT할 테이블과 일치하기만 하면 된다.INSERT SELECT명령은SELECT명령의 결과를INSERT INTO로 지정한 테이블에 전부 추가한다.SELECT명령의 실행 결과를 클라이언트로 반환하지 않고 지정된 테이블에 추가하는 것이다.- 이 때문에 데이터의 복사나 이동을 할 때 자주 사용하는 명령이다.
- 열 구성이 똑같은 테이블 사이에는 다음과 같은
INSERT SELECT명령으로 행을 복사할 수도 있다.
테이블의 행 복사하기
INSERT INTO sample542 SELECT * FROM sample543;
꿈꾸는 개발자