Consultas SQL INNER JOIN Inner join sólo produce los registros que coinciden en las dos tablas A y B. Código (sql) 1. SELECT * FROM TablaA 2. INNER JOIN TablaB 3. ON TablaA.nombre = TablaB.nombre

Full outer join produce el conjunto de todos los registros en las tablas A y B, con registros coincidentes en ambos lados cuando sea posible. Si no hay coincidencia, el lado que falta contendrá null. Código (sql) 1. SELECT * FROM TablaA 2. FULL OUTER JOIN TablaB 3. ON TablaA.nombre = TablaB.nombre

Left outer join produce el conjunto completo de registros de la tabla A, con los registros coincidentes (si están disponibles) en la tabla B. Si no hay coincidencia, el lado derecho contendrá null. Código (sql) 1. SELECT * FROM TablaA 2. LEFT OUTER JOIN TablaB 3. ON TablaA.nombre = TablaB.nombre

Para producir el conjunto de registros en la tabla A, pero no en la tabla B, usamos el mismo Left Outer Join, y luego excluimos los registros que no queremos del lado derecho mediante una cláusula Where. Código (sql) 1. SELECT * FROM TablaA 2. LEFT OUTER JOIN TablaB 3. ON TablaA.nombre = TablaB.nombre

4. WHERE TablaB.id IS NULL

Para producir el conjunto de registros únicos de la tabla A y la tabla B, usamos el mismo Full Outer Join, y luego excluimos los registros que no queremos de los dos lados mediante una cláusula Where. Código (sql) 1. SELECT * FROM TablaA 2. FULL OUTER JOIN TablaB 3. ON TablaA.nombre = TablaB.nombre 4. WHERE TablaA.id IS NULL 5. OR TablaB.id IS NULL

También hay un cross join, el cuál no puede ser expresado con un diagrama de Venn: Código (sql) 1. SELECT * FROM TablaA 2. CROSS JOIN TablaB Esto une “todo con todo”, dando como resultado 4 x 4 = 16 filas, muchas más de las que teníamos en los conjuntos originales. Si haces unos simples cálculos, puedes ver por qué es un Join muy peligroso de ejecutar en tablas grandes.

UNION El comando unión trabaja sobre los resultados de las consultas, nos sirve para unir las columnas resultantes. Código (sql) 1. SELECT * FROM tablaA 2. WHERE tablaA.nombre = “Juan” 3. UNION 4. SELECT * FROM tablaB 5. WHERE tablaB.nombre = “Juan”

GROUP BY La cláusula GROUP BY en SQL permite realizar agrupaciones de registros de una tabla. Examinemos la siguiente tabla que contiene datos de los empleados de una empresa.

Si en esta tabla deseáramos calcular el salario medio de los empleados agrupados por oficio, deberíamos escribir una sentencia SQL que agrupara las filas con un mismo nombre de oficio. De cada uno de los grupos que se formen se aplicaría la función AVG sobre el campo salario. De la misma forma, si quisiéramos contar el número de empleados de cada departamento deberíamos realizar un agrupamiento de los empleados por el campo departamento. De cada uno de los grupos formados se aplicaría la función COUNT. La sintaxis básica de la cláusula GROUP BY en una sentencia SELECT es la siguiente: 1. 2. 3. 4. 5.

SELECT campos FROM tablas WHERE condiciones GROUP BY campo1, campo2, campo3... ORDER BY campo1, campo2, campo3...

La cláusula GROUP BY siempre va detrás de la cláusula WHERE (si es que la hay), y delante siempre de ORDER BY (si es que realizamos la ordenación de acuerdo un campo determinado). 1. SELECT oficio, AVG(salario) 2. FROM emple 3. GROUP BY oficio;

Se quiere calcular a continuación el número de empleados de cada departamento. En esta ocasión debemos realizar un agrupamiento de registros por el campo dept_no. De cada grupo que se forme debemos contar el número de registros que forman cada grupo. La sentencia SQL quedaría: 1. 2. 3. 4.

SELECT dept_no, COUNT(*) FROM emple GROUP BY dept_no ORDER BY dept_no;

Hemos empleado la cláusula ORDER BY para mostrar el resultado ordenado por número de departamento. La cláusula GROUP BY DEPT_NO obliga a COUNT a contar las filas que se han agrupado por cada departamento. Veamos otro ejemplo más. Queremos obtener por cada agrupamiento de departamento y oficio el salario medio. Es decir, por cada departamento queremos calcular el salario medio agrupado por oficio. En este caso debemos agrupar los registros por número de departamento, y dentro de cada departamento por oficio. 1. SELECT dept_no, oficio, 2. FROM emple GROUP BY dept_no,oficio;

AVG(salario)

En una sentencia SELECT pueden aparecer juntos tanto la cláusula WHERE como la cláusula GROUP BY. Si quisiéramos calcular, por ejemplo, el salario medio de cada departamento sin tener en cuenta aquellos empleados que cobren menos de 1000 euros, la sentencia correcta en SQL sería: 1. 2. 3. 4.

SELECT dept_no, AVG(salario) FROM emple WHERE salario>=1000 GROUP BY dept_no;

En este caso no se tiene en cuenta el empleado 1005 para realizar la media de salarios.

HAVING La cláusula HAVING permite especificar condiciones a los agrupamientos realizados con GROUP BY. Del mismo modo que existe la cláusula WHERE para filas individuales en la sentencia SELECT, también se puede especificar una condición para grupos de registros. Al utilizar la cláusula HAVING no se incluyen aquellos grupos que no cumplan una determinada condición. La cláusula HAVING siempre va detrás de la cláusula GROUP BY y no puede existir sin ésta. Si queremos visualizar, por ejemplo, el salario medio de cada departamento pero sólo de aquellos cuyo salario medio sea mayor de 1200, la sentencia en SQL sería: 1. 2. 3. 4.

