The Firebird 5.0 Language Reference | SELECT | Joins

CommentsProcedural SQL (PSQL) Statements

Qualified joins

Qualified joins specify conditions for the combining of rows.This happens either explicitly in an ON clause or implicitly in a USING clause.

Syntax

<qualified-join> ::=
  <table-reference> [<join-type>] JOIN <table-primary>
  { ON <search-condition>
  | USING (<column-name-list>) }

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

Explicit-condition joins

Most qualified joins have an ON clause, with an explicit condition that can be any valid Boolean expression, but usually involves a comparison between the two sources involved.

Often, the condition is an equality test (or a number of ANDed equality tests) using the “=” operator.Joins like these are called equi-joins.(The examples in the section on inner and outer joins were all equi-joins.)

Examples of joins with an explicit condition:

/* Select all Detroit customers who made a purchase
   in 2013, along with the purchase details: */
select * from customers c
  join sales s on s.cust_id = c.id
  where c.city = 'Detroit' and s.year = 2013;

/* Same as above, but include non-buying customers: */
select * from customers c
  left join sales s on s.cust_id = c.id
  where c.city = 'Detroit' and s.year = 2013;

/* For each man, select the women who are taller than he.
   Men for whom no such woman exists are not included. */
select m.fullname as man, f.fullname as woman
  from males m
  join females f on f.height > m.height;

/* Select all pupils with their class and mentor.
   Pupils without a mentor are also included.
   Pupils without a class are not included. */
select p.firstname, p.middlename, p.lastname,
       c.name, m.name
  from pupils p
  join classes c on c.id = p.class
  left join mentors m on m.id = p.mentor;

Named columns joins

Equi-joins often compare columns that have the same name in both tables.If this is the case, we can also use the second type of qualified join: the named columns join.

Note	Named columns joins are not supported in Dialect 1 databases.

Named columns joins have a USING clause which states only the column names.So instead of this:

select * from flotsam f
  join jetsam j
  on f.sea = j.sea
  and f.ship = j.ship;

we can also write:

select * from flotsam
  join jetsam using (sea, ship)

which is considerably shorter.The result set is a little different though — at least when using “SELECT *”:

The explicit-condition join — with the ON clause — will contain each of the columns SEA and SHIP twice: once from table FLOTSAM, and once from table JETSAM.Obviously, they will have the same values.
The named columns join — with the USING clause — will contain these columns only once.

If you want all the columns in the result set of the named columns join, set up your query like this:

select f.*, j.*
  from flotsam f
  join jetsam j using (sea, ship);

This will give you the same result set as the explicit-condition join.

For an OUTER named columns join, there’s an additional twist when using “SELECT *” or an unqualified column name from the USING list:

If a row from one source set doesn’t have a match in the other but must still be included because of the LEFT, RIGHT or FULL directive, the merged column in the joined set gets the non-NULL value.That is fair enough, but now you can’t tell whether this value came from the left set, the right set, or both.This can be especially deceiving when the value came from the right hand set, because “*” always shows combined columns in the left hand part — even in the case of a RIGHT join.

Whether this is a problem or not depends on the situation.If it is, use the “a.*, b.*” approach shown above, with a and b the names or aliases of the two sources.Or better yet, avoid “*” altogether in your serious queries and qualify all column names in joined sets.This has the additional benefit that it forces you to think about which data you want to retrieve and where from.

It is your responsibility to make sure the column names in the USING list are of compatible types between the two sources.If the types are compatible but not equal, the engine converts them to the type with the broadest range of values before comparing the values.This will also be the data type of the merged column that shows up in the result set if “SELECT *” or the unqualified column name is used.Qualified columns on the other hand will always retain their original data type.

Tip

If, when joining by named columns, you are using a join column in the WHERE clause, always use the qualified column name, otherwise an index on this column will not be used.

SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE x = 0;

-- PLAN JOIN (A NATURAL , B INDEX (RDB$2))

However:

SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE a.x = 0;
-- PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE b.x = 0;
-- PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

