Eddy A. Velasco

INNER JOIN

El INNER JOIN es otro tipo de composición de tablas, permite emparejar filas de distintas tablas de forma más eficiente que con el producto cartesiano cuando una de las columnas de emparejamiento está indexada. Ya que en vez de hacer el producto cartesiano completo y luego seleccionar la filas que cumplen la condición de emparejamiento, para cada fila de una de las tablas busca directamente en la otra tabla las filas que cumplen la condición, con lo cual se emparejan sólo las filas que luego aparecen en el resultado.

La sintaxis es la siguiente:

FROM -tabla1- INNER JOIN -tabla2- ON -tabla1.col1-comp-tabla2.col2

Ejemplo:

SELECT *

FROM   empleado

       INNER JOIN departamento

          ON empleado.IDDepartamento = departamento.IDDepartamento

SELECT *

FROM pedidos INNER JOIN clientes ON pedidos.clie = clientes.numclie

SELECT *

FROM pedidos INNER JOIN productos ON (pedidos.fab = productos.idfab) AND (pedidos.producto = productos.idproducto)

SELECT *

FROM (pedidos INNER JOIN clientes ON pedidos.clie = clientes.numclie) INNER JOIN empleados ON pedidos.rep = empleados.numemp

SELECT *

FROM clientes INNER JOIN (pedidos INNER JOIN empleados ON pedidos.rep = empleados.numemp) ON pedidos.clie = clientes.numclie

Combinación externa (OUTER JOIN)

Mediante esta operación no se requiere que cada registro en las tablas a tratar tenga un registro equivalente en la otra tabla. El registro es mantenido en la tabla combinada si no existe otro registro que le corresponda.

En SQL:2003 no existe una notación implícita para las combinaciones externas.

Este tipo de operación se subdivide dependiendo de la tabla a la cual se le admitirán los registros que no tienen correspondencia, ya sean de tabla izquierda, de tabla derecha, o combinación completa.

de tabla izquierda (LEFT OUTER JOIN o LEFT JOIN)

El resultado de esta operación siempre contiene todos los registros de la tabla de la izquierda (la primera tabla que se menciona en la consulta), aun cuando no exista un registro correspondiente en la tabla de la derecha, para uno de la izquierda.

    La sentencia LEFT OUTER JOIN retorna la pareja de todos los valores de la tabla izquierda con los valores de la tabla de la derecha correspondientes, o retorna un valor nulo NULL en caso de no correspondencia.

A diferencia del resultado presentado en los ejemplos A y B (de combinación interna) donde no se mostraba el empleado cuyo departamento no existía; en el siguiente ejemplo se presentarán los empleados con su respectivo departamento, e inclusive se presentará el empleado, cuyo departamento no existe.

Utilice una operación LEFT JOIN para crear una combinación externa por la izquierda. Las combinaciones externas por la izquierda incluyen todos los registros de la primera de las dos tablas (izquierda), incluso si no hay valores coincidentes para los registros en la segunda tabla (derecha).

H. Ejemplo de tabla izquierda para la combinación externa:

SELECT *

FROM   empleado

       LEFT OUTER JOIN departamento

          ON empleado.IDDepartamento = departamento.IDDepartamento

 

de tabla derecha (RIGHT OUTER JOIN o RIGHT JOIN)

Esta operación es inversa a la anterior; el resultado de esta operación siempre contiene todos los registros de la tabla de la derecha (la segunda tabla que se menciona en la consulta), aun cuando no exista un registro correspondiente en la tabla de la izquierda, para uno de la derecha.

    La sentencia RIGHT OUTER JOIN retorna la pareja de todos los valores de la tabla derecha con los valores de la tabla de la izquierda correspondientes, o retorna un valor nulo NULL en caso de no correspondencia.

Utilice una operación RIGHT JOIN para crear una combinación externa por la derecha. Las combinaciones externas por la derecha incluyen todos los registros de la segunda de las dos tablas (derecha), incluso si no hay valores coincidentes para los registros en la primera tabla (izquierda).

I. Ejemplo de tabla derecha para la combinación externa:

SELECT *

FROM   empleado

       RIGHT OUTER JOIN departamento

          ON empleado.IDDepartamento = departamento.IDDepartamento

Funciones de agregado (Transact-SQL)