SELECT dept_no, AVG(salario) FROM emple GROUP BY dept_no HAVING AVG(salario)>1200;

Si, por ejemplo, queremos obtener el número de empleados de cada departamento pero sólo de aquellos que tengan más de 2 empleados, la manera correcta sería: 1. 2. 3. 4.

SELECT dept_no,COUNT(*) FROM emple GROUP BY dept_no HAVING COUNT(*)>2;

Si queremos ordenar la salida ordenada descendentemente por número de empleados emplearíamos la cláusula ORDER BY. 1. 2. 3. 4. 5.

SELECT dept_no,COUNT(*) FROM emple GROUP BY dept_no HAVING COUNT(*)>2 ORDER BY COUNT(*) DESC;

La cláusula WHERE y la cláusula HAVING pueden aparecer juntas en la misma sentencia SQL. La cláusula HAVING es similar a la cláusula WHERE, pero trabaja con grupos de filas en vez que con filas individuales de una tabla. Si quisiéramos calcular, por ejemplo, el salario medio de cada departamento sin tener en cuenta aquellos empleados que cobren menos de 1000 euros, y además no queremos que en resultado se visualicen aquellos departamentos cuyo salario sea menor que 1200, la sentencia correcta en SQL sería: 1. 2. 3. 4. 5.

SELECT dept_no, AVG(salario) FROM emple WHERE salario>1000 GROUP BY dept_no HAVING AVG(salario)>1200;

SENTENCIAS ANIDADAS La gerencia te pide que expongas en un reporte un resumen de las compras y ventas de los artículos en el último mes, donde compares lado a lado, cuantos se vendieron, cuantos se compraron por artículo, quedando quizás algo por el estilo:

ID Articulo Descripcion Compras Ventas 001

Camisa

XXX

XXX

002

Pantalon

XXX

XXX

003

Blusa

XXX

XXX

Parece algo sencillo y no muy difícil de realizar, pero es típico que en el diseño de tu base de datos hayas dejado por separado una entidad para compras y una entidad para ventas, por lo que tendríamos los siguientes entidades:

Lo primero que se nos podría ocurrir es una consulta donde proyectáramos "todos" vs "todos" para obtener conjunto de datos deseados: 1. 2. 3. 4. 5. 6. 7.

SELECT Ventas.IDArticulo, SUM(compras.Cantidad) AS SumaCompras, SUM(ventas.cantidad) AS SumaVentas FROM Compras FULL JOIN Ventas ON Ventas.IdArticulo = Compras.IdArticulo GROUP BY Ventas.idarticulo INTO CURSOR cResumen

A primera vista parece buena la idea, sumamos todas las cantidades de compras, todas las cantidades de ventas de una mezcla de todos los registros, no? Pero sabrás que pasa?: No funciona, ya que obtendremos datos que nada tienen que ver, primeramente porque se están cualificando las tuplas de la tabla ventas contra las tuplas de la tabla compras, pero nótese que hay algunos IDs que están en una, pero que no están en la otra, dándonos el siguiente resultado:

Podrás comentar, "Ya casi", solo me faltó el código 002 que es de la tabla compras que no estaba en la tabla ventas, en efecto es así, puedes seguir intentando cambiar las cláusulas por LEFT JOIN, RIGTH JOIN, cambiar Ventas.Articulo = Compras.IdArticulo por Compras.IdArticulo = Ventas.Articulo en lo correspondiente a la cláusula ON (de los operadores de proyección), pero obtendrás diferentes resultados que seguirán sin ser los que esperabas. Inclusive, puedes llegar a pensar que tal vez el operador de igualdad sale sobrando, e intentas una más para ver si funciona: 1. 2. 3. 4. 5. 6.

SELECT ventas.idArticulo, SUM(ventas.cantidad) AS TotalVentas, SUM(compras.cantidad) AS TotalCompras FROM ventas,compras GROUP BY ventas.idArticulo INTO CURSOR cReporte

Y tenemos el siguiente resultado:

Que sucedió?, lamentablemente al hacerlo de esta forma se está utilizando implícitamente un operador de igualdad entre las entidades (INNER JOIN), y eso sin tomar en cuenta que la suma no son lo que debería tener, por lo que tampoco obtenemos lo que queremos. Entonces? Qué es lo que debemos hacer?, la solución es sencilla, el uso de subconsultas.

Resulta ser que el álgebra de conjunto (que es la teoría que sustenta a la práctica de el lenguaje SQL) tiene un pequeño "truco" para normalizar este pequeño desperfecto en lo que parece no tener solución. Esto se le llaman subconsultas (mas adelante veremos que otros nombres se les conoce). En el párrafo anterior se hace mención a un tema que es base de esto, la normalización, en vez de intentar proyectar las columnas que serán calculadas (en nuestro ejemplo se sumaron, pero bien pudieron haber sido contadas) para llegar al resultado inmediato, debemos crear un conjunto de datos intermedio, el cual, nos servirá para ahora si proyectarlo correctamente: 1. 2. 3. 4. 5. 6.

SELECT idArticulo, 0000 as nCompras, Cantidad as nVentas FROM Ventas UNION SELECT idArticulo, Cantidad as nCompras , 0000 as nVentas FROM Compras INTO CURSOR cResumen

Con el query anterior obtendremos un conjunto de datos normalizados como el que sigue:

Aquí se hará uso de lo que se le denomina sentencias anidadas, en donde tenemos varias partes clave. 1. 2. 3. 4. 5. 6.

SELECT idArticulo, SUM(nCompras) as TotalCompras , SUM(nVentas) as TotalVentas FROM cResumen GROUP BY idArticulo INTO CURSOR cReporte

Quedando como sigue: