[54] 230316 SQL Ch 5. 집계와 서브쿼리: 23강 서브쿼리, 24강 상관 서브쿼리 [K-디지털 트레이닝 54일]

230316 Thu 54th class

Ch 5. 집계와 서브쿼리
진도: p.  216 ~ (교재: SQL 첫걸음, 저자: 아사이 아츠시)
MySQL Workbench Manual: https://dev.mysql.com/doc/workbench/en/
MySQL 튜토리얼: https://www.tutorialspoint.com/mysql/
SQLD 요약 pdf: https://yurimac.tistory.com/40
책 요약 (9p) pdf: https://brunch.co.kr/@ydy702/10

SQL 첫걸음 | 아사이 아츠시 - 교보문고

오늘 배운 것 중 기억할 것을 정리했다.

---

요약

서브쿼리 (sub query)
쿼리 안에 또 다른 쿼리를 작성

(select 구문)

서브쿼리의 종류
1. 스칼라 서브쿼리 (Scalar Subquery) p. 210
주로 SELECT 절에 위치하지만 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다.
컬럼 대신 사용되므로 반드시 하나의 값만을 반환해야 한다.

select (select * from 테이블명) from 테이블명

select (select * from 테이블명) from 테이블명

---

2. 중첩 서브쿼리 (Nested Subquery) p. 206
WHERE 절, HAVING 절에 사용할 수 있다.

select * from 테이블명 where 열명 = (select * from 테이블명)

select * from 테이블명 where 열명 = (select * from 테이블명)

교재 p. 206 (MySQL 사용이므로 인라인 뷰로 임시 테이블을 만들어서 처리했다.)

2.1 단일행 (Single Row) 서브쿼리 =
단일 행 비교 연산자: =, <, >, <> (같지 않음) 등
서브쿼리가 1건 이하의 데이터를 반환한다.

where 열명 = (select * from 테이블명)

결과가 하나만 나온다.

---

2.2 다중행 (Multi Row) 서브쿼리 IN, ANY, ALL
다중 행 비교 연산자: IN, ALL, ANY, SOME, EXITS
서브쿼리가 여러 건의 데이터를 반환한다.
결과가 여러 개다.

IN: OR (같은 것에 대해 비교)
ANY: OR (크기 비교)
ALL: AND 

IN : 메인쿼리의 비교조건이 서브쿼리 결과 중에서 하나라도 일치하면 참
서브쿼리 출력결과와 하나라도 일치하면 참이 된다.

교재의 예시

no = 으로 설정시 결과가 여러 개 이므로 에러 메시지가 뜬다

---

ANY, SOME : 메인쿼리의 비교조건이 서브쿼리의 검색결과와 하나 이상 일치하면 참

---

ALL : 메인쿼리의 비교조건이 서브쿼리의 검색결과와 모두 일치하면 참

---

EXIST : 메인쿼리의 비교조건이 서브쿼리의 결과 중 만족하는 값이 하나라도 존재하면 참

where 열명 IN (select * from 테이블명)

참고: https://mainichibenkyo.tistory.com/75

참고

2.3 다중 컬럼 (Multi Column) 서브쿼리
서브쿼리가 여러 컬럼의 데이터를 반환 

---

3. 인라인 뷰 (Inline View) (*as키워드를 이용하여 가상의 테이블명을 반드시 꼭 선언해야 한다.*) p. 212
FROM 절 등 테이블명이 올 수 있는 위치에 사용 가능하다. 

select * from (select * from 테이블명) as 가상테이블명

select * from (select * from 테이블명) as 가상테이블명

---

5장 집계와 서브쿼리

책의 목차
__20강__행 개수 구하기 - COUNT
1. COUNT로 행 개수 구하기
2. 집계함수와 NULL값
3. DISCOUNT로 중복 제거
4. 집계함수에서 DISTINCT

__21강__COUNT 이외의 집계함수
1. SUM으로 합계 구하기
2. AVG로 평균내기
3. MIN, MAX로 최솟값, 최댓값 구하기

__22강__그룹화 - GROUP BY
1. GROUP BY로 그룹화
2. HAVING 구로 조건 지정
3. 복수열의 그룹화
4. 결괏값 정렬

