Exists

Does the record count inside subquery in the Exists clause affect the
overall output ?
eg.
SELECT *
FROM table1
WHERE EXISTS ( SELECT * FROM table2 WHERE table2.field1 = blah)
Will the overall output the same when
1) the subquery returns 1 record
2) the subquery returns 100 recordsFirst of all there are not records in SQL. You missed the entire point
of RDBMS in your database class.
Then you slept thru your logic class. You have no subquery in what you
posted. All you have is an EXISTS() predicate. Predicates are
subqueries!!
You also have no COUNT() aggregate in your example, so how do we know
what it returns'
Would you like to try to ask a question that makes any kind of sense?|||From the Books Online:
<Excerpt href="http://links.10026.com/?link=acdata.chm::/ac_8_qd_11_9u5v.htm">
When a subquery is introduced with the keyword EXISTS, it functions as an
existence test. The WHERE clause of the outer query tests for the existence
of rows returned by the subquery. The subquery does not actually produce any
data; it returns a value of TRUE or FALSE.
</Excerpt>
Consequently, the output is the same as long as at least one row satisfies
the criteria. The EXISTS condition is false when no rows match.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:u8BZ97KDFHA.960@.TK2MSFTNGP09.phx.gbl...
> Does the record count inside subquery in the Exists clause affect the
> overall output ?
> eg.
> SELECT *
> FROM table1
> WHERE EXISTS ( SELECT * FROM table2 WHERE table2.field1 = blah)
>
> Will the overall output the same when
> 1) the subquery returns 1 record
> 2) the subquery returns 100 records
>
>|||
--CELKO-- wrote:

>First of all there are not records in SQL. You missed the entire point
>of RDBMS in your database class.
>
Or he had a teacher who made more than one point in the course.

>Then you slept thru your logic class. You have no subquery in what you
>posted. All you have is an EXISTS() predicate. Predicates are
>subqueries!!
>
Huh? What's the thing inside the exists predicate if it
is not a subquery? From the SQL-92 standard, which
I believe you helped write:
<exists predicate> ::= EXISTS <table subquery>
Then again, I'm not sure how the Celko logic goes here. In
my logic course, these three statements of yours can never
all be true.
1. Your example contains no subquery.
2. Your example contains an EXISTS() predicate.
3. Predicates are subqueries
(All database programmers are poodles, too?)

>You also have no COUNT() aggregate in your example, so how do we know
>what it returns'
>
He asked about the row count of the subquery, not COUNT().
For the record (pun intended), I believe SQL Server handles
an <exists predicate> as if it contained a proprietary and
possibly nondeterministic TOP 1. In other words, The entire
result set of the table subquery after EXISTS is *not*
evaluated just because it's there.

>Would you like to try to ask a question that makes any kind of sense?
>
Will you return the favor if he does?
Steve Kass
Drew University|||
> Huh? What's the thing inside the exists predicate if it
> is not a subquery? From the SQL-92 standard, which
> I believe you helped write:
> <exists predicate> ::= EXISTS <table subquery>
> Then again, I'm not sure how the Celko logic goes here. In
> my logic course, these three statements of yours can never
> all be true.
> 1. Your example contains no subquery.
> 2. Your example contains an EXISTS() predicate.
> 3. Predicates are subqueries
I was wondering about the same thing.

> (All database programmers are poodles, too?)
How did you find out?
Sincerely,
Chris O.|||Actually, I know the EXISTS return FALSE when now records in subquery and
TRUE when there is records returned.
The thing I in the dark is using this EXISTS predicate, how the subquery
relate to the outer query ?
SELECT *
FROM table1
WHERE EXISTS ( SELECT * FROM table2 WHERE table2.field1 = blah)
The subquery has no relationship with the outer table1.

