서브쿼리

banjjoknim·2021년 2월 27일
0

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);
  • 서브쿼리를 사용하면 이렇게 DELETESELECT를 결합시킬 수 있다.

  • 괄호로 둘러싼 서브쿼리 부분을 먼저 실행한 후 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 구, UPDATESET 구 등 다양한 구 안에서 지정할 수 있다.
  • 문법적으로 서브쿼리는 하나의 항목으로 취급한다.
  • 단, 문법적으로는 문제없지만 실행하면 에러가 발생하는 경우가 자주 있다. 이는 스칼라 값의 반환여부에 따라 생기는 현상으로, 서브쿼리를 사용할 때는 스칼라 서브쿼리로 되어있는지 확인해야 한다.
  • 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 구에서 서브쿼리 사용하기

  • UPDATESET 구에서도 서브쿼리를 사용할 수 있다.

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라 불리는 명령으로 INSERTSELECT를 합친 것과 같은 명령이 되었다.
  • 위 예제에서는 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;

profile
꿈꾸는 개발자

0개의 댓글