Información sobre las subconsultas
Una subconsulta es una consulta que aparece dentro de otra declaración de consulta. Las subconsultas también se denominan sub-SELECT o SELECT anidados. La sintaxis completa de SELECT es válida en las subconsultas.
Subconsultas de expresión
Las subconsultas de expresión se usan en una consulta en cualquier lugar en que las expresiones sean válidas. Muestran un solo valor, y no una columna o tabla. Las subconsultas de expresión pueden correlacionarse.
Subconsultas escalares
( subquery )
Descripción
Una subconsulta dentro de una expresión se interpreta como una subconsulta escalar.
Las subconsultas escalares suelen usarse en la lista SELECT o en la cláusula WHERE.
Una subconsulta escalar debe seleccionar una sola columna. Si intentas seleccionar varias columnas, se generará un error de análisis. Una lista SELECT con una sola expresión es la forma más simple de seleccionar una sola columna. El tipo de resultado de la subconsulta escalar es el tipo de esa expresión.
Otra posibilidad es usar SELECT AS STRUCT para definir una subconsulta que selecciona un solo valor de tipo STRUCT cuyos campos están definidos por una o más expresiones.
Si la subconsulta muestra exactamente una fila, ese único valor es el resultado de la subconsulta escalar. Si la subconsulta muestra cero filas, el resultado es NULL.
Si la subconsulta muestra más de una fila, la consulta genera un error de entorno de ejecución.
Ejemplos
En este ejemplo, una subconsulta escalar correlacionada muestra las mascotas para una lista de jugadores mediante las tablas Players y Mascots:
SELECT
username,
(SELECT mascot FROM Mascots WHERE Players.team = Mascots.team) AS player_mascot
FROM
Players;
+---------------------------+
| username | player_mascot |
+---------------------------+
| gorbie | cardinal |
| junelyn | finch |
| corba | parrot |
+---------------------------+
En este ejemplo, una subconsulta escalar global calcula avg_level, el nivel promedio de un usuario en la tabla Players.
SELECT
username,
level,
(SELECT AVG(level) FROM Players) AS avg_level
FROM
Players;
+---------------------------------------+
| username | level | avg_level |
+---------------------------------------+
| gorbie | 29 | 24.66 |
| junelyn | 2 | 24.66 |
| corba | 43 | 24.66 |
+---------------------------------------+
Subconsultas ARRAY
ARRAY ( subquery )
Descripción
Una subconsulta ARRAY es un caso especial de subconsulta de expresión, ya que muestra un ARRAY. Si la subconsulta muestra cero filas, muestra un ARRAY vacío.
Nunca muestra un ARRAY NULL.
La lista SELECT en una subconsulta ARRAY debe tener exactamente una columna de cualquier tipo, que define el tipo de elemento del arreglo que muestra la subconsulta del arreglo. De lo contrario, se muestra un error. Cuando la subconsulta se escribe con SELECT AS STRUCT, la lista SELECT puede incluir varias columnas y el valor que muestra la subconsulta del arreglo es un ARRAY de los STRUCT construidos.
Seleccionar varias columnas sin usar SELECT AS es un error.
Las subconsultas ARRAY pueden usar SELECT AS STRUCT para compilar arreglos de structs.
Consulta Funciones de arreglo para obtener una semántica completa.
Ejemplos
En este ejemplo, una subconsulta de ARRAY muestra un arreglo de nombres de usuario asignados al equipo rojo en la tabla NPCs:
SELECT
ARRAY(SELECT username FROM NPCs WHERE team = 'red') AS red;
+-----------------+
| red |
+-----------------+
| [niles,jujul] |
+-----------------+
Subconsultas IN
value [ NOT ] IN ( subquery )
Descripción
El resultado es TRUE si value está en el conjunto de filas que muestra la subconsulta.
El resultado es FALSE si la subconsulta muestra cero filas.
La lista SELECT de la subconsulta debe tener una sola columna de cualquier tipo y su tipo debe ser comparable con el tipo para value. De lo contrario, se muestra un error. Para obtener una semántica completa, incluido el control de NULL, consulta el operador IN.
Si necesitas usar una subconsulta IN con un arreglo, estos ejemplos son equivalentes:
value [ NOT ] IN ( subquery )
value [ NOT ] IN UNNEST( ARRAY( subquery ) )
Ejemplos
En este ejemplo, el operador IN verifica si existe un nombre de usuario llamado corba en la tabla Players:
SELECT
'corba' IN (SELECT username FROM Players) AS result;
+--------+
| result |
+--------+
| TRUE |
+--------+
Subconsultas EXISTS
EXISTS( subquery )
Descripción
El resultado es TRUE si la subconsulta produce una o más filas. El resultado es FALSE si la subconsulta produce cero filas. Nunca muestra NULL. A diferencia de todas las demás subconsultas de expresión, no hay reglas que se apliquen a la lista de columnas.
Se puede seleccionar cualquier cantidad de columnas sin que esto afecte el resultado de la consulta.
Ejemplos
En este ejemplo, el operador EXISTS verifica si se producen filas mediante la tabla Players:
SELECT
EXISTS(SELECT username FROM Players WHERE team = 'yellow') AS result;
+--------+
| result |
+--------+
| FALSE |
+--------+
Subconsultas de tabla
FROM ( subquery ) [ [ AS ] alias ]
Descripción
Con las subconsultas de tabla, la consulta externa trata el resultado de la subconsulta como una tabla. Solo puedes usarlas en la cláusula FROM.
Ejemplos
En este ejemplo, una subconsulta muestra una tabla de nombres de usuario de la tabla Players:
SELECT results.username
FROM (SELECT * FROM Players) AS results;
+-----------+
| username |
+-----------+
| gorbie |
| junelyn |
| corba |
+-----------+
En este ejemplo, se muestra una lista de NPCs asignadas al grupo rojo.
SELECT
username
FROM (
WITH red_team AS (SELECT * FROM NPCs WHERE team = 'red')
SELECT * FROM red_team
);
+-----------+
| username |
+-----------+
| niles |
| jujul |
+-----------+
Subconsultas correlacionadas
Una subconsulta correlacionada es una subconsulta que hace referencia a una columna desde el exterior de esa subconsulta. La correlación evita que se reutilice el resultado de la subconsulta. Puedes obtener más información sobre este tema aquí.
Ejemplos
En este ejemplo, se muestra una lista de mascotas que no tienen jugadores asignados. Se hace referencia a las tablas Mascots y Players.
SELECT mascot
FROM Mascots
WHERE
NOT EXISTS(SELECT username FROM Players WHERE Mascots.team = Players.team);
+----------+
| mascot |
+----------+
| sparrow |
+----------+
En este ejemplo, una subconsulta escalar correlacionada muestra las mascotas para una lista de jugadores mediante las tablas Players y Mascots:
SELECT
username,
(SELECT mascot FROM Mascots WHERE Players.team = Mascots.team) AS player_mascot
FROM Players;
+---------------------------+
| username | player_mascot |
+---------------------------+
| gorbie | cardinal |
| junelyn | finch |
| corba | parrot |
+---------------------------+
Subconsultas inestables
Una subconsulta inestable es una subconsulta que no siempre produce el mismo resultado a partir de las mismas entradas. Por ejemplo, si una subconsulta incluye una función que muestra un número aleatorio, la subconsulta es inestable porque el resultado no siempre es el mismo.
Ejemplos
En este ejemplo, se muestra una cantidad aleatoria de nombres de usuario de la tabla Players.
SELECT
results.username
FROM
(SELECT * FROM Players WHERE RAND() < 0.5) AS results;
-- The results are not always the same when you execute
-- the preceding query, but will look similar to this:
+----------+
| username |
+----------+
| gorbie |
| junelyn |
+----------+
Reglas de evaluación para subconsultas
Algunas subconsultas se evalúan una vez, otras con mayor frecuencia.
- Una subconsulta volátil no correlacionada puede que se vuelva a evaluar una vez por fila, según el plan de consultas.
- Una subconsulta correlacionada debe volver a evaluarse de forma lógica para cada conjunto distinto de valores del parámetro. Según tu plan de consulta, una subconsulta correlacionada se puede volver a evaluar una vez por fila, incluso si varias filas tienen los mismos valores del parámetro.
Tablas comunes que se usan en los ejemplos
En algunos ejemplos se hace referencia a una tabla llamada Players:
+-----------------------------+
| username | level | team |
+-----------------------------+
| gorbie | 29 | red |
| junelyn | 2 | blue |
| corba | 43 | green |
+-----------------------------+
En algunos ejemplos se hace referencia a una tabla llamada NPCs:
+-------------------+
| username | team |
+-------------------+
| niles | red |
| jujul | red |
| effren | blue |
+-------------------+
En algunos ejemplos se hace referencia a una tabla llamada Mascots:
+-------------------+
| mascot | team |
+-------------------+
| cardinal | red |
| parrot | green |
| finch | blue |
| sparrow | yellow |
+-------------------+
Puedes usar esta cláusula WITH a fin de emular nombres de tabla temporales para Players y NPCs en subconsultas que admitan la cláusula WITH:
WITH
Players AS (
SELECT 'gorbie' AS username, 29 AS level, 'red' AS team UNION ALL
SELECT 'junelyn', 2 , 'blue' UNION ALL
SELECT 'corba', 43, 'green'),
NPCs AS (
SELECT 'niles' AS username, 'red' AS team UNION ALL
SELECT 'jujul', 'red' UNION ALL
SELECT 'effren', 'blue'),
Mascots AS (
SELECT 'cardinal' AS mascot , 'red' AS team UNION ALL
SELECT 'parrot', 'green' UNION ALL
SELECT 'finch', 'blue' UNION ALL
SELECT 'sparrow', 'yellow')
SELECT * FROM (
SELECT username, team FROM Players UNION ALL
SELECT username, team FROM NPCs);