Curso de ASP NET Avanzado

Un JOIN de dos tablas es una combinación de estas tablas, en la condición fijada entre dos columnas, una de cada tabla. El JOIN opera de modo que compara por parejas de filas, haciendo coincidir los contenidos de las columnas relacionadas.

La consulta de un JOIN de dos tablas tiene el mismo aspecto que cualquier consulta SELECT, pero tenemos dos características novedosas. Lo primero la cláusula FROM presenta dos tablas separadas por coma. La otra novedad, en la condición de búsqueda se comparan las columnas de las dos tablas.

Ya que se trata de una consulta que afecta a dos tablas, la condición de búsqueda restringe las parejas de filas que generan los resultados.

Para entendernos con las consultas JOIN de tablas relacionadas, podemos decir que la tabla con la clave foránea es el hijo de la relación, mientras que la que contiene la clave primaria es la tabla padre. En la consulta es obligatorio que la condición compare la clave foránea y la clave primaria.

Para practicar con las consultas JOIN vamos a instalarnos la base de datos Northwind. Puedes descargarte el archivo para instalar esta base de datos en nuestro servidor. Esta base de datos puedes descargarla junto con los libros en pantalla de Microsoft SQL Server 2005. De todos modos, te pongo un enlace aquí para la descarga directa:

Instalación de base de datos Northwind

Una vez descargado el archivo puedes ejecutarlo, directamente se abre el administrador de SQL Server 2005 y nos muestra el código SQL. Ejecutamos esta fichero SQL y se creará nuestra base de datos:

Con la base de datos creada, podemos proseguir con la explicación.

En el siguiente ejemplo, vamos a realizar una consulta sobre las tablas Employees y EmployeeTerritories, que están relacionadas tal y como puedes ver en la siguiente figura del diagrama:

Como ya sabemos para realizar la consulta, tenemos que poner la consulta con una condición que realice una comparación de la clave foránea de una tabla y la clave principal de la otra. Por lo tanto, vamos a obtener las columnas FirstName, LastName y TerritoryID:

Vemos que nos lanza un error la consulta construida, el motivo es que si vamos a utilizar columnas de diferentes tablas que reciben el mismo nombre, debemos especificar a que tabla pertenece esa columna del siguiente modo: nombreTabla.nombreColumna

Construimos correctamente la consulta con esta modificación y obtenemos el resultado deseado:

Hasta ahora todo lo que hemos visto del operador JOIN se conoce como JOIN INTERNO u operación de composición que combina información que procede de las dos tablas consultadas a partir de la formación de filas relacionadas entre ambas tablas y que se comparan en la condición de la búsqueda. Los pares de filas que componen la tabla compuesta son aquellos que  corresponden en cada una de las dos tablas que tienen el mismo valor. Esto significa que en caso de que no exista un emparejamiento estricto de filas (por ejemplo, una de ellas tiene valor NULL) no aparece en los resultados.

Si nos interesa que los resultados aparezcan, a pesar de no haber un emparejamiento estricto entre las filas de la condición de búsqueda del JOIN. Para  obtener estos resultados, tenemos que utilizar el operador JOIN EXTERNO. Con esto conseguimos debilitar las condiciones de composición interna y otorgar más información en el resultado.

Un JOIN EXTERNO completo consiste en ampliar el JOIN INTERNO que esta formado por los siguientes:

• El JOIN INTERNO de las dos tablas.
• Para cada fila de la primera  tabla consultada que no corresponda a ninguna fila de la segunda tabla, se crea una fila a los resultados, que incluye los valores de las columnas de la primera tabla  y les supone un valor NULL para todas las columnas de la segunda tabla correspondientes en el emparejado.
• Para cada fila de la segunda tabla que no corresponda a ninguna fila de la tabla consultada, se crea una fila a los resultados,  utilizando los valores de las columnas de la segunda tabla y suponiendo un valor NULL para todas las columnas de la primera tabla correspondientes en el emparejamiento.

La sintaxis del JOIN EXTERNO completo utiliza OUTER JOIN en la cláusula FROM de la consulta y la sintaxis ON en lugar de la cláusula WHERE que precede la condición de búsqueda del JOIN. Este tipo de sintaxis también puede utilizarse para el JOIN normal (o JOIN INTERNO) cambiando OUTER JOIN por INNER JOIN.

Hemos visto el JOIN EXTERNO completo, pero podemos estudiar también el JOIN EXTERNO IZQUIERDO entre dos tablas obteniendo los siguiente:

