tsql - minus - union all mysql

¿Por qué EXCEPT existe en T-SQL?

Question

Acabo de leer sobre EXCEPTO e INTERSECT en MSDN Library y encontré este ejemplo de cómo usar INTERSECT:

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

Quizás esté pasado de moda, pero normalmente usaría el siguiente código para lograr el mismo resultado:

SELECT P.ProductID 
FROM Production.Product P 
INNER JOIN Production.WorkOrder W ON W.ProductID = P.ProductID

¿Me estoy perdiendo algo, o es INTERSECT lo mismo que INNER JOIN? ¿Hay un beneficio de rendimiento al usar uno sobre el otro?

La misma pregunta por EXCEPTO. Cómo es esto:

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

diferente de esto:

SELECT P.ProductID 
FROM Production.Product P 
LEFT JOIN Production.WorkOrder W ON W.ProductID = P.ProductID
WHERE W.ProductID IS NULL

?

Answer 1

En mi opinión, EXCEPT e INTERSECT se utilizan para hacer las mismas cosas que el comando JOIN, pero es más simple con tablas que no tienen claves primarias, por ejemplo:

con INTERSECT :

SELECT FIRSTNAME,
       LASTNAME,
       ADDRESSLINE1,
       CITY,
       STATEPROVINCECODE,
       POSTALCODE
FROM   MANAGER 
EXCEPT
SELECT FIRSTNAME,
       LASTNAME,
       ADDRESSLINE1,
       CITY,
       STATEPROVINCECODE,
       POSTALCODE
FROM   CUSTOMER

Y para tener los mismos resultados con JOIN, debes hacer:

SELECT   M.FIRSTNAME,
         M.LASTNAME,
         M.ADDRESSLINE1,
         M.CITY,
         M.STATEPROVINCECODE,
         M.POSTALCODE
FROM     MANAGER M
WHERE    NOT EXISTS (SELECT *
                     FROM   CUSTOMER C
                     WHERE  M.FIRSTNAME = C.FIRSTNAME
                            AND M.LASTNAME = C.LASTNAME
                            AND M.ADDRESSLINE1 = C.ADDRESSLINE1
                            AND M.CITY = C.CITY
                            AND M.POSTALCODE = C.POSTALCODE)
GROUP BY M.FIRSTNAME,M.LASTNAME,M.ADDRESSLINE1,M.CITY,
         M.STATEPROVINCECODE,M.POSTALCODE 

Más información here .

Answer 2

Me centraré en EXCEPT solo porque estoy más familiarizado con él. Además, como descargo de responsabilidad, mis ejemplos estarán en Sqlite, ya que estoy en una caja de Linux. Sin embargo, tanto Sqlite como SQL Server deberían admitir la funcionalidad.

Tanto INTERSECT como EXCEPT son operadores de conjuntos, derivados de las ideas subyacentes en el álgebra relacional . Operan en valores distintos , siendo operadores establecidos.

Tu ejemplo es simplista. Daré un contraejemplo, usando una versión de Sqlite de la base de datos de ejemplo de Northwind .

Digamos que desea obtener los CustomerID de todos los clientes que hicieron un pedido con EmployeeID de 5, pero NO aquellos que también hicieron un pedido con EmployeeID de 6. Esto es simple y natural con un EXCEPT .

SELECT CustomerID FROM orders
WHERE EmployeeID = 5
EXCEPT
SELECT CustomerID FROM orders
WHERE EmployeeID = 6

Esto devuelve 14 filas en mi versión de Northwind.

Supongamos que decide reescribir esto utilizando JOIN s. Tal vez algo como esto?

SELECT o1.CustomerID
FROM orders o1 INNER JOIN orders o2 ON o1.CustomerID = o2.CustomerID
WHERE o1.EmployeeID = 5 AND o2.EmployeeID != 6

Whoops, 525 filas. Tal vez agregar un DISTINCT ?

SELECT DISTINCT o1.CustomerID
FROM orders o1 INNER JOIN orders o2 ON o1.CustomerID = o2.CustomerID
WHERE o1.EmployeeID = 5 AND o2.EmployeeID != 6

Ahora son 28 filas, aún mucho más de lo que obtuvimos EXCEPT . La razón es que esto no elimina los CustomerID que han realizado un pedido con 6. En su lugar, devuelve todos los CustomerID que tienen un pedido con 5 y algún EmployeeID distinto de 6, ya sea que tengan un pedido con EmployeeID 6 o no.

