UNION / EXCEPT

Combina los resultados de dos o más consultas en un solo conjunto de resultados que incluye todas las filas que pertenecen a las consultas de la unión. La operación UNION es distinta de la utilización de combinaciones de columnas de dos tablas.

A continuación se muestran las reglas básicas para combinar los conjuntos de resultados de dos consultas con UNION:

• El número y el orden de las columnas deben ser idénticos en todas las consultas.
• Los tipos de datos deben ser compatibles.

Sintaxis

---

    { <query_specification> | ( <query_expression> ) } 
  UNION [ ALL ] 
  <query_specification | ( <query_expression> ) 
 [ UNION [ ALL ] <query_specification> | ( <query_expression> ) 
    [ ...n ] ] 

Argumentos

---

<query_specification> | ( <query_expression> )

Es una especificación o expresión de consulta que devuelve datos que se van a combinar con los datos de otra especificación o expresión de consulta. No es preciso que las definiciones de las columnas que forman parte de una operación UNION sean iguales, pero deben ser compatibles a través de una conversión implícita. Cuando los tipos de datos difieren, el tipo de datos resultante se determina según las reglas de prioridad de tipos de datos. Cuando los tipos son los mismos pero varían en cuanto a precisión, escala o longitud, el resultado se determina según las mismas reglas para combinar expresiones. Para obtener más información, vea Precisión, escala y longitud (Transact-SQL).

Las columnas del tipo de datos xml deben ser equivalentes. Todas las columnas deben tener un tipo de esquema XML o no tener tipo. Si tienen tipo, debe ser el de la misma colección de esquemas XML.

UNION

Especifica que se deben combinar varios conjuntos de resultados para ser devueltos como un solo conjunto de resultados.

ALL

Agrega todas las filas a los resultados. Incluye las filas duplicadas. Si no se especifica, las filas duplicadas se quitan.

Ejemplos

---

A.Usar una instrucción UNION simple

En el siguiente ejemplo, el conjunto de resultados incluye el contenido de las columnas ProductModelID y Name de las tablas ProductModel y Gloves.

Transact-SQL

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO

-- Here is the simple union.
USE AdventureWorks2012;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

B.Usar SELECT INTO con UNION

En el siguiente ejemplo, la cláusula INTO de la segunda instrucción SELECT especifica que la tabla denominada ProductResults contiene el conjunto final de resultados de la unión de las columnas designadas de las tablas ProductModel y Gloves. Tenga en cuenta que la tabla Gloves se crea en la primera instrucción SELECT.

Transact-SQL

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL
DROP TABLE dbo.ProductResults;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO

USE AdventureWorks2012;
GO
SELECT ProductModelID, Name
INTO dbo.ProductResults
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves;
GO

SELECT ProductModelID, Name 
FROM dbo.ProductResults;

C.Usar UNION con dos instrucciones SELECT y ORDER BY

El orden de algunos parámetros utilizados con la cláusula UNION es importante. En el siguiente ejemplo se muestra el uso correcto e incorrecto de UNIONen dos instrucciones SELECT en las que se va a cambiar el nombre de una columna en el resultado.

Transact-SQL

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO

/* INCORRECT */
USE AdventureWorks2012;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
ORDER BY Name
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves;
GO

/* CORRECT */
USE AdventureWorks2012;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

D.Usar UNION de tres instrucciones SELECT para mostrar los efectos de ALL y los paréntesis

En los siguientes ejemplos se utiliza UNION para combinar los resultados de tres tablas que tienen las mismas 5 filas de datos. En el primer ejemplo se utiliza UNION ALL para mostrar los registros duplicados y se devuelven las 15 filas. En el segundo ejemplo se utiliza UNION sin ALL para eliminar las filas duplicadas de los resultados combinados de las tres instrucciones SELECT y se devuelven 5 filas.

En el tercer ejemplo se utiliza ALL con el primer UNION y los paréntesis incluyen al segundo UNION que no utiliza ALL. El segundo UNION se procesa en primer lugar porque se encuentra entre paréntesis. Devuelve 5 filas porque no se utiliza la opción ALL y se quitan los duplicados. Estas 5 filas se combinan con los resultados del primer SELECT mediante las palabras clave UNION ALL. Esto no quita los duplicados entre los dos conjuntos de 5 filas. El resultado final es de 10 filas.

Transact-SQL

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeOne;
GO
IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeTwo;
GO
IF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeThree;
GO

SELECT pp.LastName, pp.FirstName, e.JobTitle 
INTO dbo.EmployeeOne
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
SELECT pp.LastName, pp.FirstName, e.JobTitle 
INTO dbo.EmployeeTwo
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
SELECT pp.LastName, pp.FirstName, e.JobTitle 
INTO dbo.EmployeeThree
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
-- Union ALL
SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeOne
UNION ALL
SELECT LastName, FirstName ,JobTitle
FROM dbo.EmployeeTwo
UNION ALL
SELECT LastName, FirstName,JobTitle 
FROM dbo.EmployeeThree;
GO

SELECT LastName, FirstName,JobTitle
FROM dbo.EmployeeOne
UNION 
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeTwo
UNION 
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeThree;
GO

SELECT LastName, FirstName,JobTitle 
FROM dbo.EmployeeOne
UNION ALL
(
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeTwo
UNION
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeThree
);
GO

EXCEPT
Devuelven valores distintos al comparar los resultados de dos consultas.

EXCEPT devuelve los valores distintos de la consulta izquierda que no se encuentran en la consulta derecha.

INTERSECT devuelve los valores distintos devueltos por las consultas situadas a los lados izquierdo y derecho del operando INTERSECT.

