Guía para principiantes sobre el verdadero orden de las operaciones SQL

El lenguaje SQL es muy intuitivo. Hasta que no lo es.

A lo largo de los años, mucha gente ha criticado el lenguaje SQL por diversas razones. Por ejemplo: Los IDEs no pueden adivinar fácilmente qué opciones de autocompletado ofrecer, porque mientras no se especifique la cláusula FROM, no hay tablas en el ámbito (todavía):

-- Don't you wish this would be completed to first_name?SELECT first_na...-- Aaah, now it works:SELECT first_na...FROM customer

Estas cosas son raras, porque el orden léxico de las operaciones no coincide con el orden lógico de las mismas. Los humanos a veces (a menudo) entendemos intuitivamente esta diferencia de orden. Por ejemplo, sabemos que vamos a seleccionar de la tabla de clientes. Pero el IDE no lo sabe.

GROUP BY contribuye a la mayor confusión

Cuando un desarrollador junior / principiante de SQL comienza a trabajar con SQL, bastante rápidamente, se enterarán de la agregación y GROUP BY. Y rápidamente escribirán cosas como:

SELECT count(*)FROM customer

¡Sí, tenemos 200 clientes!

Y luego:

SELECT count(*)FROM customerWHERE first_name = 'Steve'

¡Vaya, 90 de ellos se llaman Steve! Interesante. Averigüemos cuántos tenemos por nombre…

SELECT first_name, count(*)FROM customerGROUP BY first_name

¡Ajá!

FIRST_NAME COUNT------------------Steve 90Jane 80Joe 20Janet 10

Muy bonito. Pero, ¿son todos iguales? Comprobemos también el apellido

SELECT first_name, last_name, count(*)FROM customerGROUP BY first_name

¡Oops!

ORA-00979: not a GROUP BY expression

Jeez, ¿qué significa? (nota, desafortunadamente, los usuarios de MySQL que no usen el modo STRICT seguirán obteniendo un resultado aquí con apellidos arbitrarios!, por lo que un nuevo usuario de MySQL no entenderá su error)

¿Cómo explicar fácilmente esto a un novato en SQL? Parece obvio para los «profesionales», pero ¿es realmente obvio? Es lo suficientemente obvio como para poder explicarlo fácilmente a un novato? Piensa en ello. ¿Por qué cada una de estas afirmaciones son semánticamente correctas o incorrectas?

El problema está relacionado con la sintaxis

La sintaxis de SQL funciona de forma similar a la lengua inglesa. Se trata de un comando. Comenzamos los comandos con verbos. El verbo es SELECT (o INSERT, UPDATE, DELETE, CREATE, DROP, etc. etc.)

Desgraciadamente, el lenguaje humano es increíblemente inadecuado para el mundo mucho más formal de la programación. Aunque ofrece algún consuelo a los nuevos usuarios (posiblemente no programadores) que son principiantes absolutos, sólo hace las cosas difíciles para todos los demás. Todas las diferentes cláusulas SQL tienen interdependencias extremadamente complejas. Por ejemplo:

  • En presencia de una cláusula GROUP BY, sólo las expresiones construidas a partir de expresiones GROUP BY (o dependencias funcionales de las mismas), o las funciones agregadas pueden ser utilizadas en las cláusulas HAVING, SELECT, y ORDER BY.
  • Por razones de simplicidad, ni siquiera vamos a hablar de GROUPING SETS
  • De hecho, hay incluso algunos casos en los que GROUP BY está implícito. Por ejemplo. si escribes una cláusula HAVING «desnuda»
  • Una sola función de agregación en la cláusula SELECT (en ausencia de GROUP BY) forzará la agregación en una sola fila
  • De hecho, esto también puede estar implícito poniendo esa función de agregación en ORDER BY (por la razón que sea)
  • Puedes ORDER BY bastantes expresiones que hagan referencia a cualquier columna de la cláusula FROM sin SELECT. Pero eso ya no es cierto si escribes SELECT DISTINCT

La lista es interminable. Si te interesa, puedes leer los documentos estándar de SQL y comprobar la cantidad de interdependencias extrañas y complicadas que existen entre las numerosas cláusulas de la sentencia SELECT.

¿Se puede entender esto alguna vez?

¡Por suerte, sí! Hay un sencillo truco, que siempre estoy explicando a los delegados que visitan mi Masterclass de SQL. El orden léxico (sintáctico) de las operaciones (cláusulas) de SQL no se corresponde en absoluto con el orden lógico de las operaciones (aunque, a veces, coinciden). Gracias a los optimizadores modernos, el orden tampoco se corresponde con el orden real de las operaciones, por lo que realmente tenemos: orden sintáctico -> lógico -> real, pero dejemos eso de lado por ahora.

