Intersect and Except Not worked on sqlserver 2000

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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 ????

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts