SQL Except

Syntax for SQL2K

[aspdaddy]

Does anyone know the syntax for this function, it is supported but I cant find an example.

I want to do a set difference to select the people who enquire but diddnt buy - something like

(SELECT Name FROM Enquiries)
EXCEPT
(SELECT Name FROM Sales)

This gives error - Incorrect syntax

Any ideas?

Thanks.
ASPDaddy

[asmith_2048]

I wasn't aware you could embed subqueries in the Except function ... and I've never been able to get it to work either. I _think_ it's an Analysis Services-specific thingy.

This should do what you want:

SELECT Name
FROM Enquiries WITH (NOLOCK)
WHERE Name NOT IN (SELECT Name FROM Sales WITH (NOLOCK))

(the "WITH (NOLOCK)" stuff is just a common SQL Server optimisation for read-only queries and has no direct relevance to your question)

[aspdaddy]

Nice one.

I was set on using except and totally forgot about WHERE NOT IN, does exactly the same.
Thanks.

[markus007]

the "where not in" will give you MAJOR problems if the table gets big as your doing a table scan, the following will do a index seek instead. If you have lots of records in the table, the with nolock will allow you to read uncommited data.

SELECT Name
FROM Enquiries WITH (NOLOCK)
where not exists (select 1 from sales with (nolock) where sales.name =enquiries.name)

[aspdaddy]

Thanks for the tip.

[asmith_2048]

Yeah markus007 has the more efficient query.