> Consequently, the output is the same as long as at least one row satisfies
> the criteria. The EXISTS condition is false when no rows match.|||I see what you're trying to say. You need to relate the queries yourself in
the WHERE clause of the subquery, something like this:
SELECT *
FROM table1
WHERE EXISTS (SELECT * FROM table2 WHERE table2.field1 = table1.field1)
The correlation you're looking for is between a column in the table1 outer
query and a column in the table2 subquery. You introduce that via the WHERE
clause of the subquery. I can't imagine too many situations where you would
add an EXISTS predicate with a subquery that didn't relate to your outer
query, at least none that couldn't be better modeled using a simple JOIN in
the outer query.
Thanks,
Michael C#
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:eoE9neMDFHA.464@.TK2MSFTNGP15.phx.gbl...
> Actually, I know the EXISTS return FALSE when now records in subquery and
> TRUE when there is records returned.
> The thing I in the dark is using this EXISTS predicate, how the subquery
> relate to the outer query ?
> SELECT *
> FROM table1
> WHERE EXISTS ( SELECT * FROM table2 WHERE table2.field1 = blah)
> The subquery has no relationship with the outer table1.
>
>|||> The subquery has no relationship with the outer table1.
The EXISTS predicate is still evaluated regardless of whether or not the
subquery is correlated to the outer table. Whether or not this makes
logical sense depends on what you are trying to accomplish.
CREATE TABLE table1
(
blah int NOT NULL PRIMARY KEY
)
CREATE TABLE table2
(
field1 int NOT NULL PRIMARY KEY
)
INSERT INTO table1 values(1)
INSERT INTO table1 values(2)
INSERT INTO table2 values(1)
INSERT INTO table2 values(3)
SELECT *
FROM table1
WHERE EXISTS (SELECT * FROM table2 WHERE table2.field1 = blah)
SELECT *
FROM table1
WHERE EXISTS (SELECT * FROM table2)
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:eoE9neMDFHA.464@.TK2MSFTNGP15.phx.gbl...
> Actually, I know the EXISTS return FALSE when now records in subquery and
> TRUE when there is records returned.
> The thing I in the dark is using this EXISTS predicate, how the subquery
> relate to the outer query ?
> SELECT *
> FROM table1
> WHERE EXISTS ( SELECT * FROM table2 WHERE table2.field1 = blah)
> The subquery has no relationship with the outer table1.
>
>|||So now take the example from Dan and Michael:
SELECT *
FROM table1
WHERE EXISTS (SELECT * FROM table2 WHERE table2.field1 = table1.blah)
Is the outer table execute row by row:
For the first row, the value of the table1.blah in first row(ie. field value
is 1) will be tested in the subquery and returns that in EXISTS predicate.
If it is true, then return the first row.
Then the second row, table1.blah (ie. field value is 2) and tested in the
predicate ?

> CREATE TABLE table1
> (
> blah int NOT NULL PRIMARY KEY
> )
> CREATE TABLE table2
> (
> field1 int NOT NULL PRIMARY KEY
> )
> INSERT INTO table1 values(1)
> INSERT INTO table1 values(2)
> INSERT INTO table2 values(1)
> INSERT INTO table2 values(3)
> SELECT *
> FROM table1
> WHERE EXISTS (SELECT * FROM table2 WHERE table2.field1 = blah)
> SELECT *
> FROM table1
> WHERE EXISTS (SELECT * FROM table2)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:eoE9neMDFHA.464@.TK2MSFTNGP15.phx.gbl...
and
>|||>> Is the outer table execute row by row: For the first row, the value
of the table1.blah in first row(ie. field [sic] value is 1) will be
tested in the subquery and returns that in EXISTS predicate. If it is
true, then return the first row. Then the second row, table1.blah (ie.
field [sic] value is 2) and tested in the predicate ? <<
It could, but probably will not in most products with a good optimizer.
Other ways of doing this:
0) EXISTS (SELECT * FROM table2) should be executed by looking at the
row count in the stats or other schema information table. But even a
correlated subquery expression will get a row count check first to see
if the table is empty or not.
1) See if a PK-FK relationship exists between the tables, so we know
without looking at any rows that there are matches.
2) See if an index exists and search it instead of the base table.
3) Use hashing to build buckets in parallel and which values match
(Teradata)
4) As you get a match, build a working table in main storage on the fly
which you search before doing any joins (DB2, I think).
5) Use bit vectors on the common domain to look for matches (Nucleus).
And the next execution of the same query could change the method used,
based on statistics or a new optimizer or a new access method.
You are still thinking in terms of a sequential file system model of
execution instead of sets. Unless you are designing an optimizer, you
don't really care about the low-level details.