Las funciones de agregado realizan un cálculo sobre un conjunto de valores y devuelven un solo valor. Si exceptuamos la función COUNT, todas las funciones de agregado ignoran los valores NULL. Las funciones de agregado se suelen usar con la cláusula GROUP BY de la instrucción SELECT.

Todas las funciones de agregado son deterministas. Esto significa que las funciones de agregado devuelven el mismo valor cada vez que se las llama con un conjunto específico de valores de entrada. Para obtener más información acerca del determinismo de las funciones, vea Funciones deterministas y no deterministas. La cláusula OVER puede seguir a todas las funciones de agregado, excepto CHECKSUM.

Las funciones de agregado sólo se pueden usar como expresiones en:

    La lista de selección de una instrucción SELECT (en una subconsulta o en la consulta externa).

    Cláusulas COMPUTE o COMPUTE BY.

    Cláusulas HAVING.

Transact-SQL proporciona las siguientes funciones de agregado:

o   AVG

Devuelve el promedio de los valores de un grupo. Los valores NULL se pasan por alto. Puede ir seguida de la cláusula OVER.

USE AdventureWorks2008R2;

GO

SELECT AVG(ListPrice)

FROM Production.Product;

o   MIN

Devuelve el valor mínimo de la expresión. Puede ir seguida de la cláusula OVER

En el ejemplo siguiente se devuelve la tasa de impuestos más baja (mínima).

USE AdventureWorks2008R2;

GO

SELECT MIN(TaxRate)

FROM Sales.SalesTaxRate;

GO

o   CHECKSUM_AGG

Devuelve la suma de comprobación de los valores de un grupo. Los valores NULL se pasan por alto. Puede ir seguida de la cláusula OVER.

El ejemplo siguiente utiliza CHECKSUM_AGG para detectar cambios en la columna Quantity de la tabla ProductInventory en la base de datos AdventureWorks2008R2.

--Get the checksum value before the column value is changed.

USE AdventureWorks2008R2;

GO

SELECT CHECKSUM_AGG(CAST(Quantity AS int))

FROM Production.ProductInventory;

GO

o   Cláusula OVER

Determina las particiones y el orden del conjunto de filas antes de que se aplique la función de ventana asociada.

Las funciones de categoría: ROW_NUMBER, DENSE_RANK, RANK, NTILE utilizan la cláusula OVER. En el siguiente ejemplo se muestra el uso de la cláusula OVER con ROW_NUMBER.

USE AdventureWorks2008R2;

GO

SELECT p.FirstName, p.LastName

    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'

    ,s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson s

    INNER JOIN Person.Person p

        ON s.BusinessEntityID = p.BusinessEntityID

    INNER JOIN Person.Address a

        ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL

    AND SalesYTD <> 0;

GO

o   COUNT

Devuelve el número de elementos de un grupo. COUNT funciona como la función COUNT_BIG. La única diferencia entre ambas funciones está en los valores devueltos. COUNT siempre devuelve un valor de tipo de datos int. COUNT_BIG siempre devuelve un valor de tipo de datos bigint. Puede ir seguida de la cláusula OVER.

                En el ejemplo siguiente se muestra el número de cargos diferentes que puede tener un empleado que trabaja en Adventure Works Cycles.

USE AdventureWorks2008R2;

GO

SELECT COUNT(DISTINCT JobTitle)

FROM HumanResources.Employee;

GO

o   ROWCOUNT_BIG

Devuelve el número de filas afectadas por la última instrucción ejecutada. Esta función actúa como @@ROWCOUNT, pero el tipo de valor devuelto de ROWCOUNT_BIG es bigint.

Sintaxis

ROWCOUNT_BIG ( )

o   COUNT_BIG

Devuelve el número de elementos de un grupo. COUNT_BIG funciona como COUNT. La única diferencia entre ambas funciones está en los valores devueltos. COUNT_BIG siempre devuelve un valor de tipo de datos bigint. COUNT siempre devuelve un valor de tipo de datos int. Puede ir seguida de la OVER (cláusula de Transact-SQL).

Sintaxis

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )

o   STDEV

Devuelve la desviación típica estadística de todos los valores de la expresión especificada. Puede ir seguida de la cláusula OVER.

En el ejemplo siguiente se obtiene la desviación típica de todos los valores de bonos en la tabla SalesPerson.

USE AdventureWorks2008R2;

GO

SELECT STDEV(Bonus)

FROM Sales.SalesPerson;

GO

o   GROUPING

