6. Profundización en el lenguaje SQL
6.1. Introduction
En este capítulo presentamos
- otras sintaxis del comando SELECT que lo convierten en un comando de consulta muy potente, especialmente para consultar varias tablas a la vez.
- sintaxis ampliadas de comandos ya estudiados
Para ilustrar los diversos comandos, trabajaremos con las siguientes tablas utilizadas para la gestión de pedidos en una PME de distribución de libros:
6.1.1. la tabla CLIENTS
Almacena información sobre los clients de la PME:
 |
N.º que identifica al cliente de forma única - clave primaria | |
nombre del cliente | |
I = Particular, E = Empresa, A = Administración | |
nombre en el caso de un particular | |
Apellidos de la persona de contacto del cliente (en el caso de una empresa o una administración) | |
Dirección del cliente - calle | |
ciudad | |
código postal | |
Teléfono | |
¿Desde cuándo es cliente? | |
O (Sí) si el cliente debe dinero a la empresa y N (No) en caso contrario. |
6.1.2. La tabla ARTICLES
Almacena información sobre los productos vendidos, en este caso libros. Su estructura es la siguiente:
N.º que identifica un libro de forma única (ISBN = International Standard Book Number) - clave primaria | |
Título del libro | |
Código que identifica de forma única a una editorial | |
Nombre del autor | |
Resumen del libro | |
Cantidad vendida en el año | |
Cantidad vendida el año anterior | |
Fecha de la última venta | |
Cantidad de la última entrega | |
Fecha de la última entrega | |
Precio de venta | |
Coste de compra | |
Cantidad mínima de pedido | |
Umbral mínimo de stock | |
Cantidad en stock |
Su contenido podría ser el siguiente:
6.1.3. la tabla COMMANDES
Almacena la información sobre los pedidos realizados por los clients. Su estructura es la siguiente:
N.º que identifica un pedido de forma única - clave primaria | |
N.º de cliente que realiza este pedido - clave externa - referencia CLIENTS(ID) | |
Fecha de introducción de este pedido | |
O (Sí) si el pedido se ha cancelado y N (No) en caso contrario. |
6.1.4. la tabla DETAILS
Contiene los detalles de un pedido, es decir, las referencias y cantidades de los libros solicitados. Su estructura es la siguiente:
N.º de pedido: clave externa que hace referencia a la columna NOCMD de la tabla COMMANDES | |
N.º del libro pedido: clave externa que hace referencia a la columna ISBN de la tabla LIVRES | |
Cantidad solicitada |
Su contenido podría ser el siguiente:
En el ejemplo anterior, vemos que el pedido n.º 3 (NOCMD) corresponde a tres libros. Esto significa que el cliente ha pedido tres libros al mismo tiempo. Las referencias de este cliente se pueden encontrar en la tabla [COMMANDES], donde se ve que el pedido n.º 3 lo realizó el cliente n.º 5. La tabla [CLIENTS] nos indica que el cliente n.º 5 es la empresa NetLogos de Segré.
6.2. El pedido SELECT
Nos proponemos aquí profundizar en nuestro conocimiento del pedido SELECT presentando nuevas sintaxis del mismo.
6.2.1. Sintaxis de una consulta multitabla
SELECT columna1, columna2, ... FROM tabla1, tabla2, ..., tablap WHERE condition ORDER BY ... | |
La novedad aquí radica en que las columnas columna1, columna2, ... proceden de varias tablas tabla1, tabla2, ... Si dos tablas tienen columnas con el mismo nombre, se resuelve la ambigüedad mediante la notación tablei.colonnej. condition puede referirse a las columnas de las diferentes tablas. |
Funcionamiento
Se genera la tabla cartesiana de table1, table2, ..., tablep. Si ni es el número de filas de tablei, la tabla construida tendrá, por tanto, n1*n2*...*np filas que contienen el conjunto de columnas de las diferentes tablas. | |
Se aplica la condition de la WHERE a esta tabla. De este modo, se genera una nueva tabla | |
Esta se ordena según el modo indicado en ORDER. | |
Se muestran las columnas solicitadas tras SELECT. |
Ejemplos
Se utilizan las tablas presentadas anteriormente. Queremos conocer el detalle de los pedidos realizados después del 25 de septiembre:
SQL>select details.nocmd,isbn,qte from commandes,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
Cabe señalar que detrás de FROM se incluye el nombre de todas las tablas cuyas columnas se están consultando. En el ejemplo anterior, todas las columnas seleccionadas pertenecen a la tabla DETAILS. Sin embargo, la condición hace referencia a la tabla COMMANDES. De ahí la necesidad de nombrar esta última detrás de FROM. La operación que comprueba la igualdad de columnas de dos tablas diferentes se denomina a menudo «equijunción».
La consulta SELECT también se podría haber escrito de la siguiente manera:
SQL> select details.nocmd,isbn,qte from commandes
inner join details on details.nocmd=commandes.nocmd
where commandes.datecmd>'25-sep-91'
Continuemos con nuestros ejemplos. Queremos el mismo resultado que antes, pero con el título del libro solicitado, en lugar de su n.º ISBN:
SQL>select commandes.nocmd, articles.titre, details.qte
from commandes,articles,details
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
Se obtiene el mismo resultado con la siguiente consulta SQL, menos legible:
SQL> select details.nocmd,articles.titre,details.qte from details
inner join commandes on details.nocmd=commandes.nocmd
inner join articles on details.isbn=articles.isbn
where commandes.datecmd>'25-sep-91'
En el ejemplo anterior, se realizan dos uniones internas con la tabla [DETAILS]:
- una con la tabla [COMMANDES] para acceder a la fecha de pedido de un libro
- una con la tabla [ARTICLES] para acceder al título del libro solicitado
Además, queremos el nombre del cliente que realiza el pedido:
SQL>select commandes.nocmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
Además, queremos las fechas de pedido y que se muestren en orden descendente:
SQL>select commandes.nocmd, commandes.datecmd, articles.titre, qte ,clients.nom
from commandes,details,articles,clients
where commandes.datecmd>'25-sep-91'
and details.nocmd=commandes.nocmd
and details.isbn=articles.isbn
and commandes.idcli=clients.id
order by commandes.datecmd descending
A continuación se indican algunas reglas que deben observarse en las uniones:
- Detrás de SELECT, se colocan las columnas que se desean mostrar. Si la columna existe en varias tablas, se le antepone el nombre de la tabla.
- Detrás de FROM, se colocan todas las tablas que serán exploradas por SELECT, es decir, las tablas propietarias de las columnas que se encuentran detrás de SELECT y WHERE.
6.2.2. La autojunción
Queremos conocer los libros que tienen un precio de venta superior al del libro «Using SQL»:
SQL>select a.titre from articles a, articles b
where b.titre='Using SQL'
and a.prixvente>b.prixvente
Las dos tablas de la unión son aquí idénticas: la tabla articles. Para diferenciarlas, se les asigna un alias: from artículos a, artículos b. El alias de la primera tabla se llama a y el de la segunda, b. Esta sintaxis se puede utilizar incluso si las tablas son diferentes. Al utilizar un alias, este debe emplearse en todo el comando SELECT en lugar de la tabla a la que hace referencia.
6.2.3. Unión externa
Queremos conocer los clients que compraron algo en septiembre con la fecha del pedido. Los demás clients se muestran sin esta fecha:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where datecmd between '01-sep-91' and '30-sep-91'
Nos sorprende aquí no obtener el resultado correcto. Deberíamos tener todos los clients presentes en la tabla [CLIENTS], lo cual no es el caso. Al analizar el funcionamiento de la unión externa, nos damos cuenta de que los clients que no han comprado se han asociado a una fila vacía de la tabla COMMANDES y, por lo tanto, a una fecha vacía (valor NULL en la terminología SQL). Esta fecha no cumple entonces la condición establecida sobre la fecha y el cliente correspondiente no se muestra. Probemos otra cosa:
SQL>select clients.nom,commandes.datecmd from clients
left outer join commandes on clients.id=commandes.idcli
where (commandes.datecmd between '01-sep-91' and '30-sep-91')
or (commandes.datecmd is null)
Esta vez obtenemos la respuesta correcta a nuestra pregunta.
6.2.4. Consultas anidadas
SELECT columna[s] FROM tabla[s] WHERE expresión operador consulta ORDER BY ... | |
requête es un comando SELECT que devuelve un grupo de 0, 1 o varios valores. Entonces tenemos una condición WHERE del tipo expresión operador (val1, val2, ..., vali) expression y vali deben ser del mismo tipo. Si la consulta devuelve un solo valor, se reduce a una condición del tipo expresión operador valor que conocemos bien. Si la consulta devuelve una lista de valores, se podrán emplear los siguientes operadores:
expression IN (val1, val2, ..., vali): verdadero si expression tiene como valor uno de los elementos de la lista vali.
inverso de IN
debe ir precedido de =, !=, >, >=, <, <= expression >= ANY (val1, val2, .., valn): verdadero si expression es >= a uno de los valores vali de la lista
debe ir precedido de =, !=, >, >=, <, <= expression >= ALL (val1, val2, .., valn): verdadero si la expresión es >= a todos los valores válidos de la lista
consulta: verdadera si requête devuelve al menos una línea. |
Ejemplos
Retomamos la cuestión ya resuelta mediante una equijunción: mostrar los títulos que tengan un precio de venta superior al del libro «Using SQL».
SQL>select titre from ARTICLES
where prixvente > (select prixvente from ARTICLES where titre='Using SQL')
Esta solución parece más intuitiva que la de la unión equi. Se realiza un primer filtrado con un SELECT y, a continuación, un segundo filtrado sobre el resultado obtenido. De este modo, se pueden realizar varios filtrados en serie.
Queremos conocer los títulos cuyo precio de venta sea superior al precio medio de venta:
¿Cuáles son los clients que han solicitado los títulos resultantes de la consulta anterior?
SQL>select distinct idcli from COMMANDES,DETAILS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
Explicaciones
- Se seleccionan en la tabla DETAILS los códigos ISBN que se encuentran entre los libros cuyo precio es superior al precio medio de los libros.
- En las líneas seleccionadas en el paso anterior no aparece el código de cliente IDCLI. Se encuentra en la tabla COMMANDES. La relación entre las dos tablas se establece mediante el n.º de pedido NOCMD, de ahí la unión equi-junta COMMANDES.nocmd=DETAILS.nocmd.
- Un mismo cliente puede haber comprado varias veces uno de los libros en cuestión, en cuyo caso su código IDCLI aparecerá varias veces. Para evitarlo, colocamos la clave DISTINCT detrás de SELECT. DISTINCT elimina, en general, los duplicados en las filas resultantes de un SELECT.
- Para obtener el nombre del cliente, tendríamos que realizar una unión equi entre las tablas COMMANDES y CLIENTS, tal y como muestra la siguiente consulta.
SQL> select distinct CLIENTS.nom from COMMANDES,DETAILS,CLIENTS
where DETAILS.isbn in
(select isbn from ARTICLES where prixvente
> (select avg(prixvente) from ARTICLES))
and COMMANDES.nocmd=DETAILS.nocmd
and COMMANDES.IDCLI=CLIENTS.ID
Buscar los clients que no hayan realizado ningún pedido desde el 24 de septiembre:
SQL>select nom from CLIENTS
where clients.id not in
(select distinct commandes.idcli from commandes where datecmd>='24-sep-91')
Hemos visto que se pueden filtrar líneas de otra forma que no sea con la cláusula WHERE: utilizando la cláusula HAVING junto con la cláusula GROUP BY. La cláusula HAVING filtra grupos de líneas.
Al igual que en la cláusula WHERE, la sintaxis
HAVING expression opérateur requête
es posible, con la restricción ya mencionada de que expression debe ser una de las expresiones expri de la cláusula
GROUP BY expr1, expr2, ...
Ejemplos
¿Cuáles son las cantidades vendidas de los libros de más de 200F?
Mostramos primero las cantidades vendidas por título:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
Ahora, filtremos los títulos:
SQL> select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
group by titre
having titre in (select titre from ARTICLES where prixvente>200)
De una forma quizás más clara, se podría haber escrito:
SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
where DETAILS.isbn=ARTICLES.isbn
and ARTICLES.prixvente>200
group by titre
6.2.5. Consultas correlacionadas
En el caso de las consultas anidadas, hay una consulta principal (la consulta más externa) y una consulta secundaria (la consulta más interna). La consulta principal solo se evalúa cuando la consulta secundaria se ha evaluado por completo.
Las consultas correlacionadas tienen la misma sintaxis, con la siguiente diferencia: la consulta secundaria realiza una unión con la tabla de la consulta principal. En este caso, el conjunto formado por la consulta principal y la consulta secundaria se evalúa repetidamente para cada fila de la tabla principal.
Ejemplo
Retomamos el ejemplo en el que queremos los nombres de los clients que no han realizado ningún pedido desde el 24 de septiembre:
SQL>
select nom from clients
where not exists
(select idcli from commandes
where datecmd>='24-sep-91'
and commandes.idcli=clients.id)
La consulta principal se ejecuta sobre la tabla clients. La consulta secundaria realiza una unión entre las tablas clients y commandes. Por lo tanto, se trata de una consulta correlacionada. Para cada fila de la tabla clients, se ejecuta la consulta secundaria: busca el código id del cliente en los pedidos realizados después del 24 de septiembre. Si no lo encuentra (not existe), se muestra el nombre del cliente. A continuación, se pasa a la siguiente línea de la tabla clients.
6.2.6. Criterios de selección para la escritura de SELECT
Hemos visto, en varias ocasiones, que era posible obtener un mismo resultado mediante diferentes escrituras del SELECT. Veamos un ejemplo: Mostrar los clients que hayan realizado algún pedido:
Unión
Consultas anidadas
dan el mismo resultado.
Consultas correlacionadas
SQL>
select nom from clients
where exists (select * from commandes where commandes.idcli=clients.id)
da el mismo resultado.
Los autores Christian MAREE y Guy LEDANT, en su libro «SQL, Iniciación, Programación y Dominio», proponen algunos criterios de elección:
Rendimiento
El usuario no sabe cómo el SGBD «se las arregla» para encontrar los resultados que solicita. Por lo tanto, solo con la experiencia descubrirá que una sintaxis es más eficaz que otra. MAREE y LEDANT afirman, basándose en su experiencia, que las consultas correlacionadas suelen ser más lentas que las consultas anidadas o las uniones.
Formulación
La formulación mediante consultas anidadas suele ser más legible e intuitiva que la unión. Sin embargo, no siempre es utilizable. Hay que tener en cuenta dos puntos en particular:
- Las tablas propietarias de las columnas argumentos de SELECT (SELECT col1, col2, ...) deben nombrarse tras la palabra clave FROM. A continuación, se realiza el producto cartesiano de estas tablas, lo que se denomina una unión.
- Cuando la consulta muestra resultados procedentes de una sola tabla y el filtrado de las filas de esta última requiere consultar otra tabla, se pueden utilizar consultas anidadas.
6.3. Extensiones de sintaxis
Por razones de comodidad, en la mayoría de los casos hemos presentado sintaxis reducidas de los distintos comandos. En esta sección, presentamos sintaxis ampliadas. Se entienden por sí mismas, ya que son análogas a las del comando SELECT, ampliamente estudiado.
INSERT
INSERT INTO table (col1, col2, ..) VALUES (val1, val2, ...) | |
INSERT INTO table (col1, col2, ..) (requête) | |
Se han presentado estas dos sintaxis |
DELETE
DELETE FROM table WHERE condition | |
Esta sintaxis es conocida. Añadamos que la condición puede contener una consulta con la sintaxis WHERE expresión operador (consulta) |
UPDATE
UPDATE table SET col1=expr1, col2=expr2, ... WHERE condition | |
Esta sintaxis ya se ha presentado. Añadamos que la condición puede contener una consulta con la sintaxis WHERE expresión operador (consulta) |
UPDATE table SET (col1, col2, ..)= consulta1, (cola, colb, ..)= consulta2, ... WHERE condition | |
Los valores asignados a las diferentes columnas pueden proceder de una consulta. |