Incorrect syntax near the keyword 'EXISTS'.

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

You probably want to use NOT IN rather than NOT EXISTS

And you can only return ONE field in the subquery for the NOT IN statement.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 2

Using EXISTS and NOT EXISTS should be more like this (I Agree you will most likely want to use Not IN but for arguments sake)--->

USE NORTHWIND

GO

DELETE FROM [Order Details] WHERE ORDERID = 10249

SELECT DISTINCT *

FROM orders

WHERE NOT EXISTS

(SELECT *

FROM [Order Details]

WHERE [Order Details].OrderID = Orders.OrderID)

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 3

you've missinterpreted the way "exists" work.

In your case it should be :

SELECT distinct section_code, source, section_name

FROM special_sections SpS

WHERE NOT EXISTS (SELECT *

FROM SPECSECTIONS S

where S.sectionname <> ''

and S.section_code = SpS.section_code -- correlation predicate

)

order by section_code

This way you are using a correlated subquery.

Regarding the use of an in-list versus exists:

You'll have to test which one is faster for your case.

In many cases the exists will outperform because that statement is "ended" when the first occurence of data is met.

With the in-list, the list needs to be produced fully before being used.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this