CODEWITHSUNDEEP
sqlsql-serverexistsnot-exists
user3685738
user3685738

Reputation: 35

NOT IN vs NOT EXISTS and select 1 1?

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

Upvotes: 0

Views: 4429

Answers (3)

codebased
codebased

Reputation: 7073

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/

Upvotes: 0

RPh_Coder
RPh_Coder

Reputation: 883

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?

Upvotes: 1

Rahul
Rahul

Reputation: 77936

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

Upvotes: 0

Related Questions