Sintaxis de consultas de SQL estándar de Google

Las instrucciones de consulta analizan una o más tablas o expresiones y muestran las filas de resultados calculados. En este tema, se describe la sintaxis para consultas de SQL en SQL estándar de Google.

Sintaxis de SQL

query_statement:
    [ statement_hint_expr ][ table_hint_expr ][ join_hint_expr ]
query_expr statement_hint_expr: '@{' statement_hint_key = statement_hint_value [, ...] '}' statement_hint_key: { USE_ADDITIONAL_PARALLELISM | OPTIMIZER_VERSION | OPTIMIZER_STATISTICS_PACKAGE | ALLOW_DISTRIBUTED_MERGE | LOCK_SCANNED_RANGES } query_expr: [ WITH cte[, ...] ] { select | ( query_expr ) | set_operation } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [ AS { STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...] [ FROM from_clause[, ...] ] [ WHERE bool_expression ] [ GROUP BY expression [, ...] ] [ HAVING bool_expression ] table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION }

Reglas de notación

  • Los corchetes “[ ]” indican cláusulas opcionales.
  • Los paréntesis “( )” indican paréntesis literales.
  • La barra vertical “|” indica un OR lógico.
  • Las llaves “{ }” encierran un conjunto de opciones.
  • Una coma seguida de puntos suspensivos entre corchetes “[, … ]” indica que el elemento anterior se puede repetir en una lista separada por comas.

Sugerencias de instrucciones

Las siguientes sugerencias son compatibles con las instrucciones de consulta:

Sugerencia de clave Valores posibles Descripción
USE_ADDITIONAL_PARALLELISM TRUE
FALSE (valor predeterminado)
Si es TRUE, el motor de ejecución favorece el uso de más paralelismo cuando sea posible. Debido a que esto puede reducir los recursos disponibles para otras operaciones, es recomendable que evites esta sugerencia si ejecutas operaciones sensibles a la latencia en la misma instancia.
OPTIMIZER_VERSION De 1 a N|latest_version|default_version Ejecuta la consulta mediante la versión del optimizador especificada. Los valores posibles son 1 a N (la versión más reciente del optimizador), default_version o latest_version. Si no se configura la sugerencia, el optimizador se ejecuta en el paquete configurado en las opciones de la base de datos o que se especifica a través de la API del cliente. Si no se configura ninguna de esas opciones, el optimizador usa la versión predeterminada.

En términos de la prioridad de la configuración de versión, el valor establecido por la API del cliente tiene prioridad sobre el valor en las opciones de la base de datos y el valor establecido por esta sugerencia. tiene prioridad sobre todo lo demás.

Para obtener más información, consulta Optimizador de consultas.
OPTIMIZER_STATISTICS_PACKAGE package_name|latest Ejecuta la consulta con el paquete de estadísticas del optimizador especificado. Los valores posibles de package_name se pueden encontrar mediante la siguiente consulta:


SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS

Si no se configura la sugerencia, el optimizador se ejecuta en el paquete configurado en la opción de la base de datos o especificado a través de la API del cliente. Si no se configura ninguna de estas opciones, el optimizador utilizará de forma predeterminada el paquete más reciente.

El valor establecido por la API del cliente tiene prioridad sobre el valor en las opciones de la base de datos, y el valor establecido por esta sugerencia tiene prioridad sobre todo lo demás.

El paquete especificado se debe fijar con la opción de base de datos o tener allow_gc=false para evitar la recolección de elementos no utilizados.

Para obtener más información, consulta Paquetes de estadísticas del optimizador de consultas.
ALLOW_DISTRIBUTED_MERGE TRUE (predeterminado)
FALSE
Si es TRUE (predeterminado), el motor favorece el uso de un algoritmo de orden combinado distribuido para determinadas consultas ORDER BY. Cuando corresponda, los órdenes globales se cambian a los locales. Esto te da la ventaja de ordenar en paralelo cerca de donde se almacenan los datos. Luego, los datos ordenados de manera local se combinan para proporcionar datos ordenados de forma global. Esto permite quitar órdenes globales completos y una latencia potencialmente mejorada. Esta característica puede aumentar el paralelismo de ciertas consultas ORDER BY. Se proporcionó esta sugerencia para que los usuarios puedan experimentar con la desactivación del algoritmo de combinación distribuida si lo desean.
LOCK_SCANNED_RANGES exclusive
shared (valor predeterminado)
Usa esta sugerencia para solicitar un bloqueo exclusivo en un conjunto de rangos analizados por una transacción. Exigir un bloqueo exclusivo ayuda en situaciones en las que observas una disputa de escritura alta, es decir, varias transacciones intentan leer y escribir simultáneamente en los mismos datos, lo que genera una gran cantidad de anulaciones.

Sin la sugerencia, es posible que varias transacciones simultáneas adquieran bloqueos compartidos y, luego, intenten actualizarse a bloqueos exclusivos. Esto provocará un interbloqueo, ya que el bloqueo compartido de cada transacción impide que las demás transacciones se actualicen a exclusivas. Cloud Spanner anula todas las transacciones, excepto una. Cuando se solicita un bloqueo exclusivo con esta sugerencia, una transacción adquiere el bloqueo y procede a ejecutarse, mientras que otras transacciones esperan su turno para el bloqueo. La capacidad de procesamiento sigue siendo limitada porque las transacciones en conflicto solo se pueden realizar de a una, pero en este caso, Cloud Spanner siempre progresa con una transacción, lo que ahorra tiempo que, de lo contrario, se usaría en anular y reintentar transacciones.

Esta sugerencia es compatible con todos los tipos de declaraciones, tanto consultas como DML.

Cloud Spanner siempre aplica la serialización. Las sugerencias del modo de bloqueo pueden afectar qué transacciones deben quedar en espera o se deben anular en las cargas de trabajo disputadas, pero no cambian el nivel de aislamiento.

Debido a que esto es solo una sugerencia, no debe considerarse equivalente a una exclusión mutua. En otras palabras, no debes usar los bloqueos exclusivos de Cloud Spanner como un mecanismo de exclusión mutua para la ejecución de código fuera de Cloud Spanner.

Para obtener más información, consulta Bloqueo.

Tablas de muestra

Las siguientes tablas se usan para ilustrar el comportamiento de distintas cláusulas de consulta en esta referencia.

Tabla Roster

En la tabla Roster, se incluye una lista de nombres de jugadores (LastName) y el ID único asignado a su escuela (SchoolID). El resultado será el siguiente:

+-----------------------+
| LastName   | SchoolID |
+-----------------------+
| Adams      | 50       |
| Buchanan   | 52       |
| Coolidge   | 52       |
| Davis      | 51       |
| Eisenhower | 77       |
+-----------------------+

Puedes usar esta cláusula WITH con el fin de emular un nombre de tabla temporal para los ejemplos de esta referencia:

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)
SELECT * FROM Roster

Tabla PlayerStats

En la tabla PlayerStats, se incluye una lista de nombres de jugadores (LastName), el ID único asignado al oponente contra el que jugaron en un partido determinado (OpponentID) y la cantidad de puntos marcados por cada atleta en ese partido (PointsScored).

+----------------------------------------+
| LastName   | OpponentID | PointsScored |
+----------------------------------------+
| Adams      | 51         | 3            |
| Buchanan   | 77         | 0            |
| Coolidge   | 77         | 1            |
| Adams      | 52         | 4            |
| Buchanan   | 50         | 13           |
+----------------------------------------+

Puedes usar esta cláusula WITH con el fin de emular un nombre de tabla temporal para los ejemplos de esta referencia:

WITH PlayerStats AS
 (SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 77, 0 UNION ALL
  SELECT 'Coolidge', 77, 1 UNION ALL
  SELECT 'Adams', 52, 4 UNION ALL
  SELECT 'Buchanan', 50, 13)
SELECT * FROM PlayerStats

Tabla TeamMascot

En la tabla TeamMascot, se incluye una lista de los ID escolares únicos (SchoolID) y la mascota de esa escuela (Mascot).

+---------------------+
| SchoolID | Mascot   |
+---------------------+
| 50       | Jaguars  |
| 51       | Knights  |
| 52       | Lakers   |
| 53       | Mustangs |
+---------------------+

Puedes usar esta cláusula WITH con el fin de emular un nombre de tabla temporal para los ejemplos de esta referencia:

WITH TeamMascot AS
 (SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
  SELECT 51, 'Knights' UNION ALL
  SELECT 52, 'Lakers' UNION ALL
  SELECT 53, 'Mustangs')
SELECT * FROM TeamMascot

Lista SELECT

SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }]
    { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...]

La lista SELECT define las columnas que mostrará la consulta. Las expresiones de la lista SELECT se pueden referir a columnas en cualquiera de los from_item en su cláusula FROM correspondiente.

Cada elemento de la lista SELECT es uno de los siguientes:

  • *
  • expression
  • expression.*

SELECT *

SELECT *, conocida como seleccionar Destacar, genera una columna de salida para cada columna que es visible después de ejecutar la consulta completa.

SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);

+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot    |
+-------+-----------+

expression de SELECT

Los elementos en una lista SELECT pueden ser expresiones. Estas expresiones se evalúan en un solo valor y producen una columna de salida, con un alias explícito opcional.

Si la expresión no tiene un alias explícito, de ser posible, recibe un alias implícito según las reglas de los alias implícitos. De lo contrario, la columna será anónima y no se podrá hacer referencia a ella por su nombre en ninguna otra parte de la consulta.

expression.* de SELECT

Un elemento de una lista SELECT también puede adoptar la forma de expression.*. Esto produce una columna de salida para cada columna o campo de nivel superior expression. La expresión debe ser un alias de tabla o evaluar un solo valor de un tipo de datos con campos, como un STRUCT.

La siguiente consulta genera una columna de salida para cada columna en la tabla groceries, con un alias g.

WITH groceries AS
  (SELECT "milk" AS dairy,
   "eggs" AS protein,
   "bread" AS grain)
SELECT g.*
FROM groceries AS g;

+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk  | eggs    | bread |
+-------+---------+-------+

Más ejemplos:

WITH locations AS
  (SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
  UNION ALL
  SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
| Phoenix | Arizona    |
+---------+------------+
WITH locations AS
  (SELECT ARRAY<STRUCT<city STRING, state STRING>>[("Seattle", "Washington"),
    ("Phoenix", "Arizona")] AS location)
SELECT l.LOCATION[offset(0)].*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
+---------+------------+

Modificadores para el operador *

SELECT * EXCEPT

Una declaración SELECT * EXCEPT especifica los nombres de una o más columnas para que se excluyan del resultado. Todos los nombres de columna que coincidan se omiten de la salida.

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;

+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket  | 200      |
+-----------+----------+

SELECT * REPLACE

Una declaración SELECT * REPLACE especifica una o más cláusulas expression AS identifier. Cada identificador debe coincidir con el nombre de una columna de la declaración SELECT *. En la lista de las columnas de salida, la columna que coincide con el identificador en una cláusula REPLACE se reemplaza por la expresión en esa cláusula REPLACE.

Una declaración SELECT * REPLACE no cambia los nombres ni el orden de las columnas. Sin embargo, puede cambiar el valor y el tipo de valor.

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | widget    | 200      |
+----------+-----------+----------+

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | sprocket  | 100      |
+----------+-----------+----------+

Manejo de filas duplicadas

Puedes modificar los resultados que se muestran de una consulta SELECT, de la siguiente manera.

SELECT DISTINCT

Una declaración SELECT DISTINCT descarta las filas duplicadas y muestra solo las filas restantes. SELECT DISTINCT no puede mostrar las columnas de los siguientes tipos:

  • STRUCT
  • ARRAY

SELECT ALL

Una declaración SELECT ALL muestra todas las filas, incluidas las filas duplicadas. SELECT ALL es el comportamiento predeterminado de SELECT.

Usa STRUCT con SELECT

  • Las consultas que muestran un STRUCT en la raíz del tipo de datos que se muestra no son compatibles con las API de Cloud Spanner. Por ejemplo, la siguiente consulta es compatible solo como una subconsulta:

    SELECT STRUCT(1, 2) FROM Users;
    
  • Se admite que se muestre un array de structs como resultado. Por ejemplo, las consultas siguientes son compatibles con las API de Cloud Spanner:

    SELECT ARRAY(SELECT STRUCT(1 AS A, 2 AS B)) FROM Users;
    
    SELECT ARRAY(SELECT AS STRUCT 1 AS a, 2 AS b) FROM Users;
    
  • Sin embargo, las formas de consulta que pueden mostrar un valor NULL de tipo ARRAY<STRUCT<...>> o un valor de tipo ARRAY<STRUCT<...>> con un elemento que es NULL no son compatibles con las API de Cloud Spanner, por lo que la siguiente consulta es compatible solo como subconsulta:

    SELECT ARRAY(SELECT IF(STARTS_WITH(Users.username, "a"), NULL, STRUCT(1, 2)))
    FROM Users;
    

Lee Consulta elementos STRUCT en un ARRAY para obtener más ejemplos sobre cómo consultar STRUCTs dentro de un ARRAY.

Consulta también las notas sobre el uso de STRUCTs en subconsultas.

Tablas de valores

En SQL estándar de Google, una tabla de valores es una tabla en la que el tipo de fila es un valor único. En una tabla normal, cada fila está compuesta por columnas, cada una de las cuales tiene un nombre y un tipo. En una tabla de valores, el tipo de fila es solo un valor único y no existen nombres de columna.

En Cloud Spanner, las tablas de valores se usan principalmente como el resultado del operador UNNEST o una subconsulta. La cláusula WITH presenta una tabla de valores si la subconsulta usada produce una tabla de valores. Cloud Spanner no admite tablas de valores como tablas base en esquemas de bases de datos y no admite que se muestren tablas de valores en los resultados de las consultas. Como consecuencia, las consultas que producen tablas de valores no se admiten como consultas de nivel superior.

En contextos en los que se espera una consulta de solo una columna, se puede usar una consulta de tabla de valores en su lugar. Por ejemplo, las subconsultas escalares y las subconsultas de arreglo (consulta las Subconsultas), por lo general, requieren una consulta de una sola columna, pero en SQL estándar de Google, también permiten usar una consulta de tabla de valores.

Una consulta producirá una tabla de valores si usa SELECT AS mediante una de las siguientes sintaxis:

SELECT AS STRUCT

SELECT AS STRUCT expr [[AS] struct_field_name1] [,...]

Esto produce una tabla de valores con un tipo de fila STRUCT, en el que los nombres y tipos de campo STRUCT coinciden con los nombres y tipos de columnas generados en la lista SELECT.

Ejemplo:

SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)

SELECT AS STRUCT se puede usar en una subconsulta escalar o de arreglo para producir un solo tipo de STRUCT que agrupe varios valores. Las subconsultas escalares y de array (consulta las Subconsultas), por lo general, no pueden mostrar varias columnas, pero pueden mostrar una sola columna con el tipo STRUCT.

Se permiten columnas anónimas.

Ejemplo:

SELECT AS STRUCT 1 x, 2, 3

La consulta anterior produce valores de STRUCT del tipo STRUCT<int64 x, int64, int64>.. El primer campo tiene el nombre x, mientras que el segundo y tercer campo son anónimos.

En el ejemplo anterior, se produce el mismo resultado que con esta consulta de SELECT AS VALUE mediante un constructor de struct:

SELECT AS VALUE STRUCT(1 AS x, 2, 3)

Se permiten columnas duplicadas.

Ejemplo:

SELECT AS STRUCT 1 x, 2 y, 3 x

La consulta anterior produce valores de STRUCT del tipo STRUCT<int64 x, int64 y, int64 x>.. El primer y tercer campo tienen el mismo nombre x, mientras que el segundo campo tiene el nombre y.

El ejemplo anterior genera el mismo resultado que esta consulta SELECT AS VALUE mediante un constructor de struct:

SELECT AS VALUE STRUCT(1 AS x, 2 AS y, 3 AS x)

SELECT AS VALUE

SELECT AS VALUE genera una tabla de valores de cualquier lista SELECT que produce exactamente una columna. En lugar de producir una tabla de salida de una columna, posiblemente con un nombre, la salida será una tabla de valores en la que el tipo de fila es solo el tipo de valor que se generó en la única columna SELECT. Cualquier alias que tuviera la columna se descartará en la tabla de valores.

Ejemplo:

SELECT AS VALUE 1

La consulta anterior produce una tabla con el tipo de fila INT64.

Ejemplo:

SELECT AS VALUE STRUCT(1 AS a, 2 AS b) xyz

La consulta anterior produce una tabla con el tipo de fila STRUCT<a int64, b int64>.

Ejemplo:

SELECT AS VALUE v FROM (SELECT AS STRUCT 1 a, true b) v WHERE v.b

Con una tabla de valores v como entrada, la consulta anterior filtra ciertos valores en la cláusula WHERE y, luego, produce una tabla de valores con el mismo valor que estaba en la tabla de entrada. Si no se usó SELECT AS VALUE en la consulta anterior, el esquema de la tabla de salida sería distinto del esquema de la tabla de entrada porque la tabla de salida sería una tabla normal con una columna llamada v que contiene el valor de entrada.

Alias

Consulta Usa alias para obtener información sobre la sintaxis y la visibilidad de los alias de la lista SELECT.

Cláusula FROM

FROM from_clause[, ...]

from_clause:
    from_item
    [ tablesample_operator ]

from_item:
    {
      table_name [ table_hint_expr ] [ as_alias ]
      | { join_operation | ( join_operation ) }
      | ( query_expr ) [ table_hint_expr ] [ as_alias ]
      | field_path
      | unnest_operator
      | cte_name [ table_hint_expr ] [ as_alias ]
    }

table_hint_expr:
    '@{' table_hint_key = table_hint_value '}'

table_hint_key:
    {
      FORCE_INDEX
      | GROUPBY_SCAN_OPTIMIZATION
    }

as_alias:
    [ AS ] alias

La cláusula FROM indica la tabla o tablas de las cuales se pueden recuperar filas y especifica cómo unir esas filas a fin de producir una sola secuencia de filas para procesar en el resto de la consulta.

tablesample_operator

Consulta Operador TABLESAMPLE.

table_name

El nombre de una tabla existente.

SELECT * FROM Roster;
Sugerencias de tablas

Las siguientes sugerencias son compatibles con las tablas:

Sugerencia de clave Valores posibles Descripción
FORCE_INDEX String. El nombre de un índice existente en la base de datos o _BASE_TABLE para usar la tabla base en lugar de un índice.
  • Si se establece en el nombre de un índice, usa ese índice en lugar de la tabla base. Si el índice no puede proporcionar todas las columnas necesarias, realiza una unión posterior con la tabla base.
  • Si se establece en la string _BASE_TABLE, usa la tabla base para la estrategia de índice en lugar de un índice. Ten en cuenta que este es el único valor válido cuando se usa FORCE_INDEX en una expresión de sugerencia de instrucción.

Nota: En realidad, FORCE_INDEX es una directiva, no una sugerencia, lo que significa que se genera un error si el índice no existe.

