Guía SQL de Bolsillo, 4ª Edición

Capítulo 4. Conceptos básicos de consulta

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: [e-mail]

Una consulta es un apodo para una sentencia SELECT, que consta de seis cláusulas principales. Cada sección de este capítulo cubre una cláusula en detalle:

1. SELECT

2. FROM

3. WHERE

4. GROUP BY

5. HAVING

6. ORDER BY

La última sección de este capítulo trata de la cláusula LIMIT, que admiten MySQL, PostgreSQL y SQLite.

Los ejemplos de código de este capítulo hacen referencia a cuatro tablas:

waterfall

Cascadas en la Alta Península de Michigan

owner

propietarios de las cascadas

county

comarcas donde se encuentran las cascadas

tour

recorridos que consisten en múltiples paradas en cascadas

Aquí tienes un ejemplo de consulta que utiliza las seis cláusulas principales. Le siguen los resultados de la consulta, que también se conocen comoconjunto de resultados.

-- Tours with 2 or more public waterfalls
SELECT   t.name AS tour_name,
         COUNT(*) AS num_waterfalls
FROM     tour t LEFT JOIN waterfall w
         ON t.stop = w.id
WHERE    w.open_to_public = 'y'
GROUP BY t.name
HAVING   COUNT(*) >= 2
ORDER BY tour_name;

tour_name  num_waterfalls
---------- ---------------
M-28                     6
Munising                 6
US-2                     4

Consultar una base de datos significa recuperar datos de una base de datos, normalmente de una tabla o varias tablas.

Nota

También es posible consultar una vista en lugar de una tabla. Las vistas se parecen a las tablas y derivan de ellas, pero no contienen datos. Encontrarás más información sobre las vistas en "Vistas", en el Capítulo 5.

La cláusula SELECT

La cláusula SELECT especifica en las columnas que quieres que devuelva una sentencia.

En la cláusula SELECT, la palabra clave SELECT va seguida de una lista de nombres de columnas y/o expresiones separadas por comas. Cada nombre de columna y/o expresión se convierte entonces en una columna de los resultados.

Seleccionar columnas

La cláusula SELECT más sencilla enumera uno o varios nombres de columnas de las tablas de la cláusula FROM:

SELECT id, name
FROM owner;

id    name
----- ----------------
    1 Pictured Rocks
    2 Michigan Nature
    3 AF LLC
    4 MI DNR
    5 Horseshoe Falls

Seleccionar todas las columnas

Para devolver todas las columnas de una tabla, puedes utilizar un único asterisco en lugar de escribir el nombre de cada columna:

SELECT *
FROM owner;

id    name             phone         type
----- ---------------- ------------- --------
    1 Pictured Rocks   906.387.2607  public
    2 Michigan Nature  517.655.5655  private
    3 AF LLC                         private
    4 MI DNR           906.228.6561  public
    5 Horseshoe Falls  906.387.2635  private

Advertencia

El asterisco es un atajo útil cuando pruebas consultas porque puede ahorrarte bastante tecleo. Sin embargo, es arriesgado utilizar el asterisco en código de producción porque las columnas de una tabla pueden cambiar con el tiempo, haciendo que tu código falle cuando haya menos o más columnas de las esperadas.

Seleccionar expresiones

Además de enumerar simplemente columnas, también puedes enumerar expresiones más complejas dentro de la cláusula SELECT para que aparezcan como columnas en los resultados.

El enunciado siguiente incluye una expresión para calcular un descenso del 10% de la población, redondeado a cero decimales:

SELECT name, ROUND(population * 0.9, 0)
FROM county;

name       ROUND(population * 0.9, 0)
---------- ---------------------------
Alger                             8876
Baraga                            7871
Ontonagon                         7036
...

Seleccionar funciones

Las expresiones de la lista SELECT suelen hacer referencia a columnas de las tablas de las que estás extrayendo información, pero hay excepciones. Por ejemplo, una función habitual que no hace referencia a ninguna tabla es la que devuelve la fecha actual:

SELECT CURRENT_DATE;

CURRENT_DATE
-------------
2021-12-01

El código anterior funciona en MySQL, PostgreSQL y SQLite. El código equivalente que funciona en otros RDBMS se puede encontrar en "Funciones Datetime" en el Capítulo 7.

Nota

La mayoría de las consultas incluyen tanto una cláusula SELECT como una FROM, pero sólo se requiere la cláusula SELECT cuando se utilizan funciones concretas de la base de datos, como CURRENT_DATE.

También es posible incluir expresiones dentro de la cláusula SELECT que sean subconsultas (una consulta anidada dentro de otra consulta). Encontrarás más detalles en "Selección de subconsultas".

Aliasing Columnas

El objetivo de un alias de columna es dar un nombre temporal a cualquier columna o expresión que aparezca en la cláusula SELECT. Ese nombre temporal, o alias de columna, se muestra después como nombre de columna en los resultados.

Ten en cuenta que no se trata de un cambio de nombre permanente, ya que los nombres de las columnas de las tablas originales permanecen inalterados. El alias sólo existe dentro de la consulta.

Este código muestra tres columnas.

SELECT id, name,
       ROUND(population * 0.9, 0)
FROM county;

id    name       ROUND(population * 0.9, 0)
----- ---------- ---------------------------
    2 Alger                             8876
    6 Baraga                            7871
    7 Ontonagon                         7036
...

Supongamos que queremos cambiar el nombre de las columnas de los resultados. id es demasiado ambigua y nos gustaría darle un nombre más descriptivo. ROUND(population * 0.9, 0) es demasiado larga y nos gustaría darle un nombre más sencillo.

