Consultando bases de datos relacionales

( sistemas / sql )

Consultando la BD: SELECT

En este apartado voy a explicar las consultas simples en SQL. La forma básica de un SELECT es:

SELECT [opciones] valores 
FROM tablas 
WHERE condiciones 
[ORDER BY  campos]

Analizando la sintáxis tenemos

Limitando el número de registros devueltos

Es frecuente que al consultar queramos obtener un número máximo de registros, por ejemplo para realizar una paginación o simplemente porque queremos obtener el último registro insertado y no queremos andar con consultas complejas para sacarlo. Todos los SGBD tienen su método para realizar esto: MySQL tiene el LIMIT (haciendo SELECT * FROM tabla WHERE ... ORDER BY ... LIMIT [10 | 5, 10] obtendríamos los 10 primeros registros o los 10 registros a partir del número 5 con la segunda sintaxis), MSSQL Server tiene el TOP (con SELECT TOP 10 ... FROM tabla sacaríamos los 10 primeros registros), Oracle usa el ROW_NUMBER (mediante subconsultas; es un sistema bastante más incómodo que los demás, pero es más estándar), PostgreSQL usa también LIMIT (LIMIT 10 OFFSET 5)... El método más estándar es mediante cursores pero no todos los SGBD siguen las directrices del ISO SQL.

Consultando la BD: INNER JOIN

SELECT ... FROM tabla1 INNER JOIN tabla2 ON tabla1.campo = tabla2.campo WHERE ...

Cuando realizamos una INNER JOIN lo que hacemos es unir dos tablas mediante una o varias condiciones, habitualmente porque están relacionadas por un campo clave. Se pueden concatenar tantas JOIN como queramos; de hecho es la manera habitual de seguir las relaciones de las tablas al realizar consultas (una tabla se relaciona con otra, que a su vez se relaciona con otra...). INNER JOIN es exclusivo, de modo que la relación debe cumplirse siempreo sino no se devolverá el registro. Así, si consultamos todos los clientes y unimos los pedidos mediante el ID de cliente obtendremos sólo los que tengan pedidos. Si hacemos:

SELECT campo1 FROM tabla t INNER JOIN tabla2 t2 ON t.id = t2.id_t

sería lo mismo que hacer:

SELECT campo1 FROM tabla t, tabla2 t2 WHERE t.id = t2.id_t

De hecho, los optimizadores de consultas (query optimizer) suelen cambiar las JOINS por consultas así. No es que sea más óptimo realizarlo así, pero para operar sobre las tablas para optimizar la consulta se manejarán mejor así. Pero del lado del programador de BD, es más fácil de ver las uniones mediante JOINsque mirando las tablas del FROM y luego las condiciones del WHERE (al menos para mí).

Las condiciones del JOIN (el ON ...)

Cabe destacar que en la condición del JOIN se puede escribir cualquier tipo de expresión que evalúe a TRUE(o a FALSE, aunque en ese caso no se devolverá el registro): podemos usar LIKE, operaciones aritméticas, operadores >, =... pero no es recomendable pues por razones que el optimizador sabrá suele ser más lento que si las hubiéramos puesto en el WHERE. Por lo tanto:

SELECT campo1 FROM tabla t INNER JOIN tabla2 t2 ON t.id = t2.id_t AND t.campo2 > t2.campo2

es mejor traducirlo a:

SELECT campo1 FROM tabla t INNER JOIN tabla2 t2 ON t.id = t2.id_t WHERE t.campo2 > t2.campo2

Por lo demás, cualquier condición que no sea ligar las tablas por ID es mejor ponerla en el WHERE. También se puede omitir el ON con la condición, pero en ese caso el resultado será en la mayoría de los casos catastrófico, pues si usamos 2 tablas sin relacionarlas se realizará el producto cartesiano entre ellas, o sea que por cada registro de la tabla 1 se devolverán todos los registros de la tabla 2. Si son tablas de 10 y 5 registros no es mucho problema, se devolverían 50 registros, pero si son de 10000 y 50000..., además no sólo será un resultado grande, también muy lento y pesado en recursos para el servidor de BD.

Rendimiento de las JOIN: no olvidarse de las claves

