Nota
O acesso a esta página requer autorização. Podes tentar iniciar sessão ou mudar de diretório.
O acesso a esta página requer autorização. Podes tentar mudar de diretório.
Uma subconsulta é uma consulta aninhada dentro de 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
Existem 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. Existem três tipos:
- Tabela: Devolve várias linhas e várias colunas.
- Vários valores: Devolve 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, apenas subconsultas escalares e de vários valores 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 ou como uma expressão relacional na FROM cláusula.
Subconsultas de vários valores
As 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:
- Otimização
JOIN(auto-junção) expressões. - Avaliando expressões caras uma vez e referenciando várias vezes.
Otimize expressões de associação automática
As subconsultas de vários valores podem otimizar JOIN expressões empurrando predicados após cada expressão select-many em vez de depois de todas as WHEREjunções cruzadas na cláusula.
Considere a seguinte consulta:
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 esta consulta, o índice corresponde a qualquer item que tenha uma marca com um key de ou materialfabric , pelo menos um tamanho com um order valor maior que *três e pelo menos uma cor com gray como uma substring. A JOIN expressão aqui executa o produto cruzado de todos os itens de , sizese colors matrizes para cada item correspondente antes que tagsqualquer filtro seja aplicado.
A WHERE cláusula então aplica o predicado do filtro em cada tupla $<c, t, n, s>$. Por exemplo, se um item correspondente tinha dez itens em cada uma das três matrizes, ele se expande para 1.000 tuplas usando esta fórmula:
$1 x 10 x 10 x 10$$
O uso de subconsultas aqui pode ajudar na filtragem de itens de matriz associados antes de ingressar com a próxima expressão.
Esta 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 tags corresponda ao filtro e há cinco itens para matrizes de quantidade e estoque. Em JOIN seguida, a expressão se expande para 25 tuplas usando esta fórmula, em vez de 1.000 itens na primeira consulta:
$1 x 1 x 5 x 5$$
Avalie uma vez e refira 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 fazer referência a ela 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
},
...
]
Sugestão
Tenha em mente o comportamento entre produtos das JOIN expressões. Se a expressão puder ser avaliada como undefined, você deve garantir que a expressão sempre produza JOIN uma única linha retornando um objeto da subconsulta em vez do valor diretamente.
Mimetizar a junção relacional com dados de referência externos
Muitas vezes, talvez seja necessário 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 armazenamento e melhora o desempenho de gravação, mantendo o tamanho do item individual menor. Você pode usar uma subconsulta para imitar semântica de junção interna com uma coleção de dados de referência estáticos.
Por exemplo, considere este conjunto de medidas que representa o comprimento de uma peça:
| Tamanho | 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 |
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 SELECT cláusulas e WHERE .
O uso de 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 da latência da unidade de recurso (RU).
As subconsultas escalares podem ainda 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 expressões agregadas. Essas subconsultas não fornecem benefícios de otimização porque o compilador as converte em uma expressão simples maior. Não há contexto correlacionado entre as consultas internas e externas.
Como primeiro exemplo, considere esta 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
}
]
Esta próxima consulta de exemplo 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 agregada em sua projeção ou filtro que é avaliada como um único valor.
Como primeiro exemplo, considere um item com os seguintes campos.
[
{
"name": "Blators Snowboard Boots",
"colors": [
"turquoise",
"cobalt",
"jam",
"galliano",
"violet"
],
"sizes": [ ... ],
"tags": [ ... ]
}
]
Aqui está uma subconsulta com uma única expressão de função agregada em sua projeção. Essa consulta conta todas as tags de 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 agregada:
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
}
]
Finalmente, aqui está uma consulta com uma subconsulta agregada 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 na 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
EXISTE expressão
A linguagem de consulta suporta EXISTS expressões. Esta expressão é uma subconsulta escalar agregada incorporada na linguagem de consulta.
EXISTS Usa uma expressão de subconsulta e retorna true se a subconsulta retornar quaisquer linhas. Caso contrário, ele retornará false.
Como o mecanismo de consulta não diferencia entre expressões booleanas e quaisquer outras expressões escalares, você pode usar EXISTS em ambas as SELECT cláusulas e WHERE . Esse comportamento é diferente do T-SQL, onde uma expressão booleana é restrita apenas a filtros.
Se a EXISTS subconsulta retornar um único valor que é undefined, EXISTS será avaliado como false. Por exemplo, considere a seguinte consulta 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 expressão avalia EXISTS desde true que o objeto ({}) tecnicamente sai.
SELECT VALUE
EXISTS (SELECT undefined)
[
true
]
Um caso de uso comum é ARRAY_CONTAINS 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ó pode 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 seguinte consulta que filtra type com base nas 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 é realizado com seus elementos de matriz. Esta JOIN operação torna possível filtrar as propriedades dentro da matriz. No entanto, o consumo de RU desta consulta é significativo. Por exemplo, se 1.000 itens tinham 100 itens em cada matriz, ele se expande para 100.000 tuplas usando esta fórmula:
$1.000 x 100$$
Usar EXISTS ajuda a evitar este produto cruzado caro. Neste próximo exemplo, a consulta filtra os elementos da matriz dentro da EXISTS subconsulta. Se um elemento de matriz corresponder ao filtro, você o projeta e EXISTS avalia 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 podem usar o alias EXISTS e fazer referência ao 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 esse 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"
]
}
]
Tal 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"
]
}
]
As 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"
}
]