Para crear un alias de columna, sigue el nombre de una columna o expresión con (1) un nombre de alias o (2) la palabra clave AS y un nombre de alias.

-- alias_name
SELECT id county_id, name,
       ROUND(population * 0.9, 0) estimated_pop
FROM county;

o:

-- AS alias_name
SELECT id AS county_id, name,
       ROUND(population * 0.90, 0) AS estimated_pop
FROM county;

county_id  name       estimated_pop
---------- ---------- --------------
         2 Alger                8876
         6 Baraga               7871
         7 Ontonagon            7036
...

Ambas opciones se utilizan en la práctica al crear alias. Dentro de la cláusula SELECT, la segunda opción es más popular porque la palabra clave AS facilita visualmente la diferenciación de nombres de columnas y alias entre una larga lista de nombres de columnas.

Nota

Las versiones antiguas de PostgreSQL requieren el uso de AS al crear un alias de columna.

Aunque los alias de columna no son obligatorios, son muy recomendables cuando se trabaja con expresiones para dar nombres sensatos a las columnas de los resultados.

Alias con distinción entre mayúsculas y minúsculas y puntuación

Como puedes ver con los alias de columna county_id yestimated_popla convención es utilizar letras minúsculas con guiones bajos en lugar de espacios al nombrar los alias de columna.

También puedes crear alias que contengan letras mayúsculas, espacios y signos de puntuación utilizando la sintaxis de comillas dobles, como se muestra en este ejemplo:

SELECT id AS "Waterfall #",
  name AS "Waterfall Name"
FROM waterfall;

Waterfall #  Waterfall Name
------------ ---------------
           1 Munising Falls
           2 Tannery Falls
           3 Alger Falls
...

Columnas de calificación

Supongamos que escribes una consulta que extrae datos de dos tablas y ambas contienen una columna llamada name. Si sólo incluyeras name en la cláusula SELECT, el código no sabría a qué tabla te estás refiriendo.

Para resolver este problema, puedes calificar el nombre de una columna por su nombre de tabla. En otras palabras, puedes dar a una columna un prefijo para especificar a qué tabla pertenece utilizando la notación por puntos, como en table_name.column_name.

El siguiente ejemplo consulta una única tabla, por lo que, aunque no es necesario calificar las columnas aquí, se muestra amodo de demostración. Así es como calificarías una columna por su nombre de tabla:

SELECT owner.id, owner.name
FROM owner;

Consejo

Si obtienes un error en SQL haciendo referencia a un nombre de columna ambiguo, significa que varias tablas de tu consulta tienen una columna con el mismo nombre y no has especificado a qué combinación de tabla/columna te refieres. Puedes resolver el error calificando el nombre de la columna.

Tablas de clasificación

Si calificas un nombre de columna por su nombre de tabla, también puedes calificar ese nombre de tabla por su nombre de base de datos o esquema. La siguiente consulta recupera datos específicamente de la tabla dentro del esquema : owner sqlbook

SELECT sqlbook.owner.id, sqlbook.owner.name
FROM sqlbook.owner;

El código anterior es largo, ya que sqlbook.owner se repite varias veces. Para ahorrar tecleo, puedes proporcionar un alias de tabla. El siguiente ejemplo da el alias o a la tabla owner:

SELECT o.id, o.name
FROM sqlbook.owner o;

o:

SELECT o.id, o.name
FROM owner o;

Alias de columna frente a alias de tabla

Los alias de columna se definen dentro de la cláusula SELECT para renombrar una columna en los resultados. Es habitual incluir AS, aunque no es obligatorio.

-- Column alias
SELECT num AS new_col
FROM my_table;

Los alias de tabla se definen dentro de la cláusula FROM para crear un apodo temporal para una tabla. Es habitual excluir AS, aunque incluir AS también funciona.

-- Table alias
SELECT *
FROM my_table mt;

Seleccionar subconsultas

Una subconsulta es una consulta anidada dentro de otra consulta. Las subconsultas pueden estar dentro de varias cláusulas, incluida la cláusula SELECT.

En el siguiente ejemplo, además de id, name, y population, digamos que también queremos ver la población media de todos los condados. Al incluir una subconsulta, estamos creando una nueva columna en los resultados para la población media.

SELECT id, name, population,
       (SELECT AVG(population) FROM county)
       AS average_pop
FROM county;

id    name       population  average_pop
----- ---------- ----------- ------------
    2 Alger             9862        18298
    6 Baraga            8746        18298
    7 Ontonagon         7818        18298
...

Hay que tener en cuenta algunas cosas:

• Una subconsulta debe ir rodeada de paréntesis.

• Cuando escribas una subconsulta dentro de la cláusula SELECT, es muy recomendable que especifiques un alias de columna, que en este caso fue average_pop. De ese modo, la columna tendrá un nombre sencillo en los resultados.

• Sólo hay un valor en la columna average_pop que se repite en todas las filas. Al incluir una subconsulta dentro de la cláusula SELECT, el resultado de la subconsulta debe devolver una única columna y cero o una fila, como se muestra en la siguiente subconsulta para calcular lapoblación media.

SELECT AVG(population) FROM county;

AVG(population)
----------------
           18298

• Si la subconsulta devolvía cero filas, la nueva columna se rellenaría con los valores de NULL.

Subconsultas no correlacionadas frente a subconsultas correlacionadas

