EXCEPT Statment behaviour - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

EXCEPT Statment behaviour

Author

Topic

ashraff87
Starting Member

17 Posts

Posted - 2008-07-25 : 04:59:02

currently im using EXCEPT statment between two select statments which literally select two structurally equal tables


SELECT * FROM OLD

EXCEPT

SELECT * FROM NEW

My problem is ive noticed say if OLD contains three records:

NAME | DATE | TYPE
--------------------
TOM May 1
TOM May 1
DAN Aug 3

AND new contains only this one:

NAME | DATE | TYPE
--------------------
DAN Aug 3

If i do a EXCEPT statment between the two the result i get is :

NAME | DATE | TYPE
--------------------
TOM May 1

However i was expecting to get this:
NAME | DATE | TYPE
--------------------
TOM May 1
TOM May 1

But for some reason the EXCEPT Statment is performing a DISTINCT operation? Is this normal, i thought EXCEPT would literally "subtract" set B from set A and leave in the duplicate records in B?

Any help is appreciated.

Ash.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-25 : 05:05:02

did you take a look at the BOL ?
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b1019300-171a-4a1a-854f-e1e751de3565.htm

quote:

Returns distinct values by comparing the results of two queries.

EXCEPT returns any distinct values from the left query that are not also found on the right query.

KH
[spoiler]Time is always against us[/spoiler]