Intersect and Except Not worked on sqlserver 2000

Posted by Raghuldrag under Sql Server on 1/6/2014 | Points: 10 | Views : 914 | Status : [Member] | Replies : 2
Dear Friends,



I m trying to learn on set operators in MSSQL,so i created two tables



CREATE TABLE Students2000(
Name VARCHAR(15),
TotalMark INT)

CREATE TABLE Students2005(
Name VARCHAR(15),
TotalMark INT)



INSERT INTO Students2000 VALUES('Robert',1063)
INSERT INTO Students2000 VALUES('John',1070)
INSERT INTO Students2000 VALUES('Rose',1032)
INSERT INTO Students2000 VALUES('Abel',1002)

INSERT INTO Students2005 VALUES('Robert',1063)
INSERT INTO Students2005 VALUES('Rose',1032)
INSERT INTO Students2005 VALUES('Boss',1086)
INSERT INTO Students2005 VALUES('Marry',1034)



when i tried to make intersect and except in above table its not working showing error on it



select * from Students2000
except
select * from Students2005


ERROR:Incorrect syntax near the keyword 'EXCEPT'.


What was the problem here ????




Responses

Posted by: kgovindarao523-21772 on: 1/6/2014 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi,

INTERSECT &
EXCEPT are implemented in SQL Server 2005, but not in 2000.
you have to write alternative queries using joins.

Thank you,
Govind

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 1/6/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Alternate methods for the EXCEPT operation is as follows:
select * from Students2000 
except
select * from Students2005

select s2k.*
from Students2000 s2k
where not exists (select 1 from Students2005 s25
where s25.Name = s2k.Name and s25.TotalMark = s2k.TotalMark)

select s2k.*
from Students2000 s2k
LEFT JOIN Students2005 s25
ON s25.Name = s2k.Name and s25.TotalMark = s2k.TotalMark
WHERE s25.Name IS NULL


http://technet.microsoft.com/en-us/library/ms188055(v=sql.90).aspx
http://blog.sqlauthority.com/2008/08/07/sql-server-except-clause-in-sql-server-is-similar-to-minus-clause-in-oracle/
http://www.sql-programmers.com/set-operators-in-sql-server-union-union-all-intersect-except.aspx

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response