exists sql statement

[proggy]

any help is appreciated..
I have a table and a query collects certain records from that..I have to get certain records from these records by another query..
some body told me.. exists is the best way..
can anyone explain this statement ??
select * from table
where id = 'l'
exits (select 'x' from table
where id= 'm'
exists (select...so on....)))
in the select statemtn after exists i said 'x' are they displayed or not is my question...
thanks,

 

[scottsanpedro]

I had the same thought when i first saw a SubQuery. Basically a statement such as follows...
SELECT * FROM tblOrderData
WHERE EXISTS(SELECT NULL FROM GMCPUBS.dbo. etc etc)

You are checking to see if something exists or not so therefore the output has no meaning, only the fact that it exists. So in the above NULL is used. You can replace that with 1 or 9 or x if you want.

I posted the question a couple of months ago on

http://www.sqlservercentral.com

Here's the replies...

It will generate the same number of NULL rows as the number of rows in the table. Since this will return a count of records higher than 0 it will me the exists check and thus the where. However if your Select statement has a where clause that would generate no rows, then no NULL rows will generate and a 0 record count will occurr, thus does not exist.

Mr. Travis is correct in that it will return the number of rows, all having NULL to be checked by the EXISTS() function, matching the parameters of the query contained within the EXISTS(). If the EXISTS() clause gets any rows back, it evaluates to TRUE. So it doesn't matter if the query had SELECT null, SELECT 1, or SELECT <column name> there. EXISTS is just looking for the existence of at least one row.

I'm assuming that this is part of a correlated subquery, where part of a predicate (a statement that evaluates to True, False, or Unknown as is EXISTS() in this case) is dependent upon the SELECT clause. What happens is the outer query is built (the one with the SELECT CustomerID FROM Customers) and the subquery (within the EXISTS() clause) is executed for each row in the outer query in order to generate a final result set.

An example from the Northwind sample database:


SELECT DISTINCT CustomerID FROM CustomersWHERE EXISTS(SELECT NULL FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)

This will return all CustomerID for which I have an order. This query should return 89 records. However, if I was to look at the number of rows in the Customers table, I'd see there are 91.

Now, in this particular case I can do the same thing with a JOIN:


SELECT DISTINCT Customers.CustomerIDFROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID


Hope this explains it for you

Scott

 

[proggy]

hey scott..
thanks.. i gotcha..
i want a query which returns the records from within exists so. was havin few doubts ab't it..
thanks anyways..
bye