The fact is, the unspecified column in this case is implicitly replaced by `COALESCE(a.x, b.x).This trick is used to disambiguate column names, but it also interferes with the use of the index.

Natural joins

Taking the idea of the named columns join a step further, a natural join performs an automatic equi-join on all the columns that have the same name in the left and right table.The data types of these columns must be compatible.

Note	Natural joins are not supported in Dialect 1 databases.

Syntax

<natural-join> ::=
  <table-reference> NATURAL [<join-type>] JOIN <table-primary>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

Given these two tables:

create table TA (
  a bigint,
  s varchar(12),
  ins_date date
);

create table TB (
  a bigint,
  descr varchar(12),
  x float,
  ins_date date
);

A natural join on TA and TB would involve the columns a and ins_date, and the following two statements would have the same effect:

select * from TA
  natural join TB;

select * from TA
  join TB using (a, ins_date);

Like all joins, natural joins are inner joins by default, but you can turn them into outer joins by specifying LEFT, RIGHT or FULL before the JOIN keyword.

Caution

If there are no columns with the same name in the two source relations, a CROSS JOIN is performed.We’ll get to this type of join next.

Cross joins

A cross join produces the full set product — or Cartesian product — of the two data sources.This means that it successfully matches every row in the left source to every row in the right source.

Syntax

FROM <table-reference> [, <table-reference> ...]

<cross-join>
  <table-reference> CROSS JOIN <table-primary>

Use of the comma syntax is discouraged, and we recommend using the explicit join syntax.

Cross-joining two sets is equivalent to joining them on a tautology (a condition that is always true).The following two statements have the same effect:

select * from TA
  cross join TB;

select * from TA
  join TB on TRUE;

Cross joins are inner joins, because they only include matching records –- it just so happens that every record matches!An outer cross join, if it existed, wouldn’t add anything to the result, because what outer joins add are non-matching records, and these don’t exist in cross joins.

Cross joins are seldom useful, except if you want to list all the possible combinations of two or more variables.Suppose you are selling a product that comes in different sizes, different colors and different materials.If these variables are each listed in a table of their own, this query would return all the combinations:

select m.name, s.size, c.name
  from materials m
  cross join sizes s
  cross join colors c;

Implicit Joins

In the SQL:89 standard, the tables involved in a join were specified as a comma-delimited list in the FROM clause (in other words, a cross join).The join conditions were then specified in the WHERE clause among other search terms.This type of join is called an implicit join.

An example of an implicit join:

/*
 * A sample of all Detroit customers who
 * made a purchase.
 */
SELECT *
FROM customers c, sales s
WHERE s.cust_id = c.id AND c.city = 'Detroit'

Mixing Explicit and Implicit Joins

Mixing explicit and implicit joins is not recommend, but is allowed.However, some types of mixing are not supported by Firebird.

For example, the following query will raise the error “Column does not belong to referenced table”

SELECT *
FROM TA, TB
JOIN TC ON TA.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

That is because the explicit join cannot see the TA table.However, the next query will complete without error, since the restriction is not violated.

SELECT *
FROM TA, TB
JOIN TC ON TB.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

A Note on Equality

Important

This note about equality and inequality operators applies everywhere in Firebird’s SQL language, not only in JOIN conditions.

The “=” operator, which is explicitly used in many conditional joins and implicitly in named column joins and natural joins, only matches values to values.According to the SQL standard, NULL is not a value and hence two NULLs are neither equal nor unequal to one another.If you need NULLs to match each other in a join, use the IS NOT DISTINCT FROM operator.This operator returns true if the operands have the same value or if they are both NULL.

select *
  from A join B
  on A.id is not distinct from B.code;

Likewise, when you want to join on inequality, use IS DISTINCT FROM, not “<>”, if you want NULL to be considered different from any value and two NULLs considered equal:

select *
  from A join B
  on A.id is distinct from B.code;

Ambiguous field names in joins

Firebird rejects unqualified field names in a query if these field names exist in more than one dataset involved in a join.This is even true for inner equi-joins where the field name figures in the ON clause like this:

select a, b, c
  from TA
  join TB on TA.a = TB.a;

There is one exception to this rule: with named columns joins and natural joins, the unqualified field name of a column taking part in the matching process may be used legally and refers to the merged column of the same name.For named columns joins, these are the columns listed in the USING clause.For natural joins, they are the columns that have the same name in both relations.But please notice again that, especially in outer joins, plain colname isn’t always the same as left.colname or right.colname.Types may differ, and one of the qualified columns may be NULL while the other isn’t.In that case, the value in the merged, unqualified column may mask the fact that one of the source values is absent.

Joins with `LATERAL` Derived Tables

A derived table defined with the LATERAL keyword is called a lateral derived table.If a derived table is defined as lateral, then it is allowed to refer to other tables in the same FROM clause, but only those declared before it in the FROM clause.

Lateral Derived Table Examples

/* select customers with their last order date and number */
select c.name, ox.order_date as last_order, ox.number
from customer c
  left join LATERAL (
    select first 1 o.order_date, o.number
    from orders o
    where o.id_customer = c.id
    order by o.ORDER_DATE desc
  ) as ox on true
--
select dt.population, dt.city_name, c.country_name
from (select distinct country_name from cities) AS c
  cross join LATERAL (
    select first 1 city_name, population
    from cities
    where cities.country_name = c.country_name
    order by population desc
  ) AS dt;
--
select salespeople.name,
       max_sale.amount,
       customer_of_max_sale.customer_name
from salespeople,
  LATERAL ( select max(amount) as amount
            from all_sales
            where all_sales.salesperson_id = salespeople.id
  ) as max_sale,
  LATERAL ( select customer_name
            from all_sales
            where all_sales.salesperson_id = salespeople.id
            and all_sales.amount = max_sale.amount
  ) as customer_of_max_sale;

The `WHERE` clause

The WHERE clause serves to limit the rows returned to the ones that the caller is interested in.The condition following the keyword WHERE can be as simple as a check like “AMOUNT = 3” or it can be a multilayered, convoluted expression containing subselects, predicates, function calls, mathematical and logical operators, context variables and more.

The condition in the WHERE clause is often called the search condition, the search expression or simply the search.

In DSQL and ESQL, the search condition may contain parameters.This is useful if a query has to be repeated a number of times with different input values.In the SQL string as it is passed to the server, question marks are used as placeholders for the parameters.These question marks are called positional parameters because they can only be told apart by their position in the string.Connectivity libraries often support named parameters of the form :id, :amount, :a etc.These are more user-friendly;the library takes care of translating the named parameters to positional parameters before passing the statement to the server.

The search condition may also contain local (PSQL) or host (ESQL) variable names, preceded by a colon.

Syntax

SELECT ...
  FROM ...
  [...]
  WHERE <search-condition>
  [...]

Table 1. Argument of `WHERE`
Parameter	Description
search-condition	A Boolean expression returning `TRUE`, `FALSE` or `UNKNOWN` (`NULL`)

Only those rows for which the search condition evaluates to TRUE are included in the result set.Be careful with possible NULL outcomes: if you negate a NULL expression with NOT, the result will still be NULL and the row will not pass.This is demonstrated in one of the examples below.

Examples

select genus, species from mammals
  where family = 'Felidae'
  order by genus;

select * from persons
  where birthyear in (1880, 1881)
     or birthyear between 1891 and 1898;

select name, street, borough, phone
  from schools s
  where exists (select * from pupils p where p.school = s.id)
  order by borough, street;

select * from employees
  where salary >= 10000 and position <> 'Manager';

select name from wrestlers
  where region = 'Europe'
    and weight > all (select weight from shot_putters
                      where region = 'Africa');

select id, name from players
  where team_id = (select id from teams where name = 'Buffaloes');

select sum (population) from towns
  where name like '%dam'
  and province containing 'land';

select password from usertable
  where username = current_user;

The following example shows what can happen if the search condition evaluates to NULL.

Suppose you have a table listing children’s names and the number of marbles they possess.At a certain moment, the table contains this data:

CHILD	MARBLES
Anita	23
Bob E.	12
Chris	<null>
Deirdre	1
Eve	17
Fritz	0
Gerry	21
Hadassah	<null>
Isaac	6

CHILD

MARBLES

Anita

Bob E.

Chris

<null>

Deirdre

Eve

Fritz

Gerry

Hadassah

<null>

Isaac

First, please notice the difference between NULL and 0: Fritz is known to have no marbles at all, Chris’s and Hadassah’s marble counts are unknown.

Now, if you issue this SQL statement:

select list(child) from marbletable where marbles > 10;

you will get the names Anita, Bob E., Eve and Gerry.These children all have more than 10 marbles.

If you negate the expression:

select list(child) from marbletable where not marbles > 10

it’s the turn of Deirdre, Fritz and Isaac to fill the list.Chris and Hadassah are not included, because they aren’t known to have ten or fewer marbles.Should you change that last query to:

select list(child) from marbletable where marbles <= 10;

the result will still be the same, because the expression NULL <= 10 yields UNKNOWN.This is not the same as TRUE, so Chris and Hadassah are not listed.If you want them listed with the “poor” children, change the query to:

select list(child) from marbletable
where marbles <= 10 or marbles is null;

Now the search condition becomes true for Chris and Hadassah, because “marbles is null” obviously returns TRUE in their case.In fact, the search condition cannot be NULL for anybody now.

Lastly, two examples of SELECT queries with parameters in the search.It depends on the application how you should define query parameters and even if it is possible at all.Notice that queries like these cannot be executed immediately: they have to be prepared first.Once a parameterized query has been prepared, the user (or calling code) can supply values for the parameters and have it executed many times, entering new values before every call.How the values are entered and the execution started is up to the application.In a GUI environment, the user typically types the parameter values in one or more text boxes and then clicks an “Execute”, “Run” or “Refresh” button.

select name, address, phone frome stores
  where city = ? and class = ?;

select * from pants
  where model = :model and size = :size and color = :col;

The last query cannot be passed directly to the engine;the application must convert it to the other format first, mapping named parameters to positional parameters.

The `GROUP BY` clause

GROUP BY merges output rows that have the same combination of values in its item list into a single row.Aggregate functions in the select list are applied to each group individually instead of to the dataset as a whole.

If the select list only contains aggregate columns or, more generally, columns whose values don’t depend on individual rows in the underlying set, GROUP BY is optional.When omitted, the final result set consists of a single row (provided that at least one aggregated column is present).

If the select list contains both aggregate columns and columns whose values may vary per row, the GROUP BY clause becomes mandatory.

Syntax

SELECT ... FROM ...
  GROUP BY <grouping-item> [, <grouping-item> ...]
  [HAVING <grouped-row-condition>]
  ...

<grouping-item> ::=
    <non-aggr-select-item>
  | <non-aggr-expression>

<non-aggr-select-item> ::=
    column-copy
  | column-alias
  | column-position

Table 1. Arguments for the `GROUP BY` Clause
Argument	Description
<grouping-item>	Expression to group on;in the rest of this chapter, we use <value-expression> in `GROUP BY` syntax
non-aggr-expression	Any non-aggregating expression that is not included in the `SELECT` list, i.e. unselected columns from the source set or expressions that do not depend on the data in the set at all
column-copy	A literal copy, from the `SELECT` list, of an expression that contains no aggregate function
column-alias	The alias, from the `SELECT` list, of an expression (column) that contains no aggregate function
column-position	The position number, in the `SELECT` list, of an expression (column) that contains no aggregate function

A general rule of thumb is that every non-aggregate item in the SELECT list must also be in the GROUP BY list.You can do this in three ways:

By copying the item verbatim from the select list, e.g. “class” or “'D:' || upper(doccode)”.
By specifying the column alias, if it exists.
By specifying the column position as an integer literal between 1 and the number of columns.Integer values resulting from expressions or parameter substitutions are simple constant values and not column position and will be used as such in the grouping.They will have no effect though, as their value is the same for each row.

Note

If you group by a column position, the expression at that position is copied internally from the select list.If it concerns a subquery, that subquery will be executed again in the grouping phase.That is to say, grouping by the column position, rather than duplicating the subquery expression in the grouping clause, saves keystrokes and bytes, but it is not a way of saving processing cycles!

In addition to the required items, the grouping list may also contain:

Columns from the source table that are not in the select list, or non-aggregate expressions based on such columns.Adding such columns may further subdivide the groups.However, since these columns are not in the select list, you can’t tell which aggregated row corresponds to which value in the column.So, in general, if you are interested in this information, you also include the column or expression in the select list — which brings you back to the rule: “every non-aggregate column in the select list must also be in the grouping list”.
Expressions that aren’t dependent on the data in the underlying set, e.g. constants, context variables, single-value non-correlated subselects etc.This is only mentioned for completeness, as adding such items is utterly pointless: they don’t affect the grouping at all.“Harmless but useless” items like these may also figure in the select list without being copied to the grouping list.

Examples

When the select list contains only aggregate columns, GROUP BY is not mandatory:

select count(*), avg(age) from students
  where sex = 'M';

This will return a single row listing the number of male students and their average age.Adding expressions that don’t depend on values in individual rows of table STUDENTS doesn’t change that:

select count(*), avg(age), current_date from students
  where sex = 'M';

The row will now have an extra column showing the current date, but other than that, nothing fundamental has changed.A GROUP BY clause is still not required.

However, in both the above examples it is allowed.This is perfectly valid:

select count(*), avg(age) from students
  where sex = 'M'
  group by class;

This will return a row for each class that has boys in it, listing the number of boys and their average age in that particular class.(If you also leave the current_date field in, this value will be repeated on every row, which is not very exciting.)

The above query has a major drawback though: it gives you information about the different classes, but it doesn’t tell you which row applies to which class.To get that extra bit of information, add the non-aggregate column CLASS to the select list:

select class, count(*), avg(age) from students
  where sex = 'M'
  group by class;

Now we have a useful query.Notice that the addition of column CLASS also makes the GROUP BY clause mandatory.We can’t drop that clause anymore, unless we also remove CLASS from the column list.

The output of our last query may look something like this:

CLASS	COUNT	AVG
2A	12	13.5
2B	9	13.9
3A	11	14.6
3B	12	14.4
…	…	…

The headings “COUNT” and “AVG” are not very informative.In a simple case like this, you might get away with that, but in general you should give aggregate columns a meaningful name by aliasing them:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class;

Adding more non-aggregate (or, row-dependent) columns requires adding them to the GROUP BY clause too.For instance, you might want to see the above information for girls as well;and you may also want to differentiate between boarding and day students:

select class,
       sex,
       boarding_type,
       count(*) as number,
       avg(age) as avg_age
  from students
  group by class, sex, boarding_type;

This may give you the following result:

CLASS	SEX	BOARDING_TYPE	NUMBER	AVG_AGE
2A	F	BOARDING	9	13.3
2A	F	DAY	6	13.5
2A	M	BOARDING	7	13.6
2A	M	DAY	5	13.4
2B	F	BOARDING	11	13.7
2B	F	DAY	5	13.7
2B	M	BOARDING	6	13.8
…	…	…	…	…

CLASS

SEX

BOARDING_TYPE

NUMBER

AVG_AGE

BOARDING

13.3

DAY

13.5

BOARDING

13.6

DAY

13.4

BOARDING

13.7

DAY

13.7

BOARDING

13.8

…

Each row in the result set corresponds to one particular combination of the columns CLASS, SEX and BOARDING_TYPE.The aggregate results — number and average age — are given for each of these groups individually.In a query like this, you don’t see a total for boys as a whole, or day students as a whole.That’s the tradeoff: the more non-aggregate columns you add, the more you can pinpoint specific groups, but the more you also lose sight of the general picture.Of course, you can still obtain the “coarser” aggregates through separate queries.

`HAVING`

Just as a WHERE clause limits the rows in a dataset to those that meet the search condition, so the HAVING sub-clause imposes restrictions on the aggregated rows in a grouped set.HAVING is optional, and can only be used in conjunction with GROUP BY.

The condition(s) in the HAVING clause can refer to:

Any aggregated column in the select list.This is the most widely used case.
Any aggregated expression that is not in the select list, but allowed in the context of the query.This is sometimes useful too.
Any column in the GROUP BY list.While legal, it is more efficient to filter on these non-aggregated data at an earlier stage: in the WHERE clause.
Any expression whose value doesn’t depend on the contents of the dataset (like a constant or a context variable).This is valid but not useful, because it will either suppress the entire set or leave it untouched, based on conditions that have nothing to do with the set itself.

A HAVING clause can not contain:

Non-aggregated column expressions that are not in the GROUP BY list.
Column positions.An integer in the HAVING clause is just an integer, not a column position.
Column aliases –- not even if they appear in the GROUP BY clause!

Examples

Building on our earlier examples, this could be used to skip small groups of students:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having count(*) >= 5;

To select only groups that have a minimum age spread:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having max(age) - min(age) > 1.2;

Notice that if you’re interested in this information, you’ll likely also include min(age) and max(age) — or the expression “max(age) - min(age)”.

To include only 3rd classes:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having class starting with '3';

Better would be to move this condition to the WHERE clause:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M' and class starting with '3'
  group by class;

The `WINDOW` Clause

The WINDOW clause defines one or more named windows that can be referenced by window functions in the current query specification.

Syntax

<query_spec> ::=
  SELECT
    [<limit_clause>]
    [<distinct_clause>]
    <select_list>
    <from_clause>
    [<where_clause>]
    [<group_clause>]
    [<having_clause>]
    [<named_windows_clause>]
    [<plan_clause>]

<named_windows_clause> ::=
  WINDOW <window_definition> [, <window_definition> ...]

<window definition> ::=
  new-window-name AS (<window-specification-details>)

<window-specification-details> ::=
  !! See Window (Analytical) Functions !!

In a query with multiple SELECT and WINDOW clauses (for example, with subqueries), the scope of the `new_window_name_ is confined to its query context.That means a window name from an inner context cannot be used in an outer context, nor vice versa.However, the same window name can be used independently in different contexts, though to avoid confusion it might be better to avoid this.