El orden lógico de las operaciones es el siguiente (por «simplicidad» estoy dejando fuera cosas específicas del vendedor como CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT y todas las demás):

  • FROM: Esto es en realidad lo primero que ocurre, lógicamente. Antes de nada, cargamos todas las filas de todas las tablas y las unimos. Antes de que grites y te enfades: De nuevo, esto es lo que ocurre primero lógicamente, no realmente. El optimizador muy probablemente no hará esta operación primero, eso sería una tontería, sino que accederá a algún índice basado en la cláusula WHERE. Pero de nuevo, lógicamente, esto ocurre primero. Además: todas las cláusulas JOIN son en realidad parte de esta cláusula FROM. JOIN es un operador en el álgebra relacional. Al igual que + y - son operadores en aritmética. No es una cláusula independiente, como SELECT o FROM
  • WHERE: Una vez que hemos cargado todas las filas de las tablas anteriores, ahora podemos volver a tirarlas usando WHERE
  • GROUP BY: Si quieres, puedes tomar las filas que quedan después de WHERE y ponerlas en grupos o cubos, donde cada grupo contiene el mismo valor para la expresión GROUP BY (y todas las demás filas se ponen en una lista para ese grupo). En Java, se obtendría algo como: Map<String, List<Row>>. Si especifica una cláusula GROUP BY, entonces sus filas reales contienen sólo las columnas del grupo, ya no las columnas restantes, que ahora están en esa lista. Esas columnas de la lista sólo son visibles para las funciones de agregación que pueden operar sobre esa lista. Ver abajo.
  • agregaciones: Esto es importante de entender. No importa dónde ponga su función de agregación sintácticamente (es decir, en la cláusula SELECT, o en la cláusula ORDER BY), este aquí es el paso donde se calculan las funciones de agregación. Justo después de GROUP BY. (recuerde: lógicamente. Las bases de datos inteligentes pueden haberlas calculado antes, en realidad). Esto explica por qué no se puede poner una función agregada en la cláusula WHERE, porque todavía no se puede acceder a su valor. La cláusula WHERE ocurre lógicamente antes del paso de agregación. Las funciones de agregación pueden acceder a las columnas que has puesto en «esta lista» para cada grupo, arriba. Después de la agregación, «esta lista» desaparecerá y ya no estará disponible. Si no tiene una cláusula GROUP BY, sólo habrá un gran grupo sin ninguna clave, que contiene todas las filas.
  • HAVING: … pero ahora puede acceder a los valores de la función de agregación. Por ejemplo, puede comprobar que count(*) > 1 en la cláusula HAVING. Debido a que HAVING es después de GROUP BY (o implica GROUP BY), ya no podemos acceder a las columnas o expresiones que no eran GROUP BY columnas.
  • WINDOW: Si usted está usando la función de ventana impresionante, este es el paso donde se calculan todos. Sólo ahora. Y lo genial es que, como ya hemos calculado (¡lógicamente!) todas las funciones de agregado, podemos anidar funciones de agregado en funciones de ventana. Por lo tanto, está perfectamente bien escribir cosas como sum(count(*)) OVER () o row_number() OVER (ORDER BY count(*)). El hecho de que las funciones de ventana sólo se calculen lógicamente ahora también explica por qué sólo se pueden poner en las cláusulas SELECT o ORDER BY. No están disponibles para la cláusula WHERE, lo que ocurría antes. ¡Tenga en cuenta que PostgreSQL y Sybase SQL Anywhere tienen una cláusula WINDOW real!
  • SELECT: Finalmente. Ahora podemos utilizar todas las filas que se producen a partir de las cláusulas anteriores y crear nuevas filas / tuplas de ellos usando SELECT. Podemos acceder a todas las funciones de ventana que hemos calculado, a todas las funciones de agregado que hemos calculado, a todas las columnas de agrupación que hemos especificado, o si no hemos agrupado/agregado, podemos utilizar todas las columnas de nuestra cláusula FROM. Recuerda: Aunque parezca que estamos agregando cosas dentro de SELECT, esto ha ocurrido hace tiempo, y la dulce función count(*) no es más que una referencia al resultado.
  • DISTINCT: ¡Sí! DISTINCT pasa después de SELECT, incluso si se pone antes de su lista de columnas SELECT, sintácticamente. Pero piénsalo. Tiene mucho sentido. ¿Cómo podemos eliminar filas distintas, si no sabemos todas las filas (y sus columnas) todavía?
  • UNION, INTERSECT, EXCEPT: Esto es una obviedad. Un UNION es un operador que conecta dos subconsultas. Todo lo que hemos hablado hasta ahora era una subconsulta. La salida de una unión es una nueva consulta que contiene los mismos tipos de fila (es decir, las mismas columnas) que la primera subconsulta. Por lo general. Porque en el chiflado Oracle, la penúltima subconsulta es la adecuada para definir el nombre de la columna. Base de datos Oracle, el troll sintáctico 😉
  • ORDER BY: Tiene todo el sentido posponer la decisión de ordenar un resultado hasta el final, porque todas las demás operaciones podrían usar hashmaps, internamente, así que cualquier orden intermedio podría perderse de nuevo. Así que ahora podemos ordenar el resultado. Normalmente, puede acceder a un montón de filas desde la cláusula ORDER BY, incluyendo filas (o expresiones) que no SELECT. Pero cuando especificó DISTINCT, antes, ya no puede ordenar por filas / expresiones que no fueron seleccionadas. ¿Por qué? Porque el ordenamiento sería bastante indefinido.
  • OFFSET: No uses el offset
  • LIMIT, FETCH, TOP: Ahora, las bases de datos sanas ponen la cláusula LIMIT (MySQL, PostgreSQL) o FETCH (DB2, Oracle 12c, SQL Server 2012) al final, sintácticamente. En los viejos tiempos, Sybase y SQL Server pensaron que sería una buena idea tener TOP como palabra clave en SELECT. Como si la ordenación correcta de SELECT DISTINCT no fuera ya suficientemente confusa.

