SQL Server Performance Forum – Threads Archive

EXCEPT Operator in SQL Server 2005

i was trying the new EXCEPT operator of sql server 2005 to get the rows from first table which are not there in the second table. its working fine. this is my scenario…suppose i have two table of identical schema and the data would look something like this :- CREATE TABLE dbo.t1(col1 int, col2 int);
GO
CREATE TABLE dbo.t2(col1 int, col2 int);
GO INSERT INTO dbo.t1 SELECT 1, 1;
INSERT INTO dbo.t1 SELECT 2, 2;
INSERT INTO dbo.t1 SELECT 3, 3; INSERT INTO dbo.t2 SELECT 1, 1;
INSERT INTO dbo.t2 SELECT 2, 2;
INSERT INTO dbo.t2 SELECT 6, 7;
GO SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2 — THis statement will return 3rd row from table one which is quite obevious SELECT * FROM dbo.t2 EXCEPT SELECT * FROM dbo.t1 — This statment will return the 3rd row from Table 2 which is also quite normal Now i want to get both rows (from both tables) and i am using union. But what i get is only the row from the table T2. is this a normal behaviour ? –How can we interpret this behaviour
SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2
union
SELECT * FROM dbo.t2 EXCEPT SELECT * FROM dbo.t1
GO
Madhu

---

Except operator works like a "Left Anti Semi Join operator" as a logical operator that works against the join principles. It returns each row from the first query when there is no matching row in the second query. The result is clearly the Except operator brings the distinct value in the first result set that is not in the second result set {2}.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Nothing unexpected there. Its just the order of evaluation. To get the expected result, run the following (SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2)
UNION
(SELECT * FROM dbo.t2 EXCEPT SELECT * FROM dbo.t1) Your query is interpretted as ((SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2)
UNION
(SELECT * FROM dbo.t2) EXCEPT SELECT * FROM dbo.t1)
Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

ya… i got it… i was trying all the new operator in 2005 on sunday and i got struck in this. i forgot the very basic syntax… Satya, Roji thanks for the help Madhu

---

This may help. http://toponewithties.blogspot.com/2005/07/except-and-intersect.html
Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

]]>