Subject | Re: [ib-support] Re: Exists versus select first |
---|---|
Author | Ivan Prenosil |
Post date | 2002-03-21T17:03:53Z |
This is written in IB documentation (Embedded SQL guide):
"EXISTS tests that for a given value there is at least one qualifying row meeting the search condition
specified in a subquery. The SELECT clause in the subquery must use the * (asterisk) to select all columns."
Below is relevant part of older message from Diane Brown.
Ivan
http://www.volny.cz/iprenosil/interbase
==========
SQL92 says:
"If the <select list> "*" is simply contained in a <subquery> that
is immediately contained in an <exists predicate>, then the <select
list> is equivalent to a <value expression> that is an arbitrary
<literal>."
(Translated: in SQL92, "select ... from ... where exists (select * from
.....)" and "select ... from ... where exists (select '1' from ...)" are
equivalent.)
I think (but am not going to look) there are other rules so that, in
SQL92 at least, there is no difference between subquery using "select *"
or "select 1" or "select column" [oh, wait, there are problably some
differences in privileges required for the "select column" form ... oh,
and implications if column is null ... hmmm]
However, SQL92 just describes behaviour, it doesn't describe
implementation, so it could be that those forms are implemented
differently in some product(s).
In theory, no data has to be returned by subquery -- just 'yes' or 'no'
or 'unknown' to answer the "does it exist" question, so it shouldn't
matter, but it's worth checking specific products to see if it does
matter.
==========
"EXISTS tests that for a given value there is at least one qualifying row meeting the search condition
specified in a subquery. The SELECT clause in the subquery must use the * (asterisk) to select all columns."
Below is relevant part of older message from Diane Brown.
Ivan
http://www.volny.cz/iprenosil/interbase
==========
SQL92 says:
"If the <select list> "*" is simply contained in a <subquery> that
is immediately contained in an <exists predicate>, then the <select
list> is equivalent to a <value expression> that is an arbitrary
<literal>."
(Translated: in SQL92, "select ... from ... where exists (select * from
.....)" and "select ... from ... where exists (select '1' from ...)" are
equivalent.)
I think (but am not going to look) there are other rules so that, in
SQL92 at least, there is no difference between subquery using "select *"
or "select 1" or "select column" [oh, wait, there are problably some
differences in privileges required for the "select column" form ... oh,
and implications if column is null ... hmmm]
However, SQL92 just describes behaviour, it doesn't describe
implementation, so it could be that those forms are implemented
differently in some product(s).
In theory, no data has to be returned by subquery -- just 'yes' or 'no'
or 'unknown' to answer the "does it exist" question, so it shouldn't
matter, but it's worth checking specific products to see if it does
matter.
==========