GROUPBY_SCAN_OPTIMIZATION TRUE
FALSE

La optimización de análisis de agrupación puede acelerar las consultas si se usa GROUP BY o SELECT DISTINCT. Se puede aplicar si las claves de agrupación pueden formar un prefijo de la tabla subyacente o la clave de índice, y si la consulta requiere solo la primera fila de cada grupo.

La optimización se aplica si el optimizador estima que esto hará que la consulta sea más eficiente. La sugerencia anula esa decisión. Si la sugerencia se establece en FALSE, la optimización no se considera. Si la sugerencia se establece en TRUE, la optimización se aplicará siempre y cuando sea legal hacerlo.

En el siguiente ejemplo, se muestra cómo usar un índice secundario cuando se lee desde una tabla, mediante la adición de una directiva de índice del formulario @{FORCE_INDEX=index_name} al nombre de la tabla:

SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo
FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s
WHERE s.FirstName = "Catalina" AND s.LastName > "M";

Puedes incluir varios índices en una consulta, aunque solo se admite un único índice para cada referencia de tabla. Ejemplo:

SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo, c.ConcertDate
FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s JOIN
     Concerts@{FORCE_INDEX=ConcertsBySingerId} AS c ON s.SingerId = c.SingerId
WHERE s.FirstName = "Catalina" AND s.LastName > "M";

Obtén más información sobre las directivas de índice en la sección sobre índices secundarios.

join_operation

Consulta Operación JOIN.

query_expr

( query_expr ) [ [ AS ] alias ] es una subconsulta de tabla.

field_path

En la cláusula FROM, field_path es cualquier ruta que se resuelva en un campo dentro de un tipo de datos. field_path puede analizar una estructura de datos anidada, de manera arbitraria, en más profundidad.

Estos son algunos ejemplos de valores válidos de field_path:

SELECT * FROM T1 t1, t1.array_column;

SELECT * FROM T1 t1, t1.struct_column.array_field;

SELECT (SELECT ARRAY_AGG(c) FROM t1.array_column c) FROM T1 t1;

SELECT a.struct_field1 FROM T1 t1, t1.array_of_structs a;

SELECT (SELECT STRING_AGG(a.struct_field1) FROM t1.array_of_structs a) FROM T1 t1;

Las rutas de campo en la cláusula FROM deben finalizar en un campo de arreglo. Además, las rutas de campo no pueden contener arreglos antes del final de la ruta de acceso. Por ejemplo, la ruta array_column.some_array.some_array_field no es válida, ya que contiene un arreglo antes del final de la ruta de acceso.

unnest_operator

Consulta Operador UNNEST.

cte_name

Las expresiones de tabla comunes (CTE) en una cláusula WITH actúan como tablas temporales a las que puedes hacer referencia en cualquier parte de la cláusula FROM. En el siguiente ejemplo, subQ1 y subQ2 son CTE.

Ejemplo:

WITH
  subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
  subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;

La cláusula WITH oculta las tablas permanentes con el mismo nombre mientras dura la consulta, a menos que califiques el nombre de la tabla, por ejemplo:

db.Roster.

Operador UNNEST

unnest_operator:
    {
      UNNEST( array_expression )
      | UNNEST( array_path )
      | array_path
    }
    [ table_hint_expr ]
    [ as_alias ]
    [ WITH OFFSET [ as_alias ] ]

El operador UNNEST toma un ARRAY y muestra una tabla con una fila para cada elemento en el ARRAY. También puedes usar UNNEST fuera de la cláusula FROM con el operador IN.

Para los ARRAY de entrada de la mayoría de los tipos de elementos, la salida de UNNEST, por lo general, tiene una columna. Esta columna única tiene un alias opcional, que puedes usar para referirte a la columna en otras partes de la consulta. Los ARRAYS con estos tipos de elementos muestran varias columnas:

  • STRUCT

UNNEST destruye el orden de los elementos en el ARRAY de entrada. Usa la cláusula WITH OFFSET opcional para mostrar una segunda columna con los índices de los elementos del arreglo (consulta la información que se detalla a continuación).

Consulta Working with arrays para obtener más información sobre las formas de usar UNNEST, incluida la construcción, la compactación y el filtrado.

UNNEST y STRUCTS

Para un ARRAY de entrada de STRUCT, UNNEST muestra una fila de cada STRUCT, con una columna aparte de cada campo en STRUCT. El alias de cada columna es el nombre del campo STRUCT correspondiente.

Ejemplo:

SELECT *
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')]);

+---+-----+
| x | y   |
+---+-----+
| 3 | bar |
| 1 | foo |
+---+-----+

Como el operador UNNEST muestra una tabla de valores, puedes ponerle un alias a UNNEST para definir una variable de rango a la que puedas hacer referencia en otra parte de la consulta. Si haces referencia a la variable de rango en la lista SELECT, la consulta muestra una STRUCT que contiene todos los campos de la STRUCT original en la tabla de entrada.

Ejemplo:

SELECT *, struct_value
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')])
       AS struct_value;

+---+-----+--------------+
| x | y   | struct_value |
+---+-----+--------------+
| 3 | bar | {3, bar}     |
| 1 | foo | {1, foo}     |
+---+-----+--------------+

UNNEST explícito e implícito

El desanidado de ARRAY puede ser explícito o implícito. En el desanidado explícito, array_expression debe mostrar un valor ARRAY, pero no es necesario que se resuelva en un ARRAY y, además, se requiere la palabra clave UNNEST.

Ejemplo:

SELECT * FROM UNNEST ([1, 2, 3]);

En el desanidado implícito, array_path debe resolverse en un ARRAY y la palabra clave UNNEST es opcional.

Ejemplo:

SELECT x
FROM mytable AS t,
  t.struct_typed_column.array_typed_field1 AS x;

En este caso, array_path puede analizar de manera arbitraria y en detalle una estructura de datos, pero el último campo debe ser de tipo ARRAY. Ningún campo anterior en la expresión puede ser de tipo ARRAY, ya que no es posible extraer un campo con nombre de un ARRAY.

UNNEST y NULLS

UNNEST trata los NULL de la siguiente manera:

  • Los arreglos NULL y vacíos no producen filas.
  • Un arreglo que contiene valores NULL produce filas que contienen valores NULL.

La cláusula opcional WITH OFFSET muestra una columna aparte que contiene el valor de “desplazamiento” (es decir, el conteo comienza en cero) de cada fila que genera la operación UNNEST. Esta columna tiene un alias opcional. El alias predeterminado es el desplazamiento.

Ejemplo:

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

Operador TABLESAMPLE

tablesample_clause:
    TABLESAMPLE sample_method (sample_size percent_or_rows )

sample_method:
    { BERNOULLI | RESERVOIR }

sample_size:
    numeric_value_expression

percent_or_rows:
    { PERCENT | ROWS }

partition_by:
    PARTITION BY partition_expression [, ...]

Descripción

Puedes usar el operador TABLESAMPLE para seleccionar una muestra aleatoria de un conjunto de datos. Este operador es útil cuando trabajas con tablas que tienen grandes cantidades de datos y no necesitas respuestas precisas.

  • sample_method: Cuando uses el operador TABLESAMPLE, debes especificar el algoritmo de muestreo que usarás:
    • BERNOULLI: Cada fila se selecciona de forma independiente con la probabilidad determinada en la cláusula percent. Como resultado, obtienes una cantidad aproximada de N * percent/100 de filas.
    • RESERVOIR: Toma como parámetro un valor K de tamaño de muestra real (expresado como un número de filas). Si la entrada es menor que K, se muestra toda la relación de entrada. Si la entrada es mayor que K, el muestreo de reservoir exhibe una muestra de tamaño igual a K, en la que cualquier muestra de tamaño K es igual de probable.
  • sample_size: Es el tamaño de la muestra.
  • percent_or_rows: El operador TABLESAMPLE requiere que elijas ROWS o PERCENT. Si eliges PERCENT, el valor debe estar entre 0 y 100. Si eliges ROWS, el valor debe ser mayor o igual que 0.

Ejemplos

En los siguientes ejemplos, se ilustra el uso del operador TABLESAMPLE.

Selecciona de una tabla mediante el método de muestreo RESERVOIR:

SELECT MessageId
FROM Messages TABLESAMPLE RESERVOIR (100 ROWS);

Selecciona de una tabla mediante el método de muestreo BERNOULLI:

SELECT MessageId
FROM Messages TABLESAMPLE BERNOULLI (0.1 PERCENT);

Usa TABLESAMPLE con una subconsulta:

SELECT Subject FROM
(SELECT MessageId, Subject FROM Messages WHERE ServerId="test")
TABLESAMPLE BERNOULLI(50 PERCENT)
WHERE MessageId > 3;

Usa una operación TABLESAMPLE con una unión a otra tabla.

SELECT S.Subject
FROM
(SELECT MessageId, ThreadId FROM Messages WHERE ServerId="test") AS R
TABLESAMPLE RESERVOIR(5 ROWS),
Threads AS S
WHERE S.ServerId="test" AND R.ThreadId = S.ThreadId;

Operación JOIN

join_operation:
    { cross_join_operation | condition_join_operation }

cross_join_operation:
    from_item cross_join_operator [ join_hint_expr ] from_item

condition_join_operation:
    from_item condition_join_operator [ join_hint_expr ] from_item join_condition

cross_join_operator:
    { CROSS JOIN | , }

condition_join_operator:
    {
      [INNER] [ join_method ] JOIN
      | FULL [OUTER] [ join_method ] JOIN
      | LEFT [OUTER] [ join_method ] JOIN
      | RIGHT [OUTER] [ join_method ] JOIN
    }