En resumen, EXCEPT e INTERSECT son operadores de conjuntos que comparan dos consultas, devuelven tuplas únicas y ciertamente tienen su uso.

Answer 3

Sus ejemplos de sus consultas "equivalentes" son incorrectos: la consulta con INTERSECT no siempre devuelve el mismo resultado que INNER JOIN y el mismo para EXCEPT y LEFT JOIN .

Mira el ejemplo particular sobre INTERSECT:

DECLARE @t TABLE(t INT NOT NULL)
DECLARE @x TABLE(x INT NOT NULL)

INSERT @t
VALUES (1), (2), (3)

INSERT @x VALUES(1), (1), (1)

SELECT t FROM @t
INTERSECT SELECT x FROM @x

SELECT t FROM @t
INNER JOIN @x ON x = t

INTERSECT es más parecido ( pero no igual ) a la cláusula IN :

SELECT t FROM @t
WHERE t IN (select x FROM @x)

o como EXISTS

SELECT t FROM @t
WHERE EXISTS (select * FROM @x WHERE x = t)

Los mismos ejemplos se pueden adaptar a la cláusula EXCEPT .

Answer 4

• INTERSECT y EXCEPT son semi-joins
• JOIN es equi-join

Así que cuando unes 2 tablas que coinciden, digamos, 5 filas y 3 filas

• UNIR da 15 filas
• INTERSECT da 3 filas

EXCEPTO es similar a OUTER JOIN por el mismo motivo

Mientras estamos hablando de semi-uniones, entonces en su mayoría

• INTERSECT da los mismos resultados que EXISTS.
• EXCEPTO da los mismos resultados que NO EXISTE

La "mayoría" viene porque INTERSECT y EXCEPTO

• trate a NULL de manera diferente: para un ejemplo completamente trabajado, vea esto por Paul White, también conocido como SQL Kiwi
• aplicar DISTINTO

Edición, demostración rápida de todo esto.

DECLARE @t1 TABLE (t1col INT);
INSERT @t1 VALUES (1), (2), (2), (3), (3), (5), (5);

DECLARE @t2 TABLE (t2col INT);
INSERT @t2 VALUES (1), (2), (3), (4);

SELECT ''INNER JOIN'', * FROM @t1 t1 JOIN @t2 t2 ON t1.t1col = t2.t2col -- same both ways

SELECT ''t1 INTERSECT t2'', * FROM @t1 INTERSECT SELECT ''t1 INTERSECT t2'', * FROM @t2;

SELECT ''t2 INTERSECT t1'', * FROM @t2 INTERSECT SELECT ''t2 INTERSECT t1'', * FROM @t1;

SELECT ''t1 EXISTS t2'', * FROM @t1 t1
WHERE EXISTS (SELECT * FROM @t2 t2 WHERE t1.t1col = t2.t2col);

SELECT ''t2 EXISTS t1'', * FROM @t2 t2
WHERE EXISTS (SELECT * FROM @t1 t1 WHERE t1.t1col = t2.t2col);

SELECT ''t1 LEFT JOIN t2, IS NULL'', * FROM @t1 t1 LEFT JOIN @t2 t2 ON t1.t1col = t2.t2col WHERE t2.t2col IS NULL
SELECT ''t2 LEFT JOIN t1, IS NULL'', * FROM @t2 t2 LEFT JOIN @t1 t1 ON t1.t1col = t2.t2col WHERE t1.t1col IS NULL

SELECT ''t1 EXCEPT t2'', * FROM @t1 EXCEPT SELECT ''t1 EXCEPT t2'', * FROM @t2;

SELECT ''t2 EXCEPT t1'', * FROM @t2 EXCEPT SELECT ''t2 EXCEPT t1'', * FROM @t1;

SELECT ''t1 NOT EXISTS t2'', * FROM @t1 t1
WHERE NOT EXISTS (SELECT * FROM @t2 t2 WHERE t1.t1col = t2.t2col);

SELECT ''t2 NOT EXISTS t1'', * FROM @t2 t2
WHERE NOT EXISTS (SELECT * FROM @t1 t1 WHERE t1.t1col = t2.t2col);

Actualización: febrero de 2013. Se agregó una columna adicional para describir la operación.