FirebirdSQL logo

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');