How does EXISTS return things other than all rows or no rows?

Question

I am a beginning SQL programmer - I am getting most things, but not EXISTS.

It looks to me, and looks by the documentation, that an entire EXISTS statement returns a boolean value.

However, I see specific examples where it can be used and returns part of a table as opposed to all or none of it.

SELECT DISTINCT PNAME
FROM P    
WHERE EXISTS
(
    SELECT *
    FROM SP Join S ON SP.SNO = S.SNO
    WHERE SP.PNO = P.PNO
    AND S.STATUS > 25
)

This query returns to me one value, the one that meets the criteria (S.Status > 25).

However, with other queries, it seems to return the whole table I am selecting from if even one of the rows in the EXISTS subquery is true.

How does one control this?

Answer 1

Subqueries such as with EXISTS can either be correlated or non-correlated.

In your example you use a correlated subquery, which is usually the case with EXISTS. You look up records in SP for a given P.PNO, i.e. you do the lookup for each P record.

Without SP.PNO = P.PNO you would have a non-correlated subquery. I.e. the subquery no longer depends on the P record. It would return the same result for any P record (either a Status > 25 exists at all or not). Most often when this happens this is done by mistake (one forgot to relate the subquery to the record in question), but sometimes it is desired so.

Answer 2

You have actually created a Correlated subquery. Exists predicate accepts a subquery as input and returns TRUE if the subquery returns any rows and FALSE otherwise.

The outer query against table P doesn't have any filters, so all the rows from this table will be considered for which the EXISTS predicate returns TRUE.

SELECT DISTINCT PNAME -- Outer Query
FROM P

Now, the EXISTS predicate returns TRUE if the current row in table P has related rows in SP Join S ON SP.SNO = S.SNO where S.STATUS > 25

SELECT *
FROM SP Join S ON SP.SNO = S.SNO
WHERE SP.PNO = P.PNO -- Inner query
AND S.STATUS > 25

One of the benefits of using the EXISTS predicate is that it allows you to intuitively phrase English like queries. For example, this query can be read just as you would say it in ordinary English: select all unique PNAME from table P where at least one row exists in which PNO equals PNO in table SP and Status in table S > 25, provided table SP and S are joined based on SNO.

Answer 3

Which SQL language are you using?

Either EXISTS return allways true or false or it allways returning rows, but in WHERE EXISTS... it will check returned rows > 0 (=>true).

Oracle, MySQL, PostreSQL:

The EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. (http://www.techonthenet.com)

Answer 4

your condition in where clause for main query

SELECT DISTINCT PNAME FROM P

is dependent to Exist , if your subquery returns any rows , then exists returns true ,otherwise it returns false and the main query where clause return all of records in p if Exists return true and nothing if it returns false