For more information, see [fblangref50-windowfuncs].

Example Using Named Windows

select
  id,
  department,
  salary,
  count(*) over w1,
  first_value(salary) over w2,
  last_value(salary) over w2
from employee
window w1 as (partition by department),
       w2 as (w1 order by salary)
order by department, salary;

The `PLAN` Clause

The PLAN clause enables the user to submit a data retrieval plan, thus overriding the plan that the optimizer would have generated automatically.

Syntax

PLAN <plan-expression>

<plan-expression> ::=
    (<plan-item> [, <plan-item> ...])
  | <sorted-item>
  | <joined-item>
  | <merged-item>
  | <hash-item>

<sorted-item> ::= SORT (<plan-item>)

<joined-item> ::=
  JOIN (<plan-item>, <plan-item> [, <plan-item> ...])

<merged-item> ::=
  [SORT] MERGE (<sorted-item>, <sorted-item> [, <sorted-item> ...])

<hash-item> ::=
  HASH (<plan-item>, <plan-item> [, <plan-item> ...])

<plan-item> ::= <basic-item> | <plan-expression>

<basic-item> ::=
  <relation> { NATURAL
             | INDEX (<indexlist>)
             | ORDER index [INDEX (<indexlist>)] }

<relation> ::= table | view [table]

<indexlist> ::= index [, index ...]

Table 1. Arguments for the `PLAN` Clause
Argument	Description
table	Table name or its alias
view	View name
index	Index name

Every time a user submits a query to the Firebird engine, the optimizer computes a data retrieval strategy.Most Firebird clients can make this retrieval plan visible to the user.In Firebird’s own isql utility, this is done with the command SET PLAN ON.If you are only interested in looking at query plans, SET PLANONLY ON will show the plan without executing the query.Use SET PLANONLY OFF to execute the query and show the plan.

Note

A more detailed plan can be obtained when you enable an advanced plan.In isql this can be done with SET EXPLAIN ON.The advanced plan displays more detailed information about the access methods used by the optimizer, however it cannot be included in the PLAN clause of a statement.The description of the advanced plan is beyond the scope of this Language Reference.

In most situations, you can trust that Firebird will select the optimal query plan for you.However, if you have complicated queries that seem to be underperforming, it may be worth your while to examine the plan and see if you can improve on it.

Simple Plans

The simplest plans consist of a relation name followed by a retrieval method.For example, for an unsorted single-table select without a WHERE clause:

select * from students
  plan (students natural);

Advanced plan:

Select Expression
  -> Table "STUDENTS" Full Scan

If there’s a WHERE or a HAVING clause, you can specify the index to be used for finding matches:

select * from students
  where class = '3C'
  plan (students index (ix_stud_class));

Advanced plan:

Select Expression
  -> Filter
    -> Table "STUDENTS" Access By ID
      -> Bitmap
        -> Index "IX_STUD_CLASS" Range Scan (full match)

The INDEX directive is also used for join conditions (to be discussed a little later).It can contain a list of indexes, separated by commas.

ORDER specifies the index for sorting the set if an ORDER BY or GROUP BY clause is present:

select * from students
  plan (students order pk_students)
  order by id;

Advanced plan:

Select Expression
  -> Table "STUDENTS" Access By ID
    -> Index "PK_STUDENTS" Full Scan

ORDER and INDEX can be combined:

select * from students
  where class >= '3'
  plan (students order pk_students index (ix_stud_class))
  order by id;

Advanced plan:

Select Expression
  -> Filter
    -> Table "STUDENTS" Access By ID
      -> Index "PK_STUDENTS" Full Scan
        -> Bitmap
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

It is perfectly OK if ORDER and INDEX specify the same index:

select * from students
  where class >= '3'
  plan (students order ix_stud_class index (ix_stud_class))
  order by class;

Advanced plan:

Select Expression
  -> Filter
    -> Table "STUDENTS" Access By ID
      -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)
        -> Bitmap
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

For sorting sets when there’s no usable index available (or if you want to suppress its use), leave out ORDER and prepend the plan expression with SORT:

select * from students
  plan sort (students natural)
  order by name;

Advanced plan:

Select Expression
  -> Sort (record length: 128, key length: 56)
    -> Table "STUDENTS" Full Scan

Or when an index is used for the search:

select * from students
  where class >= '3'
  plan sort (students index (ix_stud_class))
  order by name;

Advanced plan:

elect Expression
  -> Sort (record length: 136, key length: 56)
    -> Filter
      -> Table "STUDENTS" Access By ID
        -> Bitmap
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Notice that SORT, unlike ORDER, is outside the parentheses.This reflects the fact that the data rows are retrieved unordered and sorted afterward by the engine.

When selecting from a view, specify the view and the table involved.For instance, if you have a view FRESHMEN that selects the first-year students:

select * from freshmen
  plan (freshmen students natural);

Advanced plan:

Select Expression
  -> Table "STUDENTS" as "FRESHMEN" Full Scan

Or, for instance:

select * from freshmen
  where id > 10
  plan sort (freshmen students index (pk_students))
  order by name desc;

Advanced plan:

Select Expression
  -> Sort (record length: 144, key length: 24)
    -> Filter
      -> Table "STUDENTS" as "FRESHMEN" Access By ID
        -> Bitmap
          -> Index "PK_STUDENTS" Range Scan (lower bound: 1/1)

Important

If a table or view has been aliased, it is the alias, not the original name, that must be used in the PLAN clause.

Composite Plans

When a join is made, you can specify the index which is to be used for matching.You must also use the JOIN directive on the two streams in the plan:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan join (s natural, c index (pk_classes));

Advanced plan:

Select Expression
  -> Nested Loop Join (inner)
    -> Table "STUDENTS" as "S" Full Scan
    -> Filter
      -> Table "CLASSES" as "C" Access By ID
        -> Bitmap
          -> Index "PK_CLASSES" Unique Scan

The same join, sorted on an indexed column:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan join (s order pk_students, c index (pk_classes))
  order by s.id;

Advanced plan:

Select Expression
  -> Nested Loop Join (inner)
    -> Table "STUDENTS" as "S" Access By ID
      -> Index "PK_STUDENTS" Full Scan
    -> Filter
      -> Table "CLASSES" as "C" Access By ID
        -> Bitmap
          -> Index "PK_CLASSES" Unique Scan

And on a non-indexed column:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan sort (join (s natural, c index (pk_classes)))
  order by s.name;

Advanced plan:

Select Expression
  -> Sort (record length: 152, key length: 12)
    -> Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Full Scan
      -> Filter
        -> Table "CLASSES" as "C" Access By ID
          -> Bitmap
            -> Index "PK_CLASSES" Unique Scan

