서브쿼리

SELECT 명령에 의한 데이터 질의로, 상부가 아닌 하부의 부수적인 질의를 의미한다.
서브쿼리는 SQL 명령문 안에 지정하는 하부 SELECT 명령으로 괄호로 묶어 지정한다.

• SELECT 구, FROM 구, WHERE 구 등 SELECT 명령의 각 구를 기술할 수 있다.

WHERE 구에서 주로 사용된다.

• WHERE 구는 SELECT, DELETE, UPDATE 구에서 사용할 수 있는데 디들 중 어떤 명령에서든 서브쿼리를 사용할 수 있다.

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

• a 열의 값이 가장 작은 행이 어느 것인지 파악할 수 없는 경우에는 SELECT 문으로 검색하고자 할 것이다.
  • SELECT MIN(a) FROM sample54 검색하고자 했던 SELECT 명령을 DELETE 명령의 WHERE 구에서 사용하면 하나의 DELETE 명령으로 원하는 행을 삭제할 수 있다. (결합 시키기)
  • 괄호로 둘러싼 서브쿼리 부분을 먼저 실행한 후 나온 값으로 DELETE 명령을 실행한다고 생각하자.
• MySQL에서는 데이터를 추가하거나 갱신할 경우 동일한 테이블을 서브쿼리에서 사용할수 없도록 되어 있어 쿼리를 실행시 에러가 발생한다.
  • DELETE 명령을 SELECT 명령으로 바꾸기
  • 인라인 뷰로 임시 테이블을 만들도록 처리하면 된다.

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

SQL에는 변수를 사용할 수 있다. (구현 방법에는 여러가지가 있다.)

변수 = (SELECT MIN(a) FROM sample 54);
DELETE FROM sample54 where a = 변수; // 이런느낌으로 여러가지 있어서 다음에 공부해서 넣기

Mysql 클라이언트 변수

• @a가 변수 set이 변수에 대입하는 명령이 된다.

mysql>set @a = (SELECT MIN(a) FROM sample54);
mysql>DELETE FROM sample54 WHERE a= @a;

---

 
스칼라 값
서브쿼리를 사용할 때 그 SELECT 명령이 어떤 값을 반환하는지 주의해야 한다.
 
다음 4가지가 일반적인 서브쿼리 패턴

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 구에서 단일 열을 지정해야 한다.
SELECT 구에서 하나의 열을 지정하고, GROUP BY를 지정하지 않은 채 집계 함수를 사용한다.

• GROUP BY로 그룹화를 하면 몇 가지의 그룹으로 나뉘어져 버릴 가능성이 있다.
• SLECT 구에서 GROUP BY 안한 값을 사용하면 에러 발생하기 때문에 그룹을 추가하면 여러개 값이 존재하니까 나눠진다.
• WHERE 조건으로 하나의 행만 검색할 수 있도록 한다.
• 특정한 두 가지가 서로 동일한지 여부를 비교할 때는 서로 단일한 값으로 비교한다.

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

• WHERE 구에서 스칼라 값을 반환하는 서브쿼리는 = 연산자로 비교할 수 있다는 뜻이다.
• SELECT 구에서 서로 다른 열을 지정하거나 GROUP BY를 지정하면 바로 에러가 발생한다.

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

 
스칼라 값을 반환하는 서브쿼리를 ‘스칼라 서브쿼리’라 부르기도 한다.
 
‘집계함수는 WHERE 구에서 사용할 수 없지만 ‘스칼라 서브쿼리’라면 WHERE 구에 사용할 수 있으므로 집계함수를 사용해 집계한 결과를 조건식으로 사용할 수 있다.

• 서브쿼리 Select 문으로 집계함수 사용한 값으로 스칼라값 뽑아서 조건문으로 쓰면 되니까

---

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

문법적으로 서브쿼리는 ‘하나의 항목’을 취급한다.