Ahí lo tenemos. Tiene todo el sentido. Y si alguna vez quieres hacer algo que no esté en el «orden correcto», el truco más sencillo es siempre recurrir a una tabla derivada. Por ejemplo, cuando quieres agrupar en una función de ventana:

¿Por qué funciona? Porque:

  • En la tabla derivada, primero pasa FROM, y luego se calcula el WINDOW, entonces se SELECTedita el cubo.
  • La SELECTexterna puede ahora tratar el resultado de este cálculo de la función de ventana como cualquier tabla ordinaria en la cláusula FROM, luego GROUP BYuna columna ordinaria, luego se agrega, luego SELECT

Revisemos nuestros ejemplos originales con una explicación de por qué funcionan o por qué no.

Piensa siempre en el orden lógico de las operaciones

Si no eres un escritor frecuente de SQL, la sintaxis puede ser realmente confusa. Especialmente GROUP BY y las agregaciones «infectan» el resto de la cláusula SELECT completa, y las cosas se ponen muy raras. Cuando nos enfrentamos a esta rareza, tenemos dos opciones:

  • Enfadarnos y gritar a los diseñadores del lenguaje SQL
  • Aceptar nuestro destino, cerrar los ojos, olvidarnos de la snytax y recordar el orden de las operaciones lógicas

En general, recomiendo esto último, porque entonces las cosas empiezan a tener mucho más sentido, incluyendo el bonito cálculo de ingresos diarios acumulados que se muestra a continuación, que anida los ingresos diarios (función agregada SUM(amount)) dentro de los ingresos acumulados (función ventana SUM(...) OVER (...)):

SELECT payment_date, SUM(SUM(amount)) OVER (ORDER BY payment_date) AS revenueFROM paymentGROUP BY payment_date

… porque las agregaciones suceden lógicamente antes de las funciones de ventana.

Caveat: ORDER BY clause

Hay algunas advertencias en torno a la cláusula ORDER BY, que podrían estar contribuyendo a una mayor confusión. Por defecto, sigue asumiendo que el orden lógico de las operaciones es correcto. Pero entonces, hay algunos casos especiales, en particular:

  • En ausencia de una cláusula DISTINCT
  • En ausencia de operaciones de conjunto como UNION

Puede hacer referencia a expresiones en ORDER BY, que no son proyectadas por SELECT. La siguiente consulta está perfectamente bien en la mayoría de las bases de datos:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Hay una proyección «virtual» / implícita de ACTOR_ID, como si hubiéramos escrito:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Pero luego, eliminado la columna ACTOR_ID de nuevo del resultado. Esto es muy conveniente, aunque puede llevar a cierta confusión sobre la semántica y el orden de las operaciones. En concreto, no se puede utilizar, por ejemplo, DISTINCT en esta situación. La siguiente consulta no es válida:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Porque, ¿qué pasa si hay dos actores con el mismo nombre pero con IDs muy diferentes? La ordenación sería ahora indefinida.

Con las operaciones de conjunto, está aún más claro por qué no se permite:

SELECT first_name, last_nameFROM actorUNIONSELECT first_name, last_nameFROM customerORDER BY actor_id -- Oops

En este caso, la columna ACTOR_ID no está presente en la tabla CUSTOMER, por lo que la consulta no tiene ningún sentido.

Más información

¿Quieres saber más? También tenemos estos artículos para que los leas:

  • SQL GROUP BY y dependencias funcionales: Una característica muy útil
  • Cómo debería haberse diseñado el GROUP BY de SQL – Como el GROUP BY implícito de Neo4j
  • Cómo traducir el GROUP BY de SQL y las agregaciones a Java 8
  • ¿Entiendes realmente las cláusulas GROUP BY y HAVING de SQL?
  • GROUP BY ROLLUP / CUBE

Deja una respuesta

Tu dirección de correo electrónico no será publicada.