Seleccione (SQL) - Select (SQL)

La instrucción SQL SELECT devuelve un conjunto de registros de resultados, de una o más tablas .

Una instrucción SELECT recupera cero o más filas de una o más tablas de base de datos o vistas de base de datos . En la mayoría de las aplicaciones, SELECTes el comando de lenguaje de manipulación de datos (DML) más utilizado . Como SQL es un lenguaje de programación declarativo , las SELECTconsultas especifican un conjunto de resultados, pero no especifican cómo calcularlo. La base de datos traduce la consulta en un " plan de consulta " que puede variar entre ejecuciones, versiones de base de datos y software de base de datos. Esta funcionalidad se denomina " optimizador de consultas ", ya que es responsable de encontrar el mejor plan de ejecución posible para la consulta, dentro de las restricciones aplicables.

La instrucción SELECT tiene muchas cláusulas opcionales:

• SELECTcláusula es la lista de columnas o expresiones SQL que debe devolver la consulta. Esta es aproximadamente la operación de proyección del álgebra relacional .
• ASopcionalmente proporciona un alias para cada columna o expresión de la SELECTcláusula. Esta es la operación de cambio de nombre del álgebra relacional .
• FROM especifica de qué tabla obtener los datos.
• WHEREespecifica qué filas recuperar. Esta es aproximadamente la operación de selección de álgebra relacional .
• GROUP BYagrupa filas que comparten una propiedad para que se pueda aplicar una función agregada a cada grupo.
• HAVING selecciona entre los grupos definidos por la cláusula GROUP BY.
• ORDER BY especifica cómo ordenar las filas devueltas.

Visión general

SELECTes la operación más común en SQL, llamada "la consulta". SELECTrecupera datos de una o más tablas o expresiones. Las SELECTdeclaraciones estándar no tienen efectos persistentes en la base de datos. Algunas implementaciones no estándar de SELECTpueden tener efectos persistentes, como la SELECT INTOsintaxis proporcionada en algunas bases de datos.

Las consultas permiten al usuario describir los datos deseados, dejando que el sistema de gestión de la base de datos (DBMS) lleve a cabo la planificación , optimización y realización de las operaciones físicas necesarias para producir ese resultado como elija.

Una consulta incluye una lista de columnas para incluir en el resultado final, normalmente inmediatamente después de la SELECTpalabra clave. Se *puede utilizar un asterisco (" ") para especificar que la consulta debe devolver todas las columnas de las tablas consultadas. SELECTes la declaración más compleja en SQL, con palabras clave y cláusulas opcionales que incluyen:

• La FROMcláusula, que indica la (s) tabla (s) para recuperar datos. La FROMcláusula puede incluir JOINsubcláusulas opcionales para especificar las reglas para unir tablas.
• La WHEREcláusula incluye un predicado de comparación, que restringe las filas devueltas por la consulta. La WHEREcláusula elimina todas las filas del conjunto de resultados donde el predicado de comparación no se evalúa como Verdadero.
• La GROUP BYcláusula proyecta filas que tienen valores comunes en un conjunto más pequeño de filas. GROUP BYse utiliza a menudo junto con funciones de agregación SQL o para eliminar filas duplicadas de un conjunto de resultados. La WHEREcláusula se aplica antes que la GROUP BYcláusula.
• La HAVINGcláusula incluye un predicado que se utiliza para filtrar filas resultantes de la GROUP BYcláusula. Dado que actúa sobre los resultados de la GROUP BYcláusula, las funciones de agregación se pueden utilizar en el HAVINGpredicado de la cláusula.
• La ORDER BYcláusula identifica qué columna (s) usar para ordenar los datos resultantes y en qué dirección ordenarlos (ascendente o descendente). Sin una ORDER BYcláusula, el orden de las filas devueltas por una consulta SQL no está definido.
• La DISTINCTpalabra clave elimina los datos duplicados.

El siguiente ejemplo de SELECTconsulta devuelve una lista de libros caros. La consulta recupera todas las filas de la tabla Libro en las que la columna de precio contiene un valor superior a 100,00. El resultado se clasifica en orden ascendente por título . El asterisco (*) en la lista de selección indica que todas las columnas de la tabla Libro deben incluirse en el conjunto de resultados.

SELECT *
 FROM  Book
 WHERE price > 100.00
 ORDER BY title;

El siguiente ejemplo muestra una consulta de varias tablas, agrupación y agregación, al devolver una lista de libros y el número de autores asociados con cada libro.

