하위 쿼리는 쿼리 언어 내의 다른 쿼리 내에 중첩된 쿼리입니다. 하위 쿼리를 내부 쿼리 또는 내부 SELECT쿼리라고도합니다. 하위 쿼리를 포함하는 문을 일반적으로 외부 쿼리라고 합니다.
하위 쿼리 유형
하위 쿼리에는 두 가지 주요 유형이 있습니다.
- 상관 관계: 외부 쿼리의 값을 참조하는 하위 쿼리입니다. 하위 쿼리는 외부 쿼리가 처리하는 각 행에 대해 한 번 평가됩니다.
- 비 상관 관계: 외부 쿼리와 독립적인 하위 쿼리입니다. 외부 쿼리에 의존하지 않고 자체적으로 실행할 수 있습니다.
하위 쿼리는 반환되는 행 및 열 수에 따라 추가로 분류할 수 있습니다. 다음의 세 가지 유형이 있습니다.
- 테이블: 여러 행과 여러 열을 반환합니다.
- 다중 값: 여러 행과 단일 열을 반환합니다.
- 스칼라: 단일 행과 단일 열을 반환합니다.
쿼리 언어의 쿼리는 항상 단일 열(단순 값 또는 복잡한 항목)을 반환합니다. 따라서 다중 값 및 스칼라 하위 쿼리만 적용할 수 있습니다. 절에서만 다중 값 하위 쿼리를 FROM 관계형 식으로 사용할 수 있습니다. 스칼라 하위 쿼리를 or 절의 스칼라 식 또는 WHERE 절의 SELECT 관계형 식 FROM 으로 사용할 수 있습니다.
다중 값 하위 쿼리
다중 값 하위 쿼리는 항목 집합을 반환하며 항상 절 내에서 FROM 사용됩니다. 다음 용도로 사용됩니다.
- 식
JOIN최적화(자체 조인). - 비용이 많이 드는 식을 한 번 평가하고 여러 번 참조합니다.
자체 조인 식 최적화
다중 값 하위 쿼리는 절의 모든 교차 조인 이후가 아니라 각 select-many 식 뒤에 조건자를 푸시하여 식을 최적화 JOIN 할 수 있습니다WHERE.
다음과 같은 쿼리를 고려해 보세요.
SELECT VALUE
COUNT(1)
FROM
products p
JOIN
t in p.tags
JOIN
s in p.sizes
JOIN
c in p.colors
WHERE
t.key IN ("fabric", "material") AND
s["order"] >= 3 AND
c LIKE "%gray%"
이 쿼리의 경우 인덱스는 태그가 *fabricmaterial3보다 크고 하나 이상의 색을 하위 문자열로 사용하여 하나 이상의 크기 order 로 태그 key 가 있는 항목과 gray 일치합니다. 이 식은 JOIN 필터가 적용되기 전에 일치하는 각 항목에 대한 모든 항목 및 colors 배열의 tagssizes교차 곱을 수행합니다.
WHERE 그런 다음 각 $<c, t, n, s>$ 튜플에 필터 조건자를 적용합니다. 예를 들어 일치하는 항목에 세 배열 각각에 10개의 항목이 있는 경우 다음 수식을 사용하여 1,000 개의 튜플로 확장됩니다.
$$1 x 10 x 10 x 10$$
여기에 하위 쿼리를 사용하면 다음 식으로 조인하기 전에 조인된 배열 항목을 필터링하는 데 도움이 될 수 있습니다.
이 쿼리는 이전 쿼리와 동일하지만 하위 쿼리를 사용합니다.
SELECT VALUE
COUNT(1)
FROM
products p
JOIN
(SELECT VALUE t FROM t IN p.tags WHERE t.key IN ("fabric", "material"))
JOIN
(SELECT VALUE s FROM s IN p.sizes WHERE s["order"] >= 3)
JOIN
(SELECT VALUE c FROM c in p.colors WHERE c LIKE "%gray%")
태그 배열의 항목 하나만 필터와 일치하며 수량 및 주식 배열에 대해 5개의 항목이 있다고 가정합니다. 그런 다음 식은 JOIN 첫 번째 쿼리에서 1,000개 항목이 아닌 이 수식을 사용하여 25개의 튜플로 확장됩니다.
$$1 x 1 x 5 x 5$$
한 번 평가 및 여러 번 참조
하위 쿼리는 UDF(사용자 정의 함수), 복잡한 문자열 또는 산술 식과 같은 비용이 많이 드는 식을 사용하여 쿼리를 최적화하는 데 도움이 될 수 있습니다. 식과 함께 하위 쿼리를 JOIN 사용하여 식을 한 번 계산하지만 여러 번 참조할 수 있습니다.
이 샘플 쿼리는 쿼리에서 25회% 배수로 가격을 계산합니다.
SELECT VALUE {
subtotal: p.price,
total: (p.price * 1.25)
}
FROM
products p
WHERE
(p.price * 1.25) < 22.25
계산을 한 번만 실행하는 동일한 쿼리는 다음과 같습니다.
SELECT VALUE {
subtotal: p.price,
total: totalPrice
}
FROM
products p
JOIN
(SELECT VALUE p.price * 1.25) totalPrice
WHERE
totalPrice < 22.25
[
{
"subtotal": 15,
"total": 18.75
},
{
"subtotal": 10,
"total": 12.5
},
...
]
팁 (조언)
식의 교차 제품 동작에 유의 JOIN 하세요. 식이 계산할 undefined수 있는 경우 값이 JOIN 아닌 하위 쿼리에서 개체를 직접 반환하여 식이 항상 단일 행을 생성하는지 확인해야 합니다.
외부 참조 데이터를 사용하여 관계형 조인 모방
측정 단위와 같이 거의 변경되지 않는 정적 데이터를 참조해야 하는 경우가 많습니다. 쿼리의 각 항목에 대해 정적 데이터를 복제하지 않는 것이 좋습니다. 이 중복을 방지하면 스토리지에 저장되고 개별 항목 크기를 더 작게 유지하여 쓰기 성능이 향상됩니다. 하위 쿼리를 사용하여 정적 참조 데이터 컬렉션을 사용하여 내부 조인 의미 체계를 모방할 수 있습니다.
예를 들어 의류의 길이를 나타내는 이 측정값 집합을 고려합니다.
| Size | Length | Units |
|---|---|---|
xs |
63.5 |
cm |
s |
64.5 |
cm |
m |
66.0 |
cm |
l |
67.5 |
cm |
xl |
69.0 |
cm |
xxl |
70.5 |
cm |
다음 쿼리는 출력에 단위의 이름을 추가하도록 이 데이터와의 조인을 모방합니다.
SELECT
p.name,
p.subCategory,
s.description AS size,
m.length,
m.unit
FROM
products p
JOIN
s IN p.sizes
JOIN m IN (
SELECT VALUE [
{size: 'xs', length: 63.5, unit: 'cm'},
{size: 's', length: 64.5, unit: 'cm'},
{size: 'm', length: 66, unit: 'cm'},
{size: 'l', length: 67.5, unit: 'cm'},
{size: 'xl', length: 69, unit: 'cm'},
{size: 'xxl', length: 70.5, unit: 'cm'}
]
)
WHERE
s.key = m.size
스칼라 하위 쿼리
스칼라 하위 쿼리 식은 단일 값으로 계산되는 하위 쿼리입니다. 스칼라 하위 쿼리 식의 값은 하위 쿼리의 프로젝션(SELECT 절) 값입니다. 스칼라 식이 유효한 여러 위치에서 스칼라 하위 쿼리 식을 사용할 수 있습니다. 예를 들어 두 절의 식에서 스칼라 하위 쿼리를 SELECTWHERE 사용할 수 있습니다.
스칼라 하위 쿼리를 사용하는 것이 쿼리를 최적화하는 데 항상 도움이 되는 것은 아닙니다. 예를 들어 스칼라 하위 쿼리를 시스템 또는 사용자 정의 함수에 인수로 전달하면 RU(리소스 단위) 사용량 또는 대기 시간을 줄일 수 없습니다.
스칼라 하위 쿼리는 다음과 같이 추가로 분류할 수 있습니다.
- 단순 식 스칼라 하위 쿼리
- 스칼라 하위 쿼리 집계
단순 식 스칼라 하위 쿼리
단순 식 스칼라 하위 쿼리는 집계 식을 포함하지 않는 절이 있는 SELECT 상관 관계가 있는 하위 쿼리입니다. 이러한 하위 쿼리는 컴파일러가 더 큰 하나의 단순 식으로 변환하기 때문에 최적화 이점을 제공하지 않습니다. 내부 쿼리와 외부 쿼리 사이에는 상관 관계가 있는 컨텍스트가 없습니다.
첫 번째 예제로 이 간단한 쿼리를 고려해 보세요.
SELECT
1 AS a,
2 AS b
단순 식 스칼라 하위 쿼리를 사용하여 이 쿼리를 다시 작성할 수 있습니다.
SELECT
(SELECT VALUE 1) AS a,
(SELECT VALUE 2) AS b
두 쿼리 모두 동일한 출력을 생성합니다.
[
{
"a": 1,
"b": 2
}
]
다음 예제 쿼리는 고유 식별자를 접두사로 단순 식 스칼라 하위 쿼리로 연결합니다.
SELECT
(SELECT VALUE CONCAT('ID-', p.id)) AS internalId
FROM
products p
이 예제에서는 단순 식 스칼라 하위 쿼리를 사용하여 각 항목에 대한 관련 필드만 반환합니다. 쿼리는 각 항목에 대해 무언가를 출력하지만 하위 쿼리 내의 필터를 충족하는 경우에만 프로젝션된 필드를 포함합니다.
SELECT
p.id,
(SELECT p.name WHERE CONTAINS(p.name, "Shoes")).name
FROM
products p
[
{
"id": "00000000-0000-0000-0000-000000004041",
"name": "Remdriel Shoes"
},
{
"id": "00000000-0000-0000-0000-000000004322"
},
{
"id": "00000000-0000-0000-0000-000000004055"
}
]
스칼라 하위 쿼리 집계
집계 스칼라 하위 쿼리는 단일 값으로 계산되는 프로젝션 또는 필터에 집계 함수가 있는 하위 쿼리입니다.
첫 번째 예제에서는 다음 필드가 있는 항목을 고려합니다.
[
{
"name": "Blators Snowboard Boots",
"colors": [
"turquoise",
"cobalt",
"jam",
"galliano",
"violet"
],
"sizes": [ ... ],
"tags": [ ... ]
}
]
다음은 프로젝션에 단일 집계 함수 식이 있는 하위 쿼리입니다. 이 쿼리는 각 항목에 대한 모든 태그를 계산합니다.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount
FROM
products p
WHERE
p.id = "00000000-0000-0000-0000-000000004389"
[
{
"name": "Blators Snowboard Boots",
"colorsCount": 5
}
]
필터가 있는 동일한 하위 쿼리는 다음과 같습니다.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount,
(SELECT VALUE COUNT(1) FROM c IN p.colors WHERE c LIKE "%t") AS colorsEndsWithTCount
FROM
products p
[
{
"name": "Blators Snowboard Boots",
"colorsCount": 5,
"colorsEndsWithTCount": 2
}
]
여러 집계 함수 식이 있는 또 다른 하위 쿼리는 다음과 같습니다.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount,
(SELECT VALUE COUNT(1) FROM s in p.sizes) AS sizesCount,
(SELECT VALUE COUNT(1) FROM t IN p.tags) AS tagsCount
FROM
products p
[
{
"name": "Blators Snowboard Boots",
"colorsCount": 5,
"sizesCount": 7,
"tagsCount": 2
}
]
마지막으로 프로젝션과 필터 모두에서 집계 하위 쿼리가 있는 쿼리는 다음과 같습니다.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Small") AS smallSizesCount,
(SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Large") AS largeSizesCount
FROM
products p
WHERE
(SELECT VALUE COUNT(1) FROM c IN p.colors) >= 5
이 쿼리를 작성하는 더 최적의 방법은 하위 쿼리에 조인하고 SELECT 및 WHERE 절 모두에서 하위 쿼리 별칭을 참조하는 것입니다. 이 쿼리는 프로젝션과 필터가 아닌 조인 문 내에서만 하위 쿼리를 실행해야 하기 때문에 더 효율적입니다.
SELECT
p.name,
colorCount,
smallSizesCount,
largeSizesCount
FROM
products p
JOIN
(SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorCount
JOIN
(SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Small") AS smallSizesCount
JOIN
(SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Large") AS largeSizesCount
WHERE
colorCount >= 5 AND
largeSizesCount > 0 AND
smallSizesCount > 0
EXISTS 식
쿼리 언어는 식을 지원 EXISTS 합니다. 이 식은 쿼리 언어에 기본 제공되는 집계 스칼라 하위 쿼리입니다.
EXISTS 는 하위 쿼리 식을 사용하고 하위 쿼리가 행을 반환하는 경우 반환 true 합니다. 그렇지 않으면 false반환합니다.
쿼리 엔진은 부울 식과 다른 스칼라 식을 구분하지 않으므로 두 절 모두에서 SELECTWHERE 사용할 EXISTS 수 있습니다. 이 동작은 부울 식이 필터로만 제한되는 T-SQL과는 다릅니다.
하위 쿼리가 EXISTS 단일 값을 반환하는 undefinedEXISTS 경우 false로 평가됩니다. 예를 들어 아무것도 반환하지 않는 다음 쿼리를 고려해 보세요.
SELECT VALUE
undefined
식과 이전 쿼리를 하위 쿼리로 사용하면 EXISTS 식이 반환됩니다 false.
SELECT VALUE
EXISTS (SELECT VALUE undefined)
[
false
]
이전 하위 쿼리의 VALUE 키워드를 생략하면 하위 쿼리는 단일 빈 개체가 있는 배열로 평가됩니다.
SELECT
undefined
[
{}
]
이때 식은 EXISTS 개체({})가 기술적으로 종료되므로 계산 true 됩니다.
SELECT VALUE
EXISTS (SELECT undefined)
[
true
]
일반적인 사용 사례 ARRAY_CONTAINS 는 배열에 항목이 존재하여 항목을 필터링하는 것입니다. 이 경우 배열에 "아우터웨어"라는 항목이 포함되어 있는지 tags 확인합니다.
SELECT
p.name,
p.colors
FROM
products p
WHERE
ARRAY_CONTAINS(p.colors, "cobalt")
동일한 쿼리를 대체 옵션으로 사용할 EXISTS 수 있습니다.
SELECT
p.name,
p.colors
FROM
products p
WHERE
EXISTS (SELECT VALUE c FROM c IN p.colors WHERE c = "cobalt")
ARRAY_CONTAINS 또한 값이 배열 내의 모든 요소와 같은지 확인할 수 있습니다. 배열 속성에 더 복잡한 필터가 필요한 경우 대신 사용합니다 JOIN .
배열을 포함하는 accessories 항목이 여러 개 있는 집합의 이 예제 항목을 생각해 보세요.
[
{
"name": "Cosmoxy Pack",
"tags": [
{
"key": "fabric",
"value": "leather",
"description": "Leather"
},
{
"key": "volume",
"value": "68-gal",
"description": "6.8 Gal"
}
]
}
]
이제 각 항목 내의 배열 및 quantityOnHand 속성을 기준으로 type 필터링하는 다음 쿼리를 고려합니다.
SELECT
p.name,
t.description AS tag
FROM
products p
JOIN
t in p.tags
WHERE
t.key = "fabric" AND
t["value"] = "leather"
[
{
"name": "Cosmoxy Pack",
"tag": "Leather"
}
]
컬렉션의 각 항목에 대해 배열 요소를 사용하여 교차 제품이 수행됩니다. 이 JOIN 작업을 통해 배열 내의 속성을 필터링할 수 있습니다. 그러나 이 쿼리의 RU 사용량은 상당합니다. 예를 들어 1,000 개 항목에 각 배열에 100 개의 항목이 있는 경우 다음 수식을 사용하여 튜플을 100,000 개까지 확장합니다.
$$1,000 x 100$$
사용하면 EXISTS 비용이 많이 드는 교차 제품을 방지하는 데 도움이 됩니다. 다음 예제에서는 쿼리가 하위 쿼리 내 EXISTS 의 배열 요소를 필터링합니다. 배열 요소가 필터와 일치하면 해당 필터를 프로젝션하고 EXISTS true로 평가합니다.
SELECT VALUE
p.name
FROM
products p
WHERE
EXISTS (
SELECT VALUE
t
FROM
t IN p.tags
WHERE
t.key = "fabric" AND
t["value"] = "leather"
)
[
"Cosmoxy Pack"
]
쿼리는 또한 별칭 EXISTS 을 지정하고 프로젝션의 별칭을 참조할 수 있습니다.
SELECT
p.name,
EXISTS (
SELECT VALUE
t
FROM
t IN p.tags
WHERE
t.key = "fabric" AND
t["value"] = "leather"
) AS containsFabricLeatherTag
FROM
products p
[
{
"name": "Cosmoxy Pack",
"containsFabricLeatherTag": true
}
]
ARRAY 식
식을 사용하여 ARRAY 쿼리 결과를 배열로 프로젝션할 수 있습니다. 쿼리 절 내에서 SELECT 만 이 식을 사용할 수 있습니다.
이러한 예제에서는 적어도 이 항목이 있는 컨테이너가 있다고 가정해 보겠습니다.
[
{
"name": "Menti Sandals",
"sizes": [
{
"key": "5"
},
{
"key": "6"
},
{
"key": "7"
},
{
"key": "8"
},
{
"key": "9"
}
]
}
]
이 첫 번째 예제에서는 절 내에서 식이 SELECT 사용됩니다.
SELECT
p.name,
ARRAY (
SELECT VALUE
s.key
FROM
s IN p.sizes
) AS sizes
FROM
products p
WHERE
p.name = "Menti Sandals"
[
{
"name": "Menti Sandals",
"sizes": [
"5",
"6",
"7",
"8",
"9"
]
}
]
다른 하위 쿼리와 마찬가지로 식이 있는 필터가 ARRAY 가능합니다.
SELECT
p.name,
ARRAY (
SELECT VALUE
s.key
FROM
s IN p.sizes
WHERE
STRINGTONUMBER(s.key) <= 6
) AS smallSizes,
ARRAY (
SELECT VALUE
s.key
FROM
s IN p.sizes
WHERE
STRINGTONUMBER(s.key) >= 9
) AS largeSizes
FROM
products p
WHERE
p.name = "Menti Sandals"
[
{
"name": "Menti Sandals",
"smallSizes": [
"5",
"6"
],
"largeSizes": [
"9"
]
}
]
배열 식은 하위 쿼리의 절 뒤에도 FROM 올 수 있습니다.
SELECT
p.name,
z.s.key AS sizes
FROM
products p
JOIN
z IN (
SELECT VALUE
ARRAY (
SELECT
s
FROM
s IN p.sizes
WHERE
STRINGTONUMBER(s.key) <= 8
)
)
[
{
"name": "Menti Sandals",
"sizes": "5"
},
{
"name": "Menti Sandals",
"sizes": "6"
},
{
"name": "Menti Sandals",
"sizes": "7"
},
{
"name": "Menti Sandals",
"sizes": "8"
}
]