UNION

CommentsProcedural SQL (PSQL) Statements

`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.