¿Por qué existe EXCEPT en T-SQL?
Estaba leyendo sobre EXCEPTO y INTERSECT en la Biblioteca de MSDN y me encontré con 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)
Tal vez soy anticuado, 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 INTERSECT es lo mismo que INNER JOIN? ¿Hay un beneficio de rendimiento al usar uno sobre el otro?
La misma pregunta para 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
?
4 answers
Me voy a 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 álgebra relacional. Operan en valores distintos, siendo operadores establecidos.
Su ejemplo es simplista. Voy a dar un contraejemplo, usando un Sqlite version of the Northwind sample database.
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 usando 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
Ups, 525 filas. Tal vez añadir 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, todavía mucho más de lo que estábamos obteniendo con EXCEPT
. La razón es que esto no es eliminar CustomerIDs que han hecho un pedido con 6. Más bien, devuelve todos los CustomerID que tienen un orden con 5 y algunos EmployeeID que no sean 6, ya sea que también tengan o no un orden con EmployeeID 6.
En resumen, EXCEPT
y INTERSECT
son operadores conjuntos que comparan dos consultas, devolviendo tuplas únicas, y ciertamente tienen su utilizar.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-02-02 05:07:31
- INTERSECAR y EXCEPTO son semi-joins
- JOIN es equi-join
Así que cuando se unen 2 tablas que coinciden, por ejemplo, 5 filas y 3 filas
- UNIR da 15 filas
- INTERSECT da 3 filas
EXCEPTO es similar a la UNIÓN EXTERNA por la misma razón
Mientras estamos sobre semi-joins, entonces mayormente
- INTERSECT da los mismos resultados que EXISTEN
- EXCEPTO da los mismos resultados que NO EXISTE
El "mayormente" viene porque ambos INTERSECTAN y EXCEPTO
- tratar NULL de manera diferente: Para un ejemplo completamente trabajado, ver esto por Paul White aka SQL Kiwi
- aplicar DISTINTO
Editar, 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: Feb 2013. Se agregó una columna adicional para describir la operación
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-02-08 09:54:10
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 un 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 como (pero no la misma) que 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
.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-02-02 09:18:52
En mi opinión EXCEPT
y 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 aquí.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-02-02 04:12:46