Y por último, el rendimiento de las JOIN. Es conveniente realizar la JOIN mediante campos que sean claves (primarias, candidatas, externas...) pues el SGBD recorrerá las tablas para satisfacer las condiciones de la JOIN, y si se usan índices el recorrido es infinítamente más rápido. Así que es importante que todo campo referencial, que son los que se usan para unir las tablas en las consultas, sean claves.

Ejemplo

Vamos a suponer una BD con clientes, pedidos y productos (1 cliente tiene N pedidos, 1 pedido es de un único cliente, y 1 pedido tiene N productos que a su vez pueden estar en M pedidos). De la relación pedidos - productos nos sale la entidad lineas_pedidos.

clientes -> pedidos -> lineas_pedido <- productos

Tabla clientes:

id_clientenombre
2 John
10 Edward
13 Rose

Tabla pedidos:

id_pedidoid_clientefecha
1 2 2009-01-24 08:00:15
2 2 2009-01-24 09:22:08
3 10 2009-01-27 13:10:22
4 2 2009-01-27 17:54:15
5 10 2009-01-29 08:13:01
6 10 2009-01-29 10:28:57
7 10 2009-01-29 18:26:59
8 2 2009-01-30 12:30:47
9 10 2009-01-30 12:31:18

Tabla productos:

id_productonombreprecio_basestock
1 frigorífico ACME 749.00 25
2 televisor plasma 799.00 87
3 minicadena HI-FI 129.00 19
4 aspirador ACME 89.00 28
5 climatizador ACME 1499.00 32

Tabla lineas_pedido:

id_pedidoid_productoprecio_unidadcantidad
1 1 709.00 1
1 4 89.00 2
2 2 729.00 1
3 2 699.00 1
4 2 729.00 1
4 3 129.00 1
4 4 89.00 2
5 2 699.00 7
6 5 1399.00 9
7 4 89.00 5
8 2 729.00 1
9 5 1399.00 8
SELECT * FROM clientes c INNER JOIN pedidos p ON c.id_cliente = p.id_cliente
id_clientenombreid_pedidofecha
2 John 1 2009-01-24 08:00:15
2 John 2 2009-01-24 09:22:08
10 Edward 3 2009-01-27 13:10:22
2 John 4 2009-01-27 17:54:15
10 Edward 5 2009-01-29 08:13:01
10 Edward 6 2009-01-29 10:28:57
10 Edward 7 2009-01-29 18:26:59
2 John 8 2009-01-30 12:30:47
10 Edward 9 2009-01-30 12:31:18

Como se puede observar no se han devuelto registros del cliente 13 porque no tiene pedidos. Y también se ve que por cada cliente se han devuelto tantos registros como coincidencias tiene en la tabla de pedidos. Si quiseramos obtener sólo un registro por cliente usaríamos GROUP BY para agrupar los registros, pero eso lo veremos más adelante.

SELECT pr.nombre, l.precio_unidad, l.cantidad, (l.precio_unidad * l.cantidad) AS total_linea,

ROUND(100 - ((l.precio_unidad * 100) / pr.precio_base), 2) AS descuento

FROM pedidos p INNER JOIN lineas_pedido l ON p.id_pedido = l.id_pedido

INNER JOIN productos pr ON l.id_producto = pr.id_producto

WHERE p.id_cliente = 2 AND p.id_pedido = 4
nombreprecio_unidadcantidadtotal_lineadescuento
televisor plasma 729.00 1 729.00 8.76
minicadena HI-FI 129.00 1 129.00 0.00
aspirador ACME 89.00 2 178.00 0.00

Lo que hemos hecho ahora es, dado un cliente y un pedido (para asegurarnos de que el pedido es de un cliente en concreto), sacar el detalle de los productos solicitados en dicho pedido. Así podríamos preparar el detalle de la facturacon el nombre del producto, su precio, su cantidad, su precio total y el % de descuento que ha tenido sobre el precio base.

Consultando la BD: LEFT JOIN

SELECT ... FROM tabla1 LEFT JOIN tabla2 ON tabla1.campo = tabla2.campo WHERE ...

