DDL - Subconsultes en FROM, Limit i altres
SUBCONSULTES EN FROM
A la clàusula FROM de la sentències SELECT podem utilitzar el que podem anomenar Vistes en línia.
Aquest terme significa utilitzar una subconsulta enlloc de posar una taula, per això sempre cal que la subconsulta estigui entre parèntesis i que tingui un àlies de taula assignat.
Les vistes en línia ens permeten simplificar consultes més complexes.
Al següent exemple, la clàusula FROM conté una sentència SELECT que recupera dades com qualsevol sentència SELECT.
A les dades retornades per la subconsulta se'ls assigna un àlies (d), que, a continuació, s'utilitza amb un JOIN per retornar columnes seleccionades de les dues taules.
SELECT e.last_name, e.salary, department_id, maxsal
FROM employees e NATURAL JOIN
(SELECT department_id, max(salary) maxsal
FROM employees
GROUP BY department_id) d
WHERE e.salary = d.maxsal;
+-----------+----------+---------------+----------+
| last_name | salary | department_id | maxsal |
+-----------+----------+---------------+----------+
| King | 24000.00 | 90 | 24000.00 |
| Hunold | 9000.00 | 60 | 9000.00 |
| Mourgos | 5800.00 | 50 | 5800.00 |
| Abel | 11000.00 | 80 | 11000.00 |
| Whalen | 4400.00 | 10 | 4400.00 |
| Hartstein | 13000.00 | 20 | 13000.00 |
| Higgins | 12000.00 | 110 | 12000.00 |
+-----------+----------+---------------+----------+
Límit de files retornades
En MariaDB tenim la clàusula
LIMIT
que permet restringir el nombre de files retornades.
D'aquesta manera és més fàcil paginar les respostes d'una SELECT anant demanant, per exemple, les dades de N en N.
Aquesta clàusula l'afegirem al final de la sentència SELECT.
LIMIT offset, row_count
o bé
LIMIT row_count OFFSET offset
Així doncs, per seleccionar tots els empleats de 7 en 7 executarem les següents instruccions fins a rebre'n menys de 7.
SELECT employee_id, last_name, hire_date
FROM employees
ORDER by hire_date
LIMIT 0,7;
SELECT employee_id, last_name, hire_date
FROM employees
ORDER by hire_date
LIMIT 7,7;
SELECT employee_id, last_name, hire_date
FROM employees
ORDER by hire_date
LIMIT 14,7;
A partir de la versió 10.6.0 s'afegeix la clàusula
OFFSET .. FETCH
que permet retornar només els elements d'un conjunt de resultats que vénen després d'un desplaçament especificat. La clàusula FETCH especifica el nombre de files a retornar, mentre que ONLY o WITH TIES especifica si també s'han de tornar o no altres resultats que empatin a l'últim lloc segons el conjunt de resultats ordenat. Aquesta segona opció WITH TIES pot permetre resoldre complicades consultes sobre els majors o menors .... Per utilitzar WITH TIES cal haver posat la clàusula ORDER BY.
Per exemple per saber els departaments amb menys empleats (i si en són més de un que els retorni tots), podem utilitzar la comanda següent:
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY COUNT(*)
OFFSET 0 ROWS FETCH NEXT 1 ROW WITH TIES;
D'aquesta manera ens retornarà totes les files que tinguin el menor nombre d'empleats, encara que siguin dues, gràcies a la opció final WITH TIES.
Nosaltres, en Windows, tenim instal·lada una versió anterior (10.4.28)
# mysqld --version
mysqld Ver 10.4.28-MariaDB for Win64 on AMD64 (mariadb.org binary distribution)
En instal·lacions en Linux ja tenim la 10.6
$ sudo mysqld -V
mysqld Ver 10.6.12-MariaDB-0ubuntu0.22.04.1 for debian-linux-gnu on x86_64 (Ubuntu 22.04)
Quines taules i vistes tinc?
En MariaDB la comanda show tables mostra les taules existents. Per veure les vistes tenim dues opcions:
SHOW [FULL] TABLES [ FROM db_name ]
[LIKE 'pattern' | WHERE expr ]
-- Mostrem vistes i taules
show full tables;
-- Mostrem només vistes
show full tables like 'view%';
-- per veure les vistes d'una base determinada
show full tables from empresa like 'view%';
-- per veure les taules d'una base determinada
show tables from hospital;