Subconsultas

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);