SELECT Book.title AS Title,
       count(*) AS Authors
 FROM  Book
 JOIN  Book_author
   ON  Book.isbn = Book_author.isbn
 GROUP BY Book.title;

La salida de ejemplo puede parecerse a la siguiente:

Title                  Authors
---------------------- -------
SQL Examples and Guide 4
The Joy of SQL         1
An Introduction to SQL 2
Pitfalls of SQL        1

Bajo la condición previa de que isbn es el único nombre de columna común de las dos tablas y que una columna llamada título solo existe en la tabla Libro , se podría volver a escribir la consulta anterior en la siguiente forma:

SELECT title,
       count(*) AS Authors
 FROM  Book
 NATURAL JOIN Book_author
 GROUP BY title;

Sin embargo, muchos proveedores no admiten este enfoque o requieren ciertas convenciones de nomenclatura de columnas para que las combinaciones naturales funcionen de manera eficaz.

SQL incluye operadores y funciones para calcular valores en valores almacenados. SQL permite el uso de expresiones en la lista de selección para proyectar datos, como en el siguiente ejemplo, que devuelve una lista de libros que cuestan más de 100,00 con una columna sales_tax adicional que contiene una cifra de impuesto sobre las ventas calculada al 6% del precio .

SELECT isbn,
       title,
       price,
       price * 0.06 AS sales_tax
 FROM  Book
 WHERE price > 100.00
 ORDER BY title;

Subconsultas

Las consultas se pueden anidar para que los resultados de una consulta se puedan utilizar en otra consulta mediante un operador relacional o una función de agregación. Una consulta anidada también se conoce como subconsulta . Si bien las combinaciones y otras operaciones de tabla proporcionan alternativas computacionalmente superiores (es decir, más rápidas) en muchos casos, el uso de subconsultas introduce una jerarquía en la ejecución que puede ser útil o necesaria. En el siguiente ejemplo, la función de agregación AVGrecibe como entrada el resultado de una subconsulta:

SELECT isbn,
       title,
       price
 FROM  Book
 WHERE price < (SELECT AVG(price) FROM Book)
 ORDER BY title;

Una subconsulta puede utilizar valores de la consulta externa, en cuyo caso se conoce como subconsulta correlacionada .

Desde 1999, el estándar SQL permite subconsultas con nombre denominadas expresiones de tabla común (nombradas y diseñadas según la implementación de IBM DB2 versión 2; Oracle las denomina factorización de subconsultas ). Los CTE también pueden ser recursivos al referirse a sí mismos; el mecanismo resultante permite recorridos de árboles o gráficos (cuando se representan como relaciones) y, de manera más general, cálculos de puntos fijos .

Tabla derivada

Una tabla derivada es el uso de hacer referencia a una subconsulta SQL en una cláusula FROM. Básicamente, la tabla derivada es una subconsulta que se puede seleccionar o unir. La funcionalidad de tabla derivada permite al usuario hacer referencia a la subconsulta como una tabla. La tabla derivada también se conoce como una vista en línea o una selección de la lista .

En el siguiente ejemplo, la instrucción SQL implica una combinación de la tabla de libros inicial a la tabla derivada "Ventas". Esta tabla derivada captura la información de ventas de libros asociada utilizando el ISBN para unirse a la tabla Libros. Como resultado, la tabla derivada proporciona el conjunto de resultados con columnas adicionales (el número de artículos vendidos y la empresa que vendió los libros):

SELECT b.isbn, b.title, b.price, sales.items_sold, sales.company_nm
FROM Book b
  JOIN (SELECT SUM(Items_Sold) Items_Sold, Company_Nm, ISBN
        FROM Book_Sales
        GROUP BY Company_Nm, ISBN) sales
  ON sales.isbn = b.isbn

Ejemplos de

Dada una tabla T, la consulta dará como resultado que se muestren todos los elementos de todas las filas de la tabla. SELECT * FROM T

Con la misma tabla, la consulta dará como resultado que se muestren los elementos de la columna C1 de todas las filas de la tabla. Esto es similar a una proyección en álgebra relacional , excepto que en el caso general, el resultado puede contener filas duplicadas. Esto también se conoce como partición vertical en algunos términos de la base de datos, lo que restringe la salida de la consulta para ver solo campos o columnas especificados. SELECT C1 FROM T

Con la misma tabla, la consulta dará como resultado que se muestren todos los elementos de todas las filas donde el valor de la columna C1 es '1'; en términos de álgebra relacional, se realizará una selección , debido a la cláusula WHERE. Esto también se conoce como partición horizontal, que restringe la salida de filas de una consulta según las condiciones especificadas. SELECT * FROM T WHERE C1 = 1