__23강__서브쿼리
1. DELETE의 WHERE 구에서 서브쿼리 사용하기
2. 스칼라 값
3. SELECT 구에서 서브쿼리 사용하기
4. SET 구에서 서브쿼리 사용하기
5. FROM 구에서 서브쿼리 사용하기
6. INSERT 명령과 서브쿼리

__24강__상관 서브쿼리
1. EXISTS
2. NOT EXISTS
3. 상관 서브쿼리
4. IN

23강 서브쿼리 p. 204

서브쿼리 (sub query)
쿼리 안에 또 다른 쿼리를 작성

(select 구문)

서브쿼리의 종류
1. 스칼라 서브쿼리 p. 210
select (select * from 테이블명) from 테이블명

2. 중첩 서브쿼리 p. 206
select * from 테이블명 where 열명 = (select * from 테이블명)

3. 인라인뷰 (as키워드를 이용하여 가상의 테이블명을 반드시 꼭 선언해야 한다.) p. 212
select * from (select * from 테이블명) as 가상테이블명

서브쿼리

(SELECT 명령)

서브 쿼리: SQL 하나의 문안에 포함되어 있는 또 다른 SQL문, 알려지지 않은 기준을 이용한 검색에 사용.
- 서브쿼리를 괄호로 감싸서 사용한다.
- 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고 복수 행 비교 연산자는 결과 건수와 상관없다.
- ORDER BY 서브쿼리에서는 를 사용하지 못한다.
- SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용 가능

---

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

예제 5-16) sample54 테이블

SELECT * FROM sample54;

---

예제 5-17) sample54에서 a의 최솟값 검색하기

SELECT MIN(a) FROM sample54;

---

예제 5-18) 최솟값을 가지는 행 삭제하기 p. 206
[2. 서브쿼리]

-- sample54 테이블에서 a열의 값이 20인 데이터의 행을 삭제하라
delete from sample54 where a = 20;

-- sample54 테이블에서 a열의 값이 최소값인 데이터의 행을 삭제하라
-- delete from sample54 where a열의 값이 최소값
-- sample54테이블에서 a열의 값이 최소값
select min(a) from sample54;

-- 괄호로 서브쿼리를 지정해 삭제
DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample54);

SELECT * FROM sample54;

MySQL에서는 위 코드가 오류가 나므로 아래 코드로 실행이 된다.
데이터를 추가하거나 갱신할 경우 동일한 테이블을 서브쿼리에서 사용할 수 없도록 되어 있기 때문에 에러가 발생한다.

해결법 1) 인라인 뷰로 임시 테이블을 만들도록 처리한다.

-- 인라인뷰 (as키워드를 이용하여 가상의 테이블명을 반드시 꼭 선언해야 한다.)
select *
from (select * from 테이블명) as 가상테이블명

DELETE FROM sample54 WHERE a = (SELECT a 
	FROM (SELECT Min(a) AS a FROM sample54) AS x);
    
-- (SELECT Min(a) AS a FROM sample54): 인라인 뷰

해결법 2) 
COLUMN: 클라이언트 변수
MySQL 클라이언트에 한해 구현할 수 있다.
@a: 변수, set: 변수에 대입하는 명령

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

---

23.2 스칼라 값 p. 207

스칼라 서브쿼리: 한 행, 한 칼럼만을 반환하는 서브쿼리

예제 5-19) 서브쿼리의 패턴
패턴 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;

Point: SELECT 명령이 하나의 값만 반환하는 것을 '스칼라 값을 반환한다'고 한다.

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

---

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

예제 5-20의 SELECT 구에서 스칼라 서브쿼리를 사용해본다.

예제 5-20) SELECT 구에서 서브쿼리 사용하기 p. 210
[1. 스칼라 서브쿼리]

SELECT
    (SELECT COUNT(*) FROM sample51) AS sql1,
    (SELECT COUNT(*) FROM sample54) AS sql2;

DBMS는 엑설과 다르게 같은 줄에 다 표현이 되는 것이 아니다.

한 줄에 한 값만 들어간다 = 스칼라 서브쿼리