• El JOIN INTERNO de las dos tablas.
• Por cada fila de la primera tabla que no corresponda a ninguna fila de la segunda tabla, se crea una fila en los resultados, utilizando los valores de las columnas de la primera tabla y suponiendo un valor NULL para todas aquellas columnas de la segunda tabla que corresponden con el emaparejado.

La sintaxis del JOIN IZQUIERDO utiliza LEFT JOIN en la cláusula FROM de la sintaxis y la sentencia ON en lugar de la cláusula WHERE que precede la condición de comparación de la columna principal y foránea.

De un modo simétrico, tenemos el JOIN DERECHO entre dos tablas que obtiene el siguiente resultado:

• El JOIN INTERNO de la ambas tablas.
• Para cada fila de la segunda tabla que no corresponda a ninguna fila de la tabla consultada, se crea una fila a los resultados,  utilizando los valores de las columnas de la segunda tabla y suponiendo un valor NULL para todas las columnas de la primera tabla correspondientes en el emparejamiento.

SELECT Columnas FROM tabla1 INNER JOIN tabla2 ON tabla1.campo1 comparación tabla2.campo2

SELECT Columnas FROM tabla1 LEFT JOIN tabla2 ON tabla1.campo1 comparación tabla2.campo2

SELECT Columnas FROM tabla1 RIGHT JOIN tabla2 ON tabla1.campo1 comparación tabla2.campo2

En nuestro ejemplo la ejecución de estas tres consultas obtiene el mismo resultado, ya que existe condición estricta en todos los casos.

Con SQL podemos hacer consultas que resumen los datos a través de funciones de columnas y  mediante cláusulas especiales de la sentencia SELECT. Este tipo de funciones aceptan una columna de datos como argumento, y producen un único resultado que resume esa columna. Por este motivo, a este tipo de consultas se les conoce como consultas resumen, y en caso de que devuelvan un único resumen de una única columna también se les conoce como consultas escalares.

Las funciones de agregado generan valores que resumen los conjuntos de resultados de las consultas. Estas funciones gestionan todos los valores seleccionados de una única columna para obtener un resultado único. Una función de agregado se puede aplicar a todas las filas de una tabla, a un grupo de la tabla determinado por la cláusula WHERE o a uno o varios grupos de filas de tablas. Por lo tanto, concluimos que una función de agregado, obtiene un valor único para cada uno de lo grupos de filas.

La función COUNT() obtiene el número de elementos de un grupo. Su sintaxis es:

COUNT(argumento)

Pudiendo usar los siguientes argumentos:

• ALL, aplica la función a todos los valores. Es el valor por defecto.
• DISTINCT, devuelve la cantidad de valores únicos que no son NULL de la columna.
• Expresión, podemos colocar una expresión para que se aplique sobre el argumento ALL o DISTINCT.
• * : Determina que la función COUNT debe contar todas las filas. No puede recibir ningún parámetro ni ser utilizado con el argumento DISTINCT, ni ninguna expresión.

Por lo tanto podemos utilizar las siguientes combinaciones de argumentos:

• COUNT (*)
• COUNT (ALL expresión)
• COUNT (DISTINCT expresión)

Como ejemplo, vamos a contar el número de empleados que tenemos en la tabla Empleados de la base de datos PracticaSQL:

La cláusula GROUP BY se usa para conseguir resultados de agregado para cada registro del conjunto de resultados. Si no utilizamos la cláusula GROUP BY (como hasta ahora) las funciones de agregado obtienen un único valor para la instrucción SELECT.

En el siguiente ejemplo vamos a obtener el número de empleados que tenemos en cada oficina de la tabla empleados de base de datos PracticaSQL:

Podemos utilizar la cláusula GROUP BY junto con la cláusula ORDER BY para ordenar los resultados, como ves en el siguiente ejemplo:

Observa que colocamos la cláusula ORDER BY en último lugar, y podemos ordenar por el alias que hemos creado para nombrar la operación de conteo.

Esta cláusula establece las condiciones de la cláusula GROUP BY, de un modo muy parecido a como establece la cláusula WHERE las condiciones de la cláusula SELECT.

La cláusula WHERE aplica sus condiciones antes de que se produzca ninguna operación de agrupamiento, en cambio HAVING las aplica después. Su sintaxis es muy parecida a la de la clásula WHERE con la diferencia de que HAVING permite funciones de agregado en sus condiciones, mientras que WHERE no.

En el siguiente ejemplo vamos a mostrar el número de reservas de la base de datos PracticaSQL, que superan un total de 350 kilómetros para cada empleado: