Todos conocemos el lenguaje SQL como el estándar de consultas para bases de datos
relacionales. Sin embargo, este estándar ha sufrido varias revisiones a lo largo de su
historia, que han ido enriqueciendo el lenguaje, y que en muchos casos, son grandes
desconocidas para los desarrolladores.
El objetivo de este post es dar un repaso a las funcionalidades menos conocidas de SQL.
1. Antes de empezar
Para los ejemplos, se va a usar la BBDD de ejemplo de
https://github.com/datacharmer/test_db
Que tiene el siguiente modelo:
El cliente de BBDD de 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.
SELECT EMP_NO, DEPT_NO
FROM DEPT_MANAGER
WHERE FROM_DATE < CURRENT_DATE
AND TO_DATE > CURRENT_DATE
Si guardamos el resultado de esta query en una variable, podemos usarla a
continuación y hacer cosas del estilo.
WITH managers AS (
SELECT EMP_NO, DEPT_NO
FROM DEPT_MANAGER
WHERE FROM_DATE < CURRENT_DATE
AND TO_DATE > CURRENT_DATE
)
SELECT
M.EMP_NO,
E.FIRST_NAME,
E.LAST_NAME,
E.GENDER,
D.DEPT_NO,
D.DEPT_NAME
FROM managers M
INNER JOIN EMPLOYEES E ON M.EMP_NO=E.EMP_NO
INNER JOIN DEPARTMENTS D ON M.DEPT_NO=D.DEPT_NO;
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.
CREATE TABLE person (
id INT NOT N ULL,
name VARCHAR(30) N OT N ULL,
mother INT NOT N ULL,
PRIMARY KEY (id)
);
Definimos una primera mujer a la que llamaremos E
va, y a partir de ahí vamos
guardando su descendencia haciendo referencia a quien es su madre.
insert into person (id, name, mother) values
(1, 'Eva', 0),
(2, 'Ana', 1),
(3, 'María', 1),
(4, 'Rocío', 2),
(5, 'Carmen', 2),
(6, 'Aurora', 3),
(7, 'Pilar', 4);
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.
WITH RCTE (id, name, mother, family, level) as
(
select
root.id,
root.name,
root.mother,
CAST('' AS VARCHAR(50)) as family,
1 as level
from PERSON root
where root.id=1
union all
select
