Observação
O acesso a essa página exige autorização. Você pode tentar entrar ou alterar diretórios.
O acesso a essa página exige autorização. Você pode tentar alterar os diretórios.
Uma subconsulta é uma consulta aninhada em outra consulta dentro da linguagem de consulta. Uma subconsulta também é chamada de consulta interna ou interna SELECT. A instrução que contém uma subconsulta normalmente é chamada de consulta externa.
Tipos de subconsultas
Há dois tipos principais de subconsultas:
- Correlacionado: uma subconsulta que faz referência a valores da consulta externa. A subconsulta é avaliada uma vez para cada linha que a consulta externa processa.
- Não correlacionado: uma subconsulta independente da consulta externa. Ele pode ser executado por conta própria sem depender da consulta externa.
As subconsultas podem ser classificadas com base no número de linhas e colunas que retornam. Há três tipos:
- Tabela: Retorna várias linhas e várias colunas.
- Multi-valor: retorna várias linhas e uma única coluna.
- Escalar: retorna uma única linha e uma única coluna.
As consultas na linguagem de consulta sempre retornam uma única coluna (um valor simples ou um item complexo). Portanto, somente subconsultas de vários valores e escalares são aplicáveis. Você pode usar uma subconsulta de vários valores somente na FROM cláusula como uma expressão relacional. Você pode usar uma subconsulta escalar como uma expressão escalar na SELECT cláusula ou WHERE como uma expressão relacional na FROM cláusula.
Subconsultas de vários valores
Subconsultas de vários valores retornam um conjunto de itens e são sempre usadas dentro da FROM cláusula. Eles são usados para:
- Otimizando
JOINexpressões (auto-junção). - Avaliando expressões caras uma vez e fazendo referência várias vezes.
Otimizar expressões de auto-junção
Subconsultas de vários valores podem otimizar JOIN expressões enviando predicados por push após cada expressão select-many em vez de após todas as junções cruzadas na WHERE cláusula.
Considere a consulta a seguir:
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%"
Para essa consulta, o índice corresponde a qualquer item que tenha uma marca com um key ou fabricmaterial, pelo menos, um tamanho com um order valor maior que *três e pelo menos uma cor com gray uma subcadeia de caracteres. A JOIN expressão aqui executa o produto cruzado de todos os itens de tags, sizese colors matrizes para cada item correspondente antes de qualquer filtro ser aplicado.
Em WHERE seguida, a cláusula aplica o predicado de filtro em cada tupla $<c, t, n, s>$ . Por exemplo, se um item correspondente tiver dez itens em cada uma das três matrizes, ele se expandirá para 1.000 tuplas usando esta fórmula:
$$1 x 10 x 10 x 10$$
Usar subconsultas aqui pode ajudar na filtragem de itens de matriz unida antes de ingressar com a próxima expressão.
Essa consulta é equivalente à anterior, mas usa subconsultas:
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%")
Suponha que apenas um item na matriz de marcas corresponda ao filtro e que haja cinco itens para matrizes de quantidade e de estoque. Em JOIN seguida, a expressão se expande para 25 tuplas usando essa fórmula em vez de 1.000 itens na primeira consulta:
$$1 x 1 x 5 x 5$$
Avaliar uma vez e fazer referência muitas vezes
As subconsultas podem ajudar a otimizar consultas com expressões caras, como UDFs (funções definidas pelo usuário), cadeias de caracteres complexas ou expressões aritméticas. Você pode usar uma subconsulta junto com uma JOIN expressão para avaliar a expressão uma vez, mas referenciá-la muitas vezes.
Esta consulta de exemplo calcula o preço com um suplemento de 25% várias vezes na consulta.
SELECT VALUE {
subtotal: p.price,
total: (p.price * 1.25)
}
FROM
products p
WHERE
(p.price * 1.25) < 22.25
Aqui está uma consulta equivalente que executa o cálculo apenas uma vez:
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
},
...
]
Dica
Tenha em mente o comportamento entre produtos das JOIN expressões. Se a expressão puder ser avaliada undefined, você deverá garantir que a JOIN expressão sempre produza uma única linha retornando um objeto da subconsulta em vez do valor diretamente.
Imitar junção relacional com dados de referência externos
Geralmente, você precisa fazer referência a dados estáticos que raramente são alterados, como unidades de medida. É ideal não duplicar dados estáticos para cada item em uma consulta. Evitar essa duplicação economiza no armazenamento e melhorar o desempenho de gravação mantendo o tamanho do item individual menor. Você pode usar uma subconsulta para imitar a semântica de junção interna com uma coleção de dados de referência estática.
Por exemplo, considere esse conjunto de medidas que representa o comprimento de uma peça de vestuário:
| Tamanho | Length | Unidades |
|---|---|---|
xs |
63.5 |
cm |
s |
64.5 |
cm |
m |
66.0 |
cm |
l |
67.5 |
cm |
xl |
69.0 |
cm |
xxl |
70.5 |
cm |
A consulta a seguir imita a junção com esses dados para que você adicione o nome da unidade à saída:
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
Subconsultas escalares
Uma expressão de subconsulta escalar é uma subconsulta que é avaliada como um único valor. O valor da expressão de subconsulta escalar é o valor da projeção (SELECT cláusula) da subconsulta. Você pode usar uma expressão de subconsulta escalar em muitos lugares onde uma expressão escalar é válida. Por exemplo, você pode usar uma subconsulta escalar em qualquer expressão nas cláusulas e WHERE nas SELECT cláusulas.
Usar uma subconsulta escalar nem sempre ajuda a otimizar sua consulta. Por exemplo, passar uma subconsulta escalar como um argumento para um sistema ou funções definidas pelo usuário não oferece nenhum benefício na redução do consumo ou latência da RU (unidade de recurso).
Subconsultas escalares podem ser classificadas como:
- Subconsultas escalares de expressão simples
- Agregar subconsultas escalares
Subconsultas escalares de expressão simples
Uma subconsulta escalar de expressão simples é uma subconsulta correlacionada que tem uma SELECT cláusula que não contém nenhuma expressão agregada. Essas subconsultas não oferecem benefícios de otimização porque o compilador as converte em uma expressão simples maior. Não há nenhum contexto correlacionado entre as consultas internas e externas.
Como primeiro exemplo, considere essa consulta trivial.
SELECT
1 AS a,
2 AS b
Você pode reescrever essa consulta usando uma subconsulta escalar de expressão simples.
SELECT
(SELECT VALUE 1) AS a,
(SELECT VALUE 2) AS b
Ambas as consultas produzem a mesma saída.
[
{
"a": 1,
"b": 2
}
]
Este próximo exemplo de consulta concatena o identificador exclusivo com um prefixo como uma subconsulta escalar de expressão simples.
SELECT
(SELECT VALUE CONCAT('ID-', p.id)) AS internalId
FROM
products p
Este exemplo usa uma subconsulta escalar de expressão simples para retornar apenas os campos relevantes para cada item. A consulta gera algo para cada item, mas só inclui o campo projetado se ele atender ao filtro dentro da subconsulta.
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"
}
]
Agregar subconsultas escalares
Uma subconsulta escalar agregada é uma subconsulta que tem uma função de agregação em sua projeção ou filtro que é avaliada como um único valor.
Como primeiro exemplo, considere um item com os campos a seguir.
[
{
"name": "Blators Snowboard Boots",
"colors": [
"turquoise",
"cobalt",
"jam",
"galliano",
"violet"
],
"sizes": [ ... ],
"tags": [ ... ]
}
]
Aqui está uma subconsulta com uma única expressão de função de agregação em sua projeção. Essa consulta conta todas as marcas para cada item.
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
}
]
Aqui está a mesma subconsulta com um filtro.
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
}
]
Aqui está outra subconsulta com várias expressões de função de agregação:
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
}
]
Por fim, aqui está uma consulta com uma subconsulta de agregação na projeção e no filtro:
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
Uma maneira mais ideal de escrever essa consulta é unir-se à subconsulta e fazer referência ao alias de subconsulta nas cláusulas SELECT e WHERE. Essa consulta é mais eficiente porque você precisa executar a subconsulta somente dentro da instrução join e não na projeção e no filtro.
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
Expressão EXISTS
A linguagem de consulta dá EXISTS suporte a expressões. Essa expressão é uma subconsulta escalar agregada incorporada à linguagem de consulta.
EXISTS usa uma expressão de subconsulta e retorna true se a subconsulta retorna linhas. Caso contrário, ele retornará false.
Como o mecanismo de consulta não diferencia entre expressões boolianas e quaisquer outras expressões escalares, você pode usar EXISTS em ambas e SELECTWHERE cláusulas. Esse comportamento é diferente do T-SQL, em que uma expressão booliana é restrita apenas a filtros.
Se a EXISTS subconsulta retornar um único valor, será EXISTSundefinedavaliada como false. Por exemplo, considere a consulta a seguir que não retorna nada.
SELECT VALUE
undefined
Se você usar a EXISTS expressão e a consulta anterior como uma subconsulta, a expressão retornará false.
SELECT VALUE
EXISTS (SELECT VALUE undefined)
[
false
]
Se a palavra-chave VALUE na subconsulta anterior for omitida, a subconsulta será avaliada como uma matriz com um único objeto vazio.
SELECT
undefined
[
{}
]
Nesse ponto, a EXISTS expressão é avaliada como true uma vez que o objeto ({}) sai tecnicamente.
SELECT VALUE
EXISTS (SELECT undefined)
[
true
]
Um caso de ARRAY_CONTAINS uso comum é filtrar um item pela existência de um item em uma matriz. Nesse caso, estamos verificando se a tags matriz contém um item chamado "outerwear".
SELECT
p.name,
p.colors
FROM
products p
WHERE
ARRAY_CONTAINS(p.colors, "cobalt")
A mesma consulta pode ser usada EXISTS como uma opção alternativa.
SELECT
p.name,
p.colors
FROM
products p
WHERE
EXISTS (SELECT VALUE c FROM c IN p.colors WHERE c = "cobalt")
Além disso, ARRAY_CONTAINS só é possível verificar se um valor é igual a qualquer elemento dentro de uma matriz. Se você precisar de filtros mais complexos nas propriedades da matriz, use JOIN em vez disso.
Considere este item de exemplo em um conjunto com vários itens cada um contendo uma accessories matriz.
[
{
"name": "Cosmoxy Pack",
"tags": [
{
"key": "fabric",
"value": "leather",
"description": "Leather"
},
{
"key": "volume",
"value": "68-gal",
"description": "6.8 Gal"
}
]
}
]
Agora, considere a consulta a seguir que filtra com base nas type propriedades e quantityOnHand na matriz dentro de cada item.
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"
}
]
Para cada um dos itens da coleção, um produto cruzado é executado com seus elementos de matriz. Essa JOIN operação possibilita filtrar as propriedades dentro da matriz. No entanto, o consumo de RU dessa consulta é significativo. Por exemplo, se 1.000 itens tiverem 100 itens em cada matriz, ele se expandirá para 100.000 tuplas usando esta fórmula:
$$1.000 x 100$$
O uso EXISTS ajuda a evitar esse produto cruzado caro. Neste próximo exemplo, a consulta filtra elementos de matriz dentro da EXISTS subconsulta. Se um elemento de matriz corresponder ao filtro, você o projetará e EXISTS será avaliado como 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"
]
As consultas também têm permissão para alias EXISTS e referenciam o alias na projeção:
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
}
]
Expressão ARRAY
Você pode usar a ARRAY expressão para projetar os resultados de uma consulta como uma matriz. Você pode usar essa expressão somente dentro da SELECT cláusula da consulta.
Para esses exemplos, vamos supor que haja um contêiner com pelo menos este item.
[
{
"name": "Menti Sandals",
"sizes": [
{
"key": "5"
},
{
"key": "6"
},
{
"key": "7"
},
{
"key": "8"
},
{
"key": "9"
}
]
}
]
Neste primeiro exemplo, a expressão é usada dentro da SELECT cláusula.
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"
]
}
]
Assim como acontece com outras subconsultas, os filtros com a ARRAY expressão são possíveis.
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"
]
}
]
Expressões de matriz também podem vir após a FROM cláusula em subconsultas.
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"
}
]