Las reglas básicas para combinar los conjuntos de resultados de dos consultas que utilizan EXCEPT o INTERSECT son las siguientes:


El número y el orden de las columnas debe ser el mismo en todas las consultas.


Los tipos de datos deben ser compatibles.






Sintaxis


















{ <query_specification> | ( <query_expression> ) } 
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }









Argumentos









<query_specification> | ( <query_expression> )

Es una especificación o expresión de consulta que devuelve datos que se van a comparar con los de otra especificación o expresión de consulta. No es preciso que las definiciones de las columnas que forman parte de una operación EXCEPT o INTERSECT sean idénticas, pero deben ser comparables por medio de una conversión implícita. Cuando los tipos de datos difieren, el tipo que se utiliza para realizar la comparación y devolver los resultados se determina según las reglas de prioridad de tipo de datos.

Cuando los tipos son los mismos pero varían en cuanto a precisión, escala o longitud, el resultado se determina según las mismas reglas para combinar expresiones. Para obtener más información, vea Precisión, escala y longitud (Transact-SQL).

La especificación o expresión de consulta no puede devolver columnas de tipo xml, text, ntext, image o no binario definido por el usuario CLR, ya que estos tipos de datos no son comparables.

EXCEPT

Devuelve los valores distintos de la consulta situada a la izquierda del operando EXCEPT que no se devuelven desde la consulta derecha.

INTERSECT

Devuelve los valores distintos devueltos por las consultas situadas a los lados izquierdo y derecho del operando INTERSECT.





Comentarios









Cuando los tipos de datos de columnas comparables devueltos por las consultas situadas a la izquierda y a la derecha de los operandos EXCEPT o INTERSECT son tipos de datos de caracteres con intercalaciones diferentes, la comparación requerida se realiza conforme a las reglas de prioridad de intercalación. Si no es posible realizar esta conversión, SQL Server Database Engine (Motor de base de datos de SQL Server) devuelve un error.

Cuando se comparan filas para determinar valores distintos, dos valores NULL se consideran equivalentes.

Los nombres de columna del conjunto de resultados devueltos por EXCEPT o INTERSECT son los mismos que han sido devueltos por la consulta situada en el lado izquierdo del operando.

Los nombres o alias de columna de las cláusulas ORDER BY deben hacer referencia a los nombres de columna devueltos por la consulta del lado izquierdo.

La nulabilidad de cualquier columna del conjunto de resultados devueltos por EXCEPT o INTERSECT es la misma que la de la columna correspondiente devuelta por la consulta situada en el lado izquierdo del operando.

Si EXCEPT o INTERSECT se utilizan con otros operadores en una expresión, ésta se evalúa en el contexto de la siguiente prioridad:


Expresiones entre paréntesis


El operando INTERSECT


EXCEPT y UNION se evalúan de izquierda a derecha según su posición en la expresión



Si EXCEPT o INTERSECT se utilizan para comparar más de dos conjuntos de consultas, la conversión del tipo de datos se determina al comparar dos consultas a la vez y mediante las reglas mencionadas de evaluación de expresiones.

EXCEPT e INTERSECT no se pueden utilizar en definiciones de vistas distribuidas con particiones, notificaciones de consultas o con las cláusulas COMPUTE y COMPUTE BY.

EXCEPT e INTERSECT se pueden utilizar en consultas distribuidas, pero sólo se ejecutan en el servidor local y no se insertan en el servidor vinculado. Por lo tanto, el uso de EXCEPT e INTERSECT en consultas distribuidas puede afectar al rendimiento.

Los cursores de sólo avance rápido o estáticos son completamente compatibles con el conjunto de resultados si se utilizan con una operación EXCEPT o INTERSECT. Si un cursor controlado por conjunto de claves o dinámico se utiliza con una operación EXCEPT o INTERSECT, el cursor del conjunto de resultados de la operación se convierte en un cursor estático.

Cuando una operación EXCEPT se muestra mediante la característica Plan de presentación gráfico de SQL Server Management Studio, la operación aparece como un operador left anti semi join y la operación INTERSECT aparece como un operador left semi join.




Ejemplos









En los ejemplos siguientes se muestra cómo utilizar los operandos INTERSECT y EXCEPT. La primera consulta devuelve todos los valores de la tablaProduction.Product para comparar los resultados con INTERSECT y EXCEPT.










USE AdventureWorks2008R2;
GO
SELECT ProductID 
FROM Production.Product ;
--Result: 504 Rows






La siguiente consulta devuelve los valores distintos devueltos por las consultas situadas a los lados izquierdo y derecho del operando INTERSECT.










USE AdventureWorks2008R2;
GO
SELECT ProductID 
FROM Production.Product
INTERSECT
SELECT ProductID 
FROM Production.WorkOrder ;
--Result: 238 Rows (products that have work orders)






La siguiente consulta devuelve los valores distintos de la consulta situados a la izquierda del operando EXCEPT que no se encuentran en la consulta derecha.










USE AdventureWorks2008R2;
GO
SELECT ProductID 
FROM Production.Product
EXCEPT
SELECT ProductID 
FROM Production.WorkOrder ;
--Result: 266 Rows (products without work orders)






La siguiente consulta devuelve los valores distintos de la consulta situados a la izquierda del operando EXCEPT que no se encuentran en la consulta derecha. Las tablas se invierten respecto al ejemplo anterior.










USE AdventureWorks2008R2;
GO
SELECT ProductID 
FROM Production.WorkOrder
EXCEPT
SELECT ProductID 
FROM Production.Product ;
--Result: 0 Rows (work orders without products)