5장 - 집계와 서브쿼리

20. 행 개수 구하기 - COUNT

SQL은 데이터베이스라 불리는 데이터 집합을 다루는 언어
집합의 개수나 합계 등을 구할 때 집계함수인 COUNT / SUM / AVG / MIN / MAX 등을 사용할 수 있음
집계함수는 인수로 집합을 지정하기 때문에 집합함수라고도 불림

COUNT로 행 개수 구하기

COUNT : 인수로 주어진 집합의 개수를 반환
SELECT COUNT(*) FROM 테이블명의 형태로 사용

• * : 모든 열을 나타낼 때 사용하는 메타문자, COUNT에 사용시 테이블 전체라는 의미로 사용
• 인수로 지정된 집합의 개수를 계산함

집계함수는 집합으로부터 하나의 값을 반환하므로 WHERE구의 유무와 관계없이 하나의 행만을 결과값으로 반환함

집계함수와 NULL값

COUNT의 인수로 열명을 지정시 해당 열에 한해서 행의 개수를 구함

• 단, 이때 NULL값이 있을 경우 이를 제외하고 처리함
• 단! COUNT(*)의 경우 모든 열의 행수를 카운트하므로 NULL값이 있어도 무시되지 않음

DISTINCT로 중복 제거

DISTINCT : 중복된 값을 제거하고 출력
SELECT DISTINCT 행명 FROM 테이블명의 형태로 사용, 중복된 데이터를 제외한 결과를 클라이언트로 반환
DISTINCT 대신 ALL을 사용할 경우 모든 행을 반환하며, 생략시 ALL으로 간주됨

집계함수에서 DISCTINCT를 사용할 때는 집계함수의 인수로 DISTINCT를 사용한 수식을 지정
SELECT COUNT(DISTINCT 열명) FROM 테이블명의 형태로 사용

---

21. COUNT 이외의 집계함수

SUM으로 합계 구하기

SUM : 집합의 합계를 구함
SELECT SUM(열명) FROM 테이블명의 형태로 사용

• 수치형에만 사용 가능
• NULL값은 무시함

AVG로 평균내기

AVG : 집합의 평균을 구함
SELECT AVG(열명) FROM 테이블명의 형태로 사용

• SUM()/COUNT()와 동일한 결과
• 수치형만 가능하며, NULL값을 제거한 뒤에 평균값을 계산함
• NULL값을 0으로 간주하고싶다면 CASE를 사용해 NULL을 0으로 변환한 뒤에 계산

MIN/MAX로 최솟값/최댓값 구하기

SELECT MIN/MAX(열명) FROM 테이블명의 형태로 사용
수치형 뿐만 아니라 문자열형 및 날짜시간형에도 사용할 수 있음
단, 다른 집계함수들과 동일하게 NULL값은 무시함

---

22. 그룹화 - GROUP BY

GROUP BY구를 사용해 집계함수로 넘겨줄 집합을 그룹으로 나눌 수 있음

GROUP BY로 그룹화

SELECT 열명 FROM 테이블명 GROUP BY 열명;의 형식으로 사용

• 지정된 열의 값이 같은 행이 하나의 그룹으로 묶임
• DISTINCT와 같이 중복을 제거하는 효과가 있으나, 그룹화된 각각의 그룹이 하나의 집합으로서 집계함수의 인수로 넘겨지기 때문에 집계함수와 함께 사용하는 것이 아니면 의미가 없음
• 그룹화할 열을 복수로 지정하는 것도 가능함

집계함수와 결합할 경우 SELECT 열명, COUNT(열명), SUM(열명), FROM 테이블명 GROUP BY 열명;의 형식으로 사용함

HAVING 구로 조건 지정

WHERE구의 처리 순서가 집계함수의 순서보다 앞서기 때문에 집계함수는 WHERE구의 조건식에서 사용할 수 없음
따라서 HAVING구를 사용하여 집계함수에서의 조건식을 지정함

• SELECT 열명, COUNT(열명) FROM 테이블명 GROUP BY 열명 HAVING COUNT(열명) = 1;의 형태로 사용
• GROUP BY구의 뒤에 기술하여 WHERE구와 동일하게 조건식을 지정함

WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY 순으로 내부처리가 이루어짐