join_method:
    { HASH }

join_hint_expr:
    '@{' join_hint_key = join_hint_value [, ...] '}'

join_hint_key:
    { FORCE_JOIN_ORDER | JOIN_METHOD }

join_condition:
    { on_clause | using_clause }

on_clause:
    ON bool_expression

using_clause:
    USING ( join_column [, ...] )

La cláusula JOIN combina dos from_item para que la cláusula SELECT pueda consultarlos como una sola fuente. La cláusula join_type y ON o USING (una “condición JOIN”) especifica cómo combinar y descartar las filas de los dos from_item para formar una sola fuente.

Sugerencias de uniones

Las siguientes sugerencias son compatibles con JOIN:

Sugerencia de clave Valores posibles Descripción
FORCE_JOIN_ORDER TRUE
FALSE (valor predeterminado)
Si se configura como verdadero, usa el orden de uniones que se especifica en la consulta.
JOIN_METHOD HASH_JOIN
APPLY_JOIN
MERGE_JOIN
PUSH_BROADCAST_HASH_JOIN
Cuando implementes una unión lógica, elige una alternativa específica para usar en el método de unión subyacente. Obtén más información en Métodos de unión.
Para usar una unión HASH, usa HASH JOIN o JOIN@{JOIN_METHOD=HASH_JOIN}, pero no ambos.
HASH_JOIN_BUILD_SIDE BUILD_LEFT
BUILD_RIGHT
Especifica qué lado de la unión hash se usa como el lado de la compilación. Solo se puede usar con JOIN_METHOD=HASH_JOIN
BATCH_MODE TRUE (default)
FALSE
Se usa para inhabilitar la unión aplicada por lotes a favor de la unión de filas a la vez. Solo se puede usar con JOIN_METHOD=APPLY_JOIN.

Métodos de unión

Los métodos de unión son implementaciones específicas de los distintos tipos de unión lógica. Algunos métodos de unión están disponibles solo para ciertos tipos de unión. La elección del método de unión que se usará depende de los detalles de tu consulta y de los datos que se consulten. La mejor manera de saber si un método de unión en particular contribuye al rendimiento de tu consulta es probar el método y ver el plan de ejecución de consultas resultante. Consulta Operadores de ejecución de consultas para obtener más detalles.

Método de unión Descripción Operandos
HASH_JOIN El operador de unión hash compila una tabla de hash de un lado (el lado de la compilación) y sondea la tabla de hash para todos los elementos del otro lado (el lado del sondeo). Para diferentes tipos de unión, se usan distintos tipos de variantes. Consulta el plan de ejecución de consultas para tu consulta a fin de ver qué variante se usa. Obtén más información sobre el operador de unión hash.
APPLY_JOIN El operador de unión apply obtiene cada elemento de un lado (el lado de entrada) y evalúa la subconsulta en el otro lado (el lado de la asignación) mediante los valores del elemento del lado de entrada. Para diferentes tipos de unión, se usan distintos tipos de variantes. El operador de aplicación cruzada (cross apply) se usa para la unión interna, y el operador de aplicación externa (outer apply) se usa para las uniones izquierdas. Obtén más información sobre los operadores de aplicación cruzada y aplicación externa.
MERGE_JOIN El operador de unión combinada combina dos transmisiones de datos ordenados. El optimizador agregará operadores de ordenamiento al plan si los datos aún no proporcionan la propiedad de orden requerida para la condición de unión determinada. El motor proporciona un orden de combinación distribuido de forma predeterminada, que, cuando se combina con la unión de combinación, puede permitir uniones más grandes, lo que puede evitar desborde de discos y mejorar el escalamiento y la latencia. Para diferentes tipos de unión, se usan distintos tipos de variantes. Consulta el plan de ejecución de consultas para tu consulta a fin de ver qué variante se usa. Obtén más información sobre el operador de unión de combinación.
PUSH_BROADCAST_HASH_JOIN El operador de unión de hash de transmisión push crea un lote de datos desde el lado de la compilación de la unión. El lote se envía en paralelo a todas las divisiones locales del lado del sondeo de la unión. En cada uno de los servidores locales, se ejecuta una unión de hash entre el lote y los datos locales. Es más probable que esta unión sea beneficiosa cuando la entrada puede caber en un lote, pero no es estricta. Otra área posible de beneficio es cuando las operaciones se pueden distribuir a los servidores locales, como una agregación que se produce después de una unión. Una unión de hash de transmisión push puede distribuir alguna agregación en la que una unión hash tradicional no puede hacerlo. Para diferentes tipos de unión, se usan distintos tipos de variantes. Consulta el plan de ejecución de consultas para tu consulta a fin de ver qué variante se usa. Obtén más información sobre el operador de unión de hash de transmisión push.

[INNER] JOIN

Una INNER JOIN, o tan solo JOIN, calcula de manera efectiva el producto cartesiano de los dos from_item y descarta todas las filas que no cumplen con la condición de unión. “De manera efectiva” significa que es posible implementar INNER JOIN sin calcular el producto cartesiano.

FROM A INNER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | k |     | 2 | b | 2 | k |
| 2 | b |     | 3 | m |     | 3 | c | 3 | m |
| 3 | c |     | 3 | n |     | 3 | c | 3 | n |
| 3 | d |     | 4 | p |     | 3 | d | 3 | m |
+-------+     +-------+     | 3 | d | 3 | n |
                            +---------------+
FROM A INNER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +-----------+
| x | y |  *  | x | z |  =  | x | y | z |
+-------+     +-------+     +-----------+
| 1 | a |     | 2 | k |     | 2 | b | k |
| 2 | b |     | 3 | m |     | 3 | c | m |
| 3 | c |     | 3 | n |     | 3 | c | n |
| 3 | d |     | 4 | p |     | 3 | d | m |
+-------+     +-------+     | 3 | d | n |
                            +-----------+

Ejemplo

Mediante esta consulta, se realiza una INNER JOIN en las tablas Roster y TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

CROSS JOIN

CROSS JOIN muestra el producto cartesiano de los dos from_item. En otras palabras, combina cada fila del primer from_item con cada fila del segundo from_item.

Si las filas de los dos from_item son independientes, el resultado tiene M x N filas, si es que hay M filas en un from_item y N en el otro. Ten en cuenta que esto se mantiene incluso si cualquiera de los dos from_item tiene cero filas.

En una cláusula FROM, se puede escribir una CROSS JOIN como la siguiente:

FROM A CROSS JOIN B

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+

Puedes usar una unión cruzada correlacionada para convertir o compactar un ARRAY en un conjunto de filas. Para obtener más información, consulta Convierte elementos de un arreglo en filas de una tabla.

Ejemplos

Mediante esta consulta, se realiza una CROSS JOIN en las tablas Roster y TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Adams      | Knights      |
| Adams      | Lakers       |
| Adams      | Mustangs     |
| Buchanan   | Jaguars      |
| Buchanan   | Knights      |
| Buchanan   | Lakers       |
| Buchanan   | Mustangs     |
| ...                       |
+---------------------------+

Combinación cruzada de comas (,)

Las CROSS JOIN se pueden escribir de forma implícita con una coma. Esto se conoce como una unión cruzada con comas.

Una unión cruzada con coma se ve de la siguiente manera en una cláusula FROM:

FROM A, B

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+

No puedes escribir CROSS JOIN con comas entre paréntesis. Para obtener más información, consulta Operaciones de unión en una secuencia.

FROM (A, B)  // INVALID

Puedes usar una unión cruzada con coma correlacionada para convertir o compactar un ARRAY en un conjunto de filas. Para obtener más información, consulta Convierte elementos de un arreglo en filas de una tabla.

Ejemplos

Esta consulta realiza una unión cruzada con coma en las tablas Roster y TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Adams      | Knights      |
| Adams      | Lakers       |
| Adams      | Mustangs     |
| Buchanan   | Jaguars      |
| Buchanan   | Knights      |
| Buchanan   | Lakers       |
| Buchanan   | Mustangs     |
| ...                       |
+---------------------------+

FULL [OUTER] JOIN

Una FULL OUTER JOIN (o simplemente FULL JOIN) muestra todos los campos de todas las filas de ambos from_item que cumplen con la condición JOIN.

FULL indica que todas las filas de ambos from_item se muestran, incluso si no cumplen con la condición JOIN.

OUTER indica que si una fila determinada de un from_item no se une a ninguna fila del otro from_item, la fila mostrará NULL para todas las columnas del otro from_item.

FROM A FULL OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+
FROM A FULL OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

Ejemplo

Mediante esta consulta, se realiza una FULL JOIN en las tablas Roster y TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster FULL JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
| NULL       | Mustangs     |
+---------------------------+

LEFT [OUTER] JOIN

El resultado de una LEFT OUTER JOIN (o tan solo LEFT JOIN) para dos from_item siempre conserva todas las filas del from_item de la izquierda en la cláusula JOIN, incluso si ninguna fila en el from_item de la derecha satisface el predicado de JOIN.

LEFT indica que se muestran todas las filas from_item de la izquierda. Si una fila determinada del from_item de la izquierda no se une a ninguna fila del from_item de la derecha, se mostrarán en la fila todas las columnas del from_item de la derecha como NULL. Las filas del from_item de la derecha que no se unen a ninguna fila del from_item de la izquierda se descartan.

FROM A LEFT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            +---------------------------+
FROM A LEFT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            +--------------------+

Ejemplo

Mediante esta consulta, se realiza una LEFT JOIN en las tablas Roster y TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster LEFT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
+---------------------------+

RIGHT [OUTER] JOIN

El resultado de una RIGHT OUTER JOIN (o tan solo RIGHT JOIN) es similar y simétrico al de una LEFT OUTER JOIN.

