0

I am very much a beginner and I completely get what NOT IN does, but don't really get EXISTS or NOT EXISTS. Even more, I don't understand what this does:

SELECT TOP 1 1 FROM tblSomeTable

What does this query actually do?

For reference, I have been working with something like this:

SELECT COUNT(E_ID) FROM tblEmployee e INNER JOIN tblManager m ON e.tbl_ID = m.tbl_ID WHERE NOT EXISTS(SELECT TOP 1 1 FROM tblEmployee e2 WHERE e2.E_ID = e.E_ID AND isFired = 'N' )

I suppose I haven't read/seen a layman's explanation yet that makes sense to me. Even after reading Diff between Top 1 1 and Select 1 in SQL Select Query I still don't get it

Improve this question
2
  • 1
    One suggestion, when using EXISTS NOT EXISTS, it's not necessary to use SELECT TOP 1 there. A simple SELECT * will use the clustered index and fast enough. One more thing, you could also check EXISTS (SELECT 1/0 FROM A) and you will see 1/0 is actually not executed. So, using TOP in EXISTS is really not a necessary. Commented May 29, 2014 at 4:16
  • @zhongxiao37: I think what you've written there is what this question is actually asking about. Consider converting your explanation into an answer (you might additionally want to expand on the "TOP unnecessary in EXISTS" bit, though). Commented May 29, 2014 at 16:57

3 Answers 3

Reset to default
1

The question that I think would actually need answering is whether EXISTS (SELECT TOP 1 1 FROM MyTable) is actually necessary.

Top 1 1 is telling the query to pick the constant "1" for any answer.

The Top 1 part is telling it to stop as soon as it finds a match and returns "1".

Wouldn't EXISTS (SELECT TOP 1 FROM MyTable) be sufficient?

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Your first query will get you only top most record (very first record) out of the total rows in result set. So, if your query returns 10 rows .. you will get the first row. Read more about TOP

SELECT TOP 1 FROM tblSomeTable

In your Second query the part under () is a subquery, in your case it's a correlated subquery which will be evaluated once for each row processed by the outer query.

NOT EXISTS will actually check for existence of the rows present in subquery 

WHERE NOT EXISTS ( SELECT TOP 1 1 FROM tblEmployee e2 WHERE e2.E_ID = e.E_ID AND isFired = 'N' )

Read more about Correlated subquery as well as Subqueries with EXISTS

Improve this answer

Comments

0

SELECT TOP 1 1 FROM <table> will return you the first row with the value as 1 always, which you have defined as constant.

So if you change this to SELECT TOP 1 2 FROM <table> it will return the value as 2 always.

Difference between IN and EXISTS operators in SQL

Please read this:

http://awesomesql.wordpress.com/2009/07/31/difference-between-in-and-exists-operators-in-sql/

Improve this answer

2 Comments

I don't feel the linked resource is very good. It shows how to use it, not what the difference it. It also states that EXISTS performs better, which is not true, that really depends.
if you have definite set (small set of ) of values then IN clause may have advantage over EXISTS. in all other cases EXISTS it produces better results over IN clause as it has to just evaluate to boolean or in other words find the occurance the first occurance

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.