El ejemplo anterior es una subconsulta no correlacionada, lo que significa que la subconsulta no hace referencia a la consulta externa. La subconsulta puede ejecutarse por sí sola, independientemente de la consulta externa.

El otro tipo de subconsulta se denomina subconsulta correlacionada, que es la que sí hace referencia a valores de la consulta externa. Esto suele ralentizar considerablemente el tiempo de procesamiento, por lo que es mejor reescribir la consulta utilizando en su lugar un JOIN. Lo que sigue es un ejemplo de subconsulta correlacionada junto con un código más eficiente.

Problemas de rendimiento con subconsultas correlacionadas

La siguiente consulta devuelve el número de cascadas de cada propietario. Observa que el paso o.id = w.owner_id de la subconsulta hace referencia a la tabla owner de la consulta externa, lo que la convierte en una subconsulta correlacionada.

SELECT o.id, o.name,
       (SELECT COUNT(*) FROM waterfall w
       WHERE o.id = w.owner_id) AS num_waterfalls
FROM owner o;

id    name             num_waterfalls
----- ---------------- ---------------
    1 Pictured Rocks                 3
    2 Michigan Nature                3
    3 AF LLC                         1
    4 MI DNR                         1
    5 Horseshoe Falls                0

Un enfoque mejor sería reescribir la consulta con un JOIN. De esta forma, primero se unen las tablas y luego se ejecuta el resto de la consulta, lo que es mucho más rápido que volver a ejecutar una subconsulta para cada fila de datos. Encontrarás más información sobre las uniones en "Unir tablas", en el Capítulo 9.

SELECT   o.id, o.name,
         COUNT(w.id) AS num_waterfalls
FROM     owner o LEFT JOIN waterfalls w
         ON o.id = w.owner_id
GROUP BY o.id, o.name

id    name             num_waterfalls
----- ---------------- ---------------
    1 Pictured Rocks                 3
    2 Michigan Nature                3
    3 AF LLC                         1
    4 MI DNR                         1
    5 Horseshoe Falls                0

DISTINTO

Cuando se incluye una columna en la cláusula SELECT, por defecto se devuelven todas las filas. Para ser más explícito, puedes incluir la palabra clave ALL, pero es puramente opcional. Las siguientes consultas devuelven cada combinación type/open_to_public.

SELECT o.type, w.open_to_public
FROM owner o
JOIN waterfall w ON o.id = w.owner_id;

o:

SELECT ALL o.type, w.open_to_public
FROM owner o
JOIN waterfall w ON o.id = w.owner_id;

type     open_to_public
-------- ---------------
public   y
public   y
public   y
private  y
private  y
private  y
private  y
public   y

Si quieres eliminar las filas duplicadas de los resultados, puedes utilizar la palabra clave DISTINCT. La siguiente consulta devuelve una lista de combinaciones únicas type/open_to_public.

SELECT DISTINCT o.type, w.open_to_public
FROM owner o
JOIN waterfall w ON o.id = w.owner_id;

type     open_to_public
-------- ---------------
public   y
private  y

CONTAR y DISTINTO

Para contar el número de valores únicos dentro de una sola columna, puedes combinar las palabras clave COUNT y DISTINCT dentro de la cláusula SELECT. La siguiente consulta devuelve el número de valores únicos type.

SELECT COUNT(DISTINCT type) AS unique
FROM owner;

unique
-------
      2

Para contar el número de combinaciones únicas de varias columnas, puedes envolver una consulta DISTINCT como una subconsulta, y luego hacer una COUNT en la subconsulta. La siguiente consulta devuelve el número de combinaciones únicas type/open_to_public.

SELECT COUNT(*) AS num_unique
FROM (SELECT DISTINCT o.type, w.open_to_public
      FROM owner o JOIN waterfall w
      ON o.id = w.owner_id) my_subquery;

num_unique
-----------
         2

MySQL y PostgreSQL admiten el uso de la sintaxis COUNT(DISTINCT) en varias columnas. Las dos consultas siguientes son equivalentes a la consulta anterior, sin necesidad de una subconsulta:

-- MySQL equivalent
SELECT COUNT(DISTINCT o.type, w.open_to_public)
       AS num_unique
       FROM owner o JOIN waterfall w
            ON o.id = w.owner_id;

-- PostgreSQL equivalent
SELECT COUNT(DISTINCT (o.type, w.open_to_public))
       AS num_unique
       FROM owner o JOIN waterfall w
            ON o.id = w.owner_id;

num_unique
-----------
          2

La cláusula FROM

La cláusula FROM se utiliza para especificar la fuente de los datos que quieres recuperar. El caso más sencillo es nombrar una única tabla o vista en la cláusula FROM de la consulta.

SELECT name
FROM waterfall;

Puedes calificar una tabla o vista con un nombre de base de datos o de esquema utilizando la notación de punto. La siguiente consulta recupera datos específicamente de la tabla waterfall dentro del esquema sqlbook:

SELECT name
FROM sqlbook.waterfall;

Desde varias tablas

En lugar de recuperar datos de una tabla, a menudo querrás reunir datos de varias tablas. La forma más habitual de hacerlo es utilizando una cláusula JOIN dentro de la cláusula FROM. La siguiente consulta recupera datos de las tablas waterfall y tour y muestra una única tabla de resultados.

SELECT *
FROM waterfall w JOIN tour t
     ON w.id = t.stop;

id    name            ... name      stop  ...
----- ---------------     --------- -----
    1 Munising Falls      M-28          1
    1 Munising Falls      Munising      1
    2 Tannery Falls       Munising      2
    3 Alger Falls         M-28          3
    3 Alger Falls         Munising      3
