EXCEPT Operator with UNION 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

Madhu,

I think that has to do with the 'order of precedence'. UNION takes PRECEDENCE over EXCEPT, so the way the query is executing is really:

Code Snippet

SELECT * FROM dbo.t1
EXCEPT
SELECT * FROM dbo.t2 UNION SELECT * FROM dbo.t2
EXCEPT
SELECT * FROM dbo.t1

To accomplish your stated goal, use parenthesis to control the order of precedence. Such as:

Code Snippet

(SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2)
UNION
(SELECT * FROM dbo.t2 EXCEPT SELECT * FROM dbo.t1)

Just like using arithmetic, JOINS follow Rules of Precedence. It is a good idea to always use parentheses to state your JOIN intentions.

|||

thanks a lot Arnie... i got the point... i forgot the very basics... i was just trying all the new operators in sql server 2005.... i got struck in this... thanks a lot again....

Madhu

|||Not a problem. Experimenting is how we all 'keep up' with the rapidly changing 'sea of knowledge'.

except command not working

When I use EXCEPT in sql server 2005 (like union, union all), I am getting the following error. Did any body used this command in sql server 2005. (select * from t1 except select * from t2)

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'EXCEPT'.

Quote:

Originally Posted by sajithamol

When I use EXCEPT in sql server 2005 (like union, union all), I am getting the following error. Did any body used this command in sql server 2005. (select * from t1 except select * from t2)

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'EXCEPT'.

Select * from table1 except select * from table2 works perfectly fine for me, on SQL 2005