Indica si una expresión de columna especificada en una lista GROUP BY es agregada o no. GROUPING devuelve 1 para agregado y 0 para no agregado, en el conjunto de resultados. GROUPING sólo se puede usar en la lista de SELECT <selección>, cláusulas HAVING y ORDER BY cuando se especifica GROUP BY.

En el ejemplo siguiente se agrupa SalesQuota y se agregan las cantidades de SaleYTD. La función GROUPING se aplica a la columna SalesQuota.

USE AdventureWorks2008R2;

GO

SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'

FROM Sales.SalesPerson

GROUP BY SalesQuota WITH ROLLUP;

GO

o   STDEVP

Devuelve la desviación estadística estándar para la población de todos los valores de la expresión especificada. Puede ir seguida de la cláusula OVER.

En el ejemplo siguiente se devuelve la desviación estándar de la población de todos los valores de bonificación de la tabla SalesPerson.

USE AdventureWorks2008R2;

GO

SELECT STDEVP(Bonus)

FROM Sales.SalesPerson;

GO

o   GROUPING_ID

Es una función que calcula el nivel de agrupación. GROUPING_ID solo se puede usar en la lista de <selección> SELECT, cláusulas HAVING u ORDER BY cuando se especifica GROUP BY.

                El ejemplo siguiente devuelve el recuento de empleados por Name y Title, Name, y total de la compañía. GROUPING_ID() se usa para crear un valor en cada fila de la columna Title que identifica su nivel de agregación.

USE AdventureWorks2008R2;

GO

SELECT D.Name

    ,CASE

    WHEN GROUPING_ID(D.Name, E.JobTitle) = 0 THEN E.JobTitle

    WHEN GROUPING_ID(D.Name, E.JobTitle) = 1 THEN N'Total: ' + D.Name

    WHEN GROUPING_ID(D.Name, E.JobTitle) = 3 THEN N'Company Total:'

        ELSE N'Unknown'

    END AS N'Job Title'

    ,COUNT(E.BusinessEntityID) AS N'Employee Count'

FROM HumanResources.Employee E

    INNER JOIN HumanResources.EmployeeDepartmentHistory DH

        ON E.BusinessEntityID = DH.BusinessEntityID

    INNER JOIN HumanResources.Department D

        ON D.DepartmentID = DH.DepartmentID    

WHERE DH.EndDate IS NULL

    AND D.DepartmentID IN (12,14)

GROUP BY ROLLUP(D.Name, E.JobTitle);

o   SUM

Devuelve la suma de todos los valores o sólo de los valores DISTINCT de la expresión. SUM sólo puede utilizarse con columnas numéricas. Los valores Null se pasan por alto. Puede ir seguida de la OVER (cláusula de Transact-SQL).

En estos ejemplos se muestran las diferencias entre las funciones de agregado y las funciones de agregado de filas. En el primero se muestran funciones de agregado que solo ofrecen datos de resumen y en el segundo, funciones de agregado de filas que ofrecen datos de resumen y de detalle.

USE AdventureWorks2008R2;

GO

SELECT Color, SUM(ListPrice), SUM(StandardCost)

FROM Production.Product

WHERE Color IS NOT NULL

    AND ListPrice != 0.00

    AND Name LIKE 'Mountain%'

GROUP BY Color

ORDER BY Color;

GO

o   MAX

Devuelve el valor máximo de la expresión. Puede ir seguida de la cláusula OVER.

En el siguiente ejemplo se devuelve el tipo impositivo mayor (máximo).

USE AdventureWorks2008R2;

GO

SELECT MAX(TaxRate)

FROM Sales.SalesTaxRate;

GO        

o   VAR

Devuelve la varianza estadística de todos los valores de la expresión especificada. Puede ir seguida de la cláusula OVER.

Este ejemplo devuelve la varianza para todos los valores de bonificación de la tabla SalesPerson.

USE AdventureWorks2008R2;

GO

SELECT VAR(Bonus)

FROM Sales.SalesPerson;

GO

o   VARP

Devuelve la varianza estadística de la población para todos los valores de la expresión especificada. Puede ir seguida de la cláusula OVER.

El siguiente ejemplo devuelve la varianza para la población de todos los valores de bonificación de la tabla SalesPerson.

USE AdventureWorks2008R2;

GO

SELECT VARP(Bonus)

FROM Sales.SalesPerson;

GO