...

Vamos a desglosar cada parte del bloque de código.

Alias de la tabla

waterfall w JOIN tour t

Las tablas waterfall y tour reciben los alias de tabla w y t, que son nombres temporales para las tablas dentro de la consulta. Los alias de tabla no son necesarios en una cláusula JOIN, pero son muy útiles para acortar los nombres de las tablas a las que hay que hacer referencia dentro de las cláusulas ON y SELECT.

ÚNETE A ... EN ...

waterfall w JOIN tour t
ON w.id = t.stop

Estas dos tablas se unen mediante la palabra clave JOIN. Una cláusula JOIN siempre va seguida de una cláusula ON, que especifica cómo deben enlazarse las tablas. En este caso, la id de la cascada de la tabla waterfall debe coincidir con la stop de la cascada de la tabla tour.

Nota

Puede que veas las cláusulas FROM, JOIN, y ON en líneas diferentes o con sangría. Esto no es necesario, pero resulta útil para facilitar la lectura, sobre todo cuando unes muchas tablas.

Tabla de resultados

Una consulta siempre da como resultado una única tabla. La tabla waterfall tiene 12 columnas y la tabla tour tiene 3 columnas. Tras unir estas tablas, la tabla de resultados tiene 15 columnas.

id    name            ... name      stop  ...
----- ---------------     --------- -----
    1 Munising Falls      M-28          1
    1 Munising Falls      Munising      1
    2 Tannery Falls       Munising      2
    3 Alger Falls         M-28          3
    3 Alger Falls         Munising      3
...

Observarás que hay dos columnas llamadas name en la tabla de resultados. La primera es de la tabla waterfall, y lasegunda es de la tabla tour. Para referirte a ellas en la cláusula SELECT, tendrías que calificar los nombres de las columnas.

SELECT w.name, t.name
FROM waterfall w JOIN tour t
     ON w.id = t.stop;

name            name
--------------- ---------
Munising Falls  M-28
Munising Falls  Munising
Tannery Falls   Munising
...

Para diferenciar las dos columnas, también querrás poner un alias a los nombres de las columnas.

SELECT w.name AS waterfall_name,
       t.name AS tour_name
FROM waterfall w JOIN tour t
     ON w.id = t.stop;

waterfall_name  tour_name
--------------- ----------
Munising Falls  M-28
Munising Falls  Munising
Tannery Falls   Munising
Alger Falls     M-28
Alger Falls     Munising
...

variaciones de JOIN

En el ejemplo anterior, si una cascada no aparece en ningún recorrido, no aparecería en la tabla de resultados. Si quisieras ver todas las cascadas en los resultados, tendrías que utilizar otro tipo de unión.

JOIN Por defecto INNER JOIN

Este ejemplo utiliza una simple palabra clave JOIN para unir los datos de dos tablas, aunque es una buena práctica indicar explícitamente el tipo de unión que estás utilizando. JOIN por sí sola tiene un valor predeterminado de an INNER JOINlo que significa que en los resultados sólo aparecen los registros que están en ambas tablas.

Hay varios tipos de unión utilizados en SQL, que se tratan con más detalle en "Unir tablas", en el Capítulo 9.

Desde Subconsultas

Una subconsulta es una consulta anidada dentro de otra consulta. Las subconsultas dentro de la cláusula FROM deben ser sentencias SELECT independientes, lo que significa que no hacen referencia alguna a la consulta externa y pueden ejecutarse por sí solas.

Nota

Una subconsulta dentro de la cláusula FROM también se conoce como tabla derivada, porque la subconsulta acaba actuando esencialmente como una tabla mientras dura la consulta.

La siguiente consulta enumera todas las cascadas de titularidad pública, con la parte de la subconsulta en negrita.

SELECT w.name AS waterfall_name,
       o.name AS owner_name
FROM (SELECT * FROM owner WHERE type = 'public') o
     JOIN waterfall w
     ON o.id = w.owner_id;

waterfall_name  owner_name
--------------- ---------------
Little Miners   Pictured Rocks
Miners Falls    Pictured Rocks
Munising Falls  Pictured Rocks
Wagner Falls    MI DNR

Es importante comprender el orden en que se ejecuta la consulta.

Paso 1: Ejecuta la subconsulta

Primero se ejecuta el contenido de la subconsulta. Puedes ver que el resultado es una tabla sólo de propietarios públicos:

SELECT * FROM owner WHERE type = 'public';

id    name            phone         type
----- --------------- ------------- -------
    1 Pictured Rocks  906.387.2607  public
    4 MI DNR          906.228.6561  public

Volviendo a la consulta original, verás que la subconsulta va seguida inmediatamente de la letra o. Éste es el nombre temporal, o alias, que estamos asignando a los resultados de la subconsulta.

Nota

Los alias son necesarios para las subconsultas dentro de la cláusula FROM en MySQL, PostgreSQL y SQL Server, pero no en Oracle y SQLite.

Paso 2: Ejecuta la consulta completa

A continuación, puedes pensar que la letra o ocupa el lugar de la subconsulta. La consulta se ejecuta ahora como de costumbre.

SELECT w.name AS waterfall_name,
       o.name AS owner_name
FROM o JOIN waterfall w
     ON o.id = w.owner_id;

waterfall_name  owner_name
--------------- ---------------
Little Miners   Pictured Rocks
Miners Falls    Pictured Rocks
Munising Falls  Pictured Rocks
Wagner Falls    MI DNR