• ORDER BY구에서는 집계함수를 사용할 수 있음
• HAVING구는 SELECT구보다 먼저 처리되므로 별명은 사용할 수 없음
• 단, MySQL과 같이 별명을 융통성 있게 사용할 수 있는 데이터베이스 제품도 있음

복수열의 그룹화

GROUP BY에 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT 구에 기술해서는 안됨

• 즉, SELECT 열1, 열2, 열3 FROM 테이블명 GROUP BY 열1;에서 열2와 열3은 GROUP BY로 지정되지 않았으므로 에러가 발생함
• 이는 그룹마다 하나의 행만을 반환해야하는 GROUP BY의 특성 때문임
• 집계함수를 사용한다면 집합당 하나의 값이 도출되므로 문제없이 실행할 수 있음

결과값 정렬

GROUP BY로 그룹화를 해도 실행결과의 순서를 정렬할 수는 없음
따라서 ORDER BY구를 사용하여 정렬해야함

---

23. 서브쿼리

서브쿼리 : SELECT 명령에 의한 데이터 질의, 상부가 아닌 하부의 부수적인 질의를 의미

• SQL 명령문 안에 지정하는 하부 SELECT 명령
• 괄호로 묶어 지정함
• SQL 명령의 WHERE 구에서 주로 사용됨

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

SELECT MIN(a) FROM 테이블명;과 DELETE FROM 테이블명 WHERE a=20;을 결합
DELETE FROM 테이블명 WHERE a = (SELECT MIN(a) FROM 테이블명);의 방식으로 사용

• 괄호로 둘러싸인 서브쿼리 부분이 먼저 실행된 후 DELETE 명령이 실행됨
• 단, 데이터를 추가하거나 갱신할 경우 동일한 테이블을 서브쿼리에서 사용할 수 없으며, 이 경우 인라인 뷰로 임시 테이블을 만들어 처리할 수 있음

mysql 클라이언트에 한해 set @변수명으로 클라이언트 변수를 설정할 수 있음

스칼라 값

일반적인 서브쿼리의 패턴에는 네종류가 있음

• 하나의 값을 반환하는 패턴
  • SELECT MIN(열명) FROM 테이블명;
• 복수의 행이 반환되지만 열은 하나인 패턴
  • SELECT 열명 FROM 테이블명;
• 하나의 행이 반환되지만 열이 복수인 패턴
  • SELECT MIN(열명),MAX(열명) FROM 테이블명;
• 복수의 행, 복수의 열이 반환되는 패턴
  • SELECT 열1,열2 FROM 테이블명;

이중 하나의 값을 반환하는 패턴의 경우 이때의 값은 단일 값 혹은 스칼라 값이라고 불림

• SELECT 구에서 하나의 열을 지정하고 그룹화를 하지 않은 경우
• WHERE 조건으로 하나의 행만 검색한 경우
• 스칼라 값을 반환하는 서브쿼리는 =연산자로 비교할 수 있음

스칼라값을 반환하는 서브쿼리를 스칼라 서브쿼리라고 부르며, 스칼라 서브쿼리는 WHERE구에 사용할 수 있기 때문에 집계함수를 사용해 집계한 결과를 조건식으로 사용할 수 있음

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

서브쿼리는 WHERE구 뿐만 아니라 SELECT, UPDATE 등 다양한 구 안에서 지정할 수 없음
서브쿼리는 문법적으로 하나의 항목으로 취급되며, 문법적으로 사용에 문제가 없으나 실행시 에러가 발생하는 경우가 종종 생김
이는 스칼라 값의 반환 여부에 따른 것으로 서브쿼리 사용시 스칼라 서브쿼리인지 확인이 필요함

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

SELECT
    (SELECT COUNT(*) FROM 테이블1) AS 별명1,
    (SELECT COUNT(*) FROM 테이블2) AS 별명2;

MySQL 등에서는 FROM구를 생략할 수 있음
단, Oracle 등 전통적인 데이터베이스 제품에서는 생략할 수 없음

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

UPDATE의 SET구에서도 서브쿼리를 사용할 수 있으며, 이 때의 서브쿼리는 스칼라 서브쿼리여야 함
UPDATE 테이블명 SET 열명=(SELECT MAX(열명) FROM 테이블명);의 형식으로 사용

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

