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

Question

I am starting a SQL programmer - I get most things, but not EXISTS.

It seems to me, and it looks according to the documentation, that the entire EXISTS statement returns a boolean value.

However, I see specific examples where it can be used and returns part of the table, unlike all or none.

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 me a single value that matches the criteria (S.Status> 25).

However, with other queries, it seems to return the entire table that I select if one of the rows in the EXISTS subquery is true.

How to control it?

Answer 1

Subqueries, such as EXISTS, may be correlated or uncorrelated.

In your example, you are using the correlated subquery, which is usually the case with EXISTS. You are looking at SP entries for a given P.PNO, i.e. Browse Each Record P.

Without SP.PNO = P.PNO , you will have an uncorrelated subquery. That is, the subquery no longer depends on the record P. It returns the same result for any record P (either stats> 25 exist or not). Most often, when this happens, this is done by mistake (one forgot to associate the subquery with the corresponding record), but sometimes this is desirable.

Answer 2

In fact, you created the Correlated subquery . The Exists predicate accepts a subquery as input and returns TRUE if the subquery returns any rows and FALSE otherwise.

An external query on table P has no filters, so all rows from this table for which the EXISTS predicate returns TRUE will be considered.

 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 formulate English queries as queries. For example, this query can be read in the same way as you would say it in ordinary English: select all unique PNAME from table P , where there is at least one row in which PNO is equal to PNO in table SP and Status in table S > 25 . provided that the table SP and S connected based on SNO .

Answer 3

What SQL language do you use?

Any of EXISTS returns allways true or false, or always returns rows, but in WHERE EXISTS ... it checks the returned rows> 0 (=> true).

Oracle, MySQL, PostreSQL:

The EXISTS clause is used in conjunction with a subquery and is considered "satisfied" if the subquery returns at least one row. ( http://www.techonthenet.com )

Answer 4

your condition is where is the offer for the main request

SELECT DISTINCT PNAME FROM P

depends on Exist, if your subquery returns any rows, then exists returns true, otherwise it returns false and the main query, where the sentence returns all records in p if Exists returns true and nothing if returns false