Hi all,
the EXISTS() function (that I like and use a lot) seems to be missing
from the Language Reference Update (and from the Release Notes). I
cannot find an information when this function was introduced into
InterBase or Firebird.
Some suggestions for a description text:
EXISTS()
Available in: DSQL, PSQL, ESQL(?)
Added in: ((?))
Changed in: ((?))
Description: Checks whether a SELECT statement will deliver at least
one result row. Fast because only this very first row gets fetched.
Result type: BOOLEAN (sort of)
Syntax:
EXISTS (<select statement>)
This function will run the select statement until this will deliver
the first result row. When there is at least one row, the EXISTS()
function will return a "True" value and then immediately stop
executing the SELECT. When the select statement does not return any
rows (empty result set), the EXISTS() function will return a "False"
value.
This can be quite fast. Other means of checking whether a SELECT will
deliver at least one row, like:
select count (*) from stock_locations where art_id = :MY_ART_ID
or
select first 1 from stock_locations where art_id = :MY_ART_ID
will fetch more than one row or even the whole dataset.
As there is no "Boolean" datatype in Firebird at the moment (this will
probably come in Firebird 3), EXISTS() can only be used in places
where a "boolean" expression is expected like in the WHERE clause of a
SELECT statement or in the WHEN clause of a CASE statement.
Examples:
-- Which articles are on stock?
select art_id, art_no, art_name
from articles
where
exists (select 1 from stock_locations where art_id = articles.art_id)
-- List of articles with a column that shows whether article is in stock
select
art_no, art_name,
case when exists (select 1 from stock_locations where art_id =
articles.art_id)
then 'In Stock' else 'Out of Stock' end as stock_status
from articles
order by art_no
Notes
It is not important to select a certain column in the SELECT statement
because EXISTS() only cares for rows returned, not columns. So you can
also use SELECT 1 (or another constant) instead of SELECT * or any
other column expression.
Best Regards
Stefan
------------------------------------------------------------------------------
Create and publish websites with WebMatrix
Use the most popular FREE web apps or write code yourself;
WebMatrix provides all the features you need to develop and
publish your website. http://p.sf.net/sfu/ms-webmatrix-sf
_______________________________________________
Firebird-docs mailing list
Firebird-docs@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-docs
