관계형 데이터베이스 실전 입문 - 08. SELECT를 공략하자
March 22, 2021
1. SELECT는 SQL의 심장부
SELECT의 본질
SELECT의 강점
SQL 안티 패턴(책 참고)
SQL 그 자체다. SELECT의 파서를 개발하면 RDB 자체를 개발하게 된다.
데이터를 얻을 수 있는 유일한 수단
데이터를 가져오는 것은 SELECT에 전부 의지하고 있다.
관계형 모델에서는 릴레이션이 연산의 단위이며 한 개 또는 여러 개의 릴레이션을 조합해 연산을 수행한다. 그 결과 릴레이션을 가지게 된다.
SQL에서 릴레이션에 대응하는 것은 테이블이다. 그 테이블에서 데이터를 참조할 수 있는 것은 SELECT뿐이다. 즉, RDB는 릴레이션의 연산에 해당하는 작업을 모두 SELECT로 수행한다. SELECT는 이를 처리하기 위해 방대한 로직을 가진 만능의 API다.
SELECT의 기본구조
SELECT 컬럼의 목록
FROM 테이블의 목록
WHERE 검색 조건컬럼의 목록은 Projection, 테이블의 목록은 Product, 검색의 조건은 Restrict(제한)에 해당하는 작업이다.
SELECT는 세 개의 릴레이션 연산을 동시에 수행하게 되어 있다. 논리적 평가 순서는 다음과 같다.
- 테이블의 목록
- 검색 조건
- 컬럼의 목록
실행 순서를 의미하는 것이 아닌 논리적인 의미이며 실제로 어떤 순서로 실행되는가에 대한 물리적 의미는 없다.
일반적으로 SELECT의 실행 계획이나 인덱스를 추가하는 법에 대해 생각할 때, SELECT의 이런 점을 잊기 쉽다.
2. SELECT의 다양한 모습
SELECT는 데이터를 참조하는 데 필요한 모든 것을 가진 매우 강력한 API다. 강력하지만 생각지 못한 함정도 많이 숨어 있다. 모든 것을 SELECT에 담았기 때문에 생기는 장단점이라고 할 수 있다.
기본형이 아닌 다양한 종류의 SELECT의 놀라운 유연성을 확인해보자.
집계함수
함수의 유무만으로 의미가 바뀐다.
SELECT에 컬럼의 목록 중에 집계함수가 포함돼 있으면 SELECT의 결과 전체가 집계결과가 된다.
다음은 어떤 학교에서 관계형 모델을 전공하는 학생의 이름과 전공을 콜론(:)으로 구분하고 한 개의 문자열로 출력하는 쿼리다.
# 콜론으로 구분하여 한 개의 문자열로 출력된다.
SELECT CONCAT(name, ':', department)
FROM students
WHERE department = '관계형 모델';쿼리에 집계함수가 포함하면 다음과 같다.
SELECT COUNT(*)
FROM students
WHERE department = '관계형 모델';앞의 쿼리에서 결과 집합에 포함된 행은 학생의 수만 존재한다. COUNT()를 사용한 쿼리에서는 결과 집합에 포함된 행이 한 개가 된다.
이처럼 집계함수의 유무만으로 결과 행의 의미에 큰 변화가 생긴다. 게다가 품질이 나빠지는 원인으로 함수의 종류가 결과에도 영향을 미친다.
COUNT의 특수성
WHERE 절의 조건에 일치하는 행이 없다면, 즉 공집합에 대한 집계를 수행한 겨로가가 사실은 COUNT와 그 이외의 집계함수가 다르다.
COUNT()는 일치하는 행이 존재하지 않으면 결과는 0이 된다. 예를 보자. 다음의 예시는 ‘5학년의 평균 나이를 구해라’라는 쿼리이다. 이 결과는 대학은 4학년까지 있으므로 5학년은 존재하지 않을 것이다.
SELECT AVG(age)
FROM students
WHERE grade = 5;AVG(age)의 값은 NULL이 된다. COUNT()만 공집합은 평가한 결과가 0이 된다라는 동작을 한다.
즉, COUNT()는 0, 그 외의 집계함수는 NULL이 된다.
GROUP BY를 이용한 집계의 서식
GROUP BY 절이 없다면 집계함수는 테이블 전체의 데이터를 대상으로 집계되지만 어떤 특정 항목별로 집계하고 싶다면 GROUP BY 절을 사용해야 한다.
GROUP BY 절을 사용하면 ‘그 SELECT가 집계를 나타내는 것이다’ 라는 사실을 쉽게 알 수 있다.
SELECT department, COUNT(*)
FROM studenets
GROUP BY department다음 예시는 소속 인원이 30명 이하인 학과의 목록이다. 이 때 사용하는 것은 HAVING 절이다.
SELECT department, COUNT(*)
FROM students
GROUP BY department
HAVING COUNT(*) <= 30;HAVING절과 WHERE 절의 차이점은 무엇일까?
다음 예시는 1~2학년에 속하는 인원수가 30명 이하인 학과의 목록을 구하는 쿼리다.
SELECT department, COUNT(*)
FROM students
WHERE grade IN (1, 2)
GROUP BY department
HAVING COUNT(*) <= 30;WHERE 절은 집계의 대상이 되는 행의 조건을 지정한다.
→ WHERE 절은 집계 전의 원래의 행에 사용되는 조건을 지정한다.
반면 HAVING 절은 집계결과에 대해서 조건을 지정한다. WHERE 절에는 집계결과에 대한 조건을 지정할 수 없다. 이 차이를 명확하게 구분할 것!
SQL 표준에는 HAVING 절에 조건을 지정할 수 있는 대상은 GROUP BY 절에 지정된 컬럼과 집계함수의 결과뿐이다. 제품에 따라서는 HAVING 절에 그 외에 다른 컬럼을 지정할 수도 있지만, 호환성이 높은 SQL을 작성하려면 그 방법은 피하는게 좋다.
또 다른 GROUP BY의 까다로운 점은 WHERE 절의 조건에 해당하는 행이 없을 때 그 항목에 관해서는 결과가 표시되지 않는 점이다.
만약 1, 2학년의 인원수가 0일 때는 그 학과는 쿼리의 결과에 포함되지 않는다. 0은 30이하니까 쿼리의 목적을 생각하면 당연히 결과에 포함될거라 생각하지만 GROUP BY로는 해결할 수 없다.
이 때는 행이 없는 항목에 관해서 집계를 수행하는 방법으로 상관 서브쿼리를 사용하는 것이다.
SELECT department, (
SELECT COUNT(*)
FROM student
WHERE department = t1.department
AND grade IN (1, 2)
) AS COUNT
FROM (
SELECT DISTINCT department # 학과 테이블이 있다면 학과 테이블을 사용하는 것이 좋다.
FROM students) t1
WHERE COUNT <= 30;이 쿼리는 1, 2학년생이 없는 학과도 표시될 것이다.
WHERE 절의 검색 조건에 일치하지 않는 행에 대한 집계(즉 0)도 필요할 때는 GROUP BY를 사용할 수 없다는 점이 까다롭다.
또 다른 예시로 소속 학생이 30명 이하인 학과 중에서 특히 인원이 적은 학과가 어떤 과인지 알고 싶을 때 결과를 정렬하고 싶다로 한다면
SELECT department, COUNT(*)
FROM students
GROUP BY department
HAVING COUNT(*) <= 30
ORDER BY COUNT(*) ASC;여기서 ORDER BY 절의 기본 정렬은 오름차순이므로 ASC는 생략이 가능하다.
GROUP BY, HAVING, ORDER BY 순서로 써야 한다. 즉 이 세 개의 절이 나타내는 의미는
‘GROUP BY 절에 지정된 컬럼의 값별로 집계하고 그 결과를 HAVING 절의 조건으로 필터링한 다음에 ORDER BY 절의 조건으로 정렬한다’ 라는 의미다.
서브쿼리
SELECT의 의미가 바뀐다는 점에서 서브쿼리(내부쿼리)가 오른쪽에 위치하는 것은 아니다.
서브쿼리가 까다로운 점은 외형은 모두 SELECT라는 점이다. 외형은 같지만 서브쿼리의 결과는 스칼라, 행, 테이블과 같은 형태로 자유롭게 변화할 수 있다. 어떤 유형의 서브쿼리가 될지는 서브쿼리의 결과에 포함된 열의 수, 행의 수에 따라 결정된다.
→ 어떤 문맥으로 서브쿼리가 사용되는지에 따라서 의미가 달라진다.
테이블 서브쿼리
테이블 서브쿼리는 서브쿼리의 결과가 테이블 형태이며 세 개의 종류가 있다.
하나는 IN, ANY(SOME), ALL 구에 따라서 사용된다. IN 절 등은 대부분 서브쿼리의 결과가 한 열이 되는 경우가 많지만, 여러 컬럼을 한 번에 비교할 수도 있다.
→ 서브쿼리의 결과는 그 구조 떄문에 테이블이 된다.
SELECT COUNT(*)
FROM course_registration
WHERE (department, course) IN (
SELECT department, course
FROM course
WHERE minimum_grade >= 2);다른 하나의 테이블 서브쿼리는 일명 FROM 절의 서브쿼리(Subcuqery in the FROM Clause) 유형이다.
서브쿼리의 결과를 FROM 절에서 일반 테이블처럼 다루고 SELECT에 따라서 추가 연산을 수행하거나 다른 테이블과 JOIN 하는 식으로 사용한다. 일반적으로 테이블 서브쿼리라고 하면 이것을 의미한다.
예시: 학과별 소속된 학생의 평균 인원수
SELECT AVG(c)
FROM (
SELECT COUNT(*) AS c
FROM students
GROUP BY department);집계결과에 관해 또 다른 집계를 할 때는 이처럼 FROM 절의 서브쿼리를 사용하는 것이 일반적이다.
EXISTS 서브쿼리는 IN, ANY, ALL 등과 같은 용도로 사용되지만, 평가되는 것은 서브쿼리를 평가한 결과, 행이 한 개라도 존재하는지 아닌지다. 서브쿼리의 결과가 1행 이상 존재한다면 EXSITS는 참이다.
서브쿼리가 반환한 결과의 내용에 대해서는 아무런 문제도 없지만, 서브쿼리의 결과에 행과 컬럼이 몇 개 포함돼 있을 수 있으므로 구조상 테이블이 된다. WHERE 절에서 사용되는 경우가 많지만, select list나 HAVING 절에서도 사용할 수 있다.
예시: 수업을 이수하지 못한 학생
SELECT name, department
FROM students
WHERE NOT EXISTS (
SELECT *
FROM course_registration
WHERE student_name = students.name);스칼라 서브쿼리
스칼라 서브쿼리는 서브쿼리의 결과가 스칼라(1행1열)여야 한다. 스칼라가 아닌 결과가 반환된다면 서브쿼리에 오류가 발생한다. 스칼라 서브쿼리는 스칼라값이 나오는 다양한 곳에서 사용할 수 있다.
예를 들어 스칼라 서브쿼리는 WHERE 절이나 HAVING 절 등에서 스칼라값과 비교하거나 select list에서 스칼라 값을 구하는 등의 목적으로 사용한다.
# WHERE 절의 서브쿼리
SELECT name, age
FROM student AS s1
WHERE age = (
SELECT max(age)
FROM student AS s2);
# HAVING 절의 서브쿼리
SELECT course, COUNT(*) AS COUNT
FROM course_registration
GROUP BY course
HAVING COUNT(*) > (
SELECT AVG(c)
FROM (
SELECT COUNT(*) AS c
FROM course_registration
GROUP BY course) AS t);
# select list 내의 서브쿼리
SELECT(
SELECT AVG(age) FROM student AS s
WHERE s.department = d.department) AS age
FROM department AS d;행 서브커리
행 서브쿼리는 서브쿼리를 평가한 결과가 1행이고 열이 여러 개일 때다. 스칼라 서브쿼리와 비슷하지만, 값이 여러개로 반환되는 점이 다르다.
예를 들어 WHERE 절에서는 여러 개의 컬럼을 괄호로 묶어서 (col1, col2) = (val1, val2) 와 같이 비교가 가능.이러한 비교 구문에 행 서브쿼리를 사용할 수 있다. 사람 이름을 나타내는 컬럼이 firstname과 lastname으로 나뉘어 있을 때 사용하면 편하다.
그러나 select list 내에 행 서브쿼리를 사용할 수 없다. select list 내에는 각 값이 스칼라여야 한다.
스칼라 서브쿼리, 행 서브쿼리 양쪽 모두 서브쿼리의 평가 결과가 여러 행이면 오류가 발생한다.
→ 스칼라 및 행 서브쿼리는 반드시 1행만 반환된다.
또한, 결과가 없으면 NULL로 처리된다. 스칼라 서브쿼리는 COALESCE 함수로 대응할 수 있으나, 행 서브쿼리는 사용하지 못하기 때문에 NULL이 될 가능성이 있다.
뷰
뷰를 사용하는 목적의 하나는 복잡성을 숨기기 위해서다. 관계형 모델은 뷰와 일반 테이블(기본 테이블)을 구별하지 않는다. 양쪽 모두 릴레이션을 나타내는 것이며 같은 릴레이션을 연산의 대상으로 다루므로 복잡한 쿼리를 뷰로 정의하면 뷰에 대한 쿼리를 깔끔하게 표현할 수 있다.
쿼리의 외형이 깔끔하다는 것은 그 쿼리가 무엇을 의미하는지 알기 쉽다. 그러나 실제로 백그라운드에서 어떤 처리가 이뤄지는지 보이지 않는다. 특히 뷰에 있는 서브쿼리나 UNION, 집계함수 등이 포함됐을 때는 성능 문제가 발생할 수 있으므로 주의해야 한다.
복잡한 뷰가 필요한 것은 DB 설계가 잘되어있지 않는다는 증거일수도 있다.
→ 테이블 정규화 or 통합을 해야하는지 DB 설계를 검토해야 한다.
UNION
SQL은 그 사양 상 결과 집합에 포함된 컬럼 수가 같다면 두 개의 SELECT를 UNION으로 더할 수 있다.
그러나 UNION으로 서로 더한 두 개의 SELECT는 다른 테이블을 참고 하고 있거나 전혀 다른 실행 계획이 있다는 점이다. 두 개의 SELECT에 공통점은 출력 형태만 비슷하다는 것이다. SELECT의 내용은 비슷하지 않을 수도 있다.
3. 관계형이 아닌 조작
SELECT를 깊이 이해하기 위해 SELECT처리가 관계형인가 아닌가를 살펴야 한다.
SELECT의 기본형은 곱집합(Product), 제한(Restrict), 사영(Projection)이라는 릴레이션 연산이 성립하지만, SELECT는 관계형 모델에 법칙을 따르지 않는 다양한 조작도 지원하고 있다.
관계형 조작의 복습
대부분 작업은 SELECT의 기본형으로 대응할 수 있다.
사실은 IN, ANY, EXSITS 서브쿼리는 JOIN과 DISTINCT를 사용해 바꿀 수 있다. 그 외에 서브쿼리는 SELECT로 표현력을 비약적으로 높이는 효과가 있다. FROM 절의 서브쿼리가 집약된 경우처럼 서브쿼리는 반드시 릴레이션 연산에 대응하는 것은 아니다.
정렬(sort)
ORDER BY 절을 이용한 집합의 정렬은 관계형 모델상의 연산은 없다.
관계형 모델은 집합 논리를 바탕으로 하는 모델이며 릴레이션은 집합이다. 중요한 점은 수학적으로 집합의 각 요소에 순서는 없다. → 행을 정렬해 순열을 붙이는 것은 집합이 아니다.
SQL에서 ORDER BY는 SELECT 자신이 아닌 커서의 조작이라고 되어 있어 까다롭다.
RDB에서 임의의 행을 정렬할 수 있으므로, 정렬을 RDB에 맡겨 개발 효율을 크게 높일 수 있다.
→ 관계형 모델에서 벗어나는 작업은 위험 요소가 있고, 다루는데 있어 주의가 필요하다.
명시적으로 정의되지 않은 컬럼
RDB 제품에 따라 ROWID, ROWNUM과 같은 컬럼을 사용할 수 있으나, 이런 컬럼을 사용하면 관계형 모델에서 벗어나게 된다. 릴레이션에서 튜플 사이에 순서가 없기 때문이다.
스토어드 함수(사용자 정의 함수)
임의의 조작을 값으로 SQL에 저장하는 스토어드 함수는 매우 유연해 보이지만 스토어드 함수의 로직은 절차적으로 작석된 것에 따라서 문제가 발생한다.
SELECT가 스토어드 함수에 포함돼 있으면 절차형으로 처리한다. 이와 같은 경우 옵티마이저는 스토어드 함수의 실행에 드는 비용을 예측할 수 없고 최적화할 수 없다.
→ 스토어드 함수가 포함된 쿼리는 실행 코스트가 높아지기 쉽다.
스토어드 프로시저로 로직을 구현하는 것도 해서는 안 된다. 스토어드 함수, 프로시저는 절차형 프로그래밍 언어의 작성 형식이다.
SQL은 선언형 프로그래밍 언어다. → 절차형 프로그래밍 언어에서 쓰는 루프를 집어 넣으면 파괴되기 쉽다.
관계형이 아닌 조작의 취급법
SELECT는 관계형인 조작과 관계형이 아닌 조작의 복합체다. SQL이 관계형 모델에 완전히 충실하다면 문제는 간단하나 사실은 그렇지 않다.
여기서 중요한 점은 관계형 조작과 그렇지 않은 조작을 명확하게 구별하는 것이다.
구별한 다음 지침에 따라 구축하자.
- 관계형 모델의 범위에서 가능한 것은 절대로 관계형이 아닌 조작으로 구현하지 않는다.
- 관계형 모델의 범위에서 작성할 수 없다면 DB 설계를 검토한다.
- 관계형 모델이 아닌 조작이 꼭 필요할 때는 관계형 조작에 대한 로직을 반드시 먼저 실시한다.
가능한 한 관계형 모델의 범위에서 처리를 작성하거나 관계형 모델에 따라서 먼저 처리되게 하는 것이 매우 중요하다. 옵티마이저에 의한 최적화는 관계형 모델의 범위에서 가장 큰 위력을 발휘하기 때문이다.
옵티마이저의 작업은 원래의 쿼리와 같은 결과를 얻을 수 있는 쿼리 중에서 최적의 실행 계획을 세운 것 또는 가장 실행 시간이 짧다고 생각되는 것을 선택한다.
릴레이션은 집합의 한 종류이므로 관계형 모델의 범위 내에서라면 옵티마이저는 집합의 연산과 같은 각종 법칙을 적용할 수 있다. ex) 교환, 결합, 분배, 드 모르간의 법칙 등등
그런데 릴레이션이 아닌 연산의 요소가 쿼리에 포함돼 있으면 집합의 연산으로 재작성할 수 없다. 그 결과 옵티마이저가 선택할 수 있는 실행 계획은 제한되며 효율적인 실행 계획을 선택할 기회가 줄어든다.
4. 들여쓰기로 SELECT 문장을 읽기 쉽게
SELECT는 매우 유연하므로 복잡한 SELECT의 구조를 파악하기는 굉장히 어려운 일이다. 이 같은 경우에 효과적으로 SELECT의 구조를 파악할 수 있게 추천하는 방법이 적절하게 들여쓰기를 하는 것이다.
들여쓰기 규칙
- SELECT 절은 들여쓰기하지 않는다.
- UNION 절은 그다음 SELECT와 함께 작성한다.
- 컬럼은 한 줄마다 작성한다.
- 컬럼의 리스트는 네 개의 공백으로 들여쓰기 한다.
- FROM 절, WHERE 절은 두 개의 공백으로 들여쓰기한다.
- FROM 절의 테이블 목록, WHERE 절의 검색 조건 목록은 한 줄씩 작성한다.
- 서브쿼리의 괄호는 각각 한줄로 작성한다.
제일 간단한 건 편집도구를 활용하여 자동 들여쓰기를 하면 된다.
요약
SELECT는 만능 도구다. SELECT는 만능이라서 얼마든지 복잡한 로직을 넣어 작성할 수 있다. 관계형 모델을 뛰어넘을 수 있으며 SELECT의 처리를 어렵게 할 수도 있다.