La sintaxis de LEFT JOIN es idéntica a INNER JOIN, pero su comportamiento es muy distinto. Con LEFT JOIN obtenemos todos los registros de la izquierda de la expresión ("tabla1" en el ejemplo de arriba) y luego se unirían con la parte derecha, si hubiera coincidencias. Por ejemplo, si unimos clientes con pedidos y un cliente no tiene pedidos, se devolvería el cliente en el resultado pero con todos los campos de la tabla pedidos a NULL. Así es muy fácil obtener todos los clientes que no tienen pedidos:

SELECT ... FROM clientes c LEFT JOIN pedidos p ON c.id_cliente = p.id_cliente WHERE p.campo IS NULL

Por lo demás la LEFT JOIN tiene las mismas características que una INNER JOIN en cuanto a las condiciones que se pueden poner en el ON, y opciones que el SGBD permita.

La variante RIGHT JOIN

Hay una variante no estándar de LEFT JOIN, la RIGHT JOIN, que hace exactamente lo mismo pero devolviendo todos los registros de la derecha de la expresión. Al no ser estándar y ser perfectamentesustituible por la LEFT JOIN (lo único que cambia es el orden en el que pongamos las tablas) no se recomienda usarla.

Ejemplo

Tabla clientes:

id_clientenombre
2 John
10 Edward
13 Rose

Tabla pedidos:

id_pedidoid_clientefecha
1 2 2009-01-24 08:00:15
2 2 2009-01-24 09:22:08
3 10 2009-01-27 13:10:22
4 2 2009-01-27 17:54:15
5 10 2009-01-29 08:13:01
6 10 2009-01-29 10:28:57
7 10 2009-01-29 18:26:59
8 2 2009-01-30 12:30:47
9 10 2009-01-30 12:31:18

SELECT * FROM clientes c LEFT JOIN pedidos p ON c.id_cliente = p.id_cliente

id_clientenombreid_pedidofecha
2 John 1 2009-01-24 08:00:15
2 John 2 2009-01-24 09:22:08
10 Edward 3 2009-01-27 13:10:22
2 John 4 2009-01-27 17:54:15
10 Edward 5 2009-01-29 08:13:01
10 Edward 6 2009-01-29 10:28:57
10 Edward 7 2009-01-29 18:26:59
2 John 8 2009-01-30 12:30:47
10 Edward 9 2009-01-30 12:31:18
13 Rose NULL NULL

A diferencia del INNER JOIN, hemos obtenido también un registro del cliente 13 aunque no tiene coincidencias en la tabla de pedidos. Ahora, para obtener los clientes que no tienen pedidos simplemente hacemos:

SELECT * FROM clientes c LEFT JOIN pedidos p ON c.id_cliente = p.id_cliente WHERE p.id_pedido IS NULL
id_clientenombreid_pedidofecha
13 Rose NULL NULL

Consultando la BD: GROUP BY

SELECT ... FROM tabla1 INNER JOIN tabla2 ON tabla1.campo = tabla2.campo WHERE ... GROUP BY campo1 [HAVING condiciones] Con GROUP BY podemos agrupar, en torno a uno o varios campos(algunos SGBD admiten alias en vez de campos), los registros devueltos. Opcionalmente podemos usar condiciones del agrupamiento en el HAVING, algo típicamente usado con las funciones de agregado.

Funciones de agregado (COUNT, SUM, MAX, AVG...)

Cuando agrupamos los registros (no hay por qué usar JOINs, se puede hacer con una sola tabla) podemos usar las funciones de agregado (COUNT para contar registros, SUM para sumar valores de un campo, MAX para obtener el valor máximo del campo, AVG para obtener el valor promedio del campo...) en el SELECT y en el HAVING. Si sólo usamos una función de agreagado en el SELECT, no es obligatorio usar GROUP BY, pero si además de eso indicamos que se devuelva un campo, el SGBD no lo permitirá y devolverá un error. Así, podemos hacer: SELECT COUNT(*) FROM tabla pero no: SELECT COUNT(*), campo1 FROM tabla Es algo ilógico que se pueda usar una función de agregado sin agrupar, pero lo que ocurre es que por simplicidad los SGBD lo permiten para cuando se quiere obtener el número de registros, o el valor mayor de un campo, cosas bastante frecuentes. En estos casos es el propio SGBD el que se encarga de agrupar y generalmente de optimizar estas consultas. Pero cuidado con el parámetro que ponemos en las funciones de agregado, porque si indicamos un valor que sea nulo no se aplicará; y tened en cuenta que COUNT(*) es una excepción, pues * no es ningún campo o valor, pero los SGBD lo admiten como atajo para simplemente contar todos los registros de la consulta... no intentéis hacer SUM(*) porque eso fallará.