FROM A RIGHT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 2    | b    | 2    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | 3    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | 3    | m    |
+-------+     +-------+     | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+
FROM A RIGHT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 2    | b    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | m    |
+-------+     +-------+     | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

Ejemplo

Mediante esta consulta, se realiza una RIGHT JOIN en las tablas Roster y TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster RIGHT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| NULL       | Mustangs     |
+---------------------------+

Cláusula ON

Una fila combinada (el resultado de la unión de dos filas) cumple con la condición de unión ON si la condición de unión muestra TRUE.

FROM A JOIN B ON A.x = B.x

Table A   Table B   Result (A.x, B.x)
+---+     +---+     +-------+
| x |  *  | x |  =  | x | x |
+---+     +---+     +-------+
| 1 |     | 2 |     | 2 | 2 |
| 2 |     | 3 |     | 3 | 3 |
| 3 |     | 4 |     +-------+
+---+     +---+

Ejemplo

Mediante esta consulta, se realiza una INNER JOIN en las tablas Roster y TeamMascot.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

Cláusula USING

La cláusula USING requiere una lista de columnas de una o más columnas que aparecen en ambas tablas de entrada. Realiza una comparación de igualdad en esa columna, y las filas cumplen la condición de unión si la comparación de igualdad muestra TRUE.

FROM A JOIN B USING (x)

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

Ejemplo

Mediante esta consulta, se realiza una INNER JOIN en las tablas Roster y TeamMascot.

Mediante esta declaración, se muestran las filas de Roster y TeamMascot en las que Roster.SchooldID es igual a TeamMascot.SchooldID. En los resultados, se incluye una sola columna SchooldID.

SELECT * FROM Roster INNER JOIN TeamMascot USING (SchoolID);

+----------------------------------------+
| SchoolID   | LastName   | Mascot       |
+----------------------------------------+
| 50         | Adams      | Jaguars      |
| 52         | Buchanan   | Lakers       |
| 52         | Coolidge   | Lakers       |
| 51         | Davis      | Knights      |
+----------------------------------------+

Equivalencia entre USING y ON

Las palabras clave USING y ON no son equivalentes, pero son similares. Mediante ON, se muestran varias columnas, y mediante USING, se muestra una.

FROM A JOIN B ON A.x = B.x
FROM A JOIN B USING (x)

Table A   Table B   Result ON     Result USING
+---+     +---+     +-------+     +---+
| x |  *  | x |  =  | x | x |     | x |
+---+     +---+     +-------+     +---+
| 1 |     | 2 |     | 2 | 2 |     | 2 |
| 2 |     | 3 |     | 3 | 3 |     | 3 |
| 3 |     | 4 |     +-------+     +---+
+---+     +---+

Si bien ON y USING no son equivalentes, pueden mostrar los mismos resultados si especificas las columnas que deseas mostrar.

SELECT x FROM A JOIN B USING (x);
SELECT A.x FROM A JOIN B ON A.x = B.x;

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

Operaciones de unión en una secuencia

La cláusula FROM puede contener varias cláusulas JOIN en una secuencia. Las JOIN están vinculadas de izquierda a derecha. Por ejemplo:

FROM A JOIN B USING (x) JOIN C USING (x)

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

También puedes insertar paréntesis para agrupar las JOIN:

FROM ( (A JOIN B USING (x)) JOIN C USING (x) )

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

Con paréntesis, puedes agrupar JOIN para que estén vinculadas en un orden diferente:

FROM ( A JOIN (B JOIN C USING (x)) USING (x) )

-- B JOIN C USING (x)       = result_1
-- A JOIN result_1          = result_2
-- result_2                 = return value

Cuando hay uniones cruzadas con comas en una consulta que tiene una secuencia de JOIN, se agrupan de izquierda a derecha como los otros tipos de JOIN:

FROM A JOIN B USING (x) JOIN C USING (x), D

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 CROSS JOIN D     = return value

No puede haber una RIGHT JOIN o una FULL JOIN después de una unión cruzada con coma, a menos que tenga paréntesis:

FROM A, B RIGHT JOIN C ON TRUE // INVALID
FROM A, B FULL JOIN C ON TRUE  // INVALID
FROM A, B JOIN C ON TRUE       // VALID
FROM A, (B RIGHT JOIN C ON TRUE) // VALID
FROM A, (B FULL JOIN C ON TRUE)  // VALID

Operación correlacionada de uniones

Una operación de unión está correlacionada cuando el from_item de la derecha contiene una referencia a, al menos, un nombre de columna o variable de rango presentado por el from_item de la izquierda.

En una operación de unión correlacionada, las filas del from_item derecho se determinan por una fila del from_item izquierdo. En consecuencia, las uniones RIGHT OUTER y FULL OUTER no se pueden correlacionar porque las filas from_item derecha no se pueden determinar en el caso cuando no hay una fila del from_item izquierdo.

Todas las operaciones de unión correlacionadas deben hacer referencia a un arreglo en el from_item derecho.

Este es un ejemplo conceptual de una operación de unión correlacionada que incluye una subconsulta correlacionada:

FROM A JOIN UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C
  • from_item izquierdo: A
  • from_item derecho: UNNEST(...) AS C
  • Una subconsulta correlacionada: (SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)

Este es otro ejemplo conceptual de una operación de unión correlacionada. array_of_IDs es parte del from_item izquierdo, pero se hace referencia a él en el from_item derecho.

FROM A JOIN UNNEST(A.array_of_IDs) AS C

El operador UNNEST puede ser explícito o implícito. Ambos se permiten:

FROM A JOIN UNNEST(A.array_of_IDs) AS IDs
FROM A JOIN A.array_of_IDs AS IDs

En una operación de unión correlacionada, el from_item derecho se vuelve a evaluar en función de cada fila diferente del from_item izquierdo. En el siguiente ejemplo conceptual, la operación de unión correlacionada primero evalúa A y B, y, luego, A y C:

FROM
  A
  JOIN
  UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C
  ON A.Name = C.Name

Ejemplos

Este es un ejemplo de una unión correlacionada, mediante las tablas Roster y PlayerStats:

SELECT *
FROM
  Roster
JOIN
  UNNEST(
    ARRAY(
      SELECT AS STRUCT *
      FROM PlayerStats
      WHERE PlayerStats.OpponentID = Roster.SchoolID
    )) AS PlayerMatches
  ON PlayerMatches.LastName = 'Buchanan'

+------------+----------+----------+------------+--------------+
| LastName   | SchoolID | LastName | OpponentID | PointsScored |
+------------+----------+----------+------------+--------------+
| Adams      | 50       | Buchanan | 50         | 13           |
| Eisenhower | 77       | Buchanan | 77         | 0            |
+------------+----------+----------+------------+--------------+

Cláusula WHERE

WHERE bool_expression

La cláusula WHERE filtra los resultados de la cláusula FROM.

Solo se incluyen las filas cuya bool_expression se evalúa como TRUE. Se descartan las filas cuya bool_expression se evalúa como NULL o FALSE.

La evaluación de una consulta con una cláusula WHERE, por lo general, se completa en este orden:

  • FROM
  • WHERE
  • GROUP BY y agregación
  • HAVING
  • WINDOW
  • QUALIFY
  • DISTINCT
  • ORDER BY
  • LIMIT

La cláusula WHERE solo puede hacer referencia a columnas disponibles a través de la cláusula FROM; no puede hacer referencia a los alias de la lista SELECT.

Ejemplos

Esta consulta muestra todas las filas de la tabla Roster en las que la columna SchoolID tiene el valor 52:

SELECT * FROM Roster
WHERE SchoolID = 52;

La bool_expression puede contener varias subcondiciones.

SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");

Las expresiones en una INNER JOIN tienen una expresión equivalente en la cláusula WHERE. Por ejemplo, una consulta que usa INNER JOIN y ON tiene una expresión equivalente mediante CROSS JOIN y WHERE. Por ejemplo, las dos consultas siguientes son equivalentes:

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;

Cláusula GROUP BY

GROUP BY expression [, ...]

La cláusula GROUP BY agrupa las filas en una tabla con valores no distintos para la expression en la cláusula GROUP BY. Para varias filas en la tabla de origen con valores no distintos de la expression, la cláusula GROUP BY produce una única fila combinada. Por lo general, GROUP BY se usa cuando las funciones de agregación están presentes en la lista SELECT o para eliminar la redundancia en la salida. El tipo de datos de expression debe ser agrupable.

Ejemplo:

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;

La cláusula GROUP BY puede hacer referencia a nombres de expresiones en la lista SELECT. La cláusula GROUP BY también permite referencias ordinales a expresiones en la lista SELECT mediante valores de números enteros. 1 se refiere a la primera expresión en la lista SELECT, 2 a la segunda, y así sucesivamente. La lista de expresiones puede combinar ordinales y nombres de expresiones.

Ejemplo:

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY LastName, FirstName;

La consulta anterior es equivalente a lo siguiente:

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY 2, FirstName;

Las cláusulas GROUP BY pueden referirse también a los alias. Si una consulta contiene alias en la cláusula SELECT, esos alias anulan los nombres en la cláusula FROM correspondiente.

Ejemplo:

SELECT SUM(PointsScored), LastName as last_name
FROM PlayerStats
GROUP BY last_name;

Cláusula HAVING

HAVING bool_expression

La cláusula HAVING filtra los resultados producidos por GROUP BY o la agregación. GROUP BY o la agregación deben estar presentes en la consulta. Si la agregación está presente, la cláusula HAVING se evalúa una vez en cada fila agregada en el conjunto de resultados.

Solo se incluyen las filas cuya bool_expression se evalúa como TRUE. Se descartan las filas cuya bool_expression se evalúa como NULL o FALSE.

La evaluación de una consulta con una cláusula HAVING, por lo general, se completa en este orden:

  • FROM
  • WHERE
  • GROUP BY y agregación
  • HAVING
  • WINDOW
  • QUALIFY
  • DISTINCT
  • ORDER BY
  • LIMIT

La cláusula HAVING puede hacer referencia a columnas disponibles a través de la cláusula FROM, así como los alias de la lista SELECT. Las expresiones a las que se hace referencia en la cláusula HAVING deben aparecer en la cláusula GROUP BY o deben ser el resultado de una función de agregación:

SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

Si una consulta contiene alias en la cláusula SELECT, esos alias anulan los nombres en una cláusula FROM.

SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;

Agregación obligatoria

La agregación no tiene que estar presente en la cláusula HAVING, pero debe estar en al menos una de las siguientes formas:

Función de agregación en la lista SELECT.

SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;

Función de agregación en la cláusula “HAVING”.

SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

Agregación en la lista SELECT y en la cláusula HAVING.

Cuando las funciones de agregación están presentes en la lista SELECT y en la cláusula HAVING, no es necesario que estas funciones y las columnas a las que hacen referencia sean las mismas. En el siguiente ejemplo, las dos funciones de agregación, COUNT() y SUM(), son diferentes y también usan columnas diferentes.

SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

Cláusula ORDER BY

ORDER BY expression
  [COLLATE collate_string]
  [{ ASC | DESC }]
  [, ...]

La cláusula ORDER BY especifica una columna o expresión como criterio de clasificación para el conjunto de resultados. Si una cláusula ORDER BY no está presente, el orden de los resultados de una consulta no se define. Se permiten los alias de columna de una cláusula FROM o una lista SELECT. Si una consulta contiene alias en la cláusula SELECT, esos alias anulan los nombres en la cláusula FROM correspondiente. El tipo de datos de expression debe ser ordenable.

Cláusulas opcionales

  • COLLATE: Define mejor la forma en que se ordenan los datos.
  • ASC | DESC: Ordenan los resultados de los valores de expression en orden ascendente o descendente. ASC es el valor predeterminado.

Ejemplos

Para usar el orden de clasificación predeterminado (ascendente), haz lo siguiente:

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true)
ORDER BY x;
+------+-------+
| x    | y     |
+------+-------+
| 1    | true  |
| 9    | true  |
+------+-------+

Para usar el orden de clasificación descendente, haz lo siguiente:

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true)
ORDER BY x DESC;
+------+-------+
| x    | y     |
+------+-------+
| 9    | true  |
| 1    | true  |
+------+-------+

Es posible ordenar por varias columnas. En el siguiente ejemplo, el conjunto de resultados se ordena primero por SchoolID y, luego, por LastName:

SELECT LastName, PointsScored, OpponentID
FROM PlayerStats
ORDER BY SchoolID, LastName;

Las siguientes reglas se aplican cuando se ordenan los valores:

  • NULL: En el contexto de la cláusula ORDER BY, los NULL son el valor mínimo posible, es decir, los valores NULL aparecen primeros en ASC y últimos en DESC.
  • Tipos de datos de punto flotante: Consulta Semántica de punto flotante sobre cómo ordenar y agrupar.

Cuando se usa junto con los operadores de conjuntos, la cláusula ORDER BY se aplica al conjunto de resultados de toda la consulta, no solo a la declaración SELECT más cercana. Por esta razón, puede ser útil (aunque no es obligatorio) usar paréntesis para mostrar el alcance de ORDER BY.

Esta consulta sin paréntesis:

SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot
ORDER BY SchoolID;

es equivalente a esta consulta con paréntesis:

( SELECT * FROM Roster
  UNION ALL
  SELECT * FROM TeamMascot )
ORDER BY SchoolID;

pero no a esta consulta, en la que la cláusula ORDER BY se aplica solo a la segunda declaración SELECT:

SELECT * FROM Roster
UNION ALL
( SELECT * FROM TeamMascot
  ORDER BY SchoolID );

También puedes usar literales de números enteros como referencias de columna en las cláusulas ORDER BY. Un literal de número entero se convierte en un ordinal (por ejemplo, el conteo comienza en 1) en la lista SELECT.

Por ejemplo, las dos consultas siguientes son equivalentes:

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY 2
ORDER BY 2;

Cláusula COLLATE

COLLATE collate_string

collate_string:
  language_tag[:collation_attribute]

Puedes usar la cláusula COLLATE para definir mejor cómo se ordenan los datos de una cláusula ORDER BY. La intercalación hace referencia a un conjunto de reglas que determinan cómo se comparan las strings según las convenciones y estándares de un idioma, una región o un país en particular. Estas reglas pueden definir la secuencia de caracteres correcta, con opciones para especificar la distinción entre mayúsculas y minúsculas.

Una collate_string contiene un language_tag y puede tener un collation_attribute opcional como sufijo, separado por dos puntos.

language_tag es un parámetro literal o de consulta:

  • Una string de configuración regional estándar. Por lo general, este nombre consta de dos o tres letras que representan el idioma, seguidas opcionalmente de un guion bajo o guion y dos letras que representan la región, por ejemplo, en_US. El repositorio de datos de configuración regional común (CLDR) define estos nombres. Consulta la intercalación de Unicode a continuación.

  • und, una string de configuración regional que representa la configuración regional indeterminada Consulta la intercalación de Unicode a continuación.

  • unicode, consulta los detalles a continuación.

Además de language_tag, un collate_string puede tener un collation_attribute opcional como sufijo, separado por dos puntos. Los valores permitidos son los siguientes: + ci para distinción entre mayúsculas y minúsculas + cs para distinción entre mayúsculas y minúsculas (ten en cuenta que “cs” es la configuración predeterminada, por lo que especificarla nunca tiene efecto).

Intercalación Unicode

Para las language_tag que no sean unicode, SQL estándar de Google sigue el Algoritmo de intercalación de Unicode. El estándar define el formato de las etiquetas de lenguaje, que incluye algunas extensiones útiles y el algoritmo que se usa para la comparación.

und es una etiqueta de idioma especial definida en el registro de subetiquetas de idioma de IANA y se usa para indicar una configuración regional indeterminada. Esto también se conoce como la configuración regional raíz y se puede considerar la intercalación predeterminada de Unicode. Define una intercalación independiente y razonable. Difiere de unicode.

Se puede extender una language_tag agregando -u-<extension>; por ejemplo, la extensión para especificar el orden numérico es kn-true. Por lo tanto, en-us-u-kn-true indicaría la configuración regional de inglés de EE.UU., con orden numérico (abc1 se considera menor que abc12). Algunos ejemplos útiles de extensiones:

Extensión Nombre Ejemplo
-ks-level2 No distingue mayúsculas de minúsculas "a1" < "A2"
-ks-level1 acento y no distingue entre mayúsculas y minúsculas "ä1" < "a2" < "A3"
-ks-level1-kc-true Sin acento "ä1" < "a2"
-kn-verdadero Orden numérico “a1b” < “a12b”

Para obtener una lista completa y detalles técnicos detallados, consulta Lenguaje de marcado de datos de configuración regional de Unicode, parte 5: intercalación.

Advertencias:

  • Las strings diferentes se pueden considerar iguales: Por ejemplo, ẞ (LAT MAYLESCULA DE MAY)SCULAS SHARP S) se considera igual que "SS" en el nivel principal, de modo que "ẞ1" < "SS2". Este proceso es similar al funcionamiento de la distinción entre mayúsculas y minúsculas.

  • Puntos de código ignorables: La intercalación de Unicode especifica un amplio rango de puntos de código que se tratan principalmente como si no estuvieran allí. Por lo tanto, las strings con y sin ellas se ordenan de forma idéntica, por ejemplo, U2060 - "JOIN de WORD".

      SELECT "oran\u2060ge1" UNION ALL SELECT "\u2060orange2" UNION ALL SELECT "orange3"
      ORDER BY 1 COLLATE "und"
      +---------+
      |         |
      +---------+
      | orange1 |
      | orange2 |
      | orange3 |
      +---------+
    
  • El orden puede cambiar: en ocasiones, Unicode realiza cambios en la intercalación predeterminada ("und") que, en circunstancias excepcionales, podría cambiar el orden relativo de las strings. Los órdenes de clasificación para los idiomas que no sean "und" cambian con más frecuencia a medida que cambian los estándares o se recopila información nueva. Si se requiere un orden de clasificación fijo, usa unicode.

Además, se admite un language_tag de unicode:

  • unicode: Muestra datos en el orden de puntos de código Unicode, que es idéntico al comportamiento de ordenamiento cuando no se usa COLLATE. El orden de clasificación será mayormente arbitrario para los usuarios humanos.
  • unicode:cs: idéntico a unicode
  • unicode:ci: idéntico a und:ci

Ejemplos

Resultados de COLLATE con inglés de Canadá:

SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_CA"

Resultados de COLLATE con un parámetro:

#@collate_param = "arg_EG"
SELECT Place
FROM Locations
ORDER BY Place COLLATE @collate_param

Uso de varias cláusulas COLLATE en una instrucción:

SELECT APlace, BPlace, CPlace
FROM Locations
ORDER BY APlace COLLATE "en_US" ASC,
         BPlace COLLATE "ar_EG" DESC,
         CPlace COLLATE "en" DESC

Intercalación sin distinción entre mayúsculas y minúsculas:

SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_US:ci"

Intercalación predeterminada de Unicode con distinción entre mayúsculas y minúsculas:

