서브쿼리
서브쿼리
서브쿼리는 더 큰 외부 쿼리의 일부로 나타나는 괄호로 묶인 쿼리 표현식이에요. 보통 SELECT ... FROM에 기반하지만, DuckDB에서는 PIVOT 같은 다른 쿼리 구성도 서브쿼리로 나타날 수 있어요. 함께 살펴볼게요.
출처: 문서
본문
스칼라 서브쿼리
스칼라 서브쿼리는 단일 값을 반환하는 서브쿼리예요. 표현식을 쓸 수 있는 어디에서나 사용할 수 있어요. 스칼라 서브쿼리가 단일 값보다 많은 값을 반환하면 오류가 발생해요 (scalar_subquery_error_on_multiple_rows를 false로 설정하면 행이 임의로 선택되는데, 그 경우는 제외).
다음 테이블을 고려해 봐요.
성적 (Grades)
| grade | course |
|---|---|
| 7 | Math |
| 9 | Math |
| 8 | CS |
CREATE TABLE grades (grade INTEGER, course VARCHAR);
INSERT INTO grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');
다음 쿼리로 최소 성적을 얻을 수 있어요.
SELECT min(grade) FROM grades;
| min(grade) |
|---|
| 7 |
WHERE 절에서 스칼라 서브쿼리를 사용하면, 이 성적이 어느 과목에서 얻어졌는지 알 수 있어요.
SELECT course FROM grades WHERE grade = (SELECT min(grade) FROM grades);
| course |
|---|
| Math |
ARRAY 서브쿼리
여러 값을 반환하는 서브쿼리는 ARRAY로 감싸 모든 결과를 리스트로 모을 수 있어요.
SELECT ARRAY(SELECT grade FROM grades) AS all_grades;
| all_grades |
|---|
| [7, 9, 8] |
서브쿼리 비교: ALL, ANY, SOME
스칼라 서브쿼리 섹션에서 스칼라 표현식을 등호 비교 연산자(=)로 서브쿼리와 직접 비교했어요. 이런 직접 비교는 스칼라 서브쿼리에서만 의미가 있어요.
스칼라 표현식은 여전히 **한정자(quantifier)**를 지정함으로써 여러 행을 반환하는 단일 컬럼 서브쿼리와 비교할 수 있어요. 사용 가능한 한정자는 ALL, ANY, SOME이며, ANY와 SOME은 동등해요.
ALL
ALL 한정자는 비교 연산자 왼쪽 표현식 과 오른쪽 서브쿼리 의 각 값들의 개별 비교 결과가 모두 true로 평가될 때 전체 비교가 true로 평가되도록 지정해요.
SELECT 6 <= ALL (SELECT grade FROM grades) AS adequate;
반환:
| adequate |
|---|
| true |
6이 서브쿼리 결과 7, 8, 9 각각보다 작거나 같기 때문이에요.
그러나 다음 쿼리는
SELECT 8 >= ALL (SELECT grade FROM grades) AS excellent;
반환:
| excellent |
|---|
| false |
8이 서브쿼리 결과 9보다 크거나 같지 않기 때문이에요. 따라서 모든 비교가 true로 평가되지 않으므로, >= ALL은 전체로 false로 평가돼요.
ANY
ANY 한정자는 개별 비교 결과 중 적어도 하나가 true로 평가될 때 전체 비교가 true로 평가되도록 지정해요.
예를 들어:
SELECT 5 >= ANY (SELECT grade FROM grades) AS fail;
반환:
| fail |
|---|
| false |
서브쿼리의 어떤 결과도 5보다 작거나 같지 않기 때문이에요.
ANY 대신 SOME 한정자를 쓸 수 있어요. ANY와 SOME은 서로 바꿔 쓸 수 있어요.
EXISTS
EXISTS 연산자는 서브쿼리 안에 어떤 행이 존재하는지 테스트해요. 서브쿼리가 하나 이상의 레코드를 반환하면 true를, 그렇지 않으면 false를 반환해요. EXISTS 연산자는 일반적으로 세미조인 연산을 표현하는 상관 서브쿼리로 가장 유용해요. 하지만 비상관 서브쿼리로도 쓸 수 있어요.
예를 들어, 특정 과목에 성적이 있는지 알아내는 데 쓸 수 있어요.
SELECT EXISTS (FROM grades WHERE course = 'Math') AS math_grades_present;
| math_grades_present |
|---|
| true |
SELECT EXISTS (FROM grades WHERE course = 'History') AS history_grades_present;
| history_grades_present |
|---|
| false |
위 예시의 서브쿼리는 DuckDB의
FROM-우선 문법 덕분에SELECT *를 생략할 수 있다는 점을 활용해요. 다른 SQL 시스템에서는 서브쿼리에SELECT절이 필요하지만,EXISTS와NOT EXISTS서브쿼리에서는 어떤 목적도 수행할 수 없어요.
NOT EXISTS
NOT EXISTS 연산자는 서브쿼리 안에 어떤 행도 없음을 테스트해요. 서브쿼리가 빈 결과를 반환하면 true를, 그렇지 않으면 false를 반환해요. NOT EXISTS 연산자는 일반적으로 안티조인 연산을 표현하는 상관 서브쿼리로 가장 유용해요. 예를 들어 관심사가 없는 Person 노드를 찾으려면:
CREATE TABLE Person (id BIGINT, name VARCHAR);
CREATE TABLE interest (PersonId BIGINT, topic VARCHAR);
INSERT INTO Person VALUES (1, 'Jane'), (2, 'Joe');
INSERT INTO interest VALUES (2, 'Music');
SELECT *
FROM Person
WHERE NOT EXISTS (FROM interest WHERE interest.PersonId = Person.id);
| id | name |
|---|---|
| 1 | Jane |
DuckDB는
NOT EXISTS쿼리가 안티조인 연산을 표현한다는 것을 자동으로 감지해요. 이런 쿼리를LEFT OUTER JOIN ... WHERE ... IS NULL로 수동으로 다시 쓸 필요가 없어요.
IN 연산자
IN 연산자는 왼쪽 표현식이 오른쪽(RHS)의 서브쿼리 결과 또는 표현식 집합 안에 포함되는지 확인해요. IN 연산자는 표현식이 RHS에 있으면 true를, 표현식이 RHS에 없고 RHS에 NULL 값이 없으면 false를, 표현식이 RHS에 없고 RHS에 NULL 값이 있으면 NULL을 반환해요.
EXISTS 연산자를 사용한 것과 비슷한 방식으로 IN 연산자를 쓸 수 있어요.
SELECT 'Math' IN (SELECT course FROM grades) AS math_grades_present;
| math_grades_present |
|---|
| true |
상관 서브쿼리
지금까지 제시된 모든 서브쿼리는 비상관(uncorrelated) 서브쿼리예요. 여기서 서브쿼리 자체는 완전히 자급자족적이며 부모 쿼리 없이 실행될 수 있어요. 상관(correlated) 서브쿼리라고 불리는 두 번째 유형의 서브쿼리도 있어요. 상관 서브쿼리에서는 서브쿼리가 부모 쿼리의 값을 사용합니다.
개념적으로 서브쿼리는 부모 쿼리의 모든 단일 행에 대해 한 번씩 실행돼요. 상관 서브쿼리를 소스 데이터셋의 모든 행에 적용되는 함수로 보는 것이 이해하기 쉬운 방법일 거예요.
예를 들어, 모든 과목의 최소 성적을 찾고 싶다고 해 봐요. 다음과 같이 할 수 있어요.
SELECT *
FROM grades grades_parent
WHERE grade =
(SELECT min(grade)
FROM grades
WHERE grades.course = grades_parent.course);
| grade | course |
|---|---|
| 7 | Math |
| 8 | CS |
이 서브쿼리는 부모 쿼리의 컬럼(grades_parent.course)을 사용해요. 개념적으로 서브쿼리를 상관 컬럼이 그 함수의 파라미터인 함수로 볼 수 있어요.
SELECT min(grade)
FROM grades
WHERE course = ?;
이제 이 함수를 각 행에 대해 실행하면, Math에 대해서는 7, CS에 대해서는 8이 반환되는 것을 볼 수 있어요. 그리고 이를 실제 행의 성적과 비교합니다. 결과적으로 (Math, 9) 행은 9 <> 7이므로 걸러져요.
서브쿼리의 각 행을 Struct로 반환하기
SELECT 절에서 서브쿼리 이름을 (특정 컬럼을 참조하지 않고) 사용하면 서브쿼리의 각 행이 struct로 바뀌어요. struct의 필드는 서브쿼리의 컬럼에 해당하죠. 예를 들어:
SELECT t
FROM (SELECT unnest(generate_series(41, 43)) AS x, 'hello' AS y) t;
| t |
|---|
| {'x': 41, 'y': hello} |
| {'x': 42, 'y': hello} |
| {'x': 43, 'y': hello} |
더 알아보기 (Learn more)
FROM-우선 문법은sql/query_syntax/from문서를 참고해 주세요.