Con más de una tabla, el conjunto de resultados será cada combinación de filas. Entonces, si dos tablas son T1 y T2, resultará en cada combinación de filas T1 con cada fila T2. Por ejemplo, si T1 tiene 3 filas y T2 tiene 5 filas, se obtendrán 15 filas. SELECT * FROM T1, T2

Aunque no es estándar, la mayoría de DBMS permite usar una cláusula select sin una tabla pretendiendo que se usa una tabla imaginaria con una fila. Esto se utiliza principalmente para realizar cálculos donde no se necesita una tabla.

La cláusula SELECT especifica una lista de propiedades (columnas) por nombre, o el carácter comodín ("*") para significar "todas las propiedades".

Limitar filas de resultados

A menudo es conveniente indicar un número máximo de filas que se devuelven. Esto se puede utilizar para realizar pruebas o para evitar consumir recursos excesivos si la consulta devuelve más información de la esperada. El enfoque para hacer esto a menudo varía según el proveedor.

En ISO SQL: 2003 , los conjuntos de resultados pueden limitarse mediante el uso de

• cursores , o
• agregando una función de ventana SQL a la instrucción SELECT

ISO SQL: 2008 introdujo la FETCH FIRSTcláusula.

Según la documentación de PostgreSQL v.9, una función de ventana SQL "realiza un cálculo en un conjunto de filas de la tabla que están relacionadas de alguna manera con la fila actual", de manera similar a las funciones agregadas. El nombre recuerda las funciones de la ventana de procesamiento de señales . Una llamada a la función de ventana siempre contiene una cláusula OVER .

Función de ventana ROW_NUMBER ()

ROW_NUMBER() OVERse puede usar para una tabla simple en las filas devueltas, por ejemplo, para devolver no más de diez filas:

SELECT * FROM
( SELECT
    ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS row_number,
    columns
  FROM tablename
) AS foo
WHERE row_number <= 10

ROW_NUMBER puede ser no determinista : si sort_key no es único, cada vez que ejecute la consulta es posible obtener diferentes números de fila asignados a cualquier fila donde sort_key sea ​​el mismo. Cuando sort_key es único, cada fila siempre obtendrá un número de fila único.

Función de ventana RANK ()

La RANK() OVERfunción de ventana actúa como ROW_NUMBER, pero puede devolver más o menos n filas en caso de condiciones de empate, por ejemplo, para devolver las 10 personas más jóvenes del top-10:

SELECT * FROM (
  SELECT
    RANK() OVER (ORDER BY age ASC) AS ranking,
    person_id,
    person_name,
    age
  FROM person
) AS foo
WHERE ranking <= 10

El código anterior podría devolver más de diez filas, por ejemplo, si hay dos personas de la misma edad, podría devolver once filas.

FETCH FIRST cláusula

Desde ISO SQL: 2008 , los límites de resultados se pueden especificar como en el siguiente ejemplo utilizando la FETCH FIRSTcláusula.

SELECT * FROM T 
FETCH FIRST 10 ROWS ONLY

Actualmente, esta cláusula es compatible con CA DATACOM / DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB versión 2.0, Oracle 12c y Mimer SQL .

Microsoft SQL Server 2008 y versionesFETCH FIRST posteriores son compatibles , pero se considera parte de la ORDER BYcláusula. Las ORDER BY, OFFSETy FETCH FIRSTlas cláusulas son todos los necesarios para este uso.

SELECT * FROM T 
ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY

Sintaxis no estándar

Algunos DBMS ofrecen una sintaxis no estándar en lugar o además de la sintaxis estándar de SQL. A continuación, se enumeran las variantes de la consulta de límite simple para diferentes DBMS:

SET ROWCOUNT 10
SELECT * FROM T

SELECT * FROM T 
LIMIT 10 OFFSET 20

SELECT * from T 
WHERE ROWNUM <= 10

SELECT * FROM T 
SAMPLE 10

SELECT * FROM T
ROWS 20 TO 30

SELECT * FROM T
WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY

SELECT * FROM T
WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY

Paginación de filas