With a search condition added:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  where s.class <= '2'
  plan sort (join (s index (fk_student_class), c index (pk_classes)))
  order by s.name;

Advanced plan:

Select Expression
  -> Sort (record length: 152, key length: 12)
    -> Nested Loop Join (inner)
      -> Filter
        -> Table "STUDENTS" as "S" Access By ID
          -> Bitmap
            -> Index "FK_STUDENT_CLASS" Range Scan (lower bound: 1/1)
      -> Filter
        -> Table "CLASSES" as "C" Access By ID
          -> Bitmap
            -> Index "PK_CLASSES" Unique Scan

As a left outer join:

select s.id, s.name, s.class, c.mentor
  from classes c
  left join students s on c.name = s.class
  where s.class <= '2'
  plan sort (join (c natural, s index (fk_student_class)))
  order by s.name;

Advanced plan:

Select Expression
  -> Sort (record length: 192, key length: 56)
    -> Filter
      -> Nested Loop Join (outer)
        -> Table "CLASSES" as "C" Full Scan
        -> Filter
          -> Table "STUDENTS" as "S" Access By ID
            -> Bitmap
              -> Index "FK_STUDENT_CLASS" Range Scan (full match)

If there are no indices available to match the join condition (or if you don’t want to use it), then it is possible connect the streams using HASH or MERGE method.

To connect using the HASH method in the plan, the HASH directive is used instead of the JOIN directive.In this case, the smaller (secondary) stream is materialized completely into an internal buffer.While reading this secondary stream, a hash function is applied and a pair {hash, pointer to buffer} is written to a hash table.Then the primary stream is read and its hash key is tested against the hash table.

select *
  from students s
  join classes c on c.cookie = s.cookie
  plan hash (c natural, s natural)

Advanced plan:

Select Expression
  -> Filter
    -> Hash Join (inner)
      -> Table "STUDENTS" as "S" Full Scan
      -> Record Buffer (record length: 145)
        -> Table "CLASSES" as "C" Full Scan

For a MERGE join, the plan must first sort both streams on their join column(s) and then merge.This is achieved with the SORT directive (which we’ve already seen) and MERGE instead of JOIN:

select * from students s
  join classes c on c.cookie = s.cookie
  plan merge (sort (c natural), sort (s natural));

Adding an ORDER BY clause means the result of the merge must also be sorted:

select * from students s
  join classes c on c.cookie = s.cookie
  plan sort (merge (sort (c natural), sort (s natural)))
  order by c.name, s.id;

Finally, we add a search condition on two indexable columns of table STUDENTS:

select * from students s
  join classes c on c.cookie = s.cookie
  where s.id < 10 and s.class <= '2'
  plan sort (merge (sort (c natural),
                    sort (s index (pk_students, fk_student_class))))
  order by c.name, s.id;

As follows from the formal syntax definition, JOINs and MERGEs in the plan may combine more than two streams.Also, every plan expression may be used as a plan item in an encompassing plan.This means that plans of certain complicated queries may have various nesting levels.

Finally, instead of MERGE you may also write SORT MERGE.As this makes no difference and may create confusion with “real” SORT directives (the ones that do make a difference), it’s best to stick to plain MERGE.

In addition to the plan for the main query, you can specify a plan for each subquery.For example, the following query with multiple plans will work:

select *
from color
where exists (
  select *
  from hors
  where horse.code_color = color.code_color
  plan (horse index (fk_horse_color)))
plan (color natural)

Warning

Occasionally, the optimizer will accept a plan and then not follow it, even though it does not reject it as invalid.One such example was

MERGE (unsorted stream, unsorted stream)

It is advisable to treat such as plan as “deprecated”.

`UNION`

The UNION clause concatenates two or more datasets, thus increasing the number of rows but not the number of columns.Datasets taking part in a UNION must have the same number of columns, and columns at corresponding positions must be of the same type.

By default, a union suppresses duplicate rows.UNION ALL shows all rows, including any duplicates.The optional DISTINCT keyword makes the default behaviour explicit.

Syntax

<query-expression> ::=
  [<with-clause>] <query-expression-body> [<order-by-clause>]
    [{ <rows-clause>
     | [<result-offset-clause>] [<fetch-first-clause>] }]

<query-expression-body> ::=
    <query-term>
  | <query-expression-body> UNION [{ DISTINCT | ALL }] <query-term>

<query-term> ::= <query-primary>

<query-primary> ::=
    <query-specification>
  | (<query-expression-body> [<order-by-clause>]
     [<result-offset-clause>] [<fetch-first-clause>])

<query-specification> ::=
  SELECT <limit-clause> [{ ALL | DISTINCT }] <select-list>
    FROM <table-reference> [, <table-reference> ...]
    [WHERE <search-condition>]
    [GROUP BY <value-expression> [, <value-expression> ...]]
    [HAVING <search-condition>]
    [WINDOW <window-definition> [, <window-definition> ...]]
    [PLAN <plan-expression>]

Note	See also [fblangref50-dml-select-full-syntax] for the full syntax.

Unions take their column names from the first select query.If you want to alias union columns, do so in the column list of the topmost SELECT.Aliases in other participating selects are allowed and may even be useful, but will not propagate to the union level.

If a union has an ORDER BY clause, the only allowed sort items are integer literals indicating 1-based column positions, optionally followed by an ASC/DESC and/or a NULLS {FIRST | LAST} directive.This also implies that you cannot order a union by anything that isn’t a column in the union.(You can, however, wrap it in a derived table, which gives you back all the usual sort options.)

Unions are allowed in subqueries of any kind and can themselves contain subqueries.They can also contain joins, and can take part in a join when wrapped in a derived table.

Examples

This query presents information from different music collections in one dataset using unions:

select id, title, artist, length, 'CD' as medium
  from cds
union
select id, title, artist, length, 'LP'
  from records
union
select id, title, artist, length, 'MC'
  from cassettes
order by 3, 2  -- artist, title;

If id, title, artist and length are the only fields in the tables involved, the query can also be written as:

select c.*, 'CD' as medium
  from cds c
union
select r.*, 'LP'
  from records r
union
select c.*, 'MC'
  from cassettes c
order by 3, 2  -- artist, title;

Qualifying the “stars” is necessary here because they are not the only item in the column list.Notice how the “c” aliases in the first and third select do not conflict with each other: their scopes are not union-wide but apply only to their respective select queries.

The next query retrieves names and phone numbers from translators and proofreaders.Translators who also work as proofreaders will show up only once in the result set, provided their phone number is the same in both tables.The same result can be obtained without DISTINCT.With ALL, these people would appear twice.

select name, phone from translators
  union distinct
select name, telephone from proofreaders;

A UNION within a subquery:

select name, phone, hourly_rate from clowns
where hourly_rate < all
  (select hourly_rate from jugglers
     union
   select hourly_rate from acrobats)
order by hourly_rate;

Using parenthesized query expressions to show the employees with the highest and lowest salaries:

(
  select emp_no, salary, 'lowest' as type
  from employee
  order by salary asc
  fetch first row only
)
union all
(
  select emp_no, salary, 'highest' as type
  from employee
  order by salary desc
  fetch first row only
);

`INSERT`

Inserts rows of data into a table or updatable view

Syntax

INSERT INTO target
  { DEFAULT VALUES
  | [(<column_list>)] [<override_opt>] <value-source> }
  [RETURNING <returning_list> [INTO <variables>]]

<column_list> ::= col_name [, col_name ...]

<override_opt> ::=
  OVERRIDING {USER | SYSTEM} VALUE

<value-source> ::= VALUES (<value-list>) | <query-expression>

<value-list> ::= <ins-value> [, <ins-value> ...]

<ins-value> :: = <value-expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value-expression>
  | [target.]col_name

<value-expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::= [:]varname [, [:]varname ...]

Table 1. Arguments for the `INSERT` Statement Parameters
Argument	Description
target	The name of the table or view to which a new row, or batch of rows, should be added
col_name	Name of a table or view column
value-expression	An expression whose value is used for inserting into the table or for returning
return_expression	The expression to be returned in the `RETURNING` clause
literal	A literal
context-variable	Context variable
varname	Name of a PSQL local variable

The INSERT statement is used to add rows to a table or to one or more tables underlying a view:

If the column values are supplied in a VALUES clause, exactly one row is inserted
The values may be provided instead by a SELECT expression, in which case zero to many rows may be inserted
With the DEFAULT VALUES clause, no values are provided at all and exactly one row is inserted.

Note	Restrictions Columns returned to the `NEW.column_name` context variables in DML triggers should not have a colon (“`:`”) prefixed to their names No column may appear more than once in the column list.

Important

ALERT : BEFORE INSERT Triggers

Regardless of the method used for inserting rows, be mindful of any columns in the target table or view that are populated by BEFORE INSERT triggers, such as primary keys and case-insensitive search columns.Those columns should be excluded from both the column_list and the VALUES list if, as they should, the triggers test the NEW.column_name for NULL.

`INSERT … VALUES`

The VALUES list must provide a value for every column in the column list, in the same order and of the correct type.The column list need not specify every column in the target but, if the column list is absent, the engine requires a value for every column in the table or view (computed columns excluded).

The expression DEFAULT allows a column to be specified in the column list, but instructs Firebird to use the default value (either NULL or the value specified in the DEFAULT clause of the column definition).For identity columns, specifying DEFAULT will generate the identity value.It is possible to include calculated columns in the column list and specifying DEFAULT as the column value.

Note	Introducer syntax provides a way to identify the character set of a value that is a string constant (literal).Introducer syntax works only with literal strings: it cannot be applied to string variables, parameters, column references or values that are expressions.

Examples

INSERT INTO cars (make, model, year)
VALUES ('Ford', 'T', 1908);

INSERT INTO cars
VALUES ('Ford', 'T', 1908, 'USA', 850);

-- notice the '_' prefix (introducer syntax)
INSERT INTO People
VALUES (_ISO8859_1 'Hans-Jörg Schäfer');

`INSERT … SELECT`

For this method of inserting, the output columns of the SELECT statement (or <query-expression>) must provide a value for every target column in the column list, in the same order and of the correct type.

Literal values, context variables or expressions of compatible type can be substituted for any column in the source row.In this case, a source column list and a corresponding VALUES list are required.

If the column list is absent — as it is when SELECT * is used for the source expression — the column_list must contain the names of every column in the target table or view (computed columns excluded).

Examples

INSERT INTO cars (make, model, year)
  SELECT make, model, year
  FROM new_cars;

INSERT INTO cars
  SELECT * FROM new_cars;

INSERT INTO Members (number, name)
  SELECT number, name FROM NewMembers
    WHERE Accepted = 1
UNION ALL
  SELECT number, name FROM SuspendedMembers
    WHERE Vindicated = 1

INSERT INTO numbers(num)
  WITH RECURSIVE r(n) as (
    SELECT 1 FROM rdb$database
    UNION ALL
    SELECT n+1 FROM r WHERE n < 100
  )
SELECT n FROM r

Of course, the column names in the source table need not be the same as those in the target table.Any type of SELECT statement is permitted, as long as its output columns exactly match the insert columns in number, order and type.Types need not be the same, but they must be assignment-compatible.

Note

Since Firebird 5.0, an INSERT … SELECT with a RETURNING clause produces zero or more rows, and the statement is described as type isc_info_sql_stmt_select.In other words, an INSERT … SELECT … RETURNING will no longer produce a “multiple rows in singleton select” error when the select produces multiple rows.

For the time being, a INSERT … VALUES (…) or INSERT … DEFAULT VALUES with a RETURNING clause is still described as isc_info_sql_stmt_exec_procedure.This behaviour may change in a future Firebird version.

`INSERT … DEFAULT VALUES`

The DEFAULT VALUES clause allows insertion of a record without providing any values at all, either directly or from a SELECT statement.This is only possible if every NOT NULL or CHECKed column in the table either has a valid default declared or gets such a value from a BEFORE INSERT trigger.Furthermore, triggers providing required field values must not depend on the presence of input values.

Specifying DEFAULT VALUES is equivalent to specifying a values list with expression DEFAULT for all columns.

Example

INSERT INTO journal
  DEFAULT VALUES
RETURNING entry_id;

`OVERRIDING`

The OVERRIDING clause controls the behaviour of an identity column for this statement only.

OVERRIDING SYSTEM VALUE: The user-provided value for the identity column is used, and no value is generated using the identity.In other words, for this insert, the identity will behave as if it is GENERATED BY DEFAULT.This option can only be specified for tables with a GENERATED ALWAYS identity column.

This can be useful when merging or importing data from another source.After such an insert, it may be necessary to change the next value of the identity sequence using ALTER TABLE to prevent subsequent inserts from generating colliding identity values.
OVERRIDING USER VALUE: The user-provided value for the identity column is ignored, and the column value is generated using the identity.In other words, for this insert, the identity will behave as if it is GENERATED ALWAYS, while allowing the identity column in the column-list.This option can be specified for both types of identity columns.

It is usually simpler to leave out the identity column to achieve the same effect.

Examples of OVERRIDING

-- for ALWAYS
-- value 11 is used anyway
insert into objects_always (id, name)
  OVERRIDING SYSTEM VALUE values (11, 'Laptop');

-- for both ALWAYS and BY DEFAULT
-- value 12 is not used
insert into objects_default (id, name)
  OVERRIDING USER VALUE values (12, 'Laptop');

The `RETURNING` Clause

An INSERT statement may optionally include a RETURNING clause to return values from the inserted rows.The clause, if present, need not contain all columns referenced in the insert statement and may also contain other columns or expressions.The returned values reflect any changes that may have been made in BEFORE INSERT triggers.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

The syntax of the returning_list is similar to the column list of a SELECT clause.It is possible to reference all columns using * or table_name.*.

The optional INTO sub-clause is only valid in PSQL.

Note	Caveats for updatable views The values reported by `RETURNING` for updatable views may be incorrect if the view is made updatable through triggers.See [fblangref50-ddl-view-updatable-returning].

Important

Multiple INSERTs

In DSQL, an INSERT … VALUES (…) RETURNING or INSERT … DEFAULT VALUES RETURNING returns only one row, and a INSERT … SELECT … RETURNING can return zero or more rows.

In PSQL, if the RETURNING clause is specified and more than one row is inserted by the INSERT statement, the statement fails and a “multiple rows in singleton select” error is returned.This behaviour may change in future Firebird versions.

Examples

INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES ('Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING lastname, fullname, id;

INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING *;

INSERT INTO Dumbbells (firstname, lastname, iq)
  SELECT fname, lname, iq
FROM Friends
  ORDER BY iq ROWS 1
  RETURNING id, firstname, iq
INTO :id, :fname, :iq;

Note

In DSQL, an INSERT … VALUES (…) RETURNING always returns exactly one row.This behaviour may change in a future Firebird version.
In DSQL, an INSERT … DEFAULT VALUES RETURNING always returns exactly one row.
In DSQL, an INSERT … SELECT … RETURNING returns zero or more rows.
In PSQL, if multiple rows are returned, the statement fails with a “multiple rows in singleton select” error.This behaviour may change in a future Firebird version.
In PSQL, if no row was inserted, nothing is returned, and the target variables keep their existing values.

Inserting into `BLOB` columns

Inserting into BLOB columns is only possible under the following circumstances:

The client application has made special provisions for such inserts, using the Firebird API.In this case, the modus operandi is application-specific and outside the scope of this manual.

The value inserted is a string literal of no more than 65,533 bytes (64KB - 3).

Note	A limit, in characters, is calculated at run-time for strings that are in multi-byte character sets, to avoid overrunning the bytes limit.For example, for a UTF8 string (max. 4 bytes/character), the run-time limit is likely to be about (floor(65533/4)) = 16383 characters.

You are using the “INSERT … SELECT” form and one or more columns in the result set are BLOBs.

`UPDATE`

Updates existing rows in tables and updatable views

Syntax

UPDATE target [[AS] alias]
  SET col_name = <upd_value> [, col_name = <upd_value> ...]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

<upd_value> ::= <value-expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value-expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value-expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::= [:]varname [, [:]varname ...]

Table 1. Arguments for the UPDATE Statement Parameters
Argument	Description
target	The name of the table or view where the records are updated
alias	Alias for the table or view
col_name	Name or alias of a column in the table or view
value-expression	Expression for the new value for a column that is to be updated in the table or view by the statement, or a value to be returned
search-conditions	A search condition limiting the set of the rows to be updated
cursorname	The name of the cursor through which the row(s) to be updated are positioned
plan_items	Clauses in the query plan
sort_items	Columns listed in an `ORDER BY` clause
m, n	Integer expressions for limiting the number of rows to be updated
return_expression	A value to be returned in the `RETURNING` clause
literal	A literal
context-variable	Context variable
varname	Name of a PSQL local variable

The UPDATE statement changes values in a table or in one or more of the tables that underlie a view.The columns affected are specified in the SET clause.The rows affected may be limited by the WHERE and ROWS clauses.If neither WHERE nor ROWS is present, all records in the table will be updated.

Using an alias

If you assign an alias to a table or a view, the alias must be used when specifying columns and also in any column references included in other clauses.

Example

Correct usage:

update Fruit set soort = 'pisang' where ...

update Fruit set Fruit.soort = 'pisang' where ...

update Fruit F set soort = 'pisang' where ...

update Fruit F set F.soort = 'pisang' where ...

Not possible:

update Fruit F set Fruit.soort = 'pisang' where ...

The `SET` Clause

In the SET clause, the assignment expressions, containing the columns with the values to be set, are separated by commas.In an assignment expression, column names are on the left and the values or expressions to assign are on the right.A column may be assigned only once in the SET clause.

A column name can be used in expressions on the right.The old value of the column will always be used in these right-side values, even if the column was already assigned a new value earlier in the SET clause.

Using the expression DEFAULT will set the column to its default value (either NULL or the value specified on the DEFAULT clause of the column definition).For an identity column, specifying DEFAULT will generate a new identity value.It is possible to “update” calculated columns in the SET clause if and only if the assigned value is DEFAULT.

Note	It is not possible to assign `DEFAULT` as a parameter value.

Here is an example

Data in the TSET table:

A B
---
1 0
2 0

The statement:

UPDATE tset SET a = 5, b = a;

will change the values to:

A B
---
5 1
5 2

Notice that the old values (1 and 2) are used to update the b column even after the column was assigned a new value (5).

The `WHERE` Clause

The WHERE clause sets the conditions that limit the set of records for a searched update.

In PSQL, if a named cursor is being used for updating a set, using the WHERE CURRENT OF clause, the action is limited to the row where the cursor is currently positioned.This is a positioned update.

Note	To be able to use the `WHERE CURRENT OF` clause in DSQL, the cursor name needs to be set on the statement handle before executing the statement.

Examples

UPDATE People
  SET firstname = 'Boris'
  WHERE lastname = 'Johnson';

UPDATE employee e
  SET salary = salary * 1.05
  WHERE EXISTS(
         SELECT *
           FROM employee_project ep
           WHERE e.emp_no = ep.emp_no);

UPDATE addresses
  SET city = 'Saint Petersburg', citycode = 'PET'
  WHERE city = 'Leningrad'

UPDATE employees
  SET salary = 2.5 * salary
  WHERE title = 'CEO'

For string literals with which the parser needs help to interpret the character set of the data, the introducer syntax may be used.The string literal is preceded by the character set name, prefixed with an underscore character:

-- notice the '_' prefix

UPDATE People
SET name = _ISO8859_1 'Hans-Jörg Schäfer'
WHERE id = 53662;

The `ORDER BY` and `ROWS` Clauses

The ORDER BY and ROWS clauses make sense only when used together.However, they can be used separately.

If ROWS has one argument, m, the rows to be updated will be limited to the first m rows.

Points to note

If m > the number of rows being processed, the entire set of rows is updated
If m = 0, no rows are updated
If m < 0, an error occurs and the update fails

If two arguments are used, m and n, ROWS limits the rows being updated to rows from m to n inclusively.Both arguments are integers and start from 1.

Points to note

If m > the number of rows being processed, no rows are updated
If n > the number of rows, rows from m to the end of the set are updated
If m < 1 or n < 1, an error occurs and the update fails
If n = m - 1, no rows are updated
If n < m -1, an error occurs and the update fails

ROWS Example

UPDATE employees
SET salary = salary + 50
ORDER BY salary ASC
ROWS 20;

The `SKIP LOCKED` Clause

When the SKIP LOCKED clause is specified, records locked by a different transaction are skipped by the statement and are not updated.

When a ROWS clause is specified, the “skip locked” check is performed after skipping the requested number of rows specified, and before counting the number of rows to update.

The `RETURNING` Clause

An UPDATE statement may include RETURNING to return some values from the updated rows.RETURNING may include data from any column of the row, not only the columns that are updated by the statement.It can include literals or expressions not associated with columns, if there is a need for that.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

When the RETURNING set contains data from the current row, the returned values report changes made in the BEFORE UPDATE triggers, but not those made in AFTER UPDATE triggers.

The context variables OLD.fieldname and NEW.fieldname can be used as column names.If OLD. or NEW. is not specified, or if the table name (target) is specified instead, the column values returned are the NEW. ones.

The syntax of the returning_list is similar to the column list of a SELECT clause.It is possible to reference all columns using *, or table_name.*, NEW.* and/or OLD.*.

In DSQL, a positioned update statement (WHERE CURRENT OF …) with RETURNING always returns a single row, a normal update statement can return zero or more rows.The update is executed to completion before rows are returned.In PSQL, attempts to execute an UPDATE … RETURNING that affects multiple rows will result in the error “multiple rows in singleton select”.This behaviour may change in a future Firebird version.

The `INTO` Sub-clause

In PSQL, the INTO clause can be used to pass the returning values to local variables.It is not available in DSQL.If no records are updated, nothing is returned and variables specified in RETURNING will keep their previous values.

RETURNING Example (DSQL)

UPDATE Scholars
SET firstname = 'Hugh', lastname = 'Pickering'
WHERE firstname = 'Henry' and lastname = 'Higgins'
RETURNING id, old.lastname, new.lastname;

Updating `BLOB` columns

Updating a BLOB column always replaces the entire contents.Even the BLOB ID, the “handle” that is stored directly in the column, is changed.BLOBs can be updated if:

The client application has made special provisions for this operation, using the Firebird API.In this case, the modus operandi is application-specific and outside the scope of this manual.

The new value is a string literal of no more than 65,533 bytes (64KB - 3).

Note	A limit, in characters, is calculated at run-time for strings that are in multi-byte character sets, to avoid overrunning the bytes limit.For example, for a UTF8 string (max. 4 bytes/character), the run-time limit is likely to be about (floor(65533/4)) = 16383 characters.

The source is itself a BLOB column or, more generally, an expression that returns a BLOB.
You use the INSERT CURSOR statement (ESQL only).

`UPDATE OR INSERT`

Updates existing rows in a table or updatable view, or — if it does not exist — inserts it

Syntax

UPDATE OR INSERT INTO
  target [(<column_list>)]
  [<override_opt>]
  VALUES (<value_list>)
  [MATCHING (<column_list>)]
  [ORDER BY <ordering-list>]
  [ROWS <m> [TO <n>]]
  [RETURNING <returning_list> [INTO <variables>]]

<column_list> ::= col_name  [, col_name ...]

<override_opt> ::=
  OVERRIDING {USER | SYSTEM} VALUE

<value_list> ::= <ins_value> [, <ins_value> ...]

<ins_value> ::= <value> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value-expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value-expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::= [:]varname [, [:]varname ...]

Table 1. Arguments for the `UPDATE OR INSERT` Statement Parameters
Argument	Description
target	The name of the table or view where the record(s) is to be updated or a new record inserted
col_name	Name of a column in the table or view
value-expression	An expression whose value is to be used for inserting or updating the table, or returning a value
return_expression	An expression returned in the RETURNING clause
varname	Variable name — PSQL only

UPDATE OR INSERT inserts a new record or updates one or more existing records.The action taken depends on the values provided for the columns in the MATCHING clause (or, if the latter is absent, in the primary key).If there are records found matching those values, they are updated.If not, a new record is inserted.A match only counts if all the columns in the MATCHING clause or primary key columns are equal.Matching is done with the IS NOT DISTINCT operator, so one NULL matches another.

Note

Restrictions

If the table has no primary key, the MATCHING clause is mandatory.
In the MATCHING list as well as in the update/insert column list, each column name may occur only once.
The “INTO <variables>” subclause is only available in PSQL.
When values are returned into the context variable NEW, this name must not be preceded by a colon (“:”).

The `ORDER BY` and `ROWS` Clauses

See [fblangref50-dml-update-orderbyclause] for UPDATE.

The `RETURNING` Clause

The optional RETURNING clause, if present, need not contain all the columns mentioned in the statement and may also contain other columns or expressions.The returned values reflect any changes that may have been made in BEFORE triggers, but not those in AFTER triggers.OLD.fieldname and NEW.fieldname may both be used in the list of columns to return;for field names not preceded by either of these, the new value is returned.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

The syntax of the returning_list is similar to the column list of a SELECT clause.It is possible to reference all columns using *, or table_name.*, NEW.* and/or OLD.*.

In DSQL, a statement with a RETURNING clause can return zero or more rows.The update or insert is executed to completion before rows are returned.In PSQL, if a RETURNING clause is present and more than one matching record is found, an error “multiple rows in singleton select” is raised.This behaviour may change in a future Firebird version.

The optional INTO sub-clause is only valid in PSQL.

Example of `UPDATE OR INSERT`

Modifying data in a table, using UPDATE OR INSERT in a PSQL module.The return value is passed to a local variable, whose colon prefix is optional.

UPDATE OR INSERT INTO Cows (Name, Number, Location)
  VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
  MATCHING (Number)
  RETURNING rec_id into :id;

UPDATE OR INSERT INTO Cows (Name, Number, Location)
  VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
  MATCHING (Number)
  RETURNING old.*, new.*;

`DELETE`

Deletes rows from a table or updatable view

Syntax

DELETE
  FROM target [[AS] alias]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value-expression>
  | [target.]col_name

<value-expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::=
  [:]varname [, [:]varname ...]

Table 1. Arguments for the `DELETE` Statement Parameters
Argument	Description
target	The name of the table or view from which the records are to be deleted
alias	Alias for the target table or view
search-conditions	Search condition limiting the set of rows being targeted for deletion
cursorname	The name of the cursor in which current record is positioned for deletion
plan_items	Query plan clause
sort_items	`ORDER BY` clause
m, n	Integer expressions for limiting the number of rows being deleted
return_expression	An expression to be returned in the `RETURNING` clause
value-expression	An expression whose value is used for returning
varname	Name of a PSQL variable

DELETE removes rows from a database table or from one or more of the tables that underlie a view.WHERE and ROWS clauses can limit the number of rows deleted.If neither WHERE nor ROWS is present, DELETE removes all the rows in the relation.

Aliases

If an alias is specified for the target table or view, it must be used to qualify all field name references in the DELETE statement.

Examples

Supported usage:

delete from Cities where name starting 'Alex';

delete from Cities where Cities.name starting 'Alex';

delete from Cities C where name starting 'Alex';

delete from Cities C where C.name starting 'Alex';

Not possible:

delete from Cities C where Cities.name starting 'Alex';

`WHERE`

The WHERE clause sets the conditions that limit the set of records for a searched delete.

In PSQL, if a named cursor is being used for deleting a set, using the WHERE CURRENT OF clause, the action is limited to the row where the cursor is currently positioned.This is a positioned delete.

Note	To be able to use the `WHERE CURRENT OF` clause in DSQL, the cursor name needs to be set on the statement handle before executing the statement.

Examples

DELETE FROM People
  WHERE firstname <> 'Boris' AND lastname <> 'Johnson';

DELETE FROM employee e
  WHERE NOT EXISTS(
    SELECT *
    FROM employee_project ep
     WHERE e.emp_no = ep.emp_no);

DELETE FROM Cities
  WHERE CURRENT OF Cur_Cities;  -- ESQL and PSQL only

`PLAN`

A PLAN clause allows the user to optimize the operation manually.

Example

DELETE FROM Submissions
  WHERE date_entered < '1-Jan-2002'
  PLAN (Submissions INDEX ix_subm_date);

`ORDER BY` and `ROWS`

The ORDER BY clause orders the set before the actual deletion takes place.It only makes sense in combination with ROWS, but is also valid without it.

The ROWS clause limits the number of rows being deleted.Integer literals or any integer expressions can be used for the arguments m and n.

If ROWS has one argument, m, the rows to be deleted will be limited to the first m rows.

Points to note

If m > the number of rows being processed, the entire set of rows is deleted
If m = 0, no rows are deleted
If m < 0, an error occurs and the deletion fails

If two arguments are used, m and n, ROWS limits the rows being deleted to rows from m to n inclusively.Both arguments are integers and start from 1.

Points to note

If m > the number of rows being processed, no rows are deleted
If m > 0 and <= the number of rows in the set and n is outside these values, rows from m to the end of the set are deleted
If m < 1 or n < 1, an error occurs and the deletion fails
If n = m - 1, no rows are deleted
If n < m -1, an error occurs and the deletion fails

Examples

Deleting the oldest purchase:

DELETE FROM Purchases
  ORDER BY date ROWS 1;

Deleting the highest custno(s):

DELETE FROM Sales
  ORDER BY custno DESC ROWS 1 to 10;

Deleting all sales, ORDER BY clause pointless:

DELETE FROM Sales
  ORDER BY custno DESC;

Deleting one record starting from the end, i.e. from Z…:

DELETE FROM popgroups
  ORDER BY name DESC ROWS 1;

Deleting the five oldest groups:

DELETE FROM popgroups
  ORDER BY formed ROWS 5;

No sorting (ORDER BY) is specified so 8 found records, starting from the fifth one, will be deleted:

DELETE FROM popgroups
  ROWS 5 TO 12;

`SKIP LOCKED`

When the SKIP LOCKED clause is specified, records locked by a different transaction are skipped by the statement and are not deleted.

When a ROWS clause is specified, the “skip locked” check is performed after skipping the requested number of rows specified, and before counting the number of rows to delete.

`RETURNING`

A DELETE statement may optionally include a RETURNING clause to return values from the deleted rows.The clause, if present, need not contain all the relation’s columns and may also contain other columns or expressions.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

The syntax of the returning_list is similar to the column list of a SELECT clause.It is possible to reference all columns using *, or table_name.*.

Note

In DSQL, a positioned delete statement (WHERE CURRENT OF …) with RETURNING always returns a singleton, never a multi-row set.If no records is deleted, the returned columns contain NULL.
A normal DELETE statement can return zero or more rows;the deletion is executed to completion before rows are returned.
In PSQL, if a RETURNING clause is present and more than one matching record is found, an error “multiple rows in singleton select” is raised.This behaviour may change in a future Firebird version.
The INTO clause is available only in PSQL
- If no row is deleted, nothing is returned and the target variables keep their values

Examples

DELETE FROM Scholars
  WHERE firstname = 'Henry' and lastname = 'Higgins'
  RETURNING lastname, fullname, id;

DELETE FROM Scholars
  WHERE firstname = 'Henry' and lastname = 'Higgins'
  RETURNING *;

DELETE FROM Dumbbells
  ORDER BY iq DESC
  ROWS 1
  RETURNING lastname, iq into :lname, :iq;

`MERGE`

Merges data from a source set into a target table or updatable view

Syntax

MERGE INTO target [[AS] target_alias]
  USING <table-reference>
  ON <join_condition>
  <merge_when> [<merge_when> ...]
  [PLAN <plan-expr>]
  [ORDER BY <ordering-list>]
  [RETURNING <returning_list> [INTO <variables>]]

<merge_when> ::=
    <merge_when_matched>
  | <merge_when_not_matched_target>
  | <merge_when_not_matched_source>

<merge_when_matched> ::=
  WHEN MATCHED [AND <condition>] THEN
  { UPDATE SET <assignment-list>
  | DELETE }

<merge_when_not_matched_target> ::=
  WHEN NOT MATCHED [BY TARGET] [AND <condition>] THEN
  INSERT [( <column_list> )] [<override_opt>]
  VALUES ( <value_list> )

<merge_when_not_matched_source> ::=
  WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
  { UPDATE SET <assignment-list>
  | DELETE }

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<assignment_list ::=
  col_name = <m_value> [, <col_name> = <m_value> ...]]

<override_opt> ::=
  OVERRIDING {USER | SYSTEM} VALUE

<column_list> ::= colname [, colname ...]

<value_list> ::= <m_value> [, <m_value> ...]

<m_value> ::= <value-expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value-expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value-expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::=
  [:]varname [, [:]varname ...]

Table 1. Arguments for the `MERGE` Statement Parameters
Argument	Description
target	Name of target relation (table or updatable view)
table-reference	Data source.It can be a table, a view, a stored procedure, a derived table or a parenthesized joined table
target_alias	Alias for the target relation (table or updatable view)
join_conditions	The (`ON`) condition(s) for matching the source records with those in the target
condition	Additional test condition in `WHEN MATCHED` or `WHEN NOT MATCHED` clause
col_name	Name of a column in the target relation
value-expression	The value assigned to a column in the target table.This expression may be a literal value, a PSQL variable, a column from the source, or a compatible context variable
return_expression	The expression to be returned in the `RETURNING` clauseCan be a column reference to source or target, or a column reference of the `NEW` or `OLD` context of the target, or a value.
ret_alias	Alias for the value expression in the `RETURNING` clause
varname	Name of a PSQL local variable

The MERGE statement merges records from a source <table-reference> into a target table or updatable view.The source may be a table, view or “anything you can SELECT from” in general.Each source record will be used to update one or more target records, insert a new record in the target table, delete a record from the target table or do nothing.

The action taken depends on the supplied join condition, the WHEN clause(s), and the — optional — condition in the WHEN clause.The join condition and condition in the WHEN will typically contain a comparison of fields in the source and target relations.

Multiple WHEN MATCHED and WHEN NOT MATCHED clauses are allowed.For each row in the source, the WHEN clauses are checked in the order they are specified in the statement.If the condition in the WHEN clause does not evaluate to true, the clause is skipped, and the next clause will be checked.This will be done until the condition for a WHEN clause evaluates to true, or a WHEN clauses without condition matches, or there are no more WHEN clauses.If a matching clause is found, the action associated with the clause is executed.For each row in the source, at most one action is executed.If the WHEN MATCHED clause is present, and several records match a single record in the target table, an error is raised.

Contrary to the other WHEN clauses, the WHEN NOT MATCHED BY SOURCE clauses evaluates records in the target which match no record in source.

Warning

At least one WHEN clause must be present.

WHEN NOT MATCHED is evaluated from the source viewpoint, that is, the table or set specified in USING.It has to work this way because if the source record does not match a target record, INSERT is executed.Of course, if there is a target record which does not match a source record, nothing is done.

Currently, in PSQL, the ROW_COUNT variable returns the value 1, even if more than one record is modified or inserted.For details and progress, refer to firebird#4722.

The `ORDER BY` Clause

The ORDER BY can be used to influence the order in which rows are evaluated.The primary use case is when combined with RETURNING, to influence the order rows are returned.

The `RETURNING` Clause

A MERGE statement can contain a RETURNING clause to return rows added, modified or removed.The merge is executed to completion before rows are returned.The RETURNING clause can contain any columns from the target table (or updatable view), as well as other columns (eg from the source) and expressions.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

In PSQL, If a RETURNING clause is present and more than one matching record is found, an error “multiple rows in singleton select” is raised.This behaviour may change in a future Firebird version.

The optional INTO sub-clause is only valid in PSQL.

Column names can be qualified by the OLD or NEW prefix to define exactly what value to return: before or after modification. The returned values include the changes made by BEFORE triggers.

The syntax of the returning_list is similar to the column list of a SELECT clause.It is possible to reference all columns using *, or table_name.*, NEW.* and/or OLD.*.

For the UPDATE or INSERT action, unqualified column names, or those qualified by the target table name or alias will behave as if qualified by NEW, while for the DELETE action as if qualified by OLD.

The following example modifies the previous example to affect one line, and adds a RETURNING clause to return the old and new quantity of goods, and the difference between those values.

Using MERGE with a RETURNING clause

MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
  JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
  AND SL.ID_PRODUCT =: ID_PRODUCT
  GROUP BY 1
) AS SRC (ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY
INTO : OLD_QUANTITY, :NEW_QUANTITY, :DIFF_QUANTITY

Examples of `MERGE`

Update books when present, or add new record if absent

MERGE INTO books b
  USING purchases p
  ON p.title = b.title and p.type = 'bk'
  WHEN MATCHED THEN
    UPDATE SET b.desc = b.desc || '; ' || p.desc
  WHEN NOT MATCHED THEN
    INSERT (title, desc, bought) values (p.title, p.desc, p.bought);

Using a derived table

MERGE INTO customers c
  USING (SELECT * from customers_delta WHERE id > 10) cd
  ON (c.id = cd.id)
  WHEN MATCHED THEN
    UPDATE SET name = cd.name
  WHEN NOT MATCHED THEN
    INSERT (id, name) values (cd.id, cd.name);

Together with a recursive CTE

MERGE INTO numbers
  USING (
    WITH RECURSIVE r(n) AS (
      SELECT 1 FROM rdb$database
      UNION ALL
      SELECT n+1 FROM r WHERE n < 200
    )
    SELECT n FROM r
  ) t
  ON numbers.num = t.n
  WHEN NOT MATCHED THEN
    INSERT(num) VALUES(t.n);

Using DELETE clause

MERGE INTO SALARY_HISTORY
USING (
  SELECT EMP_NO
  FROM EMPLOYEE
  WHERE DEPT_NO = 120) EMP
ON SALARY_HISTORY.EMP_NO = EMP.EMP_NO
WHEN MATCHED THEN DELETE

The following example updates the PRODUCT_INVENTORY table daily based on orders processed in the SALES_ORDER_LINE table.If the stock level of the product would drop to zero or lower, then the row for that product is removed from the PRODUCT_INVENTORY table.

MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM (SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
  JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
  GROUP BY 1
) AS SRC (ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE

`EXECUTE PROCEDURE`

Executes a stored procedure

Syntax

EXECUTE PROCEDURE procname
   [{ <inparam-list | ( <inparam-list> ) }]
   [RETURNING_VALUES { <outvar-list> | ( <outvar-list ) }]

<inparam-list> ::=
  <inparam> [, <inparam> ...]

<outvar-list> ::=
  <outvar> [, <outvar> ...]

<outvar> ::= [:]varname

Table 1. Arguments for the `EXECUTE PROCEDURE` Statement Parameters
Argument	Description
procname	Name of the stored procedure
inparam	An expression evaluating to the declared data type of an input parameter
varname	A PSQL variable to receive the return value

Executes an executable stored procedure, taking a list of one or more input parameters, if they are defined for the procedure, and returning a one-row set of output values, if they are defined for the procedure.

“Executable” Stored Procedure

The EXECUTE PROCEDURE statement is most commonly used to invoke “executable” stored procedures to perform some data-modifying task at the server side — those that do not contain any SUSPEND statements in their code.They can be designed to return a result set, consisting of only one row, which is usually passed, via a set of RETURNING_VALUES() variables, to another stored procedure that calls it.Client interfaces usually have an API wrapper that can retrieve the output values into a single-row buffer when calling EXECUTE PROCEDURE in DSQL.

Invoking “selectable” stored procedures is also possible with EXECUTE PROCEDURE, but it returns only the first row of an output set which is almost surely designed to be multi-row.Selectable stored procedures are designed to be invoked by a SELECT statement, producing output that behaves like a virtual table.

Note

In PSQL and DSQL, input parameters may be any expression that resolves to the expected type.
Although parentheses are not required after the name of the stored procedure to enclose the input parameters, their use is recommended for the sake of readability.
Where output parameters have been defined in a procedure, the RETURNING_VALUES clause can be used in PSQL to retrieve them into a list of previously declared variables that conforms in sequence, data type and number with the defined output parameters.
The list of RETURNING_VALUES may be optionally enclosed in parentheses and their use is recommended.
When DSQL applications call EXECUTE PROCEDURE using the Firebird API or some form of wrapper for it, a buffer is prepared to receive the output row and the RETURNING_VALUES clause is not used.

Examples of `EXECUTE PROCEDURE`

In PSQL, with optional colons and without optional parentheses:

EXECUTE PROCEDURE MakeFullName
  :FirstName, :MiddleName, :LastName
  RETURNING_VALUES :FullName;

In Firebird’s command-line utility isql, with literal parameters and optional parentheses:
```
EXECUTE PROCEDURE MakeFullName ('J', 'Edgar', 'Hoover');
```
Note

In DSQL (e.g. in isql), RETURNING_VALUES is not used.Any output values are captured by the application and displayed automatically.

A PSQL example with expression parameters and optional parentheses:

EXECUTE PROCEDURE MakeFullName
  ('Mr./Mrs. ' || FirstName, MiddleName, upper(LastName))
  RETURNING_VALUES (FullName);

`EXECUTE BLOCK`

Creates an “anonymous” block of PSQL code in DSQL for immediate execution

Available in

DSQL

Syntax

EXECUTE BLOCK [(<inparams>)]
  [RETURNS (<outparams>)]
  <psql-module-body>

<inparams> ::=  <param_decl> = ? [, <inparams> ]

<outparams> ::=  <param_decl> [, <outparams>]

<param_decl> ::=
  paramname <domain_or_non_array_type> [NOT NULL] [COLLATE collation]

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<psql-module-body> ::=
  !! See Syntax of a Module Body !!

Table 1. Arguments for the `EXECUTE BLOCK` Statement Parameters
Argument	Description
param_decl	Name and description of an input or output parameter
paramname	The name of an input or output parameter of the procedural block, up to 63 characters long.The name must be unique among input and output parameters and local variables in the block
collation	Collation

Executes a block of PSQL code as if it were a stored procedure, optionally with input and output parameters and variable declarations.This allows the user to perform “on-the-fly” PSQL within a DSQL context.

Examples

This example injects the numbers 0 through 127 and their corresponding ASCII characters into the table ASCIITABLE:

EXECUTE BLOCK
AS
declare i INT = 0;
BEGIN
  WHILE (i < 128) DO
  BEGIN
    INSERT INTO AsciiTable VALUES (:i, ascii_char(:i));
    i = i + 1;
  END
END

The next example calculates the geometric mean of two numbers and returns it to the user:
```
EXECUTE BLOCK (x DOUBLE PRECISION = ?, y DOUBLE PRECISION = ?)
RETURNS (gmean DOUBLE PRECISION)
AS
BEGIN
  gmean = SQRT(x*y);
  SUSPEND;
END
```
Because this block has input parameters, it has to be prepared first.Then the parameters can be set and the block executed.It depends on the client software how this must be done and even if it is possible at all — see the notes below.

Our last example takes two integer values, smallest and largest.For all the numbers in the range smallest…largest, the block outputs the number itself, its square, its cube and its fourth power.

EXECUTE BLOCK (smallest INT = ?, largest INT = ?)
RETURNS (number INT, square BIGINT, cube BIGINT, fourth BIGINT)
AS
BEGIN
  number = smallest;
  WHILE (number <= largest) DO
  BEGIN
    square = number * number;
    cube   = number * square;
    fourth = number * cube;
    SUSPEND;
    number = number + 1;
  END
END

Again, it depends on the client software if and how you can set the parameter values.

Input and output parameters

Executing a block without input parameters should be possible with every Firebird client that allows the user to enter their own DSQL statements.If there are input parameters, things get trickier: these parameters must get their values after the statement is prepared, but before it is executed.This requires special provisions, which not every client application offers.(Firebird’s own isql, for one, doesn’t.)

The server only accepts question marks (“?”) as placeholders for the input values, not “:a”, “:MyParam” etc., or literal values.Client software may support the “:xxx” form though, and will preprocess it before sending it to the server.

If the block has output parameters, you must use SUSPEND or nothing will be returned.

Output is always returned in the form of a result set, just as with a SELECT statement.You can’t use RETURNING_VALUES or execute the block INTO some variables, even if there is only one result row.

PSQL Links

For more information about writing PSQL, consult Chapter Procedural SQL (PSQL) Statements.

Statement Terminators

Some SQL statement editors — specifically the isql utility that comes with Firebird, and possibly some third-party editors — employ an internal convention that requires all statements to be terminated with a semicolon.This creates a conflict with PSQL syntax when coding in these environments.If you are unacquainted with this problem and its solution, please study the details in the PSQL chapter in the section entitled Switching the Terminator in isql.

Qualified Joins

Qualified joins

Explicit-condition joins

Named columns joins

Natural joins

Cross joins

Implicit Joins

Mixing Explicit and Implicit Joins

A Note on Equality

Ambiguous field names in joins

Joins with LATERAL Derived Tables

The WHERE clause

Examples

The GROUP BY clause

Examples

HAVING

Examples

The WINDOW Clause

Example Using Named Windows

The PLAN Clause

Simple Plans

Composite Plans

UNION

Examples

INSERT

INSERT …​ VALUES

INSERT …​ SELECT

INSERT …​ DEFAULT VALUES

OVERRIDING

The RETURNING Clause

Inserting into BLOB columns

UPDATE

Using an alias

Example

The SET Clause

The WHERE Clause

The ORDER BY and ROWS Clauses

The SKIP LOCKED Clause

The RETURNING Clause

The INTO Sub-clause

RETURNING Example (DSQL)

Updating BLOB columns

UPDATE OR INSERT

The ORDER BY and ROWS Clauses

The RETURNING Clause

Example of UPDATE OR INSERT

DELETE

Aliases

Examples

WHERE

Examples

PLAN

ORDER BY and ROWS

Examples

SKIP LOCKED

RETURNING

Examples

MERGE

The ORDER BY Clause

The RETURNING Clause

Examples of MERGE

EXECUTE PROCEDURE

“Executable” Stored Procedure

Examples of EXECUTE PROCEDURE

EXECUTE BLOCK

Examples

Input and output parameters

Statement Terminators

Joins with `LATERAL` Derived Tables

The `WHERE` clause

The `GROUP BY` clause

`HAVING`

The `WINDOW` Clause

The `PLAN` Clause

`UNION`

`INSERT`

`INSERT … VALUES`

`INSERT … SELECT`

`INSERT … DEFAULT VALUES`

`OVERRIDING`

The `RETURNING` Clause

Inserting into `BLOB` columns

`UPDATE`

The `SET` Clause

The `WHERE` Clause

The `ORDER BY` and `ROWS` Clauses

The `SKIP LOCKED` Clause

The `RETURNING` Clause

The `INTO` Sub-clause

Updating `BLOB` columns

`UPDATE OR INSERT`

The `ORDER BY` and `ROWS` Clauses

The `RETURNING` Clause

Example of `UPDATE OR INSERT`

`DELETE`

`WHERE`

`PLAN`

`ORDER BY` and `ROWS`

`SKIP LOCKED`

`RETURNING`

`MERGE`

The `ORDER BY` Clause

The `RETURNING` Clause

Examples of `MERGE`

`EXECUTE PROCEDURE`

Examples of `EXECUTE PROCEDURE`

`EXECUTE BLOCK`