예제 5-21) SELECT 구에서 서브쿼리 사용하기 (Oracle의 경우)

SELECT
    (SELECT COUNT(*) FROM sample51) AS sql1,
    (SELECT COUNT(*) FROM sample54) AS sql2 FROM DUAL;

---

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

예제 5-22) SET 구에서 서브쿼리 사용하기
MySQL에서는 안 된다.

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

수정된 코드)

update sample54
set a = (select a from (select max(a) as a from sample54) as x);
SELECT * FROM sample54;

---

23.5 FROM 구에서 서브쿼리 사용하기 (인라인 뷰)

인라인 뷰: FROM 절에서 사용되는 서브쿼리. ORDER BY를 사용 가능
게시판 페이징 처리 때 쓴다.
limit, offset의 경우 mySQL에만 있다.

예제 5-23) FROM 구에서 서브쿼리 사용하기
sq = 가상의 테이블

SELECT * FROM (SELECT * FROM sample54) sq;

---

예제 5-24) FROM 구에서 서브쿼리 사용하기 (AS로 지정) p. 212
[3. 인라인뷰 (*as 키워드를 이용하여 가상의 테이블명을 반드시 꼭 선언해야 한다.)]

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

---

예제 5-25) FROM 구에서 서브쿼리 사용하기 (3단계)

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

---

- 실제 업무에서 FROM 구에 서브쿼리를 지정하여 사용하는 경우
예제 6-26) Oracle에서 LIMIT 구의 대체 명령

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

		sample54
		no	a
@rownum:=0 @rownum(변수)를 0으로 초기화했다. int rownum = 0 (Java) mySQL에서는 @를 붙인다.		1	900
		2	900
		3	900

select @rownum:=@rownum + 1 rownum, no, a -- @rownum: 변수 호출 -- +1: 차이값
from sample54, 
(select @rownum:=0) tmp -- @rownum:=0: 변수 선언, 임시의 테이블명을 준다. Aliases
;

교재 p. 111 참고

2023.03.14 - [🌳K-Digital Revision 2023✨/SQL, MariaDB] - [52] 230314 [K-디지털 트레이닝 52일] SQL Ch 3. 정렬과 연산: 9강 정렬 - ORDER BY, 10강 복수의 열을 지정해 정렬하기, 11강 결과 행 제한하기 - LIMIT, 12강 수치 연산, 13강 문자열 연산, 14강 날짜 연산, 15..

게시판	board
ROWNUM	no	title	content	regdate	writer	count
1	1	제목1	내용1	2023-03-15	정자바	1
중간에 글 삭제 됨
10	12

1~10, 11~20, 21~30처럼 10단위씩 1 page, 2 page, 3 page가 된다. 

1PAGE	NO
	1	10
2PAGE	NO
	11	20
3PAGE	NO
	21	30

만약 글 하나를 삭제를 하면 문제가 발생한다. 
∴ ROWNUM이 필요하다.

1PAGE	ROWNUM
	1	10
2PAGE	ROWNUM
	11	20
3PAGE	ROWNUM
	21	30

---

23.6 INSERT 명령과 서브쿼리

예제 5-27) VALUES 구에서 서브쿼리 사용하기
VALUES 구의 값으로 서브쿼리를 사용하는 예

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

- INSERT SELECT 
예제 5-28) SELECT 결과를 INSERT하기

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

아래의 코드와 결과가 같다. 

INSERT INTO sample541 VALUES (1, 2)

열 구성이 똑같은 테이블 사이에는 INSERT SELECT 명령으로 행을 복사할 수 있다.
예제 5-22) 테이블의 행 복사하기

INSERT INTO sample542 SELECT * FROM sample541;

---

24강 상관 서브쿼리 p. 216

서브쿼리의 일종인 '상관 서브쿼리'를 EXITS 술어로 조합시켜서 서브쿼리를 사용하는 방법에 관해 알아본다.

exists
select 했을 때 그 테이블에 데이터가 있는지 없는지를 판별할 때
추가하려는 데이터가 테이블에 있는지를 판별해서
없으면 insert
있으면 update

