Nota
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
Una subconsulta es una consulta anidada dentro de otra consulta dentro del lenguaje de consulta. Una subconsulta también se denomina consulta interna o interna SELECT. La instrucción que contiene una subconsulta normalmente se denomina consulta externa.
Tipos de subconsultas
Hay dos tipos principales de subconsultas:
- Correlacionado: subconsulta que hace referencia a valores de la consulta externa. La subconsulta se evalúa una vez para cada fila que procesa la consulta externa.
- No correlacionado: una subconsulta independiente de la consulta externa. Se puede ejecutar por sí mismo sin depender de la consulta externa.
Las subconsultas se pueden clasificar aún más en función del número de filas y columnas que devuelven. Existen tres tipos:
- Tabla: devuelve varias filas y varias columnas.
- Multivalor: devuelve varias filas y una sola columna.
- Escalar: devuelve una sola fila y una sola columna.
Las consultas en el lenguaje de consulta siempre devuelven una sola columna (un valor simple o un elemento complejo). Por lo tanto, solo se aplican las subconsultas de varios valores y escalares. Puede usar una subconsulta de varios valores solo en la FROM cláusula como una expresión relacional. Puede usar una subconsulta escalar como una expresión escalar en la SELECT cláusula o WHERE o como una expresión relacional en la FROM cláusula .
Subconsultas multivalor
Las subconsultas de varios valores devuelven un conjunto de elementos y siempre se usan dentro de la FROM cláusula . Se usan para:
- Optimización de
JOINexpresiones (autocombinación). - Evaluar expresiones costosas una vez y hacer referencia varias veces.
Optimización de expresiones de autocombinación
Las subconsultas de varios valores pueden optimizar JOIN las expresiones insertando predicados después de cada expresión select-many en lugar de después de todas las combinaciones cruzadas de la WHERE cláusula .
Considere la consulta siguiente:
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, el índice coincide con cualquier elemento que tenga una etiqueta con un key de fabric o material, al menos un tamaño con un order valor mayor que *tres y al menos un color con gray como subcadena. La JOIN expresión aquí realiza el producto cruzado de todos los elementos de tags, sizesy colors matrices para cada elemento coincidente antes de aplicar cualquier filtro.
A WHERE continuación, la cláusula aplica el predicado de filtro en cada tupla $<c, t, n, s>$. Por ejemplo, si un elemento coincidente tenía diez elementos en cada una de las tres matrices, se expande a 1000 tuplas con esta fórmula:
$1 x 10 x 10 x 10$$
El uso de subconsultas aquí puede ayudar a filtrar los elementos de matriz unidos antes de combinar con la siguiente expresión.
Esta consulta es equivalente a la anterior, pero 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%")
Supongamos que solo un elemento de la matriz de etiquetas coincide con el filtro y hay cinco elementos para las matrices de cantidades y existencias. A continuación, la JOIN expresión se expande a 25 tuplas con esta fórmula en lugar de 1000 elementos en la primera consulta:
$$1 x 1 x 5 x 5$$
Evaluar una vez y hacer referencia muchas veces
Las subconsultas pueden ayudar a optimizar las consultas con expresiones costosas, como funciones definidas por el usuario (UDF), cadenas complejas o expresiones aritméticas. Puede usar una subconsulta junto con una JOIN expresión para evaluar la expresión una vez, pero hacer referencia a ella muchas veces.
Esta consulta de ejemplo calcula el precio con un suplemento de 25% varias veces en la consulta.
SELECT VALUE {
subtotal: p.price,
total: (p.price * 1.25)
}
FROM
products p
WHERE
(p.price * 1.25) < 22.25
Esta es una consulta equivalente que ejecuta el cálculo solo una 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
},
...
]
Sugerencia
Tenga en cuenta el comportamiento entre productos de JOIN las expresiones. Si la expresión puede evaluarse como undefined, debe asegurarse de que la JOIN expresión siempre genera una sola fila devolviendo un objeto de la subconsulta en lugar del valor directamente.
Imitación de la combinación relacional con datos de referencia externos
A menudo, es posible que tenga que hacer referencia a datos estáticos que rara vez cambian, como unidades de medida. Es ideal no duplicar datos estáticos para cada elemento de una consulta. Evitar esta duplicación ahorra en el almacenamiento y mejorar el rendimiento de escritura manteniendo el tamaño de elemento individual más pequeño. Puede usar una subconsulta para imitar la semántica de combinación interna con una colección de datos de referencia estáticos.
Por ejemplo, considere este conjunto de medidas que representa la longitud de una prenda:
| Tamaño | 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 |
La consulta siguiente imita la combinación con estos datos para agregar el nombre de la unidad a la salida:
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
Una expresión de subconsulta escalar es una subconsulta que se evalúa como un valor único. El valor de la expresión de subconsulta escalar es el valor de la proyección (SELECT cláusula) de la subconsulta. Puede usar una expresión de subconsulta escalar en muchos lugares donde una expresión escalar es válida. Por ejemplo, puede usar una subconsulta escalar en cualquier expresión de las SELECT cláusulas y WHERE .
El uso de una subconsulta escalar no siempre ayuda a optimizar la consulta. Por ejemplo, pasar una subconsulta escalar como argumento a un sistema o funciones definidas por el usuario no proporciona ninguna ventaja para reducir el consumo o la latencia de la unidad de recursos (RU).
Las subconsultas escalares se pueden clasificar aún más como:
- Subconsultas escalares de expresión simple
- Subconsultas escalares agregadas
Subconsultas escalares de expresión simple
Una subconsulta escalar de expresión simple es una subconsulta correlacionada que tiene una SELECT cláusula que no contiene ninguna expresión de agregado. Estas subconsultas no proporcionan ventajas de optimización porque el compilador las convierte en una expresión simple más grande. No hay ningún contexto correlacionado entre las consultas internas y externas.
Como primer ejemplo, considere esta consulta trivial.
SELECT
1 AS a,
2 AS b
Puede volver a escribir esta consulta mediante una subconsulta escalar de expresión simple.
SELECT
(SELECT VALUE 1) AS a,
(SELECT VALUE 2) AS b
Ambas consultas generan la misma salida.
[
{
"a": 1,
"b": 2
}
]
En este ejemplo siguiente, la consulta concatena el identificador único con un prefijo como subconsulta escalar de expresión simple.
SELECT
(SELECT VALUE CONCAT('ID-', p.id)) AS internalId
FROM
products p
En este ejemplo se usa una subconsulta escalar de expresión simple para devolver solo los campos pertinentes para cada elemento. La consulta genera algo para cada elemento, pero solo incluye el campo proyectado si cumple el filtro dentro de la 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"
}
]
Subconsultas escalares agregadas
Una subconsulta escalar agregada es una subconsulta que tiene una función de agregado en su proyección o filtro que se evalúa como un valor único.
Como primer ejemplo, considere un elemento con los campos siguientes.
[
{
"name": "Blators Snowboard Boots",
"colors": [
"turquoise",
"cobalt",
"jam",
"galliano",
"violet"
],
"sizes": [ ... ],
"tags": [ ... ]
}
]
Esta es una subconsulta con una única expresión de función de agregado en su proyección. Esta consulta cuenta todas las etiquetas de cada elemento.
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
}
]
Esta es la misma subconsulta con un 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
}
]
Esta es otra subconsulta con varias expresiones de función de agregado:
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 último, esta es una consulta con una subconsulta agregada en la proyección y el 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
Una manera más óptima de escribir esta consulta es unirse a la subconsulta y hacer referencia al alias de la subconsulta en las cláusulas SELECT y WHERE. Esta consulta es más eficaz porque necesita ejecutar la subconsulta solo dentro de la instrucción join y no en la proyección y el 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
Expresión EXISTS
El lenguaje de consulta admite EXISTS expresiones. Esta expresión es una subconsulta escalar agregada integrada en el lenguaje de consulta.
EXISTS toma una expresión de subconsulta y devuelve true si la subconsulta devuelve filas. De lo contrario, devuelve false.
Dado que el motor de consultas no diferencia entre expresiones booleanas y cualquier otra expresión escalar, puede usar EXISTS en ambas SELECT cláusulas y WHERE . Este comportamiento es diferente de T-SQL, donde una expresión booleana está restringida solo a filtros.
Si la EXISTS subconsulta devuelve un valor único que es undefined, EXISTS se evalúa como false. Por ejemplo, considere la siguiente consulta que no devuelve nada.
SELECT VALUE
undefined
Si usa la EXISTS expresión y la consulta anterior como subconsulta, la expresión devuelve false.
SELECT VALUE
EXISTS (SELECT VALUE undefined)
[
false
]
Si se omite la palabra clave VALUE de la subconsulta anterior, la subconsulta se evalúa como una matriz con un único objeto vacío.
SELECT
undefined
[
{}
]
En ese momento, la EXISTS expresión se true evalúa como desde que el objeto ({}) se cierra técnicamente.
SELECT VALUE
EXISTS (SELECT undefined)
[
true
]
Un caso de uso común de ARRAY_CONTAINS es filtrar un elemento por la existencia de un elemento en una matriz. En este caso, estamos comprobando si la tags matriz contiene un elemento denominado "outerwear".
SELECT
p.name,
p.colors
FROM
products p
WHERE
ARRAY_CONTAINS(p.colors, "cobalt")
La misma consulta puede usarse EXISTS como opción alternativa.
SELECT
p.name,
p.colors
FROM
products p
WHERE
EXISTS (SELECT VALUE c FROM c IN p.colors WHERE c = "cobalt")
Además, ARRAY_CONTAINS solo puede comprobar si un valor es igual a cualquier elemento de una matriz. Si necesita filtros más complejos en las propiedades de la matriz, use JOIN en su lugar.
Considere este elemento de ejemplo en un conjunto con varios elementos que contienen una accessories matriz.
[
{
"name": "Cosmoxy Pack",
"tags": [
{
"key": "fabric",
"value": "leather",
"description": "Leather"
},
{
"key": "volume",
"value": "68-gal",
"description": "6.8 Gal"
}
]
}
]
Ahora, tenga en cuenta la siguiente consulta que filtra según las type propiedades y quantityOnHand de la matriz dentro de cada elemento.
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 uno de los elementos de la colección, se realiza un producto cruzado con sus elementos de matriz. Esta JOIN operación permite filtrar por las propiedades de la matriz. Sin embargo, el consumo de RU de esta consulta es significativo. Por ejemplo, si 1000 elementos tenían 100 elementos en cada matriz, se expande a 100 000 tuplas con esta fórmula:
$1000 x 100$$
El uso EXISTS ayuda a evitar este costoso producto cruzado. En este ejemplo siguiente, la consulta filtra los elementos de matriz dentro de la EXISTS subconsulta. Si un elemento de matriz coincide con el filtro, lo proyecta y EXISTS se evalúa 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"
]
Las consultas también pueden establecer alias EXISTS y hacer referencia al alias en la proyección:
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
}
]
Expresión ARRAY
Puede usar la ARRAY expresión para proyectar los resultados de una consulta como una matriz. Esta expresión solo se puede usar dentro de la SELECT cláusula de la consulta.
En estos ejemplos, supongamos que hay un contenedor con al menos este elemento.
[
{
"name": "Menti Sandals",
"sizes": [
{
"key": "5"
},
{
"key": "6"
},
{
"key": "7"
},
{
"key": "8"
},
{
"key": "9"
}
]
}
]
En este primer ejemplo, la expresión se usa dentro de la 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"
]
}
]
Al igual que con otras subconsultas, los filtros con la ARRAY expresión son posibles.
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"
]
}
]
Las expresiones de matriz también pueden aparecer después de la FROM cláusula en 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"
}
]