SELECT Place
FROM Locations
ORDER BY Place COLLATE "und:ci"

Configurar operadores

set_operation:
  query_expr set_operator query_expr

set_operator:
  UNION { ALL | DISTINCT } | INTERSECT { ALL | DISTINCT } | EXCEPT { ALL | DISTINCT }

Los operadores de conjuntos combinan los resultados de dos o más consultas de entrada en un único conjunto de resultados. Debes especificar ALL o DISTINCT; si especificas ALL, se conservan todas las filas. Si especificas DISTINCT, se descartan las filas duplicadas.

Si una determinada fila R aparece exactamente m veces en la primera consulta de entrada y n veces en la segunda consulta de entrada (m> = 0, n> = 0):

  • En UNION ALL, R aparece exactamente m + n veces en el resultado.
  • Para INTERSECT ALL, R aparecerá exactamente como MIN(m, n) en el resultado.
  • Para EXCEPT ALL, R aparece exactamente como MAX(m - n, 0) en el resultado.
  • En UNION DISTINCT, DISTINCT se calcula después de que se calcula UNION, por lo que R aparece exactamente una vez.
  • En INTERSECT DISTINCT, DISTINCT se calcula después de que se calcula el resultado anterior.
  • En EXCEPT DISTINCT, la fila R aparece una vez en el resultado si m > 0 y n = 0.
  • Si hay más de dos consultas de entrada, las operaciones anteriores se generalizan y la salida es la misma que si las entradas se combinaran de forma incremental de izquierda a derecha.

Se aplican las siguientes reglas:

  • Para las operaciones de conjuntos distintas de UNION ALL, todos los tipos de columna deben admitir la comparación de igualdad.
  • Las consultas de entrada a cada lado del operador deben mostrar la misma cantidad de columnas.
  • Los operadores sincronizan las columnas que se muestran por cada consulta de entrada de acuerdo con las posiciones de las columnas en sus respectivas listas SELECT. Es decir, la primera columna en la primera consulta de entrada se sincroniza con la primera columna en la segunda consulta de entrada.
  • El conjunto de resultados siempre usa los nombres de columna de la primera consulta de entrada.
  • El conjunto de resultados siempre usa los supertipos de tipos de entrada en las columnas correspondientes, por lo que las columnas sincronizadas también deben tener el mismo tipo de datos o un supertipo en común.
  • Debes usar paréntesis si deseas separar diferentes operaciones de conjuntos. Para este fin, las operaciones de conjuntos como UNION ALL y UNION DISTINCT son diferentes. Si la declaración solo repite la misma operación de conjunto, no se necesitan los paréntesis.

Ejemplos:

query1 UNION ALL (query2 UNION DISTINCT query3)
query1 UNION ALL query2 UNION ALL query3

No válido:

query1 UNION ALL query2 UNION DISTINCT query3
query1 UNION ALL query2 INTERSECT ALL query3;  // INVALID.

UNION

El operador UNION combina los conjuntos de resultados de dos o más consultas de entrada mediante la sincronización de las columnas del conjunto de resultados de cada consulta, concatenándolas de forma vertical.

INTERSECT

El operador INTERSECT muestra las filas que se encuentran en los conjuntos de resultados de las consultas de entrada izquierda y derecha. A diferencia de como ocurre en EXCEPT, la posición de las consultas de entrada (a la izquierda en lugar de a la derecha del operador INTERSECT) no importa.

EXCEPT

El operador EXCEPT muestra filas de la consulta de entrada izquierda que no están presentes en la consulta de entrada derecha.

Ejemplo:

SELECT * FROM UNNEST(ARRAY<int64>[1, 2, 3]) AS number
EXCEPT DISTINCT SELECT 1;

+--------+
| number |
+--------+
| 2      |
| 3      |
+--------+

Cláusulas LIMIT y OFFSET

LIMIT count [ OFFSET skip_rows ]

LIMIT especifica un count no negativo de tipo INT64 y no se mostrarán más que las filas count. LIMIT 0 muestra 0 filas.

Si hay una operación de conjunto, se aplica LIMIT después de que se evalúa esa operación.

OFFSET especifica un número no negativo de filas que se omitirán antes de aplicar LIMIT. skip_rows es del tipo INT64.

Estas cláusulas aceptan solo valores literales o de parámetros. Las filas que muestran LIMIT y OFFSET no se especifican, a menos que estos operadores se usen después de ORDER BY.

Ejemplos:

SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2

+---------+
| letter  |
+---------+
| a       |
| b       |
+---------+
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1

+---------+
| letter  |
+---------+
| b       |
| c       |
| d       |
+---------+

Cláusula WITH

WITH cte[, ...]

Una cláusula WITH contiene una o más expresiones de tabla comunes (CTE). Una CTE funciona como una tabla temporal a la que puedes hacer referencia dentro de una sola expresión de consulta. Cada CTE vincula los resultados de una subconsulta a un nombre de tabla, que se puede usar en otro lugar de la misma expresión de consulta, pero se aplican reglas.

CTE

cte:
    cte_name AS ( query_expr )

Una expresión de tabla común (CTE) contiene una subconsulta y un nombre asociado con ella.

  • Una CTE no puede hacer referencia a sí misma.
  • La expresión de consulta que contiene la cláusula WITH puede hacer referencia a una CTE, pero se aplican reglas.
Ejemplos

En este ejemplo, una cláusula WITH define dos CTE a las que se hace referencia en la operación de conjunto relacionada, en la que cada CTE hace referencia a una CTE por las expresiones de consulta de entrada de la operación set:

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2

WITH no es compatible con una subconsulta. Se mostrará el siguiente error:

SELECT account
FROM (
  WITH result AS (SELECT * FROM NPCs)
  SELECT *
  FROM result)

La cláusula WITH no es compatible con las declaraciones DML.

Las tablas temporales definidas por la cláusula WITH se almacenan en la memoria. El SQL estándar de Google asigna memoria de manera dinámica a todas las tablas temporales creadas por una consulta. Si los recursos disponibles no son suficientes, la consulta fallará.

Reglas y restricciones del CTE

Se puede hacer referencia a las expresiones de tabla comunes (CTE) dentro de la expresión de consulta que contiene la cláusula WITH.

Estas son algunas reglas y limitaciones generales que debes tener en cuenta cuando trabajes con CTE:

  • Cada CTE en la misma cláusula WITH debe tener un nombre único.
  • Una CTE definida en una cláusula WITH solo es visible para otras CTE en la misma cláusula WITH que se definió después de ella.
  • Una CTE local anula una CTE externo o una tabla con el mismo nombre.
  • Una CTE en una subconsulta no puede hacer referencia a columnas correlacionadas de la consulta externa.

Visibilidad de CTE

Las referencias entre las expresiones de tablas comunes (CTE) en la cláusula WITH pueden ir hacia atrás, pero no hacia adelante.

Esto es lo que sucede cuando tienes dos CTE que hacen referencia a sí mismas o entre sí en una cláusula WITH. Supongamos que A es el primer CTE y B es el segundo CTE en la cláusula:

  • A hace referencia a A = No válido
  • A hace referencia a B = No válido
  • B hace referencia a A = Válido
  • A hace referencia a B hace referencia a A = No válido (no se permiten ciclos).

Esto produce un error. A no puede hacer referencia a sí mismo porque no se admiten las autoreferencias:

WITH
  A AS (SELECT 1 AS n UNION ALL (SELECT n + 1 FROM A WHERE n < 3))
SELECT * FROM A

-- Error

Esto produce un error. A no puede hacer referencia a B porque las referencias entre las CTE pueden ir hacia atrás, pero no hacia delante:

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT 1 AS n)
SELECT * FROM B

-- Error

B puede hacer referencia a A porque las referencias entre CTE pueden revertirse:

WITH
  A AS (SELECT 1 AS n),
  B AS (SELECT * FROM A)
SELECT * FROM B

+---+
| n |
+---+
| 1 |
+---+

Esto produce un error. A y B hacen referencia entre sí, lo que crea un ciclo:

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT * FROM A)
SELECT * FROM B

-- Error

Usa alias

Un alias es un nombre temporal que se le otorga a una tabla, columna o expresión presente en una consulta. Puedes ingresar alias explícitos en la lista SELECT o en la cláusula FROM, o SQL estándar de Google inferirá un alias implícito para algunas expresiones. Las expresiones sin alias explícito ni implícito son anónimas y la consulta no puede hacer referencia a ellas por nombre.

Alias explícitos

Puedes ingresar alias explícitos en la cláusula FROM o en la lista SELECT.

En una cláusula FROM, puedes ingresar los alias explícitos para cualquier elemento, incluidas tablas, arreglos, subconsultas y cláusulas UNNEST, mediante el [AS] alias. La palabra clave AS es opcional.

Ejemplo:

SELECT s.FirstName, s2.SongName
FROM Singers AS s, (SELECT * FROM Songs) AS s2;

Puedes ingresar los alias explícitos para cualquier expresión en la lista SELECT mediante el [AS] alias. La palabra clave AS es opcional.

Ejemplo:

SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;

Alias implícitos

En la lista SELECT, si existe una expresión que no tiene un alias explícito, Google Standard SQL asigna un alias implícito según las siguientes reglas. Puede haber varias columnas con el mismo alias en la lista SELECT.

  • Para los identificadores, el alias es el identificador. Por ejemplo, SELECT abc implica AS abc.
  • Para las expresiones de ruta de acceso, el alias es el último identificador en la ruta. Por ejemplo, SELECT abc.def.ghi implica AS ghi.
  • Para el acceso de campo que usa el operador de acceso de campo del miembro “punto”, el alias es el nombre del campo. Por ejemplo, SELECT (struct_function()).fname implica AS fname.

