mindol1004/learn-with-making-clean-architecture

Files

$WIN10-01\UserK$ WIN10-01\UserK 326dd3a4bc refactor : 전체 디렉토리 구조 변경

2021-12-13 21:23:43 +09:00

13 KiB

Raw Permalink Blame History

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;