Filtrando los grupos con HAVING

Junto con las funciones de agregado es habitual usar el HAVING para obtener, de los grupos de registros devueltos, los que cumplan una o varias condiciones: obtener los clientes que han facturado más de 60000 euros en un año, los que han realizado más de 20 pedidos en un mes, los que su media de importe de pedidos es superior a 1000 euros... En el HAVING se puede poner cualquier tipo de condición, incluso las que pueden ir en el WHERE, pero es muy recomendable que si una condición puede ir en el WHERE vaya en el WHERE, pues esas condiciones se usan en la optimización para recorrer más rápido los registros implicados; el HAVING, debido al orden de procesamiento de una consulta, se ejecuta casi al final del todo. El orden de procesamiento de una consulta suele ser: se unen y filtran las tablas implicadas (mediante las condiciones WHERE y las ON de las JOIN), se aplica el GROUP BY si lo hubiera (en este punto algunos SGBD ya saben qué alias hemos asignado a los valores del SELECT y nos permiten usarlos) y el HAVING si también lo hubiera (algunos SGBD también permiten usar alias aquí) y finalmente se aplica el ORDER BY. Huelga decir que los optimizadores de consultas tienen mucho que decir en estos pasos, pues a veces no necesitan ordenar el resultado, otras veces tienen que repasar el resultado de nuevo para ordenarlo...

Ejemplo

Tabla clientes:

id_clientenombre
2 John
10 Edward
13 Rose

Ahora vamos a añadir el importe del pedido a la tabla de pedidos. El importe de un pedido es, generalmente, propiedad del pedido pues la suma de los productos que contiene podría variar y el precio del pedido no debería variar. Es discutible pues se puede argumentar que el cambio de precio de uno de los productos que pidió el cliente no debe implicar el cambio del precio del pedido, y también se puede argumentar que si se considera inviolable el precio de un pedido también lo tienen que ser los precios de los productos que contiene.

id_pedidoid_clientefechaimporte
1 2 2009-01-24 08:00:15 940.00
2 2 2009-01-24 09:22:08 100.00
3 10 2009-01-27 13:10:22 64.00
4 2 2009-01-27 17:54:15 85.00
5 10 2009-01-29 08:13:01 540.00
6 10 2009-01-29 10:28:57 50.00
7 10 2009-01-29 18:26:59 35.00
8 2 2009-01-30 12:30:47 1420.00
9 10 2009-01-30 12:31:18 680.00
SELECT c.id_cliente, COUNT(*) num_pedidos, SUM(p.importe) total 

FROM clientes c 

INNER JOIN pedidos p ON c.id_cliente = p.id_cliente 

GROUP BY c.id_cliente
id_clientenum_pedidostotal
2 4 2545.00
10 5 1369.00

Como vemos, el cliente 2 tiene 4 pedidos y el 10 tiene 5. El 13 no aparece porque no tiene ningún pedido y hemos usado INNER JOIN, pero si hubieramos usado LEFT JOIN sí aparecería con num_pedidos a 0 (ojo, no NULL, pues es una función, no un campo). También hemos sacado, a la vez, la suma de importes de sus pedidos. Si queremos saber cuántos pedidos al día hace un cliente, y de cuánto es su pedido con importe más pequeño, hacemos (MySQL):

SELECT c.id_cliente, DATE_FORMAT(p.fecha, '%Y-%m-%d') dia, COUNT(*) num_pedidos, MIN(p.importe) AS minimo 

FROM clientes c 

INNER JOIN pedidos p ON c.id_cliente = p.id_cliente 