Subconsultas frente a la cláusula WITH

Una alternativa a escribir una subconsulta es escribir una expresión común de tabla (CTE) utilizando en su lugar una cláusula WITH. La ventaja de la cláusula WITH es que la subconsulta se nombra desde el principio, lo que hace que el código sea más limpio y también permite hacer referencia a la subconsulta varias veces.

WITH o AS (SELECT * FROM owner
           WHERE type = 'public')

SELECT w.name AS waterfall_name,
       o.name AS owner_name
FROM o JOIN waterfall w
     ON o.id = w.owner_id;

La cláusula WITH es compatible con MySQL 8.0+ (2018 y posteriores), PostgreSQL, Oracle, SQL Server y SQLite. "Expresiones comunes de tabla" en el Capítulo 9 incluye más ejemplos de esta técnica.

¿Por qué utilizar una subconsulta en la cláusula FROM?

La principal ventaja de utilizar subconsultas es que puedes convertir un problema grande en otros más pequeños. Aquí tienes dos ejemplos:

Ejemplo 1: Múltiples pasos para llegar a los resultados

Supongamos que quieres hallar el número medio de paradas de un recorrido. En primer lugar, tendrías que hallar el número de paradas de cada recorrido y, a continuación, calcular la media de los resultados.

La siguiente consulta encuentra el número de paradas de cada recorrido:

SELECT name, MAX(stop) as num_stops
FROM tour
GROUP BY name;

name      num_stops
--------- ----------
M-28              11
Munising           6
US-2              14

Entonces podrías convertir la consulta en una subconsulta y escribir otra consulta a su alrededor para encontrar la media:

SELECT AVG(num_stops) FROM
(SELECT name, MAX(stop) as num_stops
FROM tour
GROUP BY name) tour_stops;

AVG(num_stops)
-----------------
10.3333333333333

Ejemplo 2: La tabla de la cláusula FROM es demasiado grande

El objetivo original era hacer una lista de todas las cascadas de titularidad pública. En realidad, esto puede hacerse sin una subconsulta y, en su lugar, con JOIN:

SELECT w.name AS waterfall_name,
       o.name AS owner_name
FROM   owner o
       JOIN waterfall w ON o.id = w.owner_id
WHERE  o.type = 'public';

waterfall_name  owner_name
--------------- ---------------
Little Miners   Pictured Rocks
Miners Falls    Pictured Rocks
Munising Falls  Pictured Rocks
Wagner Falls    MI DNR

Supongamos que la consulta tarda mucho tiempo en ejecutarse. Esto puede ocurrir cuando unes tablas enormes (piensa en decenas de millones de filas). Hay varias formas de reescribir la consulta para acelerarla, y una de ellas es utilizar una subconsulta.

Como sólo nos interesan los propietarios públicos, podemos escribir primero una subconsulta que filtre todos los propietarios privados. La tabla owner, más pequeña, se uniría entonces a la tabla waterfall, lo que llevaría menos tiempo y produciría los mismos resultados.

SELECT w.name AS waterfall_name,
       o.name AS owner_name
FROM   (SELECT * FROM owner
       WHERE type = 'public') o
       JOIN waterfall w ON o.id = w.owner_id;

waterfall_name  owner_name
--------------- ---------------
Little Miners   Pictured Rocks
Miners Falls    Pictured Rocks
Munising Falls  Pictured Rocks
Wagner Falls    MI DNR

Estos son sólo dos de los muchos ejemplos de cómo se pueden utilizar las subconsultas para dividir una consulta mayor en pasos más pequeños.

La cláusula WHERE

La cláusula WHERE se utiliza para restringir los resultados de la consulta a sólo las filas de interés, o dicho de forma sencilla, es el lugar donde filtrar los datos. Rara vez querrás mostrar todas las filas de una tabla, sino las que coincidan con criterios específicos.

Consejo

Cuando explores una tabla con millones de filas, nunca querrás hacer un SELECT * FROM my_table; porque tardará un tiempo innecesariamente largo en ejecutarse.

En su lugar, es una buena idea filtrar los datos. Dos formas habituales de hacerlo son

Filtra por una columna dentro de la cláusula WHERE

Mejor aún, filtra por una columna que ya esté indexada para que la recuperación sea aún más rápida.

SELECT *
FROM my_table
WHERE year_id = 2021;

Muestra las primeras filas de datos con la cláusula LIMIT cláusula

(o WHERE ROWNUM <= 10 en Oracle o SELECT TOP 10 * en SQL Server)

SELECT *
FROM my_table
LIMIT 10;

La siguiente consulta busca todas las cascadas que no contengan Cataratas en el nombre. Encontrarás más información sobre la palabra clave LIKE en el capítulo 7.

SELECT id, name
FROM waterfall
WHERE name NOT LIKE '%Falls%';

id    name
----- ----------------
    7 Little Miners
   14 Rapid River Fls

La sección en negrita suele denominarse declaración condicional o predicado. El predicado realiza una comparación lógica para cada fila de datos que da como resultado TRUE/FALSE/UNKNOWN.

La tabla waterfall tiene 16 filas. Para cada fila, comprueba siel nombre dela cascada contiene o no Cataratas. Si no contiene Cataratas, entonces el predicado name NOT LIKE '%Falls%' es TRUE, y la fila se devuelve en los resultados, como ocurrió con las dosfilas anteriores.

Predicados múltiples

También es posible combinar varios predicados con operadores como AND o OR. El siguiente ejemplo muestra cascadas sin Cataratas en su nombre y que tampoco tienen propietario:

SELECT id, name
FROM waterfall
WHERE name NOT LIKE '%Falls%'
      AND owner_id IS NULL;

id    name
----- ----------------
   14 Rapid River Fls

Encontrarás más detalles sobre los operadores en Operadores, en el capítulo 7.

Filtrar en subconsultas

Una subconsulta es una consulta anidada dentro de otra consulta, y la cláusula WHERE es un lugar habitual para encontrar una. El siguiente ejemplo recupera las cascadas de acceso público situadas en el condado de Alger:

SELECT w.name
FROM   waterfall w
WHERE  w.open_to_public = 'y'
       AND w.county_id IN (
           SELECT c.id FROM county c
           WHERE c.name = 'Alger');

name
---------------
Munising Falls
Tannery Falls
Alger Falls
...

Nota

A diferencia de las subconsultas dentro de la cláusula SELECT o la cláusula FROM, las subconsultas en la cláusula WHERE no requieren un alias. De hecho, obtendrás un error si incluyes un alias.

¿Por qué utilizar una subconsulta en la cláusula WHERE?

El objetivo original era recuperar las cascadas de acceso público situadas en el condado de Alger. Si tuvieras que escribir esta consulta desde cero, probablemente empezarías con lo siguiente:

SELECT w.name
FROM   waterfall w
WHERE  w.open_to_public = 'y';

Llegados a este punto, ya tienes todas las cascadas que son de acceso público. El toque final es encontrar las que están específicamente en el condado de Alger. Sabes que la tabla waterfall no tiene una columna con el nombre del condado, pero la tabla county sí.

Tienes dos opciones para obtener el nombre del condado en los resultados. Puedes (1) escribir una subconsulta dentro de la cláusula WHERE que extraiga específicamente la información del condado de Alger o (2) unir las tablas waterfall y county:

-- Subquery in WHERE clause
SELECT w.name
FROM   waterfall w
WHERE  w.open_to_public = 'y'
       AND w.county_id IN (
           SELECT c.id FROM county c
           WHERE c.name = 'Alger');

o:

-- JOIN clause
SELECT w.name
FROM   waterfall w INNER JOIN county c
       ON w.county_id = c.id
WHERE  w.open_to_public = 'y'
       AND c.name = 'Alger';

name
---------------
Munising Falls
Tannery Falls
Alger Falls
...

Las dos consultas producen los mismos resultados. La ventaja del primer enfoque es que las subconsultas suelen ser más fáciles de entender que las uniones. La ventaja del segundo enfoque es que las uniones suelen ejecutarse más rápido que las subconsultas.

Trabajar > Optimizar

Al escribir código SQL, a menudo hay varias formas de hacer lo mismo.

Tu máxima prioridad debe ser escribir código que funcione. Si tarda mucho en ejecutarse o es feo, no importa... ¡funciona!

El siguiente paso, si tienes tiempo, es optimizar el código mejorando el rendimiento, quizás reescribiéndolo con un JOIN, haciéndolo más legible con indentaciones y mayúsculas, etc.

No te estreses por escribir el código más optimizado desde el principio, sino por escribir código que funcione. Escribir código elegante viene con la experiencia.

Otras formas de filtrar datos

La cláusula WHERE no es el único lugar dentro de una sentencia SELECT para filtrar filas de datos.

• FROM cláusula Al unir tablas, la cláusula ON especifica cómo deben enlazarse. Aquí es donde puedes incluir condiciones para restringir las filas de datos devueltas por la consulta. Para más detalles, consulta Unir tablas en el Capítulo 9.

• HAVING cláusula: Si hay agregaciones dentro de la sentencia SELECT, la cláusula HAVING es donde se especifica cómo deben filtrarse las agregaciones. Consulta "La cláusula HAVING" para más detalles.

• LIMIT cláusula LIMIT: Para mostrar un número concreto de filas, puedes utilizar la cláusula LIMIT. En Oracle, esto se hace con WHERE ROWNUM y en SQL Server, con SELECT TOP. Para más detalles, consulta "La cláusula LIMIT" en este capítulo.

La cláusula GROUP BY

El propósito de la cláusula GROUP BY es reunir las filas en grupos y resumir las filas dentro de los grupos de alguna manera, devolviendo finalmente sólo una fila por grupo. A veces se denomina a esto "trocear" las filas en grupos y "enrollar" las filas de cada grupo.

La siguiente consulta cuenta el número de cascadas a lo largo de cada uno de los recorridos:

SELECT   t.name AS tour_name,
         COUNT(*) AS num_waterfalls
FROM     waterfall w INNER JOIN tour t
         ON w.id = t.stop
GROUP BY t.name;

tour_name  num_waterfalls
---------- ---------------
M-28                     6
Munising                 6
US-2                     4

Hay dos partes en las que centrarse aquí:

• La recopilación de filas, que se especifica en la cláusula GROUP BY

• La integración de filas dentro de grupos, que se especifica en la cláusula SELECT

Paso 1: Recogida de filas

En la cláusula GROUP BY:

GROUP BY t.name

decimos que nos gustaría que mirara todas las filas de datos y agrupara las cascadas de la excursión M-28, todas las cascadas de la excursión Munising, etc. Entre bastidores, los datos se agrupan así:

tour_name  waterfall_name
---------- ----------------
M-28       Munising Falls
M-28       Alger Falls
M-28       Scott Falls
M-28       Canyon Falls
M-28       Agate Falls
M-28       Bond Falls

Munising   Munising Falls
Munising   Tannery Falls
Munising   Alger Falls
Munising   Wagner Falls
Munising   Horseshoe Falls
Munising   Miners Falls



US-2       Bond Falls
US-2       Fumee Falls
US-2       Kakabika Falls
US-2       Rapid River Fls

Paso 2: El resumen de filas

En la cláusula SELECT,

SELECT t.name AS tour_name,
       COUNT(*) AS num_waterfalls

decimos que para cada grupo, o cada recorrido, queremos contar el número de filas de datos del grupo. Como cada fila representa una cascada, el resultado sería el número total de cascadas a lo largo de cada recorrido.

La función COUNT() se conoce más formalmente como función agregada, o función que resume muchas filas de datos en un único valor. Puedes encontrar más funciones agregadas en "Funciones agregadas", en el Capítulo 7.

Advertencia

En este ejemplo, COUNT(*) devuelve el número de cascadas de cada recorrido. Sin embargo, esto sólo se debe a que cada fila de datos de las tablas waterfall y tour representa una única cascada.

Si una única cascada apareciera en varias filas, COUNT(*) daría como resultado un valor mayor del esperado. En este caso, podrías utilizar COUNT(DISTINCT waterfall_name) en su lugar para encontrar las cascadas únicas. Puedes encontrar más detalles en COUNT and DISTINCT.

La clave es que es importante volver a comprobar manualmente los resultados de la función de agregado para asegurarte de que resume los datos de la forma que pretendías.

Ahora que se han creado los grupos con la cláusula GROUP BY, la función de agregado se aplicará una vez a cada grupo:

tour_name  COUNT(*)
---------- ---------
M-28               6
M-28
M-28
M-28
M-28
M-28

Munising           6
Munising
Munising
Munising
Munising
Munising

US-2               4
US-2
US-2
US-2

Las columnas a las que no se ha aplicado una función de agregado, que en este caso es la columna tour_name, se contraen ahora en un solo valor:

tour_name  COUNT(*)
---------- ---------
M-28               6
Munising           6
US-2               4

Nota

Este colapso de muchas filas detalladas en una fila agregada significa que al utilizar una cláusula GROUP BY, la cláusula SELECT sólo debe contener:

• Todas las columnas de la cláusula GROUP BY: t.name

• Agregaciones: COUNT(*)

SELECT t.name AS tour_name,
       COUNT(*) AS num_waterfalls
...
GROUP BY t.name;

No hacerlo podría dar lugar a un mensaje de error o devolver valores inexactos.

GROUP BY En la práctica

Estos son los pasos que debes seguir cuando utilices un GROUP BY:

1. Averigua qué columna(s) quieres utilizar para separar, o agrupar, tus datos (por ejemplo, el nombre del recorrido).

2. Averigua cómo te gustaría resumir los datos dentro de cada grupo (por ejemplo, contar las cascadas dentro de cada recorrido).

Cuando te hayas decidido por ellos:

1. En la cláusula SELECT, indica la(s) columna(s) por la(s) que quieres agrupar (por ejemplo, nombre del recorrido) y la(s) agregación(es) que quieres calcular dentro de cada grupo (por ejemplo, recuento de cascadas).

2. En la cláusula GROUP BY, enumera todas las columnas que no sean agregaciones (por ejemplo, nombre del recorrido).

Para situaciones de agrupación más complejas, como ROLLUP, CUBE y GROUPING SETS, ve a "Agrupar y resumir" en el capítulo 8.

La cláusula HAVING

La cláusula HAVING impone restricciones a las filas devueltas por una consulta GROUP BY. En otras palabras, te permite filtrar los resultados después de aplicar GROUP BY.

Nota

Una cláusula HAVING siempre sigue inmediatamente a una cláusula GROUP BY. Sin una cláusula GROUP BY, no puede haber cláusula HAVING.

Se trata de una consulta que enumera el número de cascadas de cada recorrido utilizando una cláusula GROUP BY:

SELECT   t.name AS tour_name,
         COUNT(*) AS num_waterfalls
FROM     waterfall w INNER JOIN tour t
         ON w.id = t.stop
GROUP BY t.name;


tour_name  num_waterfalls
---------- ---------------
M-28                     6
Munising                 6
US-2                     4

Supongamos que sólo queremos listar los recorridos que tienen exactamente seis paradas. Para ello, añadirías una cláusula HAVING después de la cláusula GROUP BY:

SELECT   t.name AS tour_name,
         COUNT(*) AS num_waterfalls
FROM     waterfall w INNER JOIN tour t
         ON w.id = t.stop
GROUP BY t.name
HAVING   COUNT(*) = 6;

tour_name  num_waterfalls
---------- ---------------
M-28                     6
Munising                 6

DONDE frente a TENER

La finalidad de ambas cláusulas es filtrar datos. Si lo que pretendes es

• Filtra sobre determinadas columnas, escribe tus condiciones dentro de la cláusula WHERE

• Filtra sobre agregaciones, escribe tus condiciones dentro de la cláusula HAVING

El contenido de una cláusula WHERE y HAVING no se puede intercambiar:

• Nunca pongas una condición con agregación en la cláusula WHERE. Obtendrás un error.

• Nunca pongas una condición en la cláusula HAVING que no implique una agregación. Esas condiciones se evalúan mucho más eficazmente en la cláusula WHERE.

Verás que la cláusula HAVING remite a la agregación COUNT(*),

SELECT COUNT(*) AS num_waterfalls
...
HAVING COUNT(*) = 6;

y no el alias,

