Why does EXCEPT not work? Incorrect Syntax near the Word Except

Question

SELECT SKU
FROM PartProductSupplemental
EXCEPT
SELECT SKU
FROM Inventory

Why do I get this error:

Incorrect Syntax near the Word Except

I check on line and syntax is syntactically correct:

SELECT ProductID 
FROM Production.WorkOrder
EXCEPT
SELECT ProductID 
FROM Production.Product

Conversion

Try putting it on one line - it might just be a missing whitespace. – Impractical

I know this is flagged correctly, but in the future using SQL 08 as a title could make people think you are talking about an ANSI standard when just reading the title vs a Microsoft product. More reasonable title would change SQL 08 would be Microsoft SQL Server 2008. Picky I know:). – Chemmy

Answer 1

Your database compatibility mode is probably set to 2000 (80) or earlier.

In Management Studio:

1. Right click on the database name under the "Databases" heading in the Object Explorer
2. In the Properties window that pops up, select "Options" - Compatibility Level is third from the top, on the right.

Kahler

Should work in 2005. msdn.microsoft.com/en-us/library/ms188055%28v=SQL.90%29.aspx – Defiance

compatability level is SQL Server 2008(80) – Conversion

@bizness: 80 is 2000; 100 means 2008 – Longdistance

80 or 2008? 80 - 2000, 90 - 2005, 100 - 2008 – Kahler

@bizness: that's wrong. It's either "SQL Server 2000(80)" or "SQL Server 2005(90)" or "SQL Server 2008(100)" – Urita

and i guess that solves mystery cuz i cant change compatability cuz im nota llowed. Thanks guys – Conversion

In the Properties window that pops up, select "Options" - Compatibility Level is third from the top, on the right. >> and then do what? cmon people – Jello

Answer 2

try using distinct and MINUS just to test.

Except should have worked as well, are the fields of the same type ?

(it works also on 2005, according to documentation and you don't need () on the 2nd phrase).

Beamer

Answer 3

When I run the following it works fine:

with PartProductSupplemental as
(
  SELECT 1 sku
  UNION
  select 2
  UNION
  SELECT 3
  UNION
  select 4
  UNION 
  SELECT 5
),
Inventory as
(
  SELECT 1 sku
  UNION
  select 2
  UNION
  SELECT 3

)

SELECT SKU
FROM PartProductSupplemental
EXCEPT
SELECT SKU
FROM Inventory

Are you sure this is actually what you are running? Is there any sql above that?

Defiance

No i swear it just says incorrect syntax near except, and im like but why, and then i look on interwebs and it says this is how we do it – Conversion

Lol, i believe you that it throws the error. Is there any SQL above the section that uses the EXCEPT. Your syntax is valid, so something else is up. – Defiance

SELECT COUNT (*) FROM(SELECT SKU FROM PartProductSupplemental EXCEPT SELECT SKU FROM Inventory ) – Conversion

I tested on 2005 with a db set to compatibility 80 -- worked fine for me, no errors – Longdistance

I think the real problem has to do with his SELECT COUNT FROM (SubQusery) but oh well – Defiance