GROUP BY c.id_cliente, dia
id_clientedianum_pedidosminimo
2 2009-01-24 2 100.00
2 2009-01-27 1 85.00
10 2009-01-27 1 64.00
10 2009-01-29 3 35.00
2 2009-01-30 1 1420.00
10 2009-01-30 1 680.00

Consultando la BD: optimizar consultas

Sobre la optimización hay muchos matices, que dependen siempre del SGBD. Principalmente cada vez que lanzamos una consulta a la BD el SGBD usa el optimizador para satisfacerla lo más rápido posible. Si bien es cierto que en ocasiones consigue todo lo contrario: que se ejecute más lenta. Como esta información es muy específica para cada SGBD, intentaré dar los consejos más frecuentes para que las consultas vayan mejor, y luego ampliaré un poco con MySQL pues es un sistema que se deja optimizar muy bien (aunque sería preferible no tener que optimizarlo...).

Consejos
Optimizar gracias a EXPLAIN (MySQL)

En MySQL tenéis una herramienta muy útil para la depuración de consultas: EXPLAIN. Anteponiendo EXPLIAN a la consulta (EXPLAIN SELECT ... FROM tabla WHERE ...) obtendremos un detalle de la ejecución de ella, con datos valiosos como el índice que se usa para cada JOIN, si se realiza un escaneo completo de la tabla, si el sistema tiene que volver a recorrer el resultado para ordenar... es un tema muy amplio para explicarlo en este artículo (además de que es algo exclusivo de MySQL) así que conviene echarle un ojo a la documentación oficial para que os hagáis una idea de cómo interpretar el resultado de EXPLAIN. Cuando depuréis una consulta con EXPLAIN, a grandes rasgos, lo más importante es fijarse en la columna "type" que determina el tipo de JOIN que realiza el SGBD: "all" significaría un escaneo completo de la tabla, "const" que sólo hay un resultado pues se está filtrando por una clave única comparada con un valor constante, "eq_ref" cuando es una JOIN de tablas relacionadas 1 a N, "ref" cuando la JOIN es de tablas N a M... conviene estudiarlo porque si en una JOIN que une dos tablas el "type" es "all" debemos eperar un resultado lento. Es cierto que en toda consulta con JOINs alguna de las tablas deberá ser la primera en leerse y si no se usan filtros de índice en el WHERE obtendremos un "all", pero quitando ese caso particular en ningún otra JOIN deberíamos tener algo que no sea "ref" o "eq_ref". Al fin y al cabo esto es lo que explicábamos más atras: si no se usan índices referenciales para satisfacer las JOINs se realiza el producto cartesiano de las tablas con su consecuente consumo de CPU y memoria.

Detectando las consultas lentas

Por lo demás, cuando nuestras aplicaciones vayan muy lentas y detectemos que es por las consultas a la BD, debemos encontrar las consultas que las ralentizan. MySQL tiene el slow_query_log que una vez habilitado en el servidor almacenará todas las consultas que tarden en ejecutarse más del tiempo que le especifiquemos (un segundo es muy lento, pero medio también en una consulta muy frecuente). Una vez que sepamos las consultas podemos revisar los consejos de arriba para ver si estamos haciendo algo mejorable, o quizá tengamos un mal diseño de la BD. También, en ocasiones, nos podemos encontrar que se nos ha olvidado declarar un índice, y eso tiene una solución muy fácil. Porque decir que la BD, o una tabla, se nos ha quedado poequeña, es algo muy poco probable: mejor revisar el diseño y el uso que le damos porque dándole una vuelta podemos ver fallos en el diseño y mejorar sobremanera el rendimiento. Si la búsqueda de las consultas lentas se hace muy complicada, siempre podremos buscar algún monitor específico para nuestro SGBD (en esto el administrador de la BD nos puede ayudar mucho: le conviene) pues estas herramientas suelen proporcionar información vital para hallar este tipo de problemas.


Artículo escrito por Eloy Bote Falcón, publicado originalmente en: php-hispano.net

Modificado el 18 Abril, 2015
Creado el 30 abril, 2010
   

Compartiendo conocimiento desde 1995 - I.M.D. I.M.D.