FROM 구에서도 서브쿼리를 사용할 수 있으며, 이 때의 서브쿼리는 스칼라 값이 아니어도 가능함SELECT * FROM (SELECT * FROM 테이블명) AS 별명;`의 형식으로 사용

• SELECT 명령이 중첩되어있으므로 네스티드(nested) 구조 또는 중첩구조 혹은 내포구조라고 부름
• FROM구에서는 테이블이나 서브쿼리에 별명을 붙일 수 있음
• 별명을 붙일 때는 AS를 사용하지만, Oracle의 경우에는 붙이지 않음

SELECT * FROM(
        SELECT * FROM 테이블명 ORDER BY 열명 DESC
) 별명
WHERE ROWNUM <= 2;

Oracle에는 LIMIT구가 없기 때문에 ROWNUM으로 행 개수 제한 후 정렬하여 추출하는 조건은 붙일 수 없음
이 때 FROM구에서 서브쿼리를 사용하는 것으로 정렬 후의 행 제한을 할 수 있음

INSERT 명령과 서브쿼리

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

INSERT INTO 테이블1 VALUES(
    (SELECT COUNT(*) FROM 테이블2),
    (SELECT COUNT(*) FROM 테이블3)
);
SELECT * FROM 테이블1;

VALUES구의 일부로 서브쿼리를 사용할 경우 서브쿼리는 스칼라 서브쿼리여야하며, 자료형도 일치해야함

INSERT INTO 테이블1 SELECT 값1, 값2;
SELECT * FROM 테이블1;

VALUES구 대신 SELECT명령을 사용할 수도 있음
이를 INSERT SELECT라고 부르며, 위의 명령은 INSERT INTO 테이블1 VALUES (값1, 값2)와 같음

• 이 때 SELECT의 반환값은 스칼라 값이 아니어도 되지만 반환하는 열 수와 자료형은 INSERT할 테이블과 일치해야함
• INSERT SELECT명령은 SELECT 명령의 결과를 INSERT INTO로 지정한 테이블에 전부 추가함
• INSERT INTO 테이블1 SELECT * FROM 테이블2로 열 구성이 똑같은 테이블 사이의 행을 복사할 수도 있음

---

24. 상관 서브쿼리

EXISTS

EXISTS : 서브쿼리를 사용할 때 데이터가 존재하는지 아닌지 판별하기 위한 조건을 지정하기 위한 술어

UPDATE 테이블1 SET 열1='있음'WHERE
    EXISTS (SELECT * FROM 테이블2 WHERE 열2=값);

특정 테이블의 특정 열에 있는 값이 다른 테이블에 있는지 판별하는 경우 EXISTS 술어에 서브쿼리를 지정하여 참 거짓을 반환하여 판별할 수 있음

NOT EXISTS

UPDATE 테이블1 SET 열1='없음'WHERE
    NOT EXISTS (SELECT * FROM 테이블2 WHERE 열2=값);

NOT EXISTS는 EXISTS의 반대로 값을 부정할 때 사용 가능함

상관 서브쿼리

상관 서브쿼리 : 부모 명령과 자식인 서브쿼리가 특정한 관계를 맺는 것

• 상관 서브쿼리는 부모 명령과 연관되어 처리되기 때문에 서브쿼리 부분을 따로 실행할 수 없음

서로 다른 테이블에서 같은 이름의 열을 처리해야할 경우, 열 앞에 테이블명.을 붙여 어느 테이블의 열인지를 지정하여 문제를 해결함

IN

스칼라 값을 비교할때는 =을 사용
집합 안의 값이 존재하는지를 비교할 때는 IN을 사용함

• 열명 IN(집합)의 형태로 사용
• 왼쪽에 지정된 값과 같은 값이 오른쪽의 집합 안에 존재하면 참을 반환
• 집합에 해당하는 부분을 서브쿼리로도 지정할 수 있으며, 이때의 서브쿼리는 스칼라 서브쿼리일 필요는 없음
• NOT IN으로 지정하여 반대의 값을 구할수도 있음
• IN은 집합 안의 NULL을 무시하지 않지만, NULL=NULL은 계산할 수 없으므로 비ㄱ는 할 수 없기 때문에 IS NULL을 사용해야함