UPDATE sample551 SET a = '있음' WHERE EXISTS (
SELECT * FROM sample552 WHERE no2 = no -- 서브쿼리, no: sample551의 no 열
);
-- no2의 a열 값과 no의 a열 값이 같으면 a를 '있음'으로 바꾼다.

EXITS

EXISTS (SELECT명령)

The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns TRUE if the subquery returns one or more records.

---

24.1 EXISTS (존재하면)

예제 5-30) sample551 테이블과 sample552 테이블

SELECT * FROM sample551;
SELECT * FROM sample552;

sample552 테이블에 있는지를 조사한 결괏값을 넣고 싶다.

예제 5-30) sample551 테이블과 sample552 테이블

-- sample551테이블의 a열 값이 sample552테이블의 a2열에 존재하면
UPDATE sample551 SET a = '있음' WHERE
EXISTS (SELECT * FROM sample552 WHERE no2 = no);

SELECT * FROM sample551;

'있음'으로 갱신되었다.

---

24.2 NOT EXISTS 

예제 5-32) NOT EXISTS를 사용해 '없음'으로 갱신하기

UPDATE sample551 SET a = '없음' WHERE
	NOT EXISTS (SELECT * FROM sample552 WHERE no2 = no);

SELECT * FROM sample551;

no행의 1, 2, 4열이 '없음'으로 갱신되었다.

---

24.3 상관 서브쿼리

부모 명령과 자식인 서브쿼리가 특정 관계를 맺는 것을 '상관 서브쿼리'라 부른다.
상관 서브쿼리에서는 부모 명령과 연관되어 처리되기 때문에 서브쿼리 부분만을 따로 떼어내어 실행시킬 수 없다.

UPDATE sample551 SET a = '있음' WHERE
 EXISTS (SELECT * FROM sample552 WHERE no2 = no);

SELECT * FROM sample552 WHERE no2 = no;
-- ->에러: no2가 불명확하다.

---

- 테이블명 붙이기
양쪽 테이블 모두 같은 이름의 열로 되어있다면 잘 동작하지 않는다.

예제 5-33) 열에 테이블명 붙이기
열명 앞에 '테이블명.'을 붙인다.

sample552.no2
sample551.no

MySQL에서는 WHERE sample552.no = sample552.no가 되어 조건식은 항상 참이 된다. 결과적으로 모든 행은 a열 값이 '있음'으로 갱신된다.

아래 코드는 문제 없이 실행할 수 있다.

UPDATE sample551 SET a = '있음' WHERE
 EXISTS (SELECT * FROM sample552 WHERE sample552.no2 = sample551.no);

---

24.4 IN (열명 IN (집합)) p. 221

IN연산자
WHERE 조건식 OR 조건식
WHERE IN (조건식, 조건식)

IN을 사용하면 집합 안의 값이 존재하는지를 조사할 수 있다.

IN

열명 IN(집합)

IN에서는 오른쪽에 집합을 지정한다. 왼쪽에 지정된 값과 같은 값이 집합 안에 존재하면 참을 반환한다.
OR로 기술했을 때와 별 차이가 없는 것 같지만, 값을 여러개 지정할 경우에는 조건식이 상당히 깔끔해진다.

예제 5-34) IN을 사용해 조건식 기술

 SELECT * FROM sample551 WHERE no IN(3, 5);

지정된 값이 반환된다. 테이블 sample551의 no열에 없는 값을 넣어도 있는 값만 반환된다. 

예제 5-35) IN의 오른쪽을 서브쿼리로 지정하기

SELECT * FROM sample551 WHERE no IN
	(SELECT no2 FROM sample552)

---

- IN과 NULL

IN에서는 집합 안에 NULL 값이 있어도 무시하지는 않는다. NULL을 비교할 때는 IS NULL을 사용해야 한다.

MySQL에서 집합에 NULL이 포함되어 있는 경우, 조건식 IN은 왼쪽 값이 집합에 포함되어 있으면 참을, 그렇지 않으면 NULL을 반환한다.  조건식 NOT IN은 왼쪽 값이 집합에 포함되어 있으면 거짓을, 그렇지 않으면 NULL을 반환한다.
NULL을 반환한다: 비교를 할 수 없다는 의미이다.

---