• 문법적으로는 문제없지만 실행하면 에러가 발생하는 경우가 자주 있다.
  • 스칼라 값의 변환여부에 따라 생기는 현상으로, 서브쿼리를 사용할 때는 스칼라 서브쿼리로 되어있는지 확인해야 한다.

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

SELECT
	(SELECT COUNT(*) FROM sample51) AS sq1,
	(SELECT COUNT(*) FROM sample54) AS sq2;

• 서브쿼리가 아닌 상부의 SELECT 명령에는 FROM 구가 없다.
  • MySQL 등에서는 실제로 FROM 구를 생략할 수 있다.
  • Oracle 등 전통적인 데이터베이스 제품에는 FROM 구를 생략할 수 없다.

Oracle 과 같은 정품적인 데이터베이스

SELECT
	(SELECT COUNT(*) FROM sample51) AS sq1,
	(SELECT COUNT(*) FROM sample54) AS sq2 FROM DUAL;

• FROM DUAL로 지정하면 사용할 수 있다.
  • DUAL은 시스템 쪽에서 데이터베이스에 기본으로 작성되는 테이블이다.

sq1	sq2
5	3

 

 

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

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

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

SET 구에서 서브쿼리를 사용할 경우에도 스칼라 값을 반환하도록 스칼라 서브쿼리를 지정할 필요가 있다.

• 실제로는 자주사용하지 않는 UPDATE 명령
• 서브쿼리는 상부의 UPDATE 명령과 관련이 있는 조건식으로 지정하지 않으면 별 의미가 없다.

 

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

이걸 배우기 전까지 FROM 구에서 테이블 지정만 해왔지만 테이블 이외의 것도 지정할 수 있다.
FROM 구에는 기본적으로 테이블을 지정하는 만큼 다른 구와는 조금 상황이 다르다.

• 스칼라 값을 반환해도, 반환하지 않아도 좋다.

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

SELECT 명령 안에 SELECT 명령이 들어있는 듯 보인다.

• 이를 ‘네스티드(nested) 구조’ , 또는 ‘중첩구조’나 ‘내포구조’라 부른다.

SELECT 구에서 열이나 식에 별명을 붙일 수 있는 것처럼 FROM 구에서도 테이블이나 서브쿼리에 별명을 붙일 수 있다.

• 테이블에는 이름이 붙여 있지만 서브쿼리에는 이름이 붙여저 있지 않다.
• 별명을 붙이는 것으로 서브쿼리의 이름을 지정한다.
  • ‘AS’ 키워드를 사용하여 지정한다.
• Oracle에서는 AS를 붙이면 에러가 발생한다. (AS 안붙임)

 

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

SELECT * FROM (SELECT * FROM (SELECT * FROM smaple 54) sq1) sq2;

• 테이블 한 개를 지정하는 데 3단계 중첩구조로 작성하지 않는다.

 

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

Oracle에는 LIMIT 구가 없다.

SELECT * FROM sample33 WHERE ROWNUM <= 3;

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

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

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

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

no	a
2	900
1	100

 

 

INSERT 명령과 서브쿼리

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

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

서브쿼리는 스칼라 서브쿼리로 지정해야 한다.

• 자료형도 일치해야 한다.

a	b
5	3

VALUES 구 대신에 SELECT 명령으로 사용하는 방법

• 괄호를 붙이지 않아 서브쿼리라고 부르기 어려울 수도 있다.

INSERT INTO sample541 SELECT 1,2;
SELECT * FROM sample541;

a	b
5	3
1	2

• ‘INSERT SELECT’라 불리는 명령으로 INSERT와 SELECT를 합친 것과 같은 명령이 되었다.

SELECT가 결괏값으로 1과 2라는 상수를 반환한다.

• INSERT INTO sample541 VALUES (1, 2)의 경우와 같다.

SELECT 명령이 반환하는 값이 꼭 스칼라 값일 필요는 없다.

• SELECT가 반환하는 열 수와 자료형이 INSERT할 테이블과 일치하기만 하면 된다.

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

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

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

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

INSERT INTO sample542 SELECT * FROM sample543;