La paginación de filas es un enfoque que se utiliza para limitar y mostrar solo una parte de los datos totales de una consulta en la base de datos. En lugar de mostrar cientos o miles de filas al mismo tiempo, se solicita al servidor solo una página (un conjunto limitado de filas, por ejemplo, solo 10 filas), y el usuario comienza a navegar solicitando la página siguiente y luego la siguiente. , etcétera. Es muy útil, especialmente en sistemas web, donde no existe una conexión dedicada entre el cliente y el servidor, por lo que el cliente no tiene que esperar para leer y mostrar todas las filas del servidor.

Enfoque de datos en paginación

• {rows} = Número de filas en una página
• {page_number} = Número de la página actual
• {begin_base_0} = Número de la fila - 1 donde comienza la página = (número_página-1) * filas

Método más simple (pero muy ineficiente)

1. Seleccionar todas las filas de la base de datos
2. Leer todas las filas pero enviar para mostrar solo cuando el número de fila de las filas leídas esté entre {begin_base_0 + 1}y{begin_base_0 + rows}

Select * 
from {table} 
order by {unique_key}

Otro método simple (un poco más eficiente que leer todas las filas)

1. Seleccione todas las filas desde el principio de la tabla hasta la última fila para mostrar ( {begin_base_0 + rows})
2. Leer las {begin_base_0 + rows}filas pero enviar para mostrar solo cuando el número de filas de las filas leídas sea mayor que{begin_base_0}

select *
from {table}
order by {unique_key}
FETCH FIRST {begin_base_0 + rows} ROWS ONLY

Select *
from {table}
order by {unique_key}
LIMIT {begin_base_0 + rows}

Select TOP {begin_base_0 + rows} * 
from {table} 
order by {unique_key}

SET ROWCOUNT {begin_base_0 + rows}
Select * 
from {table} 
order by {unique_key}
SET ROWCOUNT 0

Select *
    FROM (
        SELECT * 
        FROM {table} 
        ORDER BY {unique_key}
    ) a 
where rownum <= {begin_base_0 + rows}

Método con posicionamiento

1. Seleccione solo {rows}filas a partir de la siguiente fila para mostrar ( {begin_base_0 + 1})
2. Leer y enviar para mostrar todas las filas leídas de la base de datos

Select *
from {table}
order by {unique_key}
OFFSET {begin_base_0} ROWS
FETCH NEXT {rows} ROWS ONLY

Select *
from {table}
order by {unique_key}
LIMIT {rows} OFFSET {begin_base_0}

Select * 
from {table} 
order by {unique_key}
LIMIT {begin_base_0}, {rows}

Select TOP {begin_base_0 + rows}
       *,  _offset=identity(10) 
into #temp
from {table}
ORDER BY {unique_key} 
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp

SET ROWCOUNT {begin_base_0 + rows}
select *,  _offset=identity(10) 
into #temp
from {table}
ORDER BY {unique_key} 
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp
SET ROWCOUNT 0

select TOP {rows} * 
from (
      select *, ROW_NUMBER() over (order by {unique_key}) as _offset
      from {table}
) xx 
where _offset > {begin_base_0}

SET ROWCOUNT {begin_base_0 + rows}
select *,  _offset=identity(int,1,1) 
into #temp
from {table}
ORDER BY {unique-key}
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp
SET ROWCOUNT 0

SELECT * FROM (
    SELECT rownum-1 as _offset, a.* 
    FROM(
        SELECT * 
        FROM {table} 
        ORDER BY {unique_key}
    ) a 
    WHERE rownum <= {begin_base_0 + cant_regs}
)
WHERE _offset >= {begin_base_0}

Método con filtro (es más sofisticado pero necesario para un conjunto de datos muy grande)

1. Seleccione solo entonces {rows}filas con filtro:
   1. Primera página: seleccione solo las primeras {rows}filas, según el tipo de base de datos
   2. Página siguiente: seleccione solo las primeras {rows}filas, según el tipo de base de datos, donde el {unique_key}es mayor que {last_val}(el valor {unique_key}de la última fila de la página actual)
   3. Página anterior: ordena los datos en orden inverso, selecciona solo las primeras {rows}filas, donde el {unique_key}es menor que {first_val}(el valor de {unique_key}de la primera fila en la página actual), y ordena el resultado en el orden correcto
2. Leer y enviar para mostrar todas las filas leídas de la base de datos

select *
from {table} 
order by {unique_key}
FETCH FIRST {rows} ROWS ONLY

select * 
from {table} 
where {unique_key} > {last_val}
order by {unique_key}
FETCH FIRST {rows} ROWS ONLY

select * 
 from (
   select * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
   FETCH FIRST {rows} ROWS ONLY
 ) a
 order by {unique_key}

