Doubts about Exists operator - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Doubts about Exists operator

Author

Topic

nishu81
Starting Member

11 Posts

Posted - 2009-12-14 : 00:39:14

I have read that the exists operator doesn't return the data instead it returns the value true and false.i.e if condition in exist query is true or false. Also exist doesn't check row by row data .As soon as it found that data exist it returns true and then
first query will run return data accordingly

however this was not the case .

when dealing with northwind database
when I was running following query it returned 91 rows

select customerid,companyname from customers

but when I used exist operator in following query it return only 89 rows

select customerid,companyname from customers cu where exists (select orderid from orders o where o.customerid=cu.customerid)

which means exist not only checks that particular set of data exist or not .it also returns all the cu.customer id which is equal to o.customerid

please Guide me regarding Exists operator
that what value it returns

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-12-14 : 03:12:29

I think you're understanding it wrong...you're asking for all the customers that has and order in the orders-table, and the corect 89 customers are returned. But some of these customers probably has more than one order but the customer is only returned once for the first order. What you have written is the same as

select distinct cu.customerid, cu.customername
from customers cu 
  inner join orders o 
    on o.customerid=cu.customerid

but the exists is more effective because it returns true on the first row on the join and not all of them.

- Lumbago
http://xkcd.com/327/

nishu81
Starting Member

11 Posts

Posted - 2009-12-14 : 06:24:26

I got your Point but my main doubt remains the same

See the inner query it says:

exists (select orderid from orders o where o.customerid=cu.customerid)

.i.e what query after exists operator returns (number of rows of for which o.customerid=cu.customerid or simply true/ false)????

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-12-14 : 06:51:26

I'm confused...what would you expect to get out of the full query if it was working to your current understanding?

- Lumbago
http://xkcd.com/327/

nishu81
Starting Member

11 Posts

Posted - 2009-12-14 : 07:15:41

As per my current understanding
case 1:if we have any result set (even if it is 1 row or 91 )for the subquery then it will simply Return "True " to outer query and the outer query will be simply get executed irrespective of inner query

select distinct cu.customerid, cu.customername from customers with 91 rows

case 2.if there no result set for the subquery it will return "false" to outer query and hence outerquery will not give any result

The above 2 cases were my understanding of exists operators but as per your Reply following thing is happening

1.subquery is retuning all the rows where o.customerid=cu.customerid ) to the outerquery and for these rows outer query is selecting customerid,companyname

Thanks For Being patient with me

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-14 : 07:43:23

Your understanding is wrong.

Instead imagine that for each row in your parent query


select customerid,companyname from customers cu

An exists call is made and, depending on whether that evaluates to TRUE or FALSE then the row is retained or discarded from the results

It doesn't matter in the slightest which columns you try and return in the EXISTS subquery


WHERE EXISTS (SELECT 1 FROM orders o where o.customerid=cu.customerid)

Will give you the same results as before.

NB -- I said for each row which sounds like a slow RBAR operation but I'm just saying that this is a way to understand the behaviour, not the method.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

nishu81
Starting Member

11 Posts

Posted - 2009-12-14 : 08:27:09

Now I understood fully .

Thanks a lot charlie For clearing my doubts.thanks a ton

and thanks to Lumbago also

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources