SQL 첫걸음 - 5장 서브쿼리(4/5)

2020-11-01

SQL 첫걸음

서브쿼리

SELECT 명령에 의한 데이터 질의로, 상부가 아닌 하부의 부수적인 질의를 의미.

SQL 명령문 안에 지정하는 하부 SELECT 명령으로 괄호로 묶어 지정.

특히 서브쿼리는 WHERE 구에서 주로 사용됨.

WHERE 구는 SELECT, DELETE, UPDATE 구에서 사용할 수 있는데 이들 중 어떤 명령에서든 서브쿼리 사용 가능.

1. DELETE의 WHERE 구에서 서브쿼리 사용하기

최솟값을 가지는 행 삭제 (괄호로 서브쿼리를 지정해 삭제)

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

단, MySQL에서는 위 쿼리를 실행할 수 없음..

(데이터를 추가하거나 갱신할 경우 동일한 테이블을 서브쿼리에서 사용할 수 없도록 되어 있기 때문)

에러를 발생하지 않고 실행하려면 다음과 같이 인라인 뷰로 임시 테이블을 만들도록 처리하면 됨.

DELETE 
  FROM sample54 
 WHERE a = (SELECT a 
              FROM (SELECT MIN(a) AS a 
                      FROM sample54) AS x); 

2. 스칼라 값

SELECT 명령이 하나의 값만 반환하는 것을 ‘스칼라 값을 반환한다’고 함.

스칼라 값을 반환하는 SELECT 명령은 서브쿼리로써 사용하기 용이.

스칼라 값을 반환하는 서브쿼리를 특별히 스칼라 서브쿼리라 부르기도함.

SELECT 구에서 하나의 열을 지정하고, GROUP BY를 지정하지 않은 채 집계함수를 사용하면 결과는 단일한 값이 됨.

(만약 GROUP BY로 그룹화를하면 몇 가지 그룹으로 나뉘어져 버릴 가능성이 있어 단일한 값이 반환되지 않을 수 있음)

또한, WHERE 조건으로 하나의 행만 검색하는 SELECT 명령도 스칼라값을 반환.

=연산자(비교연산자)를 사용하여 비교할 경우엔 스칼라 값끼리 비교할 필요가 있다.

집계함수를 WHERE구에 사용할 수 없어서 HAVING 구를 사용했었는데,

스칼라 서브쿼라면 WHERE 구에 사용할 수 있으므로 집계함수를 사용해 집계한 결과를 조건식으로 사용할 수도 있다.

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

SELECT 구에서 서브쿼리를 지정할 때는 스칼라 서브쿼리가 필요.

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

select (select COUNT(*) 
          from sample51) as sq1, 4 (select COUNT(*) 
                                      from sample54) as sq2

MySQL 등에서는 위와 같이 FROM 구를 생략할 수 있지만

Oracle 등 전통적인 DB제품에서는 FROM 생략이 불가능해 FROM DUAL로 지정하여 실행.

(DUAL은 시스템 쪽에서 데이터베이스에 기본으로 작성되는 테이블)

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

UPDATE의 SET구에서도 서브쿼리를 사용 가능.

ex) 모든 a열 값을 a열 값의 최대값으로 갱신.

UPDATE sample54 
   SET a = (SELECT MAX(a) 
              FROM sample54);

mysql

UPDATE sample54 
   SET a = (SELECT a 
              FROM (SELECT MAX(a) AS a 
                      FROM sample54) AS x);

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

SELECT 구나 SET구에서는 스칼라 서브쿼리를 지정해야 하지만

FROM 구에 기술할 경우에는 반드시 스칼라 값을 반환하지 않아도 됨.

SELECT * 
  FROM (SELECT * 
          FROM sample54) sq; 

위와 같은 구조를 ‘네스티드(nested) 구조’, 또는 ‘중첩구조’나 ‘내포구조’라 부름.

sq는 테이블 별명. FROM 구에서는 테이블이나 서브쿼리에 별명을 붙일 수 있는데,

테이블에는 이름이 붙여져 있지만 서브쿼리에는 이렇다 할 이름이 붙여져 있지 않음.

별명을 붙이는 것으로 비로소 서브쿼리의 이름을 지정.

AS키워드를 사용해서 AS sq 로 지정해도 됨. (단, Oracle에서는 AS를 붙이지 않음)

중첩구조는 몇 단계로든 구성할 수 있음.

3단계 중첩

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

실제 업무에서 FROM 구에 서브쿼리를 지정하여 사용하는 경우

Oracle에서는 LIMIT 구가 없음.

ROWNUM으로 행 개수를 제한할 수 있지만, 정렬(ORDER BY) 후 상위 몇건을 추출하는 조건은 붙일 수 없음.

ROWNUM의 경우 WHERE 구로 인해 번호가 할당되기 때문.

하지만 FROM 구에서 서브쿼리를 사용하는 것으로 Oracle에서도 정렬 후 상위 몇 건을 추출한다는 행 제한을 할 수 있음.

Oracle에서 LIMIT 구의 대체 명령

SELECT * 
  FROM (SELECT * 
          FROM sample54 
      ORDER BY a DESC) sq 
         WHERE ROWNUM <= 2; 

6. INSERT 명령과 서브쿼리

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

1) VALUES 구의 일부로 서브쿼리를 사용

INSERT INTO sample541 
     VALUES ((SELECT COUNT(*) 
                FROM sample51)
          , (SELECT COUNT(*)
               FROM sample54));

2) VAULES 구 대신 SELECT 명령을 사용.

INSERT INTO sample541 
    SELECT 1,2;

SELECT가 결과값으로 1과 2라는 상수를 반환하므로,

INSERT INTO sample541 
    VALUES (1,2)와 같음 

흔히 ‘INSERT SELECT’ (SELECT 결과를 INSERT)라고 불리는 명령으로 INSERT와 SELECT를 합친 것과 같은 명령.

이때 SELECT 명령이 반환하는 값이 꼭 스칼라 값일 필요는 없음. SELECT가 반환하는 열 수와 자료형이 INSERT할 테이블과 일치하기만 하면 됨.

INSERT SELECT 명령은 SELECT 명령의 결과를 INSERT INTO로 지정한 테이블에 전부 추가.

SELECT명령의 실행 결과를 클라이언트로 반환하지 않고 지정된 테이블에 추가하는 것.

이때문에 데이터의 복사나 이동을 할 때 자주 사용하는 명령.

열 구성이 똑같은 테이블 사이에는 다음과 같은 INSERT SELECT 명령으로 행을 복사할 수도 있음.

INSERT INTO sample542 
      SELECT * 
        FROM sample543; 

출처 : https://smilejh.tistory.com/

 SQL 첫걸음 - 5장 그룹화(GROUP BY)(3/5) SQL 첫걸음 - 5장 상관 서브쿼리(5/5) 