select *
from {table}
order by {unique_key}
LIMIT {rows}

select * 
from {table} 
where {unique_key} > {last_val}
order by {unique_key}
LIMIT {rows}

select * 
 from (
   select * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
   LIMIT {rows}
 ) a
 order by {unique_key}

select TOP {rows} * 
from {table} 
order by {unique_key}

select TOP {rows} * 
from {table} 
where {unique_key} > {last_val}
order by {unique_key}

select * 
 from (
   select TOP {rows} * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
 ) a
 order by {unique_key}

SET ROWCOUNT {rows}
select *
from {table} 
order by {unique_key}
SET ROWCOUNT 0

SET ROWCOUNT {rows}
select *
from {table} 
where {unique_key} > {last_val}
order by {unique_key}
SET ROWCOUNT 0

SET ROWCOUNT {rows}
 select *
 from (
   select * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
 ) a
 order by {unique_key}
 SET ROWCOUNT 0

select *
from (
    select * 
    from {table} 
    order by {unique_key}
  ) a 
where rownum <= {rows}

select *
from (
  select * 
  from {table} 
  where {unique_key} > {last_val}
  order by {unique_key}
) a 
where rownum <= {rows}

select * 
 from (
   select *
   from (
     select * 
     from {table} 
     where {unique_key} < {first_val}
     order by {unique_key} DESC
   ) a1
   where rownum <= {rows}
 ) a2
 order by {unique_key}

Consulta jerárquica

Algunas bases de datos proporcionan una sintaxis especializada para datos jerárquicos .

Una función de ventana en SQL: 2003 es una función agregada aplicada a una partición del conjunto de resultados.

Por ejemplo,

sum(population) OVER( PARTITION BY city )

calcula la suma de las poblaciones de todas las filas que tienen el mismo valor de ciudad que la fila actual.

Las particiones se especifican mediante la cláusula OVER que modifica el agregado. Sintaxis:

<OVER_CLAUSE> :: =
   OVER ( [ PARTITION BY <expr>, ... ]
          [ ORDER BY <expression> ] )

La cláusula OVER puede dividir y ordenar el conjunto de resultados. La ordenación se utiliza para funciones relativas a la orden, como número_de_fila.

Evaluación de consultas ANSI

El procesamiento de una sentencia SELECT según ANSI SQL sería el siguiente:

Compatibilidad con la función de ventana por parte de los proveedores de RDBMS

La implementación de las funciones de la ventana por parte de los proveedores de bases de datos relacionales y motores SQL difiere enormemente. La mayoría de las bases de datos admiten al menos algunos tipos de funciones de ventana. Sin embargo, cuando miramos más de cerca, queda claro que la mayoría de los proveedores solo implementan un subconjunto del estándar. Tomemos como ejemplo la poderosa cláusula RANGE. Solo Oracle, DB2, Spark / Hive y Google Big Query implementan completamente esta función. Más recientemente, los proveedores han agregado nuevas extensiones al estándar, por ejemplo, funciones de agregación de arreglos. Estos son particularmente útiles en el contexto de ejecutar SQL contra un sistema de archivos distribuido (Hadoop, Spark, Google BigQuery) donde tenemos garantías de co-localidad de datos más débiles que en una base de datos relacional distribuida (MPP). En lugar de distribuir uniformemente los datos en todos los nodos, los motores SQL que ejecutan consultas en un sistema de archivos distribuido pueden lograr garantías de co-localidad de datos anidando datos y evitando así uniones potencialmente costosas que implican una gran mezcla en la red. Las funciones agregadas definidas por el usuario que se pueden usar en funciones de ventana son otra característica extremadamente poderosa.

Generando datos en T-SQL

Método para generar datos basados ​​en la unión todos

select 1 a, 1 b union all
select 1, 2 union all
select 1, 3 union all
select 2, 1 union all
select 5, 1

SQL Server 2008 admite el "constructor de filas" especificado en el estándar SQL3 ("SQL: 1999")

select *
from (values (1, 1), (1, 2), (1, 3), (2, 1), (5, 1)) as x(a, b)

Referencias

Fuentes

• Particionamiento horizontal y vertical, Libros en pantalla de Microsoft SQL Server 2000.

enlaces externos

• Tablas con ventana y función de ventana en SQL , Stefan Deßloch
• Sintaxis de Oracle SELECT
• Sintaxis de Firebird SELECT
• Sintaxis de MySQL SELECT
• Sintaxis de PostgreSQL SELECT
• Sintaxis de SQLite SELECT