En los demás casos, no hay un alias implícito, por lo que la columna es anónima y no se puede hacer referencia a ella por nombre. Los datos de esa columna aún se mostrarán y los resultados de la consulta que se muestran pueden tener una etiqueta generada para esa columna, pero la etiqueta no se puede usar como un alias.

En una cláusula FROM, los from_item no necesitan tener un alias. Se aplican las siguientes reglas:

  • Si existe una expresión que no tiene un alias explícito, SQL estándar de Google asigna un alias implícito en estos casos:
    • Para los identificadores, el alias es el identificador. Por ejemplo, FROM abc implica AS abc.
    • Para las expresiones de ruta de acceso, el alias es el último identificador en la ruta. Por ejemplo, FROM abc.def.ghi implica AS ghi
    • La columna generada con WITH OFFSET tiene el alias implícito offset.
  • Las subconsultas de tabla no tienen alias implícitos.
  • FROM UNNEST(x) no tiene un alias implícito.

Visibilidad del alias

Después de ingresar un alias explícito en una consulta, existen restricciones sobre en qué otra parte de la consulta puedes hacer referencia a ese alias. Estas restricciones en la visibilidad de alias son el resultado de las reglas de alcance del nombre de SQL estándar de Google.

Visibilidad en la cláusula FROM

SQL estándar de Google procesa los alias en una cláusula FROM de izquierda a derecha, y los alias solo son visibles para las expresiones de ruta de acceso posteriores en una cláusula FROM.

Ejemplo:

Supongamos que la tabla Singers tenía una columna Concerts de tipo ARRAY.

SELECT FirstName
FROM Singers AS s, s.Concerts;

No válido:

SELECT FirstName
FROM s.Concerts, Singers AS s;  // INVALID.

Los alias de la cláusula FROM no son visibles para las subconsultas en la misma cláusula FROM. Las subconsultas en una cláusula FROM no pueden contener referencias correlacionadas con otras tablas en la misma cláusula FROM.

No válido:

SELECT FirstName
FROM Singers AS s, (SELECT (2020 - ReleaseDate) FROM s)  // INVALID.

Puedes usar cualquier nombre de columna de una tabla en FROM como un alias en cualquier parte de la consulta, con o sin calificación con el nombre de la tabla.

Ejemplo:

SELECT FirstName, s.ReleaseDate
FROM Singers s WHERE ReleaseDate = 1975;

Si la cláusula FROM contiene un alias explícito, debes usarlo en lugar del alias implícito para el resto de la consulta (consulta Alias implícitos). Un alias de tabla es útil por cuestiones de brevedad o para eliminar la ambigüedad en casos como autocombinaciones, en que la misma tabla se analiza varias veces durante el procesamiento de consulta.

Ejemplo:

SELECT * FROM Singers as s, Songs as s2
ORDER BY s.LastName

No válido: ORDER BY no usa el alias de la tabla:

SELECT * FROM Singers as s, Songs as s2
ORDER BY Singers.LastName;  // INVALID.

Visibilidad en la lista SELECT

Los alias en la lista SELECT son visibles solo para las siguientes cláusulas:

  • Cláusula GROUP BY
  • Cláusula ORDER BY
  • Cláusula HAVING

Ejemplo:

SELECT LastName AS last, SingerID
FROM Singers
ORDER BY last;

Visibilidad en las cláusulas GROUP BY, ORDER BY y HAVING

Las tres cláusulas, GROUP BY, ORDER BY y HAVING, solo pueden hacer referencia a los siguientes valores:

  • Tablas en la cláusula FROM y cualquiera de sus columnas
  • Alias de la lista SELECT

GROUP BY y ORDER BY también pueden hacer referencia a un tercer grupo:

  • Literales de número entero, que se refieren a elementos de la lista SELECT. El número entero 1 hace referencia al primer elemento de la lista SELECT, 2 hace referencia al segundo elemento, y así sucesivamente.

Ejemplo:

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY 1
ORDER BY 2 DESC;

La consulta anterior es equivalente a lo siguiente:

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY sid
ORDER BY s2id DESC;

Alias duplicados

Se permite la lista o subconsulta SELECT que contiene varios alias explícitos o implícitos del mismo nombre, siempre que no se haga referencia a él en otro lugar de la consulta, ya que la referencia sería ambigua.

Ejemplo:

SELECT 1 AS a, 2 AS a;

+---+---+
| a | a |
+---+---+
| 1 | 2 |
+---+---+

Alias ambiguos

SQL estándar de Google muestra un error si acceder a un nombre es ambiguo, lo que significa que puede resolverse en más de un objeto único en la consulta o en un esquema de tabla, incluido el esquema de una tabla de destino.

Ejemplos:

Esta consulta contiene nombres de columnas que entran en conflicto entre las tablas, ya que Singers y Songs tienen una columna llamada SingerID:

SELECT SingerID
FROM Singers, Songs;

Esta consulta contiene alias que son ambiguos en la cláusula GROUP BY debido a que están duplicados en la lista SELECT:

SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;

En esta consulta, se incluyen los alias de la lista SELECT y la cláusula FROM que son ambiguos, ya que comparten el mismo nombre. Supón que table tiene columnas x, y y z. z es del tipo STRUCT y tiene campos v, w y x.

Ejemplo:

SELECT x, z AS T
FROM table AS T
GROUP BY T.x;

El alias T es ambiguo y producirá un error debido a que T.x en la cláusula GROUP BY podría referirse a table.x o table.z.x.

Un nombre no es ambiguo en GROUP BY, ORDER BY o HAVING si este es un nombre de columna y un alias de la lista SELECT, siempre que el nombre se resuelva en el mismo objeto subyacente.

Ejemplo:

SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;

El alias BirthYear no es ambiguo porque se resuelve en la misma columna subyacente, Singers.BirthYear.

Variables de rango

En SQL estándar de Google, una variable de rango es un alias de una expresión de tabla en la cláusula FROM. A veces, una variable de rango se conoce como un table alias. Una variable de rango te permite hacer referencia a las filas de una expresión de tabla que se analizan. Una expresión de tabla representa un elemento de la cláusula FROM que muestra una tabla. Los elementos comunes que esta expresión puede representar son tablas, tablas de valores, subconsultas, uniones y uniones con paréntesis.

En general, una variable de rango proporciona una referencia a las filas de una expresión de tabla. Una variable de rango se puede usar para calificar una referencia de columna y, también, identificar la tabla relacionada de forma clara, por ejemplo range_variable.column_1.

Cuando se hace referencia a una variable de rango por separado sin un sufijo de columna especificado, el resultado de una expresión de tabla es el tipo de fila de la tabla relacionada. Las tablas de valores tienen tipos de fila explícitos. Por lo tanto, para las variables de rango relacionadas con tablas de valores, el tipo de resultado es el tipo de fila de la tabla de valores. Otras tablas no tienen tipos de fila explícitos y, para estas, el tipo de variable de rango es un STRUCT definido de forma dinámica en el que se incluyen todas las columnas de la tabla.

Ejemplos

En estos ejemplos, la cláusula WITH se usa para emular una tabla temporal llamada Grid. Esta tabla tiene las columnas x y y. Una variable de rango llamada Coordinate hace referencia a la fila actual a medida que se analiza la tabla. Coordinate se puede usar para acceder a toda la fila o a las columnas de la fila.

En el siguiente ejemplo, se selecciona la columna x de la variable de rango Coordinate, que en realidad selecciona la columna x de la tabla Grid.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.x FROM Grid AS Coordinate;

+---+
| x |
+---+
| 1 |
+---+

En el siguiente ejemplo, se seleccionan todas las columnas de la variable de rango Coordinate, que en realidad selecciona todas las columnas de la tabla Grid.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.* FROM Grid AS Coordinate;

+---+---+
| x | y |
+---+---+
| 1 | 2 |
+---+---+

En el siguiente ejemplo, se selecciona la variable de rango Coordinate, que es una referencia a las filas de la tabla Grid. Como Grid no es una tabla de valores, el tipo de resultado de Coordinate es un STRUCT que contiene todas las columnas de Grid.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate FROM Grid AS Coordinate;

+--------------+
| Coordinate   |
+--------------+
| {x: 1, y: 2} |
+--------------+

Apéndice A: Ejemplos con datos de muestra

En estos ejemplos, se incluyen declaraciones mediante las que se realizan consultas en las tablas Roster, TeamMascot y PlayerStats.

Cláusula GROUP BY

Ejemplo:

SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;
LastName SUM
Adams 7
Buchanan 13
Coolidge 1

UNION

El operador UNION combina los conjuntos de resultados de dos o más declaraciones SELECT mediante la sincronización de las columnas del conjunto de resultados de cada declaración SELECT y su concatenación de forma vertical.

Ejemplo:

SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;

Resultados:

X S
Jaguars 50
Knights 51
Lakers 52
Mustangs 53
Adams 3
Buchanan 0
Coolidge 1
Adams 4
Buchanan 13

INTERSECT

Esta consulta muestra los apellidos que están presentes en Roster y en PlayerStats.

SELECT LastName
FROM Roster
INTERSECT ALL
SELECT LastName
FROM PlayerStats;

Resultados:

LastName
Adams
Coolidge
Buchanan

EXCEPT

La consulta que aparece a continuación muestra los apellidos de Roster que no están presentes en PlayerStats.

SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;

Resultados:

LastName
Eisenhower
Davis

Si se invierte el orden de las instrucciones SELECT, se mostrarán los apellidos de PlayerStats que no están presentes en Roster:

SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;

Resultados:

(empty)