Doubt in Exists operator in sql 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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

Posted 13-Dec-09 18:54pm

nishuthegreat

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2009-12-13T18:56:00

You should avoid using this operator because the inner SQL is, by definition, run once for every outer row that is returned. It's expensive. I don't know what books you're reading, but the way you describe it, it would be utterly useless. It's being run once for every row returned, and it's only going to return customers who have an order.

DABBee · Answer 2 · 2009-12-13T19:31:00

You've got the correct result, this is an equivalent:

select distinct customers.customerid, companyname from customers inner join orders on orders.customerid=customers.customerid

You'll find the query executed without DISTINCT will return 830 rows

--- There are 91 customers.

So -

select customers.customerid, companyname from customers where customers.customerid NOT IN (select customerid from orders)

shows us there are two customers that have not placed an order. They don't EXIST in the order table.

Doubt in Exists operator in sql 2005

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0