El SQL menos conocido

Índice de contenidos

El objetivo de este post es dar un repaso a las funcionalidades menos conocidas de SQL.

1. Antes de comenzar

Para los ejemplos, se va a usar la BBDD de ejemplo de https://github.com/datacharmer/test_db

Que tiene el siguiente modelo:

Esta base de datos de ejemplo está hecha para MySQL, pero te costará muy poquito adaptarla al gestor que utilices. Para este ejemplo, se ha adaptado de forma que se ha recreado en un DB2 y el cliente de BBDD que he utilizado en este artículo es DBeaver v3.5.8

2. Evoluciones del estándar

El lenguaje SQL ha sufrido muchas revisiones. La última en 2016.

  • SQL-86
  • SQL-89
  • SQL-92
  • SQL-1999
  • SQL-2003
  • SQL-2008
  • SQL-2011
  • SQL-2016

La mayoría de gestores de base de datos implementan el último estándar, aunque cada uno tiene sus matices. Sin embargo, el uso mayoritario que se sigue haciendo se corresponde con el de SQL-92.

3. CTE. Common Table Expressions. WITH

SQL-1999 introduce las tablas comunes y consultas recursivas a través de la construcción WITH.

A cierto modo, WITH es como si crease una vista al vuelo y luego se utilizase en una query. En la BBDD que estamos manejando de ejemplo en este artículo, los directores actuales de cada departamento se pueden obtener de la siguiente manera.

Si guardamos el resultado de esta query en una variable, podemos usarla a continuación y hacer cosas del estilo.

En este caso, esa consulta se podría resolver de forma más óptima con un INNER JOIN, pero ejemplifica el uso que se puede hacer de la cláusula WITH.

4. CTE Recursivas. WITH… LEVEL

En ocasiones nos encontraremos con estructuras jerárquicas que requieren de consultas con cierta recursividad. Lo bueno de las CTE es que las podemos usar dentro de su propia definición, de forma que ahí tenemos la recursividad.

Por ejemplo, imaginemos que tenemos una tabla donde guardamos el linaje por línea materna.

Definimos una primera mujer a la que llamaremos Eva, y a partir de ahí vamos guardando su descendencia haciendo referencia a quien es su madre.

Y al hacer una consulta sobre esta tabla, deseamos obtener en una columna su linaje materno desde Eva hasta la madre de cada niña.

Pues podemos utilizar una CTE recursiva para hacerlo, indicando el nivel de profundidad (recursividad) que deseamos emplear, en este ejemplo 4.

Y este es el resultado de la consulta.

Este tipo de consultas, se suelen utilizar para obtener estructuras jerárquicas.

5. CUBE, ROLLUP y GROUPING SETS

También la especificación SQL-1999 amplía el significado de la cláusula GROUP BY con los operadores CUBE, ROLLUP y GROUPING SETS.

En el ejemplo que tenemos, vamos a obtener un listado de empleados, con su cargo actual y salario. La siguiente consulta nos podría dar este resultado:

E imaginemos que ahora queremos desglosar los sueldos medios por género y por cargo en la empresa. Podemos agrupar por género y cargo con cubo de la siguiente manera.

Fijémonos en los bloques de null.

Las cuatro primeras filas nos dan el sueldo medio por cargo, independientemente del sexo.

La fila cinco nos da el salario medio independientemente del género (null) y del cargo (null). Es decir, nos da el sueldo medio de este grupo.

La fila 6 y 7 nos dan el salario medio desglosado por sexos independientemente del cargo (null)

Y el resto de filas nos dan el salario medio desglosado por cargo y género.

La opción WITH ROLLUP nos da el mismo resultado, pero sin tener en cuenta las cuatro primeras filas.

La primera fila nos da el salario medio de esos empleados.

La fila 2 y 3 el salario medio desglosado por género.

Y de la 5 a la 11, el salario medio, desglosado por género y cargo que ocupa.

En el caso de grouping sets, saca la información desglosada por uno y otro campo.

6. Funciones de Ventana

Las funciones de ventana fueron introducidas en SQL2003 y posteriormente ampliadas en SQL2008 y nos proporcionan una forma de tratar datos calculados referidos a una ventana de nuestra query.

Por ejemplo, volviendo a la query de nuestro ejemplo que sacaba datos de los empleados, ahora queremos una columna adicional, que de esos datos nos devuelva el salario medio desglosado por género.

Esa columna sería

y la consulta quedaría así:

Y el resultado sería:

Igual que se calcula la media, se puede calcular también el max, min, sum, etc… así como otras funciones numéricas.

Algunas estrategias de optimización suelen hacer este tipo de queries y por fuera otra query que se queda sólo con un subconjunto de estos datos. Por ejemplo, en este caso sería muy fácil obtener los empleados que cobran por encima de la media según su género.

También se utiliza con frecuencia en combinación con rownumber() para paginar resultados.

Por ejemplo, queremos obtener los empleados ordenados por apellido y nombre, la segunda página, con un tamaño de página 20.

Y el resultado quedaría

7. Insertar o actualizar mediante MERGE

Una situación que nos encontramos con frecuencia es que si tenemos un registro en la BBDD debemos actualizarlo, y si no, debemos insertarlo.

La forma habitual es hacer el select del registro y si no nos devuelve resultados, lo insertamos, y si nos devuelve resultados, actualizamos sólo los campos afectados.

En SQL2003 se introdujo la sentencia MERGE que luego se ampliaría en SQL2008 y que nos permite esto mismo en una sentencia:

8. Conclusiones

El SQL es más que las sentencias propias de un CRUD. Y cada gestor de BBDD implementa parte o la totalidad del estándar añadiendo sus particularidades. Pero antes que esas particularidades, conveniente conocer el estándar para sacar más partido al lenguaje.

9. Enlaces y referencias