# code will not run
SELECT COUNT(*) AS num_waterfalls
...
HAVING num_waterfalls = 6;

El motivo es el orden de ejecución de las cláusulas. La cláusula SELECT se escribe antes que la cláusula HAVING. Sin embargo, la cláusula SELECT se ejecuta en realidad después de la cláusula HAVING.

Eso significa que el alias num_waterfalls de la cláusula SELECT no existe en el momento en que se ejecuta la cláusula HAVING. En su lugar, la cláusula HAVING debe hacer referencia a la agregación bruta COUNT(*).

Nota

MySQL y SQLite son excepciones, y permiten alias (num_waterfalls) en la cláusula HAVING.

La cláusula ORDER BY

La cláusula ORDER BY se utiliza para especificar cómo quieres que se ordenen los resultados de una consulta.

La siguiente consulta devuelve una lista de propietarios y cascadas, sin ordenar:

SELECT COALESCE(o.name, 'Unknown') AS owner,
       w.name AS waterfall_name
FROM   waterfall w
       LEFT JOIN owner o ON w.owner_id = o.id;

owner            waterfall_name
---------------- ---------------
Pictured Rocks   Munising Falls
Michigan Nature  Tannery Falls
AF LLC           Alger Falls
MI DNR           Wagner Falls
Unknown          Horseshoe Falls
...

La función COALESCE

La función COALESCE sustituye todos los valores NULL de una columna por un valor diferente. En este caso, convirtió los valores NULL de la columna o.name en el texto Unknown.

Si no se hubiera utilizado aquí la función COALESCE, todas las cascadas sin propietario habrían quedado fuera de los resultados. En lugar de eso, ahora se marcan como que tienen un propietario Unknown, y se pueden clasificar e incluir en los resultados.

Encontrarás más detalles en el Capítulo 7.

La siguiente consulta devuelve a la misma lista, pero primero ordenada alfabéticamente por propietario y luego por cascada:

SELECT   COALESCE(o.name, 'Unknown') AS owner,
         w.name AS waterfall_name
FROM     waterfall w
         LEFT JOIN owner o ON w.owner_id = o.id
ORDER BY owner, waterfall_name;

owner            waterfall_name
---------------- ---------------
AF LLC           Alger Falls
MI DNR           Wagner Falls
Michigan Nature  Tannery Falls
Michigan Nature  Twin Falls #1
Michigan Nature  Twin Falls #2
...

La ordenación por defecto es ascendente, lo que significa que el texto irá de la A a la Z y los números de menor a mayor. Puedes utilizar las palabras clave ASCENDING y DESCENDING (que pueden abreviarse como ASC y DESC) para controlar la ordenación de cadacolumna.

Lo siguiente es una modificación de la ordenación anterior, pero esta vez ordena los nombres de los propietarios en orden inverso:

SELECT COALESCE(o.name, 'Unknown') AS owner,
       w.name AS waterfall_name
...
ORDER BY owner DESC, waterfall_name ASC;

owner            waterfall_name
---------------- ---------------
Unknown          Agate Falls
Unknown          Bond Falls
Unknown          Canyon Falls
...

Puedes ordenar por columnas y expresiones que no estén en tu lista SELECT:

SELECT   COALESCE(o.name, 'Unknown') AS owner,
         w.name AS waterfall_name
FROM     waterfall w
         LEFT JOIN owner o ON w.owner_id = o.id
ORDER BY o.id DESC, w.id;

owner            waterfall_name
---------------- ---------------
MI DNR           Wagner Falls
AF LLC           Alger Falls
Michigan Nature  Tannery Falls
...

También puedes ordenar por posición numérica de columna:

SELECT COALESCE(o.name, 'Unknown') AS owner,
       w.name AS waterfall_name
...
ORDER BY 1 DESC, 2 ASC;

owner            waterfall_name
---------------- ---------------
Unknown          Agate Falls
Unknown          Bond Falls
Unknown          Canyon Falls
...

Como las filas de una tabla SQL no están ordenadas, si no incluyes una cláusula ORDER BY en una consulta, cada vez que la ejecutes, los resultados podrían mostrarse en un orden distinto.

No se puede utilizar ORDER BY en una subconsulta

De las seis cláusulas principales, sólo la cláusula ORDER BY no puede utilizarse en una subconsulta. Lamentablemente, no puedes forzar que las filas de una subconsulta estén ordenadas.

Para evitar este problema, tendrías que reescribir tu consulta con una lógica diferente para evitar utilizar una cláusula ORDER BY dentro de la subconsulta, y sólo incluir una cláusula ORDER BY en la consulta externa.

La cláusula LÍMITE

Al visualizar rápidamente una tabla, es una buena práctica devolver un número limitado de filas en lugar de toda la tabla.

MySQL, PostgreSQL y SQLite admiten la cláusula LIMIT. Oracle y SQL Server utilizan una sintaxis diferente con la mismafuncionalidad:

-- MySQL, PostgreSQL, and SQLite
SELECT *
FROM owner
LIMIT 3;

-- Oracle
SELECT *
FROM owner
WHERE ROWNUM <= 3;

-- SQL Server
SELECT TOP 3 *
FROM owner;

id  name             phone         type
--- ---------------- ------------- --------
  1 Pictured Rocks   906.387.2607  public
  2 Michigan Nature  517.655.5655  private
  3 AF LLC                         private

Otra forma de limitar el número de filas devueltas es filtrar por una columna dentro de la cláusula WHERE cláusula. El filtrado se ejecutará aún más rápido si la columna está indexada.