EXISTS operator in SQL ?

[HunainHafeez]

i almost spent 3 hours on EXISTS operation, used in SQL especially with subqueries, i got its purpose :) that's ok but what i don't get is that WHY TO USE IT ? e.g. couldn't understand this example:

TABLE STORES
storeName----Sales-------Txn_date

TABLE GEOGRAPHY
regionName----storeName

SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE Region_Name = 'West');

[deceptikon]

How about a different example? Let's say you have customers that have the option to enrole in a feedback survey. You want to get all of those customers to send survey emails. You could use exists to retrieve that list and exclude customers that didn't enrole.

select c.ID, c.Name, c.Email
from Customers c
where exists (select * 
              from SurveyEnrolement se
              where se.CustomerID = c.ID);

Not that this is either the only way or the best way, I just think it may be a more clear example of exists.

[HunainHafeez]

not clear, since your subquery doesn't return anything but TRUE so how on the basis of true an outer query would know that what to pick what not ?

[deceptikon]

exists looks for the first match in the subquery. If it returns true for any of the matches in the outer query, the where clause succeeds and that record will be selected.

[HunainHafeez]

let's put it in simple words, the subquery in this code will return on a boolean value TRUE, ok ?

where exists (select * 
              from SurveyEnrolement se
              where se.CustomerID = c.ID);

returns true

so if it becomes true then it's like a GO signal for Outer query and that get execute ? right ?

[HunainHafeez]

but what i concluded is that EXISTS can be handy in situations like

IF EXISTS (SELECT ...... from ....... where)
Begin
 DO SOMETHING BECAUSE IT's OBVIOUS THAT TABLE HAS ROWS
END

correct me if i'm wrong ?

[JorgeM]

since your subquery doesn't return anything but TRUE

correct...the subquery is not there to produce a result with data. it simply returns a value of TRUE or FALSE. The purpose of the query (what you are interested in when using this approach) is a test to see if there is a TRUE, meaning record exists.

So in deceptikon's example...it will return all records from the customers table where there is at least one record in the SurveyEnrolement table when the ID and CustomerID match.

[deceptikon]

let's put it in simple words, the subquery in this code will return on a boolean value TRUE, ok ?

It will select a matching record. exists evaluates to a boolean based on the presence of any matches. But the rest of your explanation is reasonable, though not strictly correct (which is okay since you're putting it into simple words). I think the issue here may be less about exists in particular and more about relational logic in general.

[HunainHafeez]

great replies (Y)

so is it correct to say that IN can not be necessarily used as a replacement from Exists, or reverse ? because with IN you can get result set and can match them in WHERE clause but not true for EXISTS since it returns only TRUE

OK ?

[HunainHafeez]

see i did some queries myself to check what it actually do,

// using IN

select customers.cus_id, customers.cusName from customers 
where customers.cus_id IN (Select cus_id from products where prodCategory= 'Consumer electronics')

// using Exists
Select customers.cus_id, customers.cusName from customers
where exists (select cus_id from products where prodCategory='Consumer electronics' AND customers.cus_id=products.cus_id)

both gives same result but when i remove this part from 2nd query's subquery

......AND customers.cus_id=products.cus_id

then it doesn't give correct results, returns all records form Customers table. Why ? i think it will clear my concepts but little help is needed , HELP !

[deceptikon]

so is it correct to say that IN can not be necessarily used as a replacement from Exists, or reverse ?

in can be used as a replacement for exists in terms of functionality (may ways to do the same thing, of course). But if the result set of the subquery is large, exists will be quite a bit faster since it's essentially taking a count that stops when a single match is found. in will typically be faster when the result set is small.

returns all records form Customers table. Why ?

Because the subquery isn't keyed on the specific customer. You can think of these simple queries as an imperative foreach (not a perfect equivalence, but helps to visualize):

a = empty recordset

foreach customer in customers
    b = empty resultset

    foreach product in products
        if product["prodCategory"] = 'Consumer electronics'
            b = b + product

    if b <> empty
        a = a + customer

Notice that the inner loop doesn't look at the current customer record at all to determine its result set, so unless the table is empty, b will always contain at least one record, and the exists test will always evaluate to true. Therefore you'll always select every customer in the customers table.

Now consider the same equivalence using the customer ID:

a = empty recordset

foreach customer in customers
    b = empty resultset

    foreach product in products
        if product["prodCategory"] = 'Consumer electronics' AND product["cus_id"] = customer["cus_id"]
            b = b + product

    if b <> empty
        a = a + customer

Now the inner loop also considers the current customer's ID, so it will only match products that have that ID as a column. The b result set is now limited to products that are tied to the customer, and the exists works as intended.

in works in a different manner:

a = empty recordset

foreach customer in customers
    b = empty resultset

    foreach product in products
        if product["prodCategory"] = 'Consumer electronics'
            b = b + product

    foreach product in b
        if product["cus_id"] = customer["cus_id"]
            a = a + customer
            break

This works correctly, but you'll notice that all products in the "Consumer electronics" category are selected. Then those products are enumerated and filtered by the customer ID to select a matching